Databases & SQL worksheet with answers

What does UNION do with the results of two SELECT queries?

1. ACombines them into one result and removes duplicate rows
2. BJoins them side by side on a key
3. CKeeps only rows that appear in both
4. DKeeps only rows that appear in the first query but not the second

Which statement adds a new column to an existing table?

1. AALTER TABLE
2. BINSERT COLUMN
3. CUPDATE TABLE
4. DCREATE COLUMN

Which SQL statement retrieves data from a table?

1. AUPDATE
2. BINSERT
3. CCREATE
4. DSELECT

Which query returns the number of orders per customer?

1. ASELECT DISTINCT COUNT(*) FROM orders
2. BSELECT customer_id, COUNT(*) FROM orders ORDER BY customer_id
3. CSELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id
4. DSELECT customer_id FROM orders WHERE COUNT(*) > 0

A table has rows where col is NULL. Which rows does WHERE col = NULL return?

1. AAll rows of the table
2. BAn error in every database
3. COnly the rows where col is NULL, as with IS NULL
4. DNo rows, because the comparison is unknown

Which query returns the highest salary in each department?

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

Scores are 90, 90 and 80. Which values does RANK() OVER (ORDER BY score DESC) give?

1. A2, 2, 3
2. B1, 1, 3
3. C1, 1, 2
4. D1, 2, 3

What does SELECT day, SUM(amount) OVER (ORDER BY day) FROM sales compute?

1. AOne row per day with that day's total only
2. BThe average amount per day
3. CThe total of all amounts on every row
4. DA running total of amount ordered by day