Cmpt 354 - Sql

Front 1

What are two components of SQL?

Back 1

1. Data Manipulation Language (DML).

2. Data Definition Language (DDL).

Front 2

What does a DML do? (1)

Back 2

CRUD for records.

Front 3

What does a DDL do? (3)

Back 3

1. CRUD for table definitions.

2. Define integrity constraints.

3. Define views.

Front 4

What is the basic form of an SQL query?

Back 4

SELECT

FROM

WHERE

Front 5

What possible conditions are used in the WHERE clause?

Back 5

<, <=, =, <>, >=, >, AND, NOT, OR

Front 6

Does SQL remove duplicates by default? Why or why not?

Back 6

No. Removing duplicates entails sorting which can be expensive.

Front 7

What keyword denotes that duplicates are to be removed in SQL?

Back 7

DISTINCT

Front 8

What does the following query return?

SELECT DISTINCT firstName, lastName, income

FROM Customer

WHERE birthdate < '1950-09-27' AND income > 100000

Back 8

Unique entries for firstName, lastName, income given the constraints.

Front 9

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'

Back 9

Return the customerId, account type and account number where the balance of the accounts is greater than 80000 and reside in the Lonsdale branch.

Front 10

What does the follow query return?

SELECT *

FROM Customer

WHERE lastName = 'Summers'

Back 10

Return all customer information (columns) from the Customer table whose last name is 'Summers'.

Front 11

What is the purpose of a tuple variable?

Where is the tuple variable declared?

Back 11

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)

Front 12

Why use a tuple variable? (4)

Back 12

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.

Front 13

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

Back 13

Return all customers that have an income greater than a Customer 'Rupert Giles'.

Front 14

How do you rename a column name?

Back 14

SELECT accNumber, balance AS currentBalance

FROM Account

Front 15

How can you perform arithmetical operations during SELECT statements?

Back 15

SELECT balance * 1.05 AS yearEndBalance

FROM Account

Front 16

How you denote a string in SQL?

Back 16

firstName = 'Buffy'

Front 17

How do you denote single quotes in a string in SQL?

Back 17

author = 'O''Brian'

Front 18

How do you compare strings lexicographically?

Back 18

'fodder' < 'foo' is TRUE.

Front 19

For pattern matching with the LIKE clause, what are the % and _ symbols for? How can they be escaped?

Back 19

1. % stands for 0+ arbitrary characters.

2. _ stands for exactly 1 arbitrary character.

3. Both % and _ are escaped with \

eg. LIKE 'C:\\Program Files\\%'

Front 20

Does LIKE ignore whitespace?

Back 20

No. 'Buffy' = 'Buffy ' is FALSE.

Front 21

What does this query return?

SELECT customerID, firstName, lastName

FROM Customer

WHERE lastName LIKE 'Sm_t%'

Back 21

Returns the customerID, firstName, lastName of Customers lastName are like Sm_t%%%%%.

Front 22

What is the difference between LIKE and SIMILAR?

Back 22

SIMILAR offers more powerful pattern matching. Regular expressions can used.

Front 23

What is the format for DATE? TIME? TIMESTAMP?

Back 23

DATE: 2015-10-17

TIME: 17:30:29

TIMESTAMP: 2015-10-17 17:30:29

Front 24

Can DATE and TIME be compared?

Back 24

Yes, using the comparison operators.

Front 25

What are the two operators to test for null values? What is returned for other comparisons with a null value?

Back 25

IS NULL

IS NOT NULL

UNKNOWN

Front 26

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

Back 26

1. true

2. false

3. unknown

4. unknown

5. unknown

6. unknown

7. unknown

Front 27

How do you order output? How do you define whether it's ascending or descending order? What is the default?

Back 27

SELECT lastName, firstName, income

FROM Customer

ORDER BY lastName, firstName (ASC, DESC)

The default is ASC.

Front 28

What three set operations does SQL support? What is the caveat?

Back 28

1. UNION

2. INTERSECT

3. EXCEPT

These must be performed on union-compatible tables.

Front 29

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')

Back 29

Returns the customerIDs and first name of customers who have accounts in either the Robson or Lonsdale branches.

Front 30

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'

Back 30

Returns the customerIDs of people who own an account at the Lonsdale branch but do not have an account at the Robson branch.

Front 31

What do UNION, INTERSECT and EXCEPT do differently than other SQL queries?

Back 31

Remove duplicates by default.

Front 32

What are the four main types of joins in SQL?

Back 32

1. INNER JOIN

2. LEFT OUTER JOIN

3. RIGHT OUTER JOIN

4. FULL OUTER JOIN

Front 33

Define the INNER JOIN.

Back 33

Only includes records where attributes from both tables meet the join condition.

Front 34

Define the LEFT OUTER JOIN.

Back 34

Includes records from the LEFT TABLE that do not meet the join condition.

Front 35

Define the RIGHT OUTER JOIN.

Back 35

Includes records from the RIGHT TABLE that do not meet the join condition.

Front 36

Define the FULL OUTER JOIN.

Back 36

Includes records from BOTH TABLES that do not meet the join condition.

Front 37

What happens in OUTER JOINS for attributes of records in only one of the tables?

Back 37

Padded with NULL values.

Front 38

Define the NATURAL JOIN.

Back 38

Includes the records that have equality on all attributes in common.

Front 39

Define the USING(A1, ... , An).

Back 39

Includes the records that have equality on all specified attributes.

Front 40

Define the ON(condition).

Back 40

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.

Front 41

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

Back 41

Return the sin, salary of all employees. If they are also Customer, return their income.

Front 42

What is preferred? LEFT or RIGHT OUT JOIN?

Back 42

Left so that nulls appear on the right hand side of the result.

Front 43

What is a nested query?

Back 43

A query that contains an embedded query.

Front 44

Where can sub-queries appear?

Back 44

1. FROM clause

2. WHERE clause

3. HAVING clause

Front 45

It is known that sub-queries in a WHERE clause often are used in additional set operations. Name the seven operations.

Back 45

1. IN

2. NOT IN

3. EXISTS

4. NOT EXISTS

5. UNIQUE

6. ANY

7. ALL

Front 46

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')

Back 46

The customerID, birth date and income of Customers with an account at the Lonsdale branch.

Front 47

What is an uncorrelated query?

What is unique about them?

Back 47

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.

Front 48

What does EXISTS or NOT EXISTS test?

Back 48

Whether the associated query is non-empty or empty.

Front 49

What is a correlated query?

What is an issue with them?

Back 49

Sub-query has references to the outer query.

Often inefficient.

Front 50

How can you accomplish division in SQL?

Back 50

Using NOT EXISTS or EXCEPT.

Front 51

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))

Back 51

...

Front 52

What does the UNIQUE operator do? NOT UNIQUE?

Back 52

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.

Front 53

SELECT C.customerID, C.firstName, C.lastName

FROM Customer C

WHERE UNIQUE

(SELECT O.CustomerID

FROM Owns O

WHERE C.customerID = O.customerID)

Back 53

Returns the customerID, first name and last name of customers who only have one account.

Front 54

1. IN is equivalent to ___

2. NOT IN is equivalent to ___

Back 54

1. = ANY

2. <> ALL

Front 55

SELECT C.customerID, C.firstName, C.lastName

FROM Customer C

WHERE C.income > ANY

(SELECT Bruce.income

FROM Customer Bruce

WHERE Bruce.firstName = 'Bruce')

Back 55

Returns the customerID, first name and last name of customers who have an income better than any customer named Bruce.

Front 56

SELECT C.customerID, C.firstName, C.lastName FROM Customer C

WHERE C.income > ALL

(SELECT Bruce.income

FROM Customer Bruce

WHERE Bruce.firstName = 'Bruce')

Back 56

Returns the customerID, first name and last name of customers who have an income better than all customers named Bruce.

Front 57

Name five aggregate operators and briefly describe them. What can be used with caution on them?

Back 57

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.

Front 58

SELECT AVG(income) AS average_income

FROM Customer

Back 58

Returns a column named average_income of all customers.

Front 59

SELECT COUNT(DISTINCT firstName) AS smith_names

FROM Customer

WHERE lastName = 'Smith' OR lastName = 'smith'

Back 59

Returns the number of different first names for customers whose last name is smith/Smith.

Front 60

What is wrong with this query?

SELECT customerID, MIN(income)

FROM Customer

Back 60

There may be two people with the same minimum income.

Front 61

SELECT C1.customerID, C1.income

FROM Customer C1

WHERE C1.income =

(SELECT MIN(C2.income)

FROM Customer C2)

Back 61

Returns the customerID and income of customers with the minimum income.

Front 62

SELECT branchName, COUNT(accNumber) AS num_acc

FROM Account

GROUP BY branchName

Back 62

Returns the number of accounts held at each branch.

Front 63

What does the HAVING clause do?

Back 63

A condition applied to each group rather than to each row.

Front 64

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

Back 64

Returns the branch name and number of accounts from branches with a budget > 500000 and total account balances greater than 1000000.

Front 65

What metric should be used for analysing query performance?

Back 65

The number of disk reads and writes because reading a black from disk is much slower than performing main memory operations.