Databases & SQL worksheet with answers

Which SQL statement retrieves data from a table?

1. ASELECT
2. BCREATE
3. CINSERT
4. DUPDATE

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

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

Which statement deletes a whole table, including its structure?

1. ADROP TABLE
2. BDELETE FROM
3. CREMOVE TABLE
4. DTRUNCATE ROWS

When is WHERE EXISTS (subquery) true for a row?

1. AWhen the subquery returns a number greater than zero
2. BWhen the subquery returns no NULL values
3. CWhen the subquery returns exactly one row
4. DWhen the subquery returns at least one row

Which query returns the highest salary in each department?

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

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

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

Scores are 90, 90 and 80. Which values does RANK() OVER (ORDER BY score DESC) give?

1. A2, 2, 3
2. B1, 2, 3
3. C1, 1, 2
4. D1, 1, 3

There is a composite index on (last_name, first_name). Which condition can use it most effectively?

1. AWHERE first_name LIKE '%a'
2. BWHERE last_name = 'Jansen'
3. CWHERE first_name = 'Eva'
4. DWHERE UPPER(first_name) = 'EVA'