This won’t be that last we will see of PostgreSQL in the course
As usual, we will be working with the COMPANY database through our examples, so we will need to create a new database for practical session 3 and load up the data from our week 1 database:
Create a backup of your prac_01 database using the pg_dump utility:
[mwelch8@turing ~]$ pg_dump mwelch8_prac_01 > ~/prac_01.sql
\i command from within the psql client:
[mwelch8@turing ~]$ createdb mwelch8_prac_02
[mwelch8@turing ~]$ psql mwelch8_prac_02
psql (9.4.4)
Type "help" for help.
mwelch8_prac_02=> \i ~/prac_01.sql
...
mwelch8_prac_02=> \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+---------
public | department | table | mwelch8
public | dependent | table | mwelch8
public | dept_locations | table | mwelch8
public | employee | table | mwelch8
public | project | table | mwelch8
public | works_on | table | mwelch8
(6 rows)
Throughout this practical session, you should run each of the example queries and review the results returned from the COMPANY database.
Now you are ready for the prac…
WHERE clause), the result is considered to be UNKNOWN.
For example:
In SQL the NULL value can be tested for using the IS and IS NOT operators. Here is an example for you to try on your COMPANY database:
-- Return all employs who do not have supervisors
SELECT fname,lname
FROM EMPLOYEE
WHERE Superssn IS NULL
-- Return all employs who do have supervisors
SELECT fname,lname
FROM EMPLOYEE
WHERE Superssn IS NOT NULL
SELECT queries have only involved a single level (i.e. they only contain a single WHERE clause)WHERE clause of another query.IN and ALL operators:
-- Returns a list of project numbers that involves
-- an employ with last name 'Smith' as a manager or a worker
SELECT DISTINCT pnumber
FROM project
WHERE pnumber IN
(SELECT pnumber
FROM project, department, employee
WHERE dnum = dnumber AND mgrssn=ssn and lname = 'Smith')
OR
pnumber IN
(SELECT pnumber
FROM works_on, employee
WHERE essn=ssn AND lname='Smith');
= operator for caparison in the outer query
SELECT DISTINCT essn
FROM works_on
WHERE (pno,hours) IN
(SELECT pno,hours
FROM works_on
WHERE essn = '123456789')
ALL operator we can can compare a single value (usually and attribute) to a multi-set of values returned from a nested query:
-- Return all employees with a salary greater
-- than all employees in department 5
SELECT fname,lname
FROM EMPLOYEE
WHERE salary > ALL
(SELECT salary
FROM employee
WHERE dno = 5);
SELECT E.fname, E.lname
FROM employee AS E
WHERE E.ssn IN
(SELECT essn
FROM dependent AS D
WHERE E.fname = D.dependent_name AND E.sex = D.sex);
EXISTS function in SQL is used to check whether the result of a correlated nested query is empty.EXISTS function is the boolean value true if the nested query result contains at least one tuple (FALSE if none are returned).
SELECT e.fname, e.lname
FROM employee AS E
WHERE EXISTS
(SELECT *
FROM dependent AS D
WHERE E.ssn = D.essn AND
E.sex = D.sex AND
E.fname = D.dependent_name)
EXISTS can be negated using the NOT OPERATOR:
-- Retrieve the names of employees who do
-- not have dependents.
SELECT fname, lname
FROM employee
WHERE NOT EXISTS
(SELECT *
FROM dependent
WHERE ssn=essn);
-- Retrieve the names of employees who do
-- have dependents.
SELECT fname, lname
FROM employee
WHERE EXISTS
(SELECT *
FROM dependent
WHERE ssn=essn);
In the previous examples, if the nested query returns any tuples, the EXISTS function will return true. This is negated by the NOT clause.
This query can be extended to name the managers with at least one dependent:
SELECT fname, lname
FROM employee
WHERE EXISTS
(SELECT *
FROM dependent
WHERE ssn=essn)
AND
EXISTS
(SELECT *
FROM department
WHERE ssn = mgrssn)
In this example we have two nested queries - If there is at lest one in the first and at least one in the second, the employee tuple will be selected.
The final construct we will look at that uses universal quantification. An example of such a query:
-- Retrieve the name of each employee who works on all
-- projects by department 4
SELECT lname, fname
FROM employee
WHERE NOT EXISTS
(SELECT *
FROM works_on B
WHERE (B.pno IN
(SELECT pnumber
FROM project
where dnum=4)
AND
NOT EXISTS
(SELECT *
FROM works_on C
WHERE C.essn = ssn AND C.pno = B.pno)));
SELECT fname,lname,address
FROM (employee JOIN department ON dno=dnumber)
WHERE dname='Research';
-- Original syntax with join through the
-- where clause
SELECT fname, lname address
FROM employee, department
WHERE dno=dnumber AND dname='Research';
SELECT fname, lname, address
FROM (employee NATURAL JOIN (department AS DEPT(dname,dno,mssn,msdate)))
WHERE dname='Research'
SELECT e.lname AS Employee_Name, s.lname AS Supervisor_name
FROM (employee AS e LEFT OUTER JOIN employee as s ON e.superssn=s.ssn);
In SQL construct following queries on the COMPANY database:
a. Retrieve the first name and last name of all employees who do not work on the project with name ‘ProjectZ’
b. Retrieve the first name and last name of all employees that work on a project that ‘John Smith’ works on.
c. List the project names of all projects that have least one employee working on them who are supervised by ‘John James’.
d. List names of employees that only work on the ‘ProductX’ project.
Using the student database that you constructed in exercise 2 of last weeks practical, implement the following SQL queries.
a. Retrieve the names and major departments of all straight-A students (students who have a grade of A in all their courses). b. Retrieve the names and major departments of all students who do not have any grade of A in any of their courses.
Returning to the COMPANY database, use aggregation to construct the following nested queries:
a) Suppose we want the number of male employees in each department rather than all employees (as in part a). Can we specify this query in SQL? Why or why not?
b) A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it.