Databases & SQL worksheet with answers

An INNER JOIN returns:

1. AOnly rows with matching values in both tables
2. BAll rows from the left table, with NULLs where there is no match
3. CRows that do not match
4. DAll rows from both tables

Which statement removes rows from a table?

1. AREMOVE
2. BTRUNCATE COLUMN
3. CDROP
4. DDELETE

Which rows does WHERE price BETWEEN 10 AND 20 select?

1. ARows with price outside the range 10 to 20
2. BRows with price from 10 to 20, including 10 and 20
3. CRows with price equal to 10 or 20 only
4. DRows with price above 10 and below 20, excluding 10 and 20

Which query returns the number of different cities in the customers table?

1. ASELECT COUNT(DISTINCT city) FROM customers
2. BSELECT SUM(DISTINCT city) FROM customers
3. CSELECT COUNT(city) FROM customers GROUP BY city
4. DSELECT DISTINCT COUNT(city) FROM customers

Which query returns the number of orders per customer?

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

Why can a table have only one clustered index?

1. AThe rows can be stored physically in only one order
2. BClustered indexes can only be on the primary key
3. CClustered indexes use too much memory
4. DThe SQL standard forbids more than one index on a 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. CA running total of amount ordered by day
4. DThe average amount per day

What is a known drawback of decomposing tables into BCNF compared with 3NF?

1. ASome functional dependencies may no longer be checkable within a single table
2. BThe decomposition always loses data when joined back
3. CBCNF allows more redundancy than 3NF
4. DBCNF tables cannot have primary keys