Page 1 of 4
Worksheet – SQL Part 1 – Table Creation
1. Draw the table that would be created if the following statement were issued:
CREATE TABLE ucfsxxx.STUDENTS
(STUDENT_PRN NUMERIC(10,0) NOT NULL,
SURNAME CHARACTER VARYING(15) NOT NULL,
NAME CHARACTER VARYING(20) NOT NULL,
DATE_OF_BIRTH DATE,
TERM_ADDRESS CHARACTER VARYING(100),
HOME_ADDRESS CHARACTER VARYING(100)
);
2. Write out the table creation script for the following table (don’t forget the
schema)
LECTURE_TOPICS
LECTURE_RE
F
TOPIC_
TITLE
DEPARTMEN
T
FACULTY LECTURER_NAM
E
LECTURER_SURNAM
E
GEOM1030 IT and
Spatial Data
Structures
Geomatic
Engineering
Engineerin
g
Jeremy Morley
GEOM1040 Spatial Data
Managemen
t and
Display
Geomatic
Engineering
Engineerin
g
Muki Haklay
Note that we have made a lot of assumptions as to the maximum length of the data in
each field – these should be documented somewhere and ideally validated with far
more data than we have here.
3. Identify the errors in the following CREATE TABLE statement (there are 6):
CREATE TABLE ucfsxxx.TUBE_FARES
START_ZONE CHARACTER VARYING(100),
END_ZONE NUMERIC(5,2),
FULL_FARE CHARACTER VARYING(20),
STUDENT_FARE NUMERIC(3,2)
CHILD_FARE NUMERIC(3,2),)
Page 2 of 4
Worksheet – SQL Part 2 – Constraints
LECTURE_TOPICS
LECTURE_RE
F
TOPIC_
TITLE
DEPARTMEN
T
FACULTY LECTURER_NAM
E
LECTURER_SURNAM
E
GEOM1030 IT and
Spatial Data
Structures
Geomatic
Engineering
Engineerin
g
Jeremy Morley
GEOM1040 Spatial Data
Managemen
t and
Display
Geomatic
Engineering
Engineerin
g
Muki Haklay
LECTURE_TIMETABLE
TIME_TABLE_
REF
LECTURE_REF ROOM_ID
DAY START_TIME END_TIME Hours
1 GEOM1030 B14 Wednesday 10:00 12:00 2
2 GEOM1040 B14 Friday 09:00 11:00 2
3 GEOM1040 GE Cluster Friday 14:00 16:00 2
4 GEOM1030 GE Cluster Tuesday 14:00 16:00 2
1. Write the SQL script that will create LECTURE_REF as the primary key for the
LECTURE_TOPICS table shown above.
2. Write the script that will create TIME_TABLE_REF as the primary key for the
LECTURE_TIMETABLE table shown above.
3. Write the script that will create LECTURE_REF as a foreign key in
LECTURE_TIMETABLE, relating to the primary key in LECTURE_TOPICS.
Page 3 of 4
Worksheet – SQL Part 3 – Data Manipulation Language
4. Write a script to insert the following values into the table created above.
LECTURE_TOPICS
LECTURE_RE
F
TOPIC_
TITLE
DEPARTMEN
T
FACULTY LECTURER_NAM
E
LECTURER_SURNAM
E
GEOM1030 IT and
Spatial Data
Structures
Geomatic
Engineering
Engineerin
g
Jeremy Moreley
GEOM1040 Spatial Data
Managemen
t and
Display
Geomatic
Engineering
Engineerin
g
Muki Haklay
5. Write a script to change the name of the Geomatic Engineering department to
‘Geomatic and Spatial Engineering’.
6. Write a script to correct the spelling of Jeremy’s surname to Morley.
7. Write a script to delete all ‘Geomatic and Spatial Engineering’ lectures from
the above table.
Worksheet – SQL Part 4 – Queries
LECTURE_TOPICS
LECTURE_R
EF
TOPIC_
TITLE
DEPARTMENT FACULTY LECTURER_NA
ME
LECTURER_SURNA
ME
GEOM1030 IT and
Spatial
Data
Structures
Geomatic
Engineering
Engineeri
ng
Jeremy Morley
GEOM1040 Spatial
Data
Manageme
nt and
Display
Geomatic
Engineering
Engineeri
ng
Muki Haklay
GEOM1000 Introductio
n to UCL
University
Undergradua
te
Group
None Joan Smith
Page 4 of 4
LECTURE_TIMETABLE
TIME_TABLE_
REF
LECTURE_REF ROOM_ID
DAY START_TIME END_TIME Hours
1 GEOM1030 B14 Wednesday 10:00 12:00 2
2 GEOM1040 B14 Friday 09:00 11:00 2
3 GEOM1040 GE
Cluster
Friday 14:00 16:00 2
4 GEOM1030 GE
Cluster
Tuesday 14:00 16:00 2
1. Write the query that will select all the rows from the LECTURE_TOPICS table
2. Write the query that will select data for the LECTURE_REF, TOPIC_TITLE AND
LECTURE_NAME columns from the LECTURE_TOPICS table.
3. Write the query that will generate the set containing all lecturer names and all
lecturer surnames.
4. Write the query that will find the total number of hours spent in class.
5. Write the query that will find the total number of hours spent in class per
subject.
6. Write a query (nested / sub query) to show how many hours Jeremy lectures
each week