QUESTIONS : Question 3. SQL In addition to the lecture notes, you should also st
ID: 3702218 • Letter: Q
Question
QUESTIONS :
Question 3. SQL In addition to the lecture notes, you should also study by yourself the SOL Plus tutorial on Canvas (the Oracle section) and other resources for Oracle syntax and useful function.s. The ER model for the Academics database is as follows: Department Has Academic Interest Field Author Paper 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* acnumt, 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.sal 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 ona 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)Explanation / Answer
3.1. select instname from DEPARTMENT;
3.2.select count(*) from ACADEMIC where deptnum=100;
3.3. select famname,givename from ACADEMIC where title is not null order by famname asc,givename asc;
3.4select panam,title from PAPER where lower(title) like "%data%" or lower(title) like "%software%";
3.5.select AU.panum from AUTHOR as AU left join ACADEMIC as AC on AC.acnum=AU.acnum where AC.famname="Yee" and AC.givenname="Mark";
3.6.select AC.famname,AC.givename from AUTHOR as AU left join ACADEMIC as AC on AC.acnum=AU.acnum where AU.acnum between 100 and 199 or AC.givename like "S%";
3.7select panum from AUTHOR group by panum having count(panum)>=2;
3.8.select AC.famname,AC.givename from DEPARTMENT as D join ACADEMIC as AC on D.deptnum=AC.adeptnum where D.instname i="QLD" or D.instname i="Qld" order by AC.famname asc,AC.givename asc;
310.it display the details of the field of research starting with an id B.1. and academics interersted in them.