Databases & SQL worksheet with answers

Which SQL statement changes values in rows that already exist?

1. AINSERT
2. BREPLACE COLUMN
3. CMODIFY
4. DUPDATE

Which keyword gives a column or table a temporary alternative name in a query?

1. AAS
2. BALIAS
3. CRENAME
4. DNAME

Which SQL keyword removes duplicate rows from a result?

1. ASINGLE
2. BDIFFERENT
3. CDISTINCT
4. DUNIQUE

Which statement about HAVING is correct?

1. AIt must come before WHERE in the SELECT statement
2. BIt can use aggregate functions to filter groups
3. CIt cannot be used with GROUP BY
4. DIt sorts the results

What does EXCEPT (called MINUS in Oracle) return?

1. AAll rows from both queries without duplicates
2. BRows of the second query that do not appear in the first one
3. CRows that appear in both queries
4. DRows of the first query that do not appear in the second

An employees table has a column manager_id that refers to another employee. How do you list each employee next to their manager's name?

1. AJoin the employees table with itself using two aliases
2. BUse GROUP BY manager_id only
3. CCreate a second copy of the table on disk
4. DCombine the table with itself using UNION ALL and a WHERE filter

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

1. A1, 1, 2
2. B1, 2, 3
3. C1, 1, 3
4. D2, 2, 3

What does SELECT name, dept, AVG(salary) OVER (PARTITION BY dept) FROM emp return?

1. AAn error because name is not in a GROUP BY
2. BEvery employee row together with the average salary of their department
3. COnly employees who earn more than their department average
4. DOne row per department with its average salary