Databases & SQL worksheet with answers

Which SQL statement changes values in rows that already exist?

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

What is a primary key?

1. AA column that may hold duplicate values but must never contain NULL
2. BA column or set of columns that uniquely identifies each row
3. CA password for the database
4. DThe first column of every table

A customer can place many orders, but each order belongs to exactly one customer. What type of relationship is this?

1. AMany-to-many
2. BOne-to-many
3. CZero-to-zero
4. DOne-to-one

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

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

Which query returns the highest salary in each department?

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

What does EXCEPT (called MINUS in Oracle) return?

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

R(A, B, C, D) has the functional dependencies A → B and C → D. What is the candidate key?

1. A{A, C}
2. B{A}
3. C{B, D}
4. D{C}

What is a known drawback of decomposing tables into BCNF compared with 3NF?

1. ABCNF allows more redundancy than 3NF
2. BBCNF tables cannot have primary keys
3. CSome functional dependencies may no longer be checkable within a single table
4. DThe decomposition always loses data when joined back