Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
65 Cards in this Set
- Front
- Back
What are two components of SQL? |
1. Data Manipulation Language (DML). 2. Data Definition Language (DDL). |
|
What does a DML do? (1) |
CRUD for records.
|
|
What does a DDL do? (3) |
1. CRUD for table definitions. 2. Define integrity constraints. 3. Define views. |
|
What is the basic form of an SQL query? |
SELECT FROM WHERE |
|
What possible conditions are used in the WHERE clause? |
<, <=, =, <>, >=, >, AND, NOT, OR |
|
Does SQL remove duplicates by default? Why or why not? |
No. Removing duplicates entails sorting which can be expensive. |
|
What keyword denotes that duplicates are to be removed in SQL? |
DISTINCT |
|
What does the following query return? SELECT DISTINCT firstName, lastName, income FROM Customer WHERE birthdate < '1950-09-27' AND income > 100000 |
Unique entries for firstName, lastName, income given the constraints. |
|
What does the following query return? SELECT O.customerID, A.type, A.accNumber, A.balance FROM Owns O, Account A WHERE A.accNumber = O.accNumber AND A.balance > 80000 AND A.branchName = 'Lonsdale' |
Return the customerId, account type and account number where the balance of the accounts is greater than 80000 and reside in the Lonsdale branch. |
|
What does the follow query return? SELECT * FROM Customer WHERE lastName = 'Summers' |
Return all customer information (columns) from the Customer table whose last name is 'Summers'. |
|
What is the purpose of a tuple variable? Where is the tuple variable declared? |
To allow tables to be referred to by an alias in a query. In the FROM clause by writing it immediately after the table it refers to. (eg. Owns O) |
|
Why use a tuple variable? (4) |
1. Distinguishing between columns with the same name but from different tables. 2. Readability. 3. Laziness (C.date vs Customer.date). 4. Easier to re-use queries. |
|
What does the following query return? SELECT C.customerID, C.firstName, C.lastName FROM Customer C, Customer RG WHERE RG.firstName = 'Rupert' AND RG.lastName = 'Giles' AND C.income > RG.income |
Return all customers that have an income greater than a Customer 'Rupert Giles'. |
|
How do you rename a column name? |
SELECT accNumber, balance AS currentBalance FROM Account |
|
How can you perform arithmetical operations during SELECT statements? |
SELECT balance * 1.05 AS yearEndBalance FROM Account |
|
How you denote a string in SQL? |
firstName = 'Buffy' |
|
How do you denote single quotes in a string in SQL? |
author = 'O''Brian' |
|
How do you compare strings lexicographically? |
'fodder' < 'foo' is TRUE. |
|
For pattern matching with the LIKE clause, what are the % and _ symbols for? How can they be escaped? |
1. % stands for 0+ arbitrary characters. 2. _ stands for exactly 1 arbitrary character. 3. Both % and _ are escaped with \ eg. LIKE 'C:\\Program Files\\%' |
|
Does LIKE ignore whitespace? |
No. 'Buffy' = 'Buffy ' is FALSE. |
|
What does this query return? SELECT customerID, firstName, lastName FROM Customer WHERE lastName LIKE 'Sm_t%' |
Returns the customerID, firstName, lastName of Customers lastName are like Sm_t%%%%%. |
|
What is the difference between LIKE and SIMILAR? |
SIMILAR offers more powerful pattern matching. Regular expressions can used. |
|
What is the format for DATE? TIME? TIMESTAMP? |
DATE: 2015-10-17 TIME: 17:30:29 TIMESTAMP: 2015-10-17 17:30:29 |
|
Can DATE and TIME be compared? |
Yes, using the comparison operators. |
|
What are the two operators to test for null values? What is returned for other comparisons with a null value? |
IS NULL IS NOT NULL UNKNOWN |
|
1. true OR unknown 2. false OR unknown 3. unknown OR unknown 4. true AND unknown 5. false AND unknown 6. unknown AND unknown 7. NOT unknown |
1. true 2. false 3. unknown 4. unknown 5. unknown 6. unknown 7. unknown |
|
How do you order output? How do you define whether it's ascending or descending order? What is the default? |
SELECT lastName, firstName, income FROM Customer ORDER BY lastName, firstName (ASC, DESC) The default is ASC. |
|
What three set operations does SQL support? What is the caveat? |
1. UNION 2. INTERSECT 3. EXCEPT These must be performed on union-compatible tables. |
|
What does the following query return? SELECT C.customerID, C.firstName FROM Customer C, Owns O, Account A WHERE C.customerID = O.customerID AND A.accNumber = O.accNumber AND (A.branchName = 'Lonsdale' OR A.branchName = 'Robson') |
Returns the customerIDs and first name of customers who have accounts in either the Robson or Lonsdale branches. |
|
SELECT O1.customerID FROM Owns O1, Account A1 WHERE A1.accNumber = O1.accNumber AND A1.branchName = 'Lonsdale' EXCEPT SELECT O2.customerID FROM Owns O2, Account A2 WHERE ... AND A2.branchName = 'Robson' |
Returns the customerIDs of people who own an account at the Lonsdale branch but do not have an account at the Robson branch. |
|
What do UNION, INTERSECT and EXCEPT do differently than other SQL queries? |
Remove duplicates by default. |
|
What are the four main types of joins in SQL? |
1. INNER JOIN
2. LEFT OUTER JOIN 3. RIGHT OUTER JOIN 4. FULL OUTER JOIN |
|
Define the INNER JOIN. |
Only includes records where attributes from both tables meet the join condition. |
|
Define the LEFT OUTER JOIN. |
Includes records from the LEFT TABLE that do not meet the join condition. |
|
Define the RIGHT OUTER JOIN. |
Includes records from the RIGHT TABLE that do not meet the join condition. |
|
Define the FULL OUTER JOIN. |
Includes records from BOTH TABLES that do not meet the join condition. |
|
What happens in OUTER JOINS for attributes of records in only one of the tables? |
Padded with NULL values. |
|
Define the NATURAL JOIN. |
Includes the records that have equality on all attributes in common. |
|
Define the USING(A1, ... , An). |
Includes the records that have equality on all specified attributes. |
|
Define the ON(condition). |
Join conditions can be applied to both outer and inner joins. Outer joins MUST be specified for an outer join. If not condition is specified for an inner join, the Cartesian product is returned. |
|
What does the following query return? SELECT E.sin, E.salary, C.income FROM Employee E LEFT OUTER JOIN Customer C ON E.sin = C.customerID |
Return the sin, salary of all employees. If they are also Customer, return their income. |
|
What is preferred? LEFT or RIGHT OUT JOIN? |
Left so that nulls appear on the right hand side of the result. |
|
What is a nested query? |
A query that contains an embedded query. |
|
Where can sub-queries appear? |
1. FROM clause 2. WHERE clause 3. HAVING clause |
|
It is known that sub-queries in a WHERE clause often are used in additional set operations. Name the seven operations. |
1. IN 2. NOT IN 3. EXISTS 4. NOT EXISTS 5. UNIQUE 6. ANY 7. ALL |
|
What does the following query return? SELECT C.customerID, C.birthDate, C.income FROM Customer C WHERE C.customerID IN (SELECT O.customerID FROM Account A, Owns A WHERE A.accNumber = O.accNumber AND A.branchName = 'Lonsdale') |
The customerID, birth date and income of Customers with an account at the Lonsdale branch. |
|
What is an uncorrelated query? What is unique about them? |
The sub-query that does not contain references to attributes of the outer query. They can be evaluated (once) before the evaluation of the outer query. |
|
What does EXISTS or NOT EXISTS test? |
Whether the associated query is non-empty or empty. |
|
What is a correlated query? What is an issue with them? |
Sub-query has references to the outer query.
Often inefficient. |
|
How can you accomplish division in SQL? |
Using NOT EXISTS or EXCEPT. |
|
SELECT C.customerID, C.firstName, C.lastName FROM Customer C WHERE NOT EXISTS ((SELECT B.branchName FROM Branch B) EXCEPT (should be on next line) (SELECT A.branchName, FROM Account A, Owns O (next line) WHERE O.customerID = C.customerID AND O.accNumber = A.accNumber)) |
... |
|
What does the UNIQUE operator do? NOT UNIQUE? |
UNIQUE returns TRUE is no row appears twice in the answer to a query. NOT UNIQUE tests to see if there are at least two identical rows in the sub-query. |
|
SELECT C.customerID, C.firstName, C.lastName FROM Customer C WHERE UNIQUE (SELECT O.CustomerID FROM Owns O WHERE C.customerID = O.customerID) |
Returns the customerID, first name and last name of customers who only have one account. |
|
1. IN is equivalent to ___ 2. NOT IN is equivalent to ___ |
1. = ANY 2. <> ALL |
|
SELECT C.customerID, C.firstName, C.lastName FROM Customer C WHERE C.income > ANY (SELECT Bruce.income FROM Customer Bruce WHERE Bruce.firstName = 'Bruce') |
Returns the customerID, first name and last name of customers who have an income better than any customer named Bruce. |
|
SELECT C.customerID, C.firstName, C.lastName FROM Customer C WHERE C.income > ALL (SELECT Bruce.income FROM Customer Bruce WHERE Bruce.firstName = 'Bruce') |
Returns the customerID, first name and last name of customers who have an income better than all customers named Bruce. |
|
Name five aggregate operators and briefly describe them. What can be used with caution on them? |
1. COUNT - the number of values in a col. 2. SUM - the sum of the values in a col. 3. AVG - the average of the values in a col. 4. MAX - the maximum value in a col. 5. MIN - the minimum value in a col. DISTINCT. |
|
SELECT AVG(income) AS average_income FROM Customer |
Returns a column named average_income of all customers. |
|
SELECT COUNT(DISTINCT firstName) AS smith_names FROM Customer WHERE lastName = 'Smith' OR lastName = 'smith' |
Returns the number of different first names for customers whose last name is smith/Smith. |
|
What is wrong with this query? SELECT customerID, MIN(income) FROM Customer |
There may be two people with the same minimum income. |
|
SELECT C1.customerID, C1.income FROM Customer C1 WHERE C1.income = (SELECT MIN(C2.income) FROM Customer C2) |
Returns the customerID and income of customers with the minimum income. |
|
SELECT branchName, COUNT(accNumber) AS num_acc FROM Account GROUP BY branchName |
Returns the number of accounts held at each branch. |
|
What does the HAVING clause do? |
A condition applied to each group rather than to each row. |
|
SELECT B.branchName, COUNT (A.accNumber) AS accs FROM Account A, Branch B WHERE A.branchName= B.brNname ANDB.budget> 500000 GROUP BY B.branchName HAVING SUM (A.balance) > 1000000 |
Returns the branch name and number of accounts from branches with a budget > 500000 and total account balances greater than 1000000. |
|
What metric should be used for analysing query performance? |
The number of disk reads and writes because reading a black from disk is much slower than performing main memory operations. |