Databases & SQL worksheet with answers

Statements such as CREATE, ALTER and DROP belong to which part of SQL?

1. AData Definition Language (DDL)
2. BData Control Language (DCL)
3. CData Manipulation Language (DML)
4. DTransaction Control Language (TCL)

What does a CHECK constraint do?

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

In a LIKE pattern, what does the _ (underscore) wildcard stand for?

1. AA space character
2. BZero or more characters
3. CAny letter but no digits
4. DExactly one character

What does GRANT SELECT ON employees TO alice do?

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

Why are indexes created on columns?

1. ATo save disk space
2. BTo prevent NULL values
3. CTo make inserts and updates faster on those columns
4. DTo speed up searches and joins on those columns

What does a common table expression (WITH name AS (...)) provide?

1. AA new database user
2. BA named temporary result that can be used within one statement
3. CA permanent table that stays stored on disk until it is dropped
4. DAn index on a column

What does the following return: SELECT name FROM students WHERE grade > (SELECT AVG(grade) FROM students)?

1. AAn error because subqueries are not allowed in WHERE
2. BThe average grade of all students
3. CStudents with the highest grade only
4. DStudents with a grade above the average grade

Why do many DBMSs use strict two-phase locking, holding exclusive locks until commit?

1. AIt prevents cascading aborts, because nobody can read uncommitted writes
2. BIt makes deadlocks impossible
3. CIt removes the need for a transaction log
4. DIt allows transactions to read dirty data safely