Databases & SQL worksheet with answers

Which SQL statement adds new rows to a table?

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

What does NULL represent in SQL?

1. AA false Boolean
2. BA missing or unknown value
3. CThe number zero in numeric columns
4. DAn empty string

What does a UNIQUE constraint on a column guarantee?

1. AThe column automatically becomes the primary key of the table
2. BNo two rows have the same non-NULL value in that column
3. CThe column cannot be changed after insert
4. DThe column values are sorted

What does GRANT SELECT ON employees TO alice do?

1. ACopies the employees table to alice
2. BMakes alice the owner of the database
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 COALESCE(phone, 'unknown') FROM customers return for a customer whose phone is NULL?

1. A'unknown'
2. BAn empty string
3. CAn error
4. DNULL

In relational algebra, the projection operator (π) corresponds to:

1. AChoosing which columns appear in the result
2. BCombining two tables on a key
3. CChoosing which rows satisfy a given condition
4. DSorting the result

After customers LEFT JOIN orders, why use COUNT(o.id) rather than COUNT(*) to count orders per customer?

1. ACOUNT(*) is not allowed after a join
2. BCOUNT(*) ignores rows with NULL values
3. CCOUNT(o.id) counts only distinct customers
4. DCOUNT(*) counts the NULL-filled row as 1 for customers without orders

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

1. ANested loop join with index lookups
2. BCross join
3. CHash join
4. DBitmap join on the primary key