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 <=599 or upper(title) like 'Data%"; 3.10. The query is meant to list paper number (panum), title and author (acnum) for papers, and research interest (fieldnum) for each author, but it has errors. Give the correct SQL query select panum, title from author, interest, paper where author.acnum=interest.acnum order by panum; 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
-
Engineering 2022-05-15 19:04:59