Databases & SQL worksheet with answers

How does UNION ALL differ from UNION?

1. AUNION ALL sorts the result
2. BUNION ALL keeps duplicate rows
3. CUNION ALL removes duplicate rows
4. DUNION ALL only works on one table

Which statement adds a new column to an existing table?

1. AALTER TABLE
2. BINSERT COLUMN
3. CCREATE COLUMN
4. DUPDATE TABLE

What is a primary key?

1. AA column that may hold duplicate values but must never contain NULL
2. BA password for the database
3. CA column or set of columns that uniquely identifies each row
4. DThe first column of every table

Table a has 3 rows and table b has 4 rows. How many rows does SELECT * FROM a CROSS JOIN b return?

1. A7
2. B12
3. C4
4. D3

Why does SELECT price * 2 AS double_price FROM t WHERE double_price > 10 fail in most databases?

1. AWHERE can only compare text columns
2. BAliases may not contain an underscore
3. CWHERE is evaluated before the SELECT list, so the alias does not exist yet
4. DArithmetic is not allowed in the SELECT list

What does GRANT SELECT ON employees TO alice do?

1. AMakes alice the owner of the database
2. BCopies the employees table to alice
3. CAllows the user alice to read the employees table
4. DAllows alice to update and delete rows in the employees table

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 total of all amounts on every row
3. CThe average amount per day
4. DA running total of amount ordered by day

For an equality join between two large tables with no useful indexes, which join algorithm does an optimiser often choose?

1. ACross join
2. BBitmap join on the primary key
3. CHash join
4. DNested loop join with index lookups