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.
Andrew of Greece Anne (Princess) Charles
person dob dod?
1885-02-25 1969-12-05 1960-02-19 null 1882-02-02 1944-12-03 1950-08-15 null 1948-11-14 null
father? mother? null null
born in Windsor London Athens London London
George I of Greece Philip
Elizabeth II null Elizabeth II Elizabeth II
person(father) ⇒ person(name)
person(mother) ⇒ 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.
Oliver Cromwell Richard Cromwell Charles II
house? Stuart Stuart null null Stuart Stuart
accession 1603-03-24 1625-03-27 1649-01-30 1658-09-03 1659-05-25 1685-02-06
coronation?
1603-07-25
1626-02-02
null 1626-02-02 1685-04-23
monarch(name) ⇒ 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 party
Conservative Labour Labour Conservative Conservative Labour Labour Conservative
David Cameron Gordon Brown Tony Blair
John Major Margaret Thatcher James Callaghan Harold Wilson Edward Heath
try 2010-05-11 2007-06-27 1997-05-02
1990-11-28 fk
1979-05-04 1976-04-05 1974-03-04 1970-06-19
prime minister(name) ⇒ 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:
psql −h db.doc.ic.ac.uk −d family history −U lab −W−f db 2018 cw1.sql
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.
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 person . name , father.nameAS father,
mother . name AS mother FROM person
JOIN person AS father
ON person . father=father .name
JOIN person AS mother
ON person.mother=mother.name
WHERE person.dod
monarch . house IS NOT NULL
ORDER BY monarch.name
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 . accession FROM monarch
WHERE monarch . accession<=ALL (SELECT later monarch . accession FROM monarch AS later monarch
WHERE monarch . house=later monarch . house ) AND monarch .house IS NOT NULL
ORDER BY accession
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 FROM
first name ,
COUNT( first name ) AS popularity
(SELECT CASE
WHEN POSITION( ’ ’ IN name)=0 THEN name
ELSE SUBSTRING(name FROM 1 FOR POSITION( ’ ’ IN name)−1)
END AS first name FROM person) AS person
GROUP BY first name
HAVING COUNT(first name)>1
ORDER BY popularity DESC, first name ;
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.
6. WriteanSQLquerythatreturnsthescheme(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.
程序代写 CS代考 加QQ: 749389476
SELECT house , COUNT(CASE
COUNT(CASE COUNT(CASE COUNT(CASE
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.
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 person . name AS father , child.nameAS child,
WHEN EXTRACT(YEAR FROM a c c e s s i o n ) BETWEEN 1600 AND 1699 THEN accession ELSE null END) AS seventeenth ,
WHEN EXTRACT(YEAR FROM a c c e s s i o n ) BETWEEN 1700 AND 1799 THEN accession ELSE null END) AS eighteenth,
WHEN EXTRACT(YEAR FROM a c c e s s i o n ) BETWEEN 1800 AND 1899 THEN accession ELSE null END) AS nineteenth ,
WHEN EXTRACT(YEAR FROM a c c e s s i o n ) BETWEEN 1900 AND 1999 THEN accession ELSE null END) AS twentieth
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.
WHEN child.name IS NOT NULL
THEN RANK() OVER (PARTITION BY child . father ORDER by child .dob)
END AS born FROM person
LEFT JOIN person AS child
ON person.name=child.father WHERE person . gender=’M’
ORDER BY person.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).
Computer Science Tutoring
SELECT DISTINCT monarch . name AS monarch , prime minister .name AS prime minister
FROM monarch CROSS JOIN prime minister
WHERE −− No later prime minister coming before the monarch acceded
NOT EXISTS (SELECT ∗
FROM prime minister AS later prime minister
WHERE later prime minister . entry<=monarch. accession
AND later prime minister.entry>prime minister.entry) −− No monarch before this prime ministers entry
NOT EXISTS (SELECT ∗
FROM monarch AS later monarch
WHERE prime minister . entry>=later monarch . accession
AND later monarch . accession>monarch. accession ) monarch . house IS NOT NULL
ORDER BY monarch ,
prime minister
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