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. 1.7) Find research fields whose title contains the string 'data' and has at least one interested academic from the department with deptnum 100. List the fieldnum and title of these research fields. You must use the EXISTS operator. Ensure your query is case-insensitive .8 List papers (panum) that have authors from the same department. Do NOT use any JOIN operator. Do NOT use any subqueries 1.9) The SQL query below is meant to list research fields (fieldnum) where no academics from RMIT CS' (deptnum-126) have an interest. But it is incorrect. Give the correct SQL query select fieldnum from interest, academic where interest.acnun-academic.acnum and deptnum 126

Answer 1

--1.7

SELECT fieldNum,title From Field WHERE EXISTS (Select acnum from interest Where fieldNum=Field.FieldNum and Field.Title Like '%data%' and acnum in (select acnum from academic where deptnum=100));

In above query we have used Exists operations and along with subquery to filter data as per requirement.

<