SQL Exercises

SQL Exercises using the PostgreSQL DBMS

PostgreSQL has a venerable history; it shares origins with the Ingres database project at the University of California,
Berkeley, which was launched in 1973. In 1985, a new project (called “post-Ingres”, but soon shortened to Postgres)
was started at Berkeley to address problems with the DBMS systems available at the time. That project was maintained
until 1993 when it was cancelled due to overwhelming popularity – it became too much effort to maintain. The source
code for Postgres was then released as open-source. Two Berkeley graduates picked up the source, added a SQL
interpreter, and released Postgres95. In 1996, the project was re-named PostgreSQL, and thus began a steady increase
in popularity that continues today.

Whilst not as well-known or popular among open-source DBMSes as MySQL, PostgreSQL has earned a reputation for
providing extensive functionality, for being at the cutting edge of SQL language innovations, and for being stable,
reliable, and having performance figures very close to that of the “big iron” industrial-strength Oracle DBMS.

PostgreSQL only communicates with the outside world via SQL. SQL goes in; query results (and sometimes error
messages) come out. It is fairly typical to manage the PostgreSQL DBMS and define new databases via SQL
commands entered at a command-line prompt. Whilst graphical and visual DBMS administration tools do exist for
almost every popular DBMS, they tend to be expensive and are frequently eschewed by database purists, who prefer
the absolute control they can only get from a command-line environment.

Therefore, in these practical exercises, you will explore SQL via the command-line.

There are two ways to use the PostgreSQL command-line with the PostgreSQL DBMS on our Computing server. You
can use a desktop database administration application called pgAdmin – which is available on-campus – or a Web-
based administration tool called phpPgAdmin. PhpPgAdmin is installed on our Computing server.

Please note that due to security restrictions, pgAdmin can only be used on-campus from the campus wired
network. It won’t work with the Computing server via Wi-Fi, and it won’t work with the Computing server
outside the university, even if you download pgAdmin (it’s free).

If you wish to use pgAdmin, read the Using_pgAdmin.doc document, then come back here to do the exercises.

If you wish to use phpPgAdmin, read the Using_phpPgAdmin.doc document, then come back here to do the exercises.

If you like, you can use pgAdmin in the labs and use phpPgAdmin from home. Since your work is stored in your
PostgreSQL database, you can access it either way.

The following exercises will help you develop familiarity with SQL in general, and with the PostgreSQL DBMS in
particular. You’ll almost certainly want to refer to the PostgreSQL documentation, which can be found at
http://www.postgresql.org/docs/9.5/interactive/index.html In particular, you should refer to the “The SQL Language”
chapter at http://www.postgresql.org/docs/9.5/interactive/sql.html

1
SQL Exercises

At this point, it is assumed that you have read either Using_pgAdmin.doc or

Using_phpPgAdmin.doc to find out how to obtain an account, set up the
demonstration database from the DEMOBLD.SQL script, and learn how to
enter SQL queries.

If not, please return to and re-read the relevant document.

PLEASE NOTE the format of these exercises, particularly the

section headings:

The first is “Reading”. It contains examples which are for

information only. You do not type them in! Just read
them.

“Reading” is followed by “Exercises”. It contains

numbered exercises for you to do.

Note: All changes you make to your database are saved as soon as you make them.
There is no need for explicit “save” or “load” operations. This is one of the benefits
of using a database!

2
SQL Exercises

Reading
SQL DATA DEFINITION

Creating Tables (just read this.)

We create tables using the CREATE TABLE command.

We remove tables using the DROP TABLE command.

Here are some examples of these commands. (Don’t type these in.)

CREATE TABLE STOCK

(STOCKNO NUMERIC(10,0) NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR(30),
SLEVEL NUMERIC(5,0));

CREATE TABLE HORSE

(NAME CHAR(30) NOT NULL PRIMARY KEY,
DATE_OF_BIRTH TIMESTAMP NOT NULL,
WEIGHT NUMERIC(8,2),
HEIGHT NUMERIC(4,2));

DROP TABLE STOCK;

DROP TABLE HORSE;

Note that for each column we create in a table, we must specify its data type.
Data types include NUMERIC, TIMESTAMP, DATE, CHAR, TEXT, and VARCHAR.

We may also specify that it is the primary key for the table.

A column may also be specified as NOT NULL. This means that every row in the resulting table must have a value for
this column.

We can add columns using ALTER TABLE e.g.

ALTER TABLE HORSE

ADD RIDER VARCHAR(20);

If you type in the command DROP TABLE HORSE;

It will immediately delete the table and all data in it (be careful with this command!) This should bring up the message
'Table Dropped' or an error message stating it does not exist.

Now try the following exercises:

Exercises
Exercise 1 (do these)

Add to the demo database a table EMP_ADDR that has the following address information. Apart from the
APT and POSTCODE columns, none of the columns may have a null value. EMPNO is to be the primary
key.

3
SQL Exercises

Here is a list of columns and data types for the table:

Employee Number: EMPNO NUMERIC(8,0)

Street Address: ADDR VARCHAR(19)
Apartment number: APT VARCHAR(4)
City or Town: CITY VARCHAR(14)
County: COUNTY VARCHAR(2)
Post Code: POSTCODE VARCHAR(7)

NOTE: column names should not have spaces in them!

Exercise 2

Use the ALTER TABLE command to add a new column CAR_RT which is 6 characters wide and may accept null
values.

4
SQL Exercises

Reading
SQL DATA MANIPULATION

Insert, Update and Delete

These commands allow data to be added to, altered or deleted from tables.

Here are some examples (don’t type them in):

INSERT creates new rows, one at a time:

INSERT INTO STOCK

VALUES (1, 'Boots', 250);

INSERT INTO STOCK (STOCKNO, SLEVEL)

VALUES (2, 650);

INSERT INTO STOCK (STOCKNO, DESCRIPTION)

VALUES (3, 'Belts');

Note that in the first command, we have not specified the columns under which the data is to be inserted.
We can only do this when inserting a specified value for every column.

With UPDATE, we can alter or add values for rows that have already been INSERTed.

UPDATE STOCK
SET SLEVEL = 300
WHERE STOCKNO = 1;

UPDATE STOCK
SET DESCRIPTION = 'Trousers'
WHERE STOCKNO = 2;

UPDATE STOCK
SET SLEVEL = 100
WHERE DESCRIPTION = 'Belts';

UPDATE STOCK
SET SLEVEL = SLEVEL + 100
WHERE STOCKNO < 3;

DELETE removes rows from a table.

DELETE FROM STOCK

WHERE STOCKNO = 2;

5
SQL Exercises

Exercises
Exercise 3

Use the INSERT command to enter up the following rows into the table EMP_ADDR:

EMPNO ADDR APT CITY COUNTY POSTCODE CAR_RT

------------------------------------------------------------------------------------------------------------------------------
7788 4207 Highbridge Rd Boston MA 02198
7839 110 Tower City 11B Brooklyn NY 11212
7369 12 Elk Court Madison WI 53701

You can check your result by using the command:

SELECT * FROM EMP_ADDR;

If your result is incorrect, then use the UPDATE and DELETE commands to achieve the desired outcome.

Exercise 4

Use the UPDATE command to set a value 'CR05' for the column CAR_RT for the employee 'Smith', to give the
following table:

EMPNO ADDR APT CITY COUNTY POSTCODE CAR_RT

------------------------------------------------------------------------------------------------------------------------------
7788 4207 Highbridge Road Boston MA 02198
7839 110 Tower City 11B Brooklyn NY 11212
7369 12 Elk Court Madison WI 53701 CRO5

6
SQL Exercises

Reading
Querying Data in Tables

The SELECT command is used for retrieving and displaying data in tables.

Here are some examples of this command. Can you explain what each of them do?

SELECT * FROM EMP;

SELECT ENAME FROM EMP;

SELECT JOB FROM EMP;

SELECT DISTINCT JOB FROM EMP;

SELECT ENAME, JOB, DEPTNO FROM EMP;

SELECT ENAME FROM EMP WHERE DEPTNO = 20;

SELECT ENAME FROM EMP WHERE SAL > 1000;

SELECT ENAME, DEPTNO FROM EMP WHERE SAL < 1000;

SELECT DISTINCT JOB, SAL FROM EMP

WHERE SAL < 1000;

SELECT ENAME FROM EMP

WHERE HIREDATE BETWEEN '01-JAN-81' AND '01-JUN-81';

SELECT ENAME FROM EMP WHERE JOB = 'CLERK' OR JOB = 'ANALYST';

SELECT DEPTNO FROM DEPT

WHERE DNAME = 'ACCOUNTING' AND LOC = 'NEW YORK';

SELECT * FROM STOCK WHERE SLEVEL IS NULL;

SELECT * FROM STOCK WHERE SLEVEL IS NOT NULL;

SELECT * FROM DEPT

WHERE LOC LIKE 'N%';

SELECT * FROM DEPT

WHERE LOC LIKE '%N';

SELECT DNAME, LOC FROM DEPT

ORDER BY LOC;

SELECT DNAME, LOC FROM DEPT

ORDER BY DNAME;

7
SQL Exercises

Exercises
Exercise 5

The DEPT table has 3 columns. List the table using '*' to indicate all of the columns, without row ordering to give the
following result:

DEPTNO DNAME LOC

--------------------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Exercise 6

Achieve the same result as the previous exercise; only specify all of the columns in your SELECT command. Take care
to spell the names of the columns correctly!

Exercise 7

To make the report easier to read, have the DEPTNO column repeated on the right hand side of the screen. List the
DEPT table as in Exercise 6, but repeat the DEPTNO column after LOC to give the following result:

DEPTNO DNAME LOC DEPTNO

------------------------------------------------------------------
10 ACCOUNTING NEW YORK 10
20 RESEARCH DALLAS 20
30 SALES CHICAGO 30
40 OPERATIONS BOSTON 40

Exercise 8

In SQL, individual search conditions contained the WHERE clause such as SAL = 2000 are known as predicates.
When a predicate is TRUE for a given row or set of rows, then that row or set of rows is returned. Those rows for
which the predicate is FALSE are not returned.

Write the SELECT command that will return the rows from the DEPT table where the name of the department is
'SALES' i.e.

DEPTNO DNAME LOC

-----------------------------------------------
30 SALES CHICAGO

Exercise 9

Suppose we had the request "Show me information on Clerks who were hired on or after May 1st 1981". We need to
translate this into an SQL query. The relevant information is in the EMP table.

We need to query this table using two predicates, one to find anyone is a clerk and the other to find anyone hired on or
after May 1st 1981.

We combine predicates in the WHERE clause using the AND and OR operators.

 We use AND when all of the combined predicates must be TRUE.

 We use OR when any of them may be TRUE.

8
SQL Exercises

The desired result for this particular query should be:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------------------------------------------------------------------------------------------------------------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

Exercise 10

Let's try a more difficult question: "Which employees in department 20 are clerks or have a salary of at least 1500?"

We now have 3 different predicates to combine : a department of 20, a job of CLERK and a salary >= 1500. The first
one must be true for all rows returned, either the second or the third must also be true for all rows returned. This means
we must use AND and OR in combining our predicates.

When AND and OR are used in the same WHERE clause, AND expressions are evaluated before OR expressions. We
can use parentheses to force a different order of evaluation, in other words by placing a parenthesis around the OR part
of a WHERE clause we can force the OR to be evaluated before the AND e.g.

A AND B OR C ---> A AND B is evaluated first, and the result of this is combined
with C using OR
A AND (B OR C) ---> B OR C is evaluated first, and the result of this is combined
with A using AND.

The result of your SELECT command should return the following table:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------------------------------------------------------------------------------------------------------------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20

Exercise 11

To put the rows of EMP into some useful sequence down the page or screen, list the employee name, job and manager
columns of EMP, and sequence the result by employee name. (Hint: use the ORDER BY clause)

The desired result would be:

ENAME JOB MGR

--------------------------------------------------
ADAMS CLERK 7788
ALLEN SALESMAN 7698
BLAKE MANAGER 7839
CLARK MANAGER 7839
FORD ANALYST 7566
JAMES CLERK 7698
JONES MANAGER 7839
KING PRESIDENT
MARTIN SALESMAN 7698
MILLER CLERK 7782
SCOTT ANALYST 7566
SMITH CLERK 7902
TURNER SALESMAN 7698
WARD SALESMAN 7698

9
SQL Exercises

Exercise 12

Rows can be sequenced in reverse order, Show the employee name, job and salary columns of EMP in order, with the
highest salary first.

The desired result:

ENAME JOB SAL

-------------------------------------------------
KING PRESIDENT 5000
SCOTT ANALYST 3000
FORD ANALYST 3000
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
ALLEN SALESMAN 1600
TURNER SALESMAN 1500
MILLER CLERK 1300
WARD SALESMAN 1250
MARTIN SALESMAN 1250
ADAMS CLERK 1100
JAMES CLERK 950
SMITH CLERK 800

Column Expressions

As well as retrieving simple columns from table, in SQL a column may be composed of an expression. Here are some
examples of expressions that perform computations:

SELECT SAL, SAL*10 AS SAL_TIMES_TEN

FROM EMP;

SELECT SAL, SAL*COMM/10 AS SAL_FACTOR

FROM EMP;

Exercise 13

Produce a report that shows each employee's name, their job, salary, commission and total earnings. Total earnings are
the salary plus the commission. The desired result should be:

ENAME JOB SAL COMM TOTAL

----------------------------------------------------------------------------------
KING PRESIDENT 5000
BLAKE MANAGER 2850
CLARK MANAGER 2450
JONES MANAGER 2975
MARTIN SALESMAN 1250 1400 2650
ALLEN SALESMAN 1600 300 1900
TURNER SALESMAN 1500 0 1500
JAMES CLERK 950
WARD SALESMAN 1250 500 1750
FORD ANALYST 3000
SMITH CLERK 800
SCOTT ANALYST 3000
ADAMS CLERK 1100
MILLER CLERK 1300

Note how many of the rows will not have a total earnings value under SAL+COMM. This is because these rows have a
NULL value for COMM. In SQL, when two column values participate in an expression, if either of them has the value
NULL, then the result will be NULL.
(How could we overcome this?).

10
SQL Exercises

Exercise 14

Let's refine Exercise 13. For those who are eligible for a commission (i.e. those whose COMM value is not NULL),
add a column what shows what percentage of their total earnings is commission. The formula is 100 * (COMM / (SAL
+ COMM))

The result should be as follows:

ENAME JOB SAL COMM TOTAL COMMISSION

----------------------------------------------------------------------------------------------------------------------------
ALLEN SALESMAN 1600 300 1900 15.7895
WARD SALESMAN 1250 500 1750 28.5714
MARTIN SALESMAN 1250 1400 2650 52.8302
TURNER SALESMAN 1500 0 1500 0

11
SQL Exercises

Reading
Aggregate functions

It is possible to show the result of a function applied to a column. Here are some examples of aggregate functions:

SELECT MAX(ENAME) FROM EMP;

SELECT MIN(SAL) FROM EMP;

SELECT AVG(SLEVEL) FROM STOCK;

Exercises
Exercise 15

From the EMP table, produce a report that lists:

 the average salary AVG (SAL),
 the average commission AVG (COMM)
 the average total earnings AVG(SAL+COMM), and
 the difference between the average total earnings and the average commission AVG(SAL+COMM) –
AVG(COMM)

The result should look like this:

AVGSAL AVGCOMM AVGTOTAL AVGDIFF

------------------------------------------------------------------------------------------------------------------------
2073.21 550 1950 1400

12
SQL Exercises

Reading
Retrieving data from multiple tables

Often we need to generate a report that uses data from more than one table. If we wished to show an employee's name
and address, we would need to join the two tables thusly:

SELECT EMP.EMPNO, ENAME, ADDR, APT, CITY, COUNTY, POSTCODE

FROM EMP, EMP_ADDR
WHERE EMP.EMPNO = EMP_ADDR.EMPNO;

This query shows us employee information from the EMP table combined, or joined, with address information from the
EMP_ADDR table, wherever the employee number in EMP matches the employee number in EMP_ADDR.

In other words, it looks up address information for employees by employee number. Or, looking at it another way, it
looks up employee information for addresses by employee number.

Exercises
Exercise 16

Write the command to display, for the employee James, his name, department number and department name. Like this:

ENAME DEPTNO DNAME

--------------------------------------------
JAMES 30 SALES

Exercise 17

A join can be used for constraining data for one table. Here is a join that displays EMP data for employees who live in
BOSTON:

SELECT ENAME, JOB

FROM EMP, EMP_ADDR
WHERE EMP.EMPNO = EMP_ADDR.EMPNO
AND CITY = 'BOSTON';

Write a query that retrieves a list of all employees whose department is located in Chicago thus:

ENAME JOB
--------------------------------------
ALLEN SALESMAN
BLAKE MANAGER
TURNER SALESMAN
JAMES CLERK
MARTIN SALESMAN
WARD SALESMAN

13
SQL Exercises

Reading
Nested queries

When a join only displays columns that come from a single table, it can be written as a nested query. The previous
result could be obtained using this command:

SELECT ENAME, JOB

FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO FROM DEPT WHERE LOC = 'CHICAGO');

We would use the IN operator where the result of the nested query could return a set of values (i.e. there could be more
than one DEPT with a LOC of Chicago).

We can use the '=', '<', '>' when we know that the nested query will yield exactly one value. This will always be the
case when an aggregate function is used. For example:

SELECT DESCRIPTION FROM STOCK

WHERE LEVEL > (SELECT AVG(LEVEL) FROM STOCK);

Exercises
Exercise 18

Write a command that will yield a list of names, salaries and departments for those employees whose salary is below
the average for all employees:

ENAME SAL DEPTNO

----------------------------------------------
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
MARTIN 1250 30
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
MILLER 1300 10

Exercise 19

Write the command that shows which analysts earn more than the average salary for managers. The result should be:

ENAME SAL DEPTNO

---------------------------------
SCOTT 3000 20
FORD 3000 20

Views

A view is a logical table that is based on one or more tables in the database.
Here is a view for all employees in DEPT 20:

CREATE VIEW DEPT_20 AS

SELECT * FROM EMP WHERE DEPTNO = 20;

This view can now be used in queries as if it were a table. For example:

14
SQL Exercises

SELECT * FROM DEPT_20;

SELECT * FROM DEPT_20

WHERE JOB = 'MANAGER';

A view may be built over a join e.g.

CREATE VIEW SALES_DEPARTMENT AS

SELECT ENAME, JOB, SAL, COMM, LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND DNAME = 'SALES';

Exercise 20

Create a view called EMP_NAD that includes the EMPNO, NAME, ADDRESS, APT, CITY, COUNTY and
POSTCODE for all employees who have an entry in the EMP_ADR table.

Test this view with the command SELECT * FROM EMP_NAD;

This should give the result:

EMPNO ENAME ADDR APT CITY COUNTY POSTCODE

----------------------------------------------------------------------------------------------------------------------------
7788 SCOTT 4207 Highbridge Rd Boston MA 02198
7839 KING 110 Tower City 11B Brooklyn NY 11212
7369 SMITH 12 Elk Court Madison WI 53701

END

15