Question

Question 3. SQL. 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, dept name, state, postcode) ACADEMIC(acnum, deptnum*, famname, give name, initials, title) PAPER(panum, title) AUTHOR(panum*, acnum*) FIELD(field num, id, title) INTEREST(field num*, 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 write several papers (AUTHOR). A research field (FIELD) often attracts many academics, and an academic can have the 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.10, 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 any explanation as comments starting with “--” to make the whole SQL script file (.sql) executable (in SQL Developer and other SQL client programs). List the deptnum of departments having at least two academics. (Hint: Join queries in the SQL2 lecture). List in alphabetical order the famname, give the name of academics who work for institutions in Victoria. Note: the values for “Victoria” include “VIC” or “Vic”. Find academics who do not have any title. Give their famname and givename. Find authors who have an interest in the field of “Logic Programming” (field.title). List their famname and given me.

Answer 1

Plzzzzzzzzzzzzzzzzzzz Upvote...
Comment for any queries.....thanks

a) SELECT deptnum from department where deptnum in (select deptnum from ACADEMIC GROUP BY deptnum HAVING COUNT(dept)>=10);

b ) select famname,givename from ACADEMIC where deptnum in(select deptnum from DEPARTMENT where instname='Vctoria' OR instname='VIC' OR instname='Vic') ORDER BY famname asc,givename asc;

c ) select famname,givename from ACADEMIC where title IS NULL;

Note:in question (a) HAVING and Group By clause is used to select only a particular category. Here the inner query selects deptnum having greater than or equal to ten academics.

In (b) inner query selects deptnum with instname is Victoria.Using this deptnum outer query selects famname,givename from ACADEMIC and it is sorted using ORDER BY asc for ascending order.

In (c), Query selects famname,givename from ACADEMIC its title value is null. The IS NULL clause is used for this purpose.

Hope I was able to help you today.Feel free to reach out if any further clarification is required.
Please do rate the answer if it helped you :)