Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

CMPUT 291 > Lecture 6- SQL Basics > Flashcards

Lecture 6- SQL Basics Flashcards

(47 cards)

Start Studying
1
Q

What are some features of SQL?

A
  • Declarative = specify the properties that should hold in the result, not how to obtain the result
  • Complex queries have procedural elements
  • Set/Bag semantics
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is part of the data definition component?

A
  • CREATE TABLE
  • DROP TABLE
  • ALTER TABLE (ADD, MODIFY, ADD constraint)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is part of the data update component?

A
  • INSERT INTO
  • DELETE FROM
  • UPDATE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is SQL?

A

A query language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is this query doing?

branch (bname, address, city, assets)

A

Find the names of all branches with assets greater than $2,500,000

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is this query doing?

branch (bname, address, city, assets)

A

Find the names of all branches in Edmonton with assets greater than $2,500,000

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is this query of two relations doing?

customer (cname, street, city)
deposit (accno, cname, bname, balance)

A

List the name and city of every customer who has an account with balance over $2,000

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Write the query for this “find customers who have both loans and deposits” and what is the type of query?

loan (accno, cname, bname, amount)
deposit (accno, cname, bname, balance)

A

Queries with tuple variables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the evaluation strategy for this algorithm?

SELECT …
FROM R1 r1, R2 r2, …
WHERE C

A
  • FROM clause produces cartesian product of listed tables
  • WHERE clause produces table containing only rows satisfying condition
  • SELECT clause retains listed columns
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

This is equivalent to what?

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

To get rid of duplicates we need to?

A

Use DISTINCT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Duplication elimination is not done ________ and must be _______

A
  • Automatically
  • Explicitly requested
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Describe queries working with strings

A
  • Equality and comparison operators apply to strings (based on lexical ordering) = Ex. WHERE cname < ‘P’
  • Concatenate operator applies to strings = Ex. WHERE bname ||’–’|| address = …
  • Expressions can also be used in SELECT clause = Ex. SELECT bname ||’–’|| address AS NameAdd FROM branch
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is partial matching?

A

Allows you to search for rows where a string column contains a certain pattern or portion of a string

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Describe the LIKE operator

A
  • Used to filter data based on pattern matching
  • Patterns can be written using wildcard characters
  • % = matches any sequence of characters (including zero characters)
  • Underscore = matches exactly one character
  • g _ % = matches values that start with letter g
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

This is an example of what?

A

Partial matching

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

These are examples of what?

A

Partial matching

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is this query looking for? (partial matching)

customer (cname, street, city)

A

Find every customer whose address starts with Computing Science

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is this query doing?

deposit (accno, cname, bname, balance)

Study These Flashcards
A
  • For every deposit holder who has over $1,000 find the customer name and the balance over $1,000
  • Naming the result
20
Q

What is this query doing?

branch (bname, address, city, assets)

Study These Flashcards
A
  • Find the names and assets of all branches with assets greater than $2,500,000 and order the results in ascending order of asset values
21
Q

What is the default order when ordering the results? And how to chose the opposit?

Study These Flashcards
A
  • Ascending order is default
  • To get descending order, use DESC keyword
22
Q

Describe the three different set operators

Study These Flashcards
A
  1. Set union = Q1 UNION Q2 (set of tuples in Q1 or Q2)
  2. Set difference = Q1 EXCEPT Q2 (The set of tuples in Q1 but not in Q2)
  3. Set intersection = Q1 INTERSECT Q2 (the set of tuples in both Q1 and Q2)
23
Q

Q1 and Q2 must be ________ (same number/ types of attributes)

Study These Flashcards
A

Union compatible

24
Q

What is this query doing?

Study These Flashcards
A

List deposit holders who have no loans (set difference)

25
What is this query doing?
List cities where there is either a customer or a branch (set union)
26
What is this query doing?
Find all cities that have both customers and branches (set intersection)
27
Describe the conceptual evaluation strategy
* Compute the cross product of the tables in the from-list * Delete rows in the cross product that fail the qualification conditions * Delete all columns that do not appear in the select-list * If DISTINCT is specific, eliminate duplicate rows
28
What does this query do?
* Find the name and asset of every branch that has a deposit account holders who lives in Jasper * The query first selects customers who live in the city of Jasper * Then it looks for deposits made by those customers * It then finds the branch where these deposits were made * Finally, it selects the branch name and assets of the branches
29
What type of query is this?
Queries within the WHERE clause of an outer query (nested queries)
30
Should you avoid nesting in queries?
Yes
31
What is a subquery?
* A subquery is a query within a query * Its an inner query placed within an outer query * The inner query is executed first, afterwards the results is passed to the outer query
32
What does this query do?
* The outer query extracts details of all employees whose annual salaries are greater than the specified value * The subquery must identify the annual salary of the assistant chef * When executed, the subquery provides a subset of data from the employees relation * This subset of data is then used as input for the outer query
33
What are the different types of nested operators?
* IN * ALL (=, >, <, <=, >=) * ANY (=, >, <, <=, >=) * SOME (=, >, <, <=, >=) * EXISTS
34
What is IN used for?
Used to specify multiple possible values for a column
35
What does this query do?
Will filter out all the student records that have a value of USA and UK
36
What does this query do? And an alternative to nesting?
* The query retrieves the names of customers who have deposited money in the same branch or branches where John Doe has made a deposit * The subquery retrives the branch names (bname) where John Doe has made deposits * It returns a list of all the branch names where John Doe has an account or deposit * The outer query uses this list of branches from the subquery to find all the customers (cname) who have also deposited money in any of these branches
37
What does ALL, SOME and ANY evaluate to?
* ALL evaluates to true iff the comparison evaluates to tru form every value in the set * SOME evaluates to true iff the comparison evaluates to true for at least one value in the set * ANY evaluates to true iff the comparison evaluates true for any value in the set
38
What type of query is this?
All subquery
39
What does this query do? branch (*bname*, address, city, assets)
Find branches that have assets greater than the assets of all branches in Calgary
40
What does this query do?
* Identifies employees earnings annual salary that's less than or equal to the annual salaries earned by all employees in the following roles - Manager, Head chef, Assistant manager, Head waiter * Outer query is to identify all employees who earn an annual salary that is less than or equal to the specified values * A subquery that extracts the data of annual salary earned by employees who are in the specified roles
41
What type of query is this? And describe what it does
* EXISTS nested query * EXISTS (SELECT ...) evaluates to true iff the results of the subquery contains at least one row * The expression is evaluated for every iteration of the outer query * EXISTS implies its non-empty * NOT EXISTS implies it is empty
42
What is this query doing? branch (*bname*, address, city, assets) customer (*cname*, street, city)
Find the names of customers who live in a city with no bank branches
43
What is division in queries?
Find the subset of items in one set that are related to all items in another set
44
What is this query doing?
* Division * Find customer who have deposit accounts in all branches * Find set A of all branches in which a particular customer c has a deposit account * Find set B of all branches * Output c if A ⊇ B or quivalently if B-A is empty * Relation A contains relation B = NOT EXISTS (B EXCEPT A)
45
What does this query do?
* Division * Find professors who have taught courses in all departments * Find set A of all departments in which a particular professor p has taught a course * Find set B of all departments * Output p if A ⊇ B or equivalently is B-A is empty * Relation A contains relation B = NOT EXISTS (B EXCEPT A)
46
SQLite vs SQL-92
* Does not support op ALL in nested queries * Does not support RIGHT OUTER JOIN and FULL OUTER JOIN * Views are read only * ALTER TABLE command is very limited * Foreign key constraints are not enforced by default * More left to be explored
47
What are the basic data types in SQLite?
* INTEGER * REAL * TEXT * BLOB * NULL * NUMERIC

CMPUT 291 (15 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions