NOTE : ALL QUESTIONS EXCEPT 3.5 , 3.6 AND 3.8 ARE COMPLETED. ( Using Oracle SQL
ID: 3702507 • Letter: N
Question
NOTE : ALL QUESTIONS EXCEPT 3.5 , 3.6 AND 3.8 ARE COMPLETED. ( Using Oracle SQL developer)
I am receivng error " SQL command not properly ended" for all 3. Please help me.
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.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;
List the names of all the institutions (instname) currently in the database. 3.2 How many academics are there in the department where deptnum#100 Return the total number. List in alphabetical order the family name (famname) and given name (givename) of academics who has a title. 3.3. 3.4. List papers whose title contains the words "Data" or "Software", in upper or lower cases. List the panum and title of these papers. 3.5. Return the panum of papers written by the academic "Mark Yee'". 3.6. Return the famname and givename of academics whose acnum is in the range [100.. 1991 or whose givename starts with "S". .7 List the panum of papers having at least two authors 3.8 List in alphabetical order the famname, givename of academics who work for institutions in Queensland. Note: the value of Queensland in the database is QLD or Qld. 3.9. The below query is intended to list the fieldnum and title of fields whose fieldnum is between 500 and 599 or whose title contains the word Data Bu t has errors. Give the correct SQL query select fieldnum, title from field where fieldnum >-500 andExplanation / Answer
/*select AU.panum from AUTHOR as AU INNER JOIN ACADEMIC as AC on AC.acnum=AU.acnum where AC.famname="Yee" and AC.givenname="Mark";*/
SELECT panum from AUTHOR WHERE acnum IN (SELECT acnum from ACADEMIC WHERE famname='Yee' AND givenname='Mark');
/*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%";*/
SELECT famname,givename from ACADEMIC WHERE givename like 'S%' OR acnum between 100 and 199;/*Here left join is not require. All data available in ACADEMIC table*/
/*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;*/
SELECT famname,givename from ACADEMIC WHERE deptnum IN (SELECT deptnum from DEPARTMENT WHERE instname IN ('Qld','QLD'))
ORDER BY famname ASC,givename ASC;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.