Academics SQL Queries

Schema

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(fieldnumt. acnum, descrip)

Queries

• List the deptnum and total number of academics for departments with postcode in the range 3000..3999, in descending order of total number of academics for each department.
• Are there any academics who have not written any papers? List the acnum, givename and famname of these academics. Your query must contain a subquery.
• Find the departments that have more than 10 academics. List the deptnum, instname and deptname of these departments.
• Find the department in Victoria that is the strongest in "Software Engineering" (field.title) research, that is the department has the largest of academics interested in "Software Engineering" research. Output the deptnum, deptname and instname of the department. You must NOT use the MAX aggregate function.
• Give the total number of academics that do not have any research interests.
• Are there any research fields where less than 10, including zero, academics are interested in. List the fieldnum, id and number of interested academics for these research fields.
• Find the papers authored by academics from departments located in Victoria, List the panum and title of these papers. You must use the NATURAL JOIN and EXISTS operators.
• Write a CREATE VIEW statement to define a view PAPER_VIEW that has three columns to keep data for each paper: panum (the paper number), n_author the total number of authors) and title (the paper title).
• The ID of research fields has three parts separated by two periods. Consider a query to find details of research fields where the first two parts of the ID are D and 2 and the last part is one character (digit). IDs like D.2.1 and D.2.3 are in the query result whereas IDs like D.2.12 or D.2.15 are not. The SQL query given below does not return the correct result. Explain the reason why it does not return the correct result and give the correct SQL query.

Select *
from field
where ID like 'B.1._

• The following SQL query is intended to find the departments with the largest total number of academics and print their department num, deptname and instname, but it has syntax errors. Identify the syntax errors in the query and give the correct SQL query.

select deptnum, deptname, instname, count(acnum)
from department, academic where academic.deptnum department.deptnum
group by deptnum
having max(count(acnum));