Using the university schema, fill in the blanks of the following query to display a list of all instructors, showing each instructor's ID and the number of sections that instructor taught. Make sure to show the number of sections as O for instructors who have not taught any section. SELECT ID, (_____ COUNT(*) AS secs_taught FROM teaches AS WHERE TT.ID = II.ID) instructor AS II; Answer for blank # 1: SELECT Answer for blank # 2: TT Answer for blank # 3: WHERE
Question 4 0/3 points Outer join expressions can be computed in SQL without using the SQL OUTER JOIN operation. To illustrate this fact, show how to rewrite the following SQL query without using the OUTER JOIN expression by using the university schema. Query to Be Rewritten: SELECT * FROM student NATURAL LEFT OUTER JOIN takes; Rewritten Query: SELECT * FROM student NATURAL UNION SELECT ____, name, FROM student AS SS WHERE EXISTS Answer for blank # 1: inner Answer for blank # 2: on takes (SELECT ID FROM AS TT WHERE TT.ID = SS.ID); Answer for blank # 3: exists Answer for blank # 4: null Answer for blank # 5: from tot cred, NULL, NULL, NULL, NULL, NULL
Question 6 1/3 points For the employee database schema given below, fill in the blanks to finish the query that finds the IDs of each employee with no manager. Note that an employee may simply have no manager listed or may have a null manager. Use an outer join expression. If you need to test these queries, you could create the database and populate it with a few example tuples. employee (ID, person_name, street, city) works (ID, company_name, salary) company (company_name, city) manages (ID, manager_ID) SELECT ID FROM WHERE IS NULL; Answer for blank # 1: employee Answer for blank # 2: ID Answer for blank # 3: ID Answer for blank # 4: select Answer for blank # 5: id Answer for blank # 6: manager_ID manages
Question 7 0/3 points Using the university schema, fill in the blanks of the following query to display a list of all instructors, showing each instructor's ID and the number of sections that instructor taught. Make sure to show the number of sections as O for instructors who have not taught any section. SELECT ID, COUNT (sec_id) AS secs taught FROM instructor LEFT GROUP BY ID; ————— Answer for blank # 1: select Answer for blank # 2: TT Answer for blank # 3: where teaches