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,...

1 answer below »










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.












Reference number




QC1-20





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;












Reference number




QC1-14





Associated Marks: 2 marks



List the famname, givename of all academics in the database, in alphabetical order.













Reference number




QC1-19





Associated Marks: 2 marks



How many academics are there in the database? Write a query to find the answer.













Reference number




QC2-13





Associated Marks: 2.5 marks



List the field number and title of fields interested by the academic whose acnum is 100.













Reference number




QC2-15





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.













Reference number




QC2-11





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.













Reference number




QC2-17





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.













Reference number




QC3-20





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.












Reference number




QC3-19





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);














Reference number




QC3-14





Associated Marks: 3 marks



Find the academics who have not authored any paper with “Steve Bruce”. List their details (acnum, famname, givename, deptname, instname).














Reference number




QC3-11





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.


Answered Same DaySep 15, 2021ISYS1055

Answer To: This part is for information only. It is not a question. You do not need to copy this to your answer...

Shikha answered on Sep 15 2021
135 Votes
Student Name
Student ID        2
Database Implementation
(Academics Database)
Submitted By
Course
Professor
Date
SQL Queries
Find
academics who have a title (title) available in the database. Write a query to return all details of these academics.
select * from academic where title is not null;
    Reference number
    QC1-20
The Correct query is
select fieldnum, count (acnum)
from interest
group by fieldnum;
    Reference number
    QC1-14
List the famname, givename of all academics in the database, in alphabetical order.
select famname, givename from academic
order by famname;
    Reference number
    QC1-19
How many academics are there in the database? Write a query to find the answer.
select count(acnum) from academic;
    Reference number
    QC2-13
List the field number and title of fields interested by the academic whose acnum is 100.
select interest.fieldnum, field.title
from interest, field
where interest.fieldnum=field.fieldnum
and interest.acnum=100;
    Reference number
    QC2-15
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.
select author.panum, paper.title, count(author.acnum)
from author, paper
where author.panum=paper.panum
group by author.panum, paper.title;
    Reference...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here