View the step-by-step solution to:

The relational schema for the Academics database is as follows:


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)

AUTHOR(panum*, acnum*)

FIELD(fieldnum, id, title)

INTEREST(fieldnum*, acnum*, 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 Q1--.9. Put your answer for Question 10) in comments (starting each line with "--").

Notes for marking:

  • Each question is worth 4 marks. For questions with the "You must (not) ..." requirement, queries failing to meet the requirement receive maximum 1 mark. For example, question
  • Q1) has "You must use the NATURAL JOIN operator".  A query not using the NATURAL JOIN operator receives maximum 1 mark.
  • Do not include the result 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. List the deptnum and total number of academics for CS departments, in alphabetical order of deptname.  CS departments are departments whose deptname contains the phrase "Computer ... Science" or "Computing ... Science" in upper case or lower case letters.  You must use the NATURAL JOIN operator.
  2. List research fields where at least one academic is interested in. List the fieldnum, ID and title of these research fields. You must use a subquery.  
  3. Find papers that have three or more authors. Give the panum, title and number of authors for these papers.
  4. For EACH academic, compute the total number of papers s/he has written. Output should include the acnum and total number of papers for each academic. In particular, an academic without any papers should have zero(0) as number of papers in the output. You must use a JOIN operator.
  5. Give the total number of academics that do not have research interests. You must use the NOT IN operator.
  6. Are there any research fields where less than 20, including zero, academics are interested in.  List the fieldnum, ID, title and number of interested academics for these research fields.
  7. Find the papers whose title contain the string 'data' and where at least one author is from the department with deptnum 100. List the panum and title of these papers. You must use the EXISTS operator. Ensure your query is case-insensitive.
  8. Return the research interest that has the largest number of interested academics. You must not use MAX. Note: An SQL query that lists all research interests in decreasing order of their total number of interested academics is incorrect.
  9. The following SQL query is intended to find academics (acnum) who are ONLY interested in "Data" (descrip) fields.  But it is incorrect. Give the correct SQL query.

select acnum

from interest

where upper(descrip) like '%DATA%';

10.Consider the SQL query given below, give the English explanation for the output of a) the subquery, and b) the whole SQL query. Literal explanation will receive zero mark.


select distinct AC1.givename, AC1.famname, AC2.givename, AC2.famname

from academic AC1, author AU1, academic AC2, author AU2

where AC1.acnum=AU1.acnum

   and AC2.acnum=AU2.acnum

   and AU1.panum=AU2.panum

   and AU2.acnum>AU1.acnum

   and not exists

       (select *

       from Interest I1, Interest I2

       where I1.acnum =AC1.acnum

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question