Question

Question 1. SQL (10 points) In addition to the lecture notes, you should also study by yourself the SQL Plus tutorial on Canvas (the Oracle section) and other resources for syntax and useful functions The relational schema for the Academics database is as follows descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) t, 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. Return the acnum of academic(s) who wrote the largest number of papers. You must NOT use MAX. An SQL query that lists all academics in decreasing order of their total number of papers is incorrect.

Answer 1

-- to get acnum having largest number of paper we can use group along with Fetch condition to get result. using fetch helps to eliminate use of MAX condition. Below is query.

Select acnum from paper group by acnum order by count(panum) desc Fetch First 1 Row only;