Question

The relational schema for the Academics database is as follows DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title) FIELD(fieldnum, id, title) INTEREST(fieldnum* acnumk, descrip) Some notes on the Academics database An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department. Research papers (PAPER) are often authored by several academics, and of course an academic often writes several papers (AUTHOR) A research field (FIELD) often attracts many academics and an academic can have interest in several research fields (INTEREST). . . Primary keys are underlined and foreign keys are marked with *. You should download the SQL script for defining and populating the database academics.sql from Canvas (the Oracle section) and run academics.sql in your Oracle account to build the database Write ONE SQL query for each of questions 1.1) through to 1.10). Your query must run in Oracle SQL Developer. Each query is worth 1 point. Notes for marking . Each question is worth 1 point. . For questions with "You must or "You must not requirement, queries failing to meet the requirement receive maximum 0.5 mark. For example, question 1.2) has "You must use a subquery". A query not using the subquery operator receives maximum 0.5 mark. Do not include the output of the query or the script used to create the tables . . Your query should not output duplicates but use DISTINCT only if necessary . Queries are marked in terms of both correctness and efficiency. Unnecessary joins will incur deduction.

Answer 1

1.7)

SELECT fieldnum, title FROM FIELD WHERE title LIKE '%data%' AND EXISTS (SELECT f.fieldnum FROM INTEREST i JOIN ACADEMIC a ON a.acnum = i.acnum JOIN FIELD f ON i.fieldnum = f.fieldnum WHERE a.deptnum = 100);

1.8)

SELECT p.panum FROM PAPER p, AUTHOR a1, AUTHOR a2 WHERE p.panum = a1.panum and p.panum= a2.panum and a1.acnum = a2.acnum;