Solved SQL Queries Assignment

This assignment focuses on solving various SQL queries involving multiple tables such as academic, department, interest, and field. It covers tasks like counting records based on department postcode, retrieving information about software engineering fields in Victorian departments, identifying fields with less than 10 entries, and analyzing paper author affiliations. The solutions include SELECT statements, GROUP BY clauses, HAVING conditions, JOIN operations, and even a CREATE VIEW statement.

Running head: SQL QUERIES
SQL Queries
Name of the Student:
Name of the University:
Author Note

1
SQL QUERIES
Answer to question 2.1
SELECT academic.deptnum, COUNT(academic.acnum)
from academic
FULL OUTER JOIN department
ON department.deptnum = academic.deptnum
WHERE department.postcode >3000 and department.postcode <3999 and academic.deptnum is
NOT NULL
GROUP BY academic.deptnum
ORDER BY COUNT(academic.acnum) DESC;
Answer to question 2.4
SELECT department.deptnum, department.deptname, department.instname,
COUNT(field.fieldnum)
FROM department
FULL OUTER JOIN academic
ON department.deptnum = academic.deptnum
FULL OUTER JOIN interest
ON academic.acnum = interest.acnum
FULL OUTER JOIN field
ON interest.fieldnum = field.fieldnum

2
SQL QUERIES
WHERE department.state = 'VIC' and field.title = 'Software Engineering' and rownum = 1
GROUP BY field.title, department.deptnum, department.deptname, department.instname
ORDER BY COUNT(field.fieldnum) DESC;
Answer to question 2.6
SELECT title, count(*) as abs
FROM field
GROUP BY title
HAVING COUNT(*) < 10;
Answer to questions 2.7
SELECT panum, title
FROM paper
NATURAL JOIN author
WHERE EXISTS (SELECT panum
FROM author
NATURAL JOIN academic
WHERE EXISTS (SELECT acnum
FROM academic
NATURAL JOIN department
WHERE state = 'VIC')

3
SQL QUERIES
)
GROUP BY Panum, title
Answer to question 2.8
CREATE VIEW PAPER_VIEW AS
SELECT paper.panum, paper.title,COUNT(*) TotalCount
FROM paper
INNER JOIN author
ON paper.panum = author.panum
GROUP BY paper.panum, paper.title
Answer to question 2.9
The provided query is so as it would select all the numbers that are starting with B.2. and
does not restrict the length of the string. The correct query for the required solution is provided
below:
SELECT fieldnum, SUBSTR(Id, 1, 5), Title
FROM field
WHERE Id like 'D.2.%'
Answer to question 2.10
The group function is nested too deeply in the query. The corrected query for the
required solution is:

4
SQL QUERIES
SELECT department.deptnum,department.deptname, department.instname, COUNT
(academic.acnum)
FROM academic
INNER JOIN department
ON academic.deptnum = department.deptnum
WHERE rownum =1
GROUP BY department.deptnum, department.deptname, department.instname
ORDER BY (COUNT(academic.acnum)) DESC;