This part is for information only. It is not a question. You do not need to copy this to your answer sheet.
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 tooneinstitution (instname) and often hasmanyacademics. An academic only works foronedepartment.
·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 (INTEREST) in several research fields.
Download and run the SQL scriptacademics.sqlon Canvas (the Oracle section) to define and populate the Academics database in your Oracle account.
Reference number
|
QC1-16
|
Associated Marks: 2 marks
Find academics who have a title (title) available in the database. Write a query to return all details of these academics.
Associated Marks: 2 marks
The following SQL query is meant to output a list of fields (fieldnum) with the total number of academics for each field. It has syntax errors and logic errors. Give the correct query.
select fieldnum, count (acnum)
from field, interest
where field.fieldnum=interest.fieldnum
group by acnum;
Associated Marks: 2 marks
List the famname, givename of all academics in the database, in alphabetical order.
Associated Marks: 2 marks
How many academics are there in the database? Write a query to find the answer.
Associated Marks: 2.5 marks
List the field number and title of fields interested by the academic whose acnum is 100.
Associated Marks: 2.5 marks
List the panum, title, and total number of academics (under the heading "NO. AUTHORS") for each paper, in increasing order (i.e. ascending order) of panum.
Associated Marks: 2.5 marks
Are there academics whose interested field descriptions are missed? Print their fieldnum and acnum. The list should be in alphabetical order of acnum and then fieldnum.
Associated Marks: 2.5 marks
List the deptnum of departments whose postcodes are in the range 3000...3999 and that do not have any academics with the title of Professor (stored as “Prof” or “Prof.” in the database) , including departments that do not have any academics. You must use (NOT) EXISTS.
Associated Marks: 3 marks
Find departments where there are academics that have at least two research interests without any description. List their instname and deptname in alphabetical order.
Associated Marks: 3 marks
Explain the following query in English. A literal explanation will receive 0 marks.
select givename, famname, deptname
from academic natural join department
where acnum not in
(select acnum
from academic natural join interest)
and deptNum in
(select deptNum
from academic natural join author
group by deptNum
having count(panum)>100);
Associated Marks: 3 marks
Find the academics who have not authored any paper with “Steve Bruce”. List their details (acnum, famname, givename, deptname, instname).
Associated Marks: 3 marks
List papers (panum) by academics with research interests in fields related to "database". You must use IN. Note that “fields related to database” includes any occurrence of the eight letters “database” within a field name, in any case.