程序代写代做代考 SQL Page 1 of 4

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