Reference no: EM132633645

Question 1:

The following SQL query is meant to output a list of fields (fieldnum) with the total number of academics for each field. It has syntax errors and logic errors. Give the correct query.

select fieldnum, count (acnum) from field, interest
where field.fieldnum=interest.fieldnum
group by acnum;

Question 2:

Explain the following query in English. Literal explanations will receive zero marks.

SELECT panum, title FROM paper
where (panum>=500 and panum<=599)
or (upper(title) like 'DATABASE %'
or upper(title) like '% 'DATABASE %' or upper(title) like '% 'DATABASE');

Question 3:

How many departments are there in the institute where instname=' Monash University'? Write a query to return the total number.

Question 4:

Return the famname and givename of academics having interests in ‘Data Structures' (descrip) with acnum in the range [100...399]. The output should be in alphabetical order of famname and then givename.

Question 5:

For each academic, give the acnum, givename, famname and the total number of fields s/he has worked in.
Note that if an academic has not worked in any fields, his/her total should be zero.

Question 6:

Are there academics whose interested field descriptions are missed? Print their fieldnum and acnum. The list should be in alphabetical order of acnum and then fieldnum.

Question 7:

List the deptnum of departments whose postcodes are in the range 3000...3999 and that do not have any academics with the title of Professor (stored as "Prof" or "Prof." in the database) , including departments that do not have any academics. You must use (NOT) EXISTS.

Question 8:

Output in alphabetical order the acnum, famname, givename of academics whose family name starts with "C" and whose interested field number is larger than 3.

Question 9:

Find departments where at least one academic has more than 10 research interests, and list the deptnum, depntname, instname of these departments. Must use a subquery.

Question 10:

Find the research fields whose number of interested academics is larger than the average. Return the fieldnum, title and the number of academics who are interested in these fields.

Question 11:

Find departments where there are academics that have at least two research interests without any description. List their instname and deptname in alphabetical order.

Question 12:

The productivity of an academic is measured by the total number of paper s/he has written. Find the papers that have the most productive author/s or the least productive author/s. Output the acnum, title, and the givename, famname of the most productive author/s or the least productive author/s together with the number of their papers.

Attachment:- Quiz Assignment SQL.rar