()
CO526 Databases Course Work 1: SQL
Due in noon Friday 16th February 2018
The tables below gives details of a family history database. In the person table, people are
identified by their name, and always have their gender, date of birth (dob) and place of birth
(born in) recorded. In addition, each person may optionally have recorded the name of their
father, and the name of their mother. If the person has died, then the date of death dod must be
present. Note that only a fragment of the data held in the database is listed below.
person
name gender dob dod? father? mother? born in
Alice F 1885-02-25 1969-12-05 null null Windsor
Andrew M 1960-02-19 null Philip Elizabeth II London
Andrew of Greece M 1882-02-02 1944-12-03 George I of Greece null Athens
Anne (Princess) F 1950-08-15 null Philip Elizabeth II London
Charles M 1948-11-14 null Philip Elizabeth II London
.
..
person(father)
fk
⇒ person(name) person(mother)
fk
⇒ person(name)
In addition, there is a table monarch which contains the English head of state — normally a
monarch (i.e. a King or Queen) — where the house of each monarch indicates which royal house
the monarch belongs to, accession indicates the date the person came to the throne, and coronation
the date any coronation of the monarch. If null appears in coronation then the person had no
coronation. Each monarch remains head of state until the succession of the next monarch. Note
that the value of null appearing in house indicates not a King or Queen, but a head of state (for
example Oliver Cromwell) who filled the role of Protector during the Commonwealth period in
the 17th Centuary.
monarch
name house? accession coronation?
James I Stuart 1603-03-24 1603-07-25
Charles I Stuart 1625-03-27 1626-02-02
Oliver Cromwell null 1649-01-30 null
Richard Cromwell null 1658-09-03 null
Charles II Stuart 1659-05-25 1626-02-02
James II Stuart 1685-02-06 1685-04-23
…
monarch(name)
fk
⇒ person(name)
Finally, there is a table prime minister, recording the party the person led whilst Prime Minister,
and the date of entry into office. A person remains Prime Minister until the date of entry to office
of the next Prime Minister. Note a person may have more than one period in office.
prime minister
name party entry
David Cameron Conservative 2010-05-11
Gordon Brown Labour 2007-06-27
Tony Blair Labour 1997-05-02
John Major Conservative 1990-11-28
Margaret Thatcher Conservative 1979-05-04
James Callaghan Labour 1976-04-05
Harold Wilson Labour 1974-03-04
Edward Heath Conservative 1970-06-19
…
prime minister(name)
fk
⇒ person(name)
Submission
To gain full marks, answers to the following questions should make full use of ANSI SQL commands
to write compact and efficient queries, and be laid out such that structure of the query is clear.
The queries must also run correctly on the Postgres version of the database, and be submitted
electronically to CATE as single batch file db 2018 cw1.sql by the coursework deadline. A template
version of the file is available on CATE for download. The queries in the file must be given in the
order of the questions below, with the comment present in the template file left unchanged, and
the query terminated by a single semi-colon.
To test your answer against the Postgres version of the database, you should run the command:
p s q l −h db . doc . i c . ac . uk −d f am i l y h i s t o r y −U l ab −W −f db 2018 cw1 . s q l
Note that 60% of the marks will be awarded for correctness, and 40% of the marks for style,
including efficiency, how concise the queries are, appropriate use of indentation, use of Capital
letters for keywords, and expressing join conditions by use of JOIN statements in the FROM
clause as opposed to using equals in the WHERE clause.
Questions
The first four questions test knowledge of SQL as an implementation of the Relational Algebra,
and the last four questions test knowledge of SQL as a Programming Language.
Style marks to apply once over the whole exercise
A Loose 4 marks if more than one instance of using commas to sepate tables in FROM clause,
rather than using JOIN. If no use or only one use is made of JOIN, then loose 8 marks.
B Loose 8 marks if all keywords in lower case.
C Loose 8 marks if no use of indentation is made, loose 4 marks if inconsistent use of
indentation is made
D Loose 4 marks for any solution that uses temporary tables when the temporary table is used
only once. Using temporary tables prevents query optimisation between the temporary table
and the main query. CTE (WITH) statements can be used.
Style marks to apply once per query
E Loose 3 marks each time an unnessary subquery is used (ie where instead the same result
could be achieved by joining inside the same SELECT, or where the result of a subquery does
not require processing by the outer query).
Correctness marks to apply once per query
F Loose 5 marks for each query that does not run due to syntax errors
G Loose 2 marks for each question that omits an ORDER BY
H Loose 3 marks for each missing WHERE or HAVING condition
I Loose 3 marks for each question failing to return columns in order requested by question, or
failing to return the requested columns.
1. Write an SQL query that returns the scheme (name,father,mother) ordered by name contain-
ing the name of all people known to have died before both their father and mother, together
with the name of the mother and the name of the father.
SELECT pe r son . name ,
f a t h e r . name AS fa t h e r ,
mother . name AS mother
FROM per son
JOIN pe r son AS f a t h e r
ON pe r son . f a t h e r=f a t h e r . name
JOIN pe r son AS mother
ON pe r son . mother=mother . name
WHERE pe r son . dod
AND per son . dod>next monarch . a c c e s s i o n )
AND monarch . house IS NOT NULL
ORDER BY monarch . name
12
L Loose 3 marks for listing Mary II who reigned jointly with William III, and died before
William III (and thus did not abdicate, despite the next monarch being after her death).
4. Write a query that returns the scheme (house,name,accession) ordered by accession that lists
house and name of monarchs who were the first of a house to accede to the throne. Maximum
marks will be given only to answers that use either the ALL or SOME operators.
SELECT monarch . house ,
monarch . name ,
monarch . a c c e s s i o n
FROM monarch
WHERE monarch . a c c e s s i o n<=ALL (SELECT l a t e r mona r ch . a c c e s s i o n
FROM monarch AS l a t e r mona r ch
WHERE monarch . house=l a t e r mona r ch . house )
AND monarch . house IS NOT NULL
ORDER BY a c c e s s i o n
12
5. Write an SQL query that returns the scheme (first name,popularity) ordered in descending
order of popularity, and then alphabetical order of first name. Your answer should also
exclude first names that only occur once in the database. A first name is taken to mean the
first word appearing the name column of person.
SELECT f i r s t n ame ,
COUNT( f i r s t n ame ) AS p o p u l a r i t y
FROM (SELECT CASE
WHEN POSITION( ’ ’ IN name)=0 THEN name
ELSE SUBSTRING(name FROM 1 FOR POSITION( ’ ’ IN name)−1)
END AS f i r s t n ame
FROM pe r son ) AS pe r s on
GROUP BY f i r s t n ame
HAVING COUNT( f i r s t n ame )>1
ORDER BY p o p u l a r i t y DESC, f i r s t n ame ;
M Loose 5 marks for not using standard SQL string functions but instead using non standard
functions such as CHARINDEX.
N Loose 2 marks for getting the string breaks wrong, so that letters are missing from names,
or spaces added to the end of names.
12
6. Write an SQL query that returns the scheme (house,seventeenth,eighteenth,nineteenth,twentieth)
ordered by house listing the number of monarchs of each royal house that acceded to the
throne in the 17th, 18th, 19th and 20th centuries.
SELECT house ,
COUNT(CASE WHEN EXTRACT(YEAR FROM ac c e s s i o n ) BETWEEN 1600 AND 1699
THEN a c c e s s i o n ELSE n u l l END) AS seven t een th ,
COUNT(CASE WHEN EXTRACT(YEAR FROM ac c e s s i o n ) BETWEEN 1700 AND 1799
THEN a c c e s s i o n ELSE n u l l END) AS e i gh t e en t h ,
COUNT(CASE WHEN EXTRACT(YEAR FROM ac c e s s i o n ) BETWEEN 1800 AND 1899
THEN a c c e s s i o n ELSE n u l l END) AS n in e t een th ,
COUNT(CASE WHEN EXTRACT(YEAR FROM ac c e s s i o n ) BETWEEN 1900 AND 1999
THEN a c c e s s i o n ELSE n u l l END) AS twen t i e t h
FROM monarch
WHERE house IS NOT NULL
GROUP BY house
ORDER BY house
It is acceptable to use comparison operators between dates instead of the EXTRACT function
to get the year.
O Loose 2 marks for converting the date to a string and using string comparisons (or
converting back to numeric format for comparison).
P Loose 5 marks if query using UNION given.
12
7. Write an SQL query returning the scheme (father,child,born) ordered by father,born that lists
as father the name of all men in the database, together with the name of each child, with
born being the number of the child of the father (i.e. returning 1 for the first born, 2 for the
second born, etc). For men with no children, the man should be listed with null for both
child and born.
SELECT pe r son . name AS f a t h e r ,
c h i l d . name AS ch i l d ,
CASE
WHEN c h i l d . name IS NOT NULL
THEN RANK( ) OVER (PARTITION BY c h i l d . f a t h e r ORDER by c h i l d . dob )
ELSE n u l l
END AS born
FROM pe r son
LEFT JOIN pe r son AS c h i l d
ON pe r son . name=c h i l d . f a t h e r
WHERE pe r son . gende r=’M’
ORDER BY pe r son . name ,
born
Note that the question does not specify a sort order where the rank is tied, and hence George
IV and William IV may appear in either order as children of George III.
Q Loose 4 marks for ranking joint birth dates lower (ie if joint seconds are listed as joint
thirds).
R Loose 3 marks for including women (!) as fathers. 16
8. Write an SQL query that returns the scheme (monarch,prime minister), ordered by monarch
and prime minister, that lists prime ministers that held office during the reign of the monarch.
SELECT DISTINCT monarch . name AS monarch ,
p r im e m i n i s t e r . name AS p r im e m i n i s t e r
FROM monarch CROSS JOIN p r im e m i n i s t e r
WHERE −− No l a t e r p r ime m i n i s t e r coming b e f o r e the monarch acceded
NOT EXISTS (SELECT ∗
FROM p r im e m i n i s t e r AS l a t e r p r i m e m i n i s t e r
WHERE l a t e r p r i m e m i n i s t e r . en t r y<=monarch . a c c e s s i o n
AND l a t e r p r i m e m i n i s t e r . en t r y>p r im e m i n i s t e r . e n t r y )
−− No monarch b e f o r e t h i s p r ime m i n i s t e r s e n t r y
AND NOT EXISTS (SELECT ∗
FROM monarch AS l a t e r mona r ch
WHERE p r im e m i n i s t e r . en t r y>=la t e r mona r ch . a c c e s s i o n
AND la t e r mona r ch . a c c e s s i o n>monarch . a c c e s s i o n )
AND monarch . house IS NOT NULL
ORDER BY monarch ,
p r im e m i n i s t e r
S Loose 4 for exluding prime ministers that servered over the start and/or end of a reign, and
hence for example missing Baldwin serving under Edward VIII.
T Loose 3 for excluding the current monarch (and thus having no results for Elizabeth II) 16
Note that the NOT EXISTS may be replaced by similar expressions comparing the outer prime
minister or monarch with the subquery using ALL.
TOTAL MARKS:100