Databases & SQL worksheet with answers

What does a CROSS JOIN return?

1. AEvery combination of rows from the two tables
2. BOnly rows that do not match
3. COnly the rows that match on a shared key column
4. DThe rows of the first table only

Which aggregate function returns the average of a numeric column?

1. AAVG
2. BSUM
3. CMEAN
4. DMEDIAN

What does a CHECK constraint do?

1. ACounts the rows in a table
2. BVerifies the user's password
3. CChecks whether a table exists before a query is run on it
4. DRejects rows whose values do not satisfy a condition

What does EXCEPT (called MINUS in Oracle) return?

1. ARows that appear in both queries
2. BRows of the first query that do not appear in the second
3. CAll rows from both queries without duplicates
4. DRows of the second query that do not appear in the first one

Which query returns the highest salary in each department?

1. ASELECT dept, MAX(salary) FROM emp GROUP BY dept
2. BSELECT dept, salary FROM emp WHERE salary = MAX(salary)
3. CSELECT MAX(dept), salary FROM emp GROUP BY salary
4. DSELECT dept, MAX(salary) FROM emp ORDER BY dept

A LEFT JOIN returns:

1. AAll rows from the left table and matching rows from the right, with NULLs where there is no match
2. BAll rows from the right table only
3. COnly matching rows from both tables
4. DRows that match in neither table

Table b contains the values 1, 2 and NULL in column y. What does SELECT * FROM a WHERE x NOT IN (SELECT y FROM b) return?

1. ANo rows at all
2. BEvery row of a
3. CRows of a where x is not 1 or 2
4. DOnly rows of a where x is NULL

A table has a million rows and a Boolean column is_active that is TRUE for 95% of them. Why might the optimiser ignore an index on is_active for WHERE is_active = TRUE?

1. ATRUE is treated as NULL by the index
2. BThe optimiser never uses indexes in WHERE clauses
3. CMost rows match, so a full table scan is cheaper than many index lookups
4. DIndexes cannot be created on Boolean columns