Question

Question 3. SQL (5 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 Oracle syntax and useful functions. 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 (INTEREST) in several research fields. Download and run the SQL script academics.sql on Canvas (the Oracle section) to define and populate the Academics database in your Oracle account. Write ONE SQL query for each of questions 3.1--3.9, and each component of an SQL statement must be on a separate line. Your query should not produce duplicates in output but use DISTINCT only if necessary. Include answer for Question 3.10 as comments starting with "." to make the whole SQL script file (.sql) executable (in SQL Developer and other SQL client programs). 3.8. Find authors who have interest in the field of “Logic Programming" (fieldtitle). List their famname and givename. The below query is meant to list the fieldnum and title of fields whose fieldnum is between 500 and 599 or whose title contains the word 'Data'. But it has errors. Give the correct SQL query 3.9. select panum, title from paper where panum >=500 and

Answer 1

Hi there,

please find the solution.

3.8

select famname, givename from ACADEMIC

join FIELD on

ACADEMIC.title = FIELD.tile

where FIELD.title = 'Logic Programming' ;

3.9

select fieldnum, title from FIELD

where fieldnum between 500 and 599

or conatins(title,"Data");

3.10

select paper.panum, paper.title, author.acnum

from paper, author

join author on

paper.panum=author.panum

join field on

author.acnum=field.acnum

order by panum;

Thanks

Feel free to write back in case of doubt.