The relational schema for the Academics database is as follows:
DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)
ACADEMIC(acnum, deptnum*, famname, givename, initials, title)
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.
- 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.
- 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.
- Find papers that have three or more authors. Give the panum, title and number of authors for these papers.
- 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.
- Give the total number of academics that do not have research interests. You must use the NOT IN operator.
- 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.
- 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.
- 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.
- 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.
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
and not exists
from Interest I1, Interest I2
where I1.acnum =AC1.acnum
Recently Asked Questions
- Please refer to the attachment to answer this question. This question was created from q._Concentration_of_Solutions_Worksheet.pdf.
- Select a minor character from a story in Unit 1, choosing from a story you in Part A of this project. Writing from this character's perspective, a conversation