SQL Queries Assignment: Database Query Solutions and Analysis

This assignment solution provides answers to a series of SQL queries, addressing various database challenges. The document includes solutions to specific questions, such as selecting data based on criteria, joining tables, and using aggregate functions. It demonstrates the use of SQL commands like SELECT, FROM, WHERE, GROUP BY, and ORDER BY. The solution covers the creation of views and includes code for filtering data based on specific conditions. Furthermore, the assignment addresses common query errors and offers corrected solutions, highlighting the importance of correct syntax and logical structure in SQL queries. The document also includes explanations for the queries, providing valuable insights into database management and SQL syntax.

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;