(Solved): 3. (Note: Read the schema of each table in the university database from ddi.sql or using schema < ...

3. (Note: Read the schema of each table in the university database from ddi.sql or using schema <table-name> before working on part a - c.) For part a-e, write the following queries in SQL, using the university schema. (You should actually check the queries you write on the database.) a. (1 point) Find the IDs and names of all students who have taken a course in Spring 2009; make sure there are no duplicates in the result. b. (1 point) Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result. c. (1 point) Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicates in the result. d. (4 points) Consider the insurance database in the following table, where primary keys are underlined. person (driver_id, name, address) car (vin, model, year) accident report.id, date, location) owns (driver_id, vin) participated (report_id, vin, driver_id, damage amt) Write SQL DDL corresponding to the above schema. Make any reasonable assumptions about data types, and be sure to declare primary and foreign keys. Schema Diagram for University Database student ID takes ID Course_id sec_id semester ramte dep?_name tot_cred year grade section course id sec id semester advisor course Course id title dept_name credits Su department dept_name building budget lear building 100ml_no time_slot_id time_slot time_slot_id day start time end time instructor classroom building room no capacity prereg Course_id ??? ??? ID 12e dept_name salary teaches ID Course_id ser id semester year Show transcribed image text 3. (Note: Read the schema of each table in the university database from ddi.sql or using schema before working on part a - c.) For part a-e, write the following queries in SQL, using the university schema. (You should actually check the queries you write on the database.) a. (1 point) Find the IDs and names of all students who have taken a course in Spring 2009; make sure there are no duplicates in the result. b. (1 point) Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result. c. (1 point) Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicates in the result. d. (4 points) Consider the insurance database in the following table, where primary keys are underlined. person (driver_id, name, address) car (vin, model, year) accident report.id, date, location) owns (driver_id, vin) participated (report_id, vin, driver_id, damage amt) Write SQL DDL corresponding to the above schema. Make any reasonable assumptions about data types, and be sure to declare primary and foreign keys.
Schema Diagram for University Database student ID takes ID Course_id sec_id semester ramte dep?_name tot_cred year grade section course id sec id semester advisor course Course id title dept_name credits Su department dept_name building budget lear building 100ml_no time_slot_id time_slot time_slot_id day start time end time instructor classroom building room no capacity prereg Course_id ??? ??? ID 12e dept_name salary teaches ID Course_id ser id semester year