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

Which rows does WHERE price BETWEEN 10 AND 20 select?

1. ARows with price above 10 and below 20, excluding 10 and 20
2. BRows with price outside the range 10 to 20
3. CRows with price equal to 10 or 20 only
4. DRows with price from 10 to 20, including 10 and 20

What does NULL represent in SQL?

1. AA false Boolean
2. BThe number zero in numeric columns
3. CAn empty string
4. DA missing or unknown value

Which ACID property means concurrent transactions do not see each other's unfinished changes?

1. AAtomicity
2. BIsolation
3. CDurability
4. DAvailability

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

1. A10
2. B30
3. CNULL
4. D15

Students can take many courses and courses have many students. How is this modelled in tables?

1. ABy merging students and courses into one table
2. BWith a list of course ids stored in one column
3. CWith a junction table holding foreign keys to both students and courses
4. DWith a foreign key column course_id in the students table pointing to courses

Which window function assigns 1, 2, 3, ... to rows within each partition without ties?

1. ADENSE_RANK()
2. BCOUNT()
3. CROW_NUMBER()
4. DRANK()

When is decomposing R into R1 and R2 guaranteed to be lossless?

1. AWhen every attribute of R appears in both R1 and R2
2. BWhen R1 and R2 have no attributes in common
3. CWhen R1 and R2 have the same number of columns
4. DWhen the common attributes of R1 and R2 form a superkey of R1 or of R2