CS110 Database Questions

1. CS110 Database Questions

2. Given the database file above, provide the IDs of the records that match the following queries (write “none” if no records match): • Name is “Bob” • Birth Year is less than 1980 • Hair Color is “Brunette” • Birth Year is less than 1980 or Hair Color is “Brunette” • Birth Year is less than 1980 or Name is “Bob” • Birth Year is less than 1980 and Name is “Bob”

3. Given the database file above, provide the IDs of the records that match the following queries (write “none” if no records match): • Name is “Bob” • 2 • Birth Year is less than 1980 • 3,4 • Hair Color is “Brunette” • 1,2 • Birth Year is less than 1980 or Hair Color is “Brunette” • 1,2,3,4 • Birth Year is less than 1980 or Name is “Bob” • 2,3,4 • Birth Year is less than 1980 and Name is “Bob” • none

4. Given the database file above, provide the IDs of the records that match the following queries (write “none” if no records match): • Hair Color is not “Black” • Birth Year is less than 1980 and Hair Color is not “Black” • Birth Year is less than 1980 or Hair Color is not “Black” • Birth Year is less than 1980 or Birth Year is greater than or equal to 1980 • Birth Year is less than 1980 and Birth Year is greater than or equal to 1980 • Birth Year is greater than or equal to 1980 and Name is “Bob”, • or Name is not “Daniella” and Hair Color is “White”

5. Given the database file above, provide the IDs of the records that match the following queries (write “none” if no records match): • Hair Color is not “Black” • 1,2,3,5 • Birth Year is less than 1980 and Hair Color is not “Black” • 3 • Birth Year is less than 1980 or Hair Color is not “Black” • 1,2,3,4,5 • Birth Year is less than 1980 or Birth Year is greater than or equal to 1980 • 1,2,3,4,5 • Birth Year is less than 1980 and Birth Year is greater than or equal to 1980 • none • Birth Year is greater than or equal to 1980 and Name is “Bob”, • or Name is not “Daniella” and Hair Color is “White” • 2,5

6. Given the database file above, provide the IDs of the records that match the following queries (write “none” if no records match): • People with brunette hair or called Alice • Brunettes named Carl or Edward • People with a birth year before 1980 and born in August or September • People who are not named Carl, Daniella or Edward, but are brunette or blonde • People born before 2000 in March or May with brunette or white hair • Brunettes born in January or December plus Blondes born in May or August

7. Given the database file above, provide the IDs of the records that match the following queries (write “none” if no records match): • People with brunette hair or called Alice • 1, 2 • Brunettes named Carl or Edward • none • People with a birth year before 1980 and born in August or September • 3 • People who are not named Carl, Daniella or Edward, but are brunette or blonde • 1,2 • People born before 2000 in March or May with brunette or white hair • 1 • Brunettes born in January or December plus Blondes born in May or August • 2,3

8. What will be the ID in the second row (excluding headers) under the following sorts? • Primary: Name Descending • Primary: Birth Year Ascending • Primary: Birth Year Ascending, Secondary: Name Descending • Primary: Hair Color Ascending, Secondary: Name Ascending • Primary: Hair Color Descending, Secondary: Name Ascending • Primary: Hair Color Descending, Secondary: Birth Year Ascending

9. What will be the ID in the second row (excluding headers) under the following sorts? • Primary: Name Descending • 4 • Primary: Birth Year Ascending • 3 • Primary: Birth Year Ascending, Secondary: Name Descending • 3 (note secondary key does nothing here) • Primary: Hair Color Ascending, Secondary: Name Ascending • 3 • Primary: Hair Color Descending, Secondary: Name Ascending • 1 • Primary: Hair Color Descending, Secondary: Birth Year Ascending • 2