Databases & SQL worksheet with answers

What does GROUP BY do in a query?

1. ASorts the rows of the result alphabetically
2. BJoins two tables on their common columns
3. CPuts rows with the same values into groups so aggregates are computed per group
4. DRemoves rows with NULL values

How do you sort a result from the highest to the lowest salary?

1. AORDER BY salary DESC
2. BORDER BY salary ASC
3. CGROUP BY salary DESC
4. DSORT salary DOWN

An INNER JOIN returns:

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

A column holds the values 10, 20 and NULL. What does AVG(col) return?

1. A30
2. B15
3. C10
4. DNULL

Which query returns the highest salary in each department?

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

What does SELECT name, CASE WHEN grade >= 5.5 THEN 'pass' ELSE 'fail' END FROM results return?

1. AEach name with the label 'pass' or 'fail' depending on the grade
2. BThe number of students who passed and failed
3. COnly the students who passed
4. DAn error because CASE is not allowed in SELECT

Two-phase locking (2PL) ensures:

1. AThat deadlocks can never happen
2. BThat transactions never wait
3. CConflict-serializable schedules
4. DThat no locks are needed

When is a schedule of concurrent transactions called serializable?

1. AWhen no transaction ever reads data
2. BWhen every transaction is given a serial number
3. CWhen the transactions run on one CPU only
4. DWhen its effect is equivalent to some serial execution of the same transactions