CSCI235 Assignment 3

This assignment is 5% of total marks in this course. The tasks for this assignment cover the implementation of queries, self join queries, outer join queries, nested queries, queries with existential quantifiers, queries with negated existential quantifiers, and queries with ANY and ALL clauses.

You can download a3create.sql and a3drop.sql from SIM student portal. Execute a3create.sql to create the sample relational database (a collection of relational tables to be precised) and loads sample data into the database. A script file a3drop.sql contains DROP TABLE statement to remove the relational tables created by a3create.sql. Ready? Let's start!

There are total of 10 tasks and basically it is about extracting required data from database using SELECT statement. I will pick up some example from the assignment and demonstrate it to you. Of course, you need to understand the SQL statement well enough to perform this assignment tasks.

Example 1:
--Implement a query as SELECT statement with self JOIN operation to find the names (NAME) of all employees older than Frederic Jones.
SQL>SELECT e1.name, e1.dob FROM employee e1 JOIN employee e2
ON e1.dob>e2.dob WHERE e2.name='Frederic Jones';

Example 2:
--Implement a query as SELECT statement with NATURAL JOIN operation to find the employee numbers(E#) of all drivers who visited Perth at least one time.
SQL>SELECT DISTINCT e# FROM driver NATURAL JOIN tripleg WHERE
departure='Perth' OR destination='Perth';

Example 3:
--Implement a query as SELECT state to find the names of all drivers who never visited Rockhampton.
SQL>SELECT name "Employee Name" FROM employee WHERE e# IN
(
SELECT e# FROM driver WHERE l# IN
(
SELECT l# FROM trip WHERE t# IN
(
SELECT t# FROM tripleg WHERE departure!='Rockhampton' OR
destination!='Rockhampton'
)
)
);

Well. Just master the SQL command and this assignment will be easy task for you.