CP5633 Extra Workshop
Further Practices on SQL Queries
- Sample Solutions -
The query S1P01 gives the answer to Section 1 problem 1 etc.
Query: s1p01
SELECT show_name, show_address
FROM show;
Query: s1p02
SELECT name
FROM horse
WHERE born = 1990;
Query: s1p03
SELECT show_name, show_address
FROM show
WHERE show_address like '*13*';
Query: s1p04
SELECT name, born, colour
FROM horse
WHERE gender = 'M'
AND (colour = 'white'
OR colour = 'grey');
Query: s1p05
SELECT name, died
FROM horse
WHERE died IS NOT NULL
ORDER BY died DESC;
Query: s1p06
SELECT DISTINCT event_name
FROM event;
Query: s1p07
SELECT event_code
FROM prize
WHERE prizemoney BETWEEN 100 AND 200
AND place = 1;
Query: s1p08
SELECT name
FROM horse
WHERE colour = 'white';
Query: s1p09
SELECT horse_id, name
FROM horse
WHERE died IS NULL
AND born < 1990
ORDER BY horse_id;
Query: s1p10
1
SELECT name, born
FROM horse
WHERE died IS NOT NULL
AND born IN (1976, 1978, 1980, 1981);
Query: s2p01
SELECT event_id, event_name, judge.name
FROM event, judge
WHERE event.judge_id = judge.judge_id;
Query: s2p02
SELECT DISTINCT name
FROM horse, entry
WHERE horse.horse_id = entry.horse_id
AND place=1;
Query: s2p03
SELECT horse.name, event_name, place
FROM horse, entry, event
WHERE horse.horse_id = entry.horse_id
AND entry.event_id = event.event_id;
Query: s2p04
SELECT event_name, show_name, show_address, judge.name, judge.address
FROM show, event, judge
WHERE show.show_id = event.show_id
AND event.judge_id = judge.judge_id;
Query: s2p05
SELECT show_held, judge.name, event_name
FROM show, event, judge
WHERE show.show_id = event.show_id
AND event.judge_id = judge.judge_id
AND show_name = 'Dubbo';
Query: s2p06
SELECT event_name, prizemoney
FROM event, prize
WHERE event_id = event_code
AND place=1;
Query: s2p07a
SELECT event.event_id, horse_id, entry.place, prizemoney
FROM entry, event, prize
WHERE event.event_id = entry.event_id
AND entry.event_id = prize.event_code
AND entry.place = prize.place
ORDER BY event.event_id, horse_id;
Query: s2p07b
SELECT entry.event_id, horse_id, entry.place, prizemoney
FROM entry, prize
WHERE entry.event_id = prize.event_code
AND entry.place = prize.place
ORDER BY entry.event_id, horse_id;
2
Query: s2p08
SELECT show_name, event_name, prizemoney
FROM show, event, prize
WHERE show.show_id = event.show_id
AND event.event_id = prize.event_code
AND place=1;
Query: s2p09
SELECT horse.name, show_name, event_name, place
FROM horse, entry, event, judge, show
WHERE horse.horse_id = entry.horse_id
AND entry.event_id = event.event_id
AND event.judge_id = judge.judge_id
AND event.show_id = show.show_id
AND judge.name = 'Green';
Query: s2p10
SELECT event.event_id, event_name
FROM horse, entry, event, judge
WHERE horse.horse_id = entry.horse_id
AND entry.event_id = event.event_id
AND event.judge_id = judge.judge_id
AND horse.name IN ('Star','Flash')
AND judge.name = 'Smith'
AND judge.address = 'Melbourne';
Query: s3p01
SELECT event_name, COUNT(*) AS number_of_events_with_this_name
FROM event
GROUP BY event_name;
Query: s3p02
SELECT show_name, COUNT(*) AS number_of_times_held
FROM show
GROUP BY show_name
ORDER BY COUNT(*) DESC;
Query: s3p03
SELECT name, COUNT(*) AS number_of_judges_with_this_name
FROM judge
GROUP BY name;
Query: s3p04
SELECT show_name, show_held, COUNT(*) AS events_contested
FROM show, event
WHERE show.show_id = event.show_id
GROUP BY show_name, show_held;
Query: s3p05
SELECT name, horse.horse_id, SUM(prizemoney) AS total_prizemoney_won
FROM horse, entry, prize
WHERE horse.horse_id = entry.horse_id
AND entry.event_id = prize.event_code
3
AND entry.place = prize.place
GROUP BY name, horse.horse_id;
Query: s3p06
SELECT name, horse.horse_id, SUM(prizemoney) AS total_prizemoney_won
FROM horse, entry, prize
WHERE horse.horse_id = entry.horse_id
AND event_id = event_code
AND entry.place = prize.place
GROUP BY name, horse.horse_id
HAVING SUM(prizemoney) >= 110;
Query: s3p07
SELECT place, COUNT(*) AS number_of_times_place_awarded, SUM(prizemoney) AS total,
MAX(prizemoney) AS maximum, MIN(prizemoney) AS miniumum, AVG(prizemoney) AS
average
FROM prize
WHERE place <= 2
GROUP BY place;
Query: s3p08
SELECT horse.horse_id, name, COUNT(*) AS times_in_top_2_places
FROM horse, entry
WHERE horse.horse_id = entry.horse_id
AND place <= 2
GROUP BY horse.horse_id, name
HAVING COUNT(*) > 1;
Query: s3p09
SELECT horse.name, COUNT(*) AS number_of_times_competing
FROM horse, entry, event, judge
WHERE horse.horse_id = entry.horse_id
AND entry.event_id = event.event_id
AND event.judge_id = judge.judge_id
AND judge.name = 'Smith'
AND judge.address = 'Melbourne'
GROUP BY horse.name;
Query: s4p01
SELECT show_name, FORMAT(show_held,"dddd d mmmm, yyyy") AS date_held
FROM show;
Query: s4p02a
SELECT event_name
FROM event, show
WHERE event.show_id = show.show_id
AND FORMAT(show_held,"yyyy") = 1996;
Query: s4p02b
SELECT event_name
FROM event, show
WHERE event.show_id = show.show_id
AND show_held BETWEEN #01/01/1996# AND #12/31/1996#;
Query: s4p03
SELECT name, event_name, show_name, FORMAT(show_held,"yyyy")-born AS
age_at_the_time
4
FROM horse, entry, event, show
WHERE horse.horse_id = entry.horse_id
AND entry.event_id = event.event_id
AND event.show_id = show.show_id;
Query: s4p04
SELECT event_name, COUNT(*) AS times_held_in_July
FROM event, show
WHERE event.show_id = show.show_id
AND FORMAT(show_held,"mm") = 7
GROUP BY event_name;
Query: s5p01
SELECT name
FROM judge
WHERE judge_id in (
SELECT judge_id
FROM event
WHERE show_id = '01');
Query: s5p02
SELECT name
FROM judge
WHERE judge_id in (
SELECT judge_id
FROM event
WHERE show_id in (
SELECT show_id
FROM show
WHERE show_name = 'Dubbo'
AND FORMAT(show_held,"yyyy") = 1995 ) );
Query: s5p03
SELECT event_id, event_name
FROM event
WHERE event_id IN (
SELECT event_id
FROM entry
WHERE horse_id IN (
SELECT horse_id
FROM horse
WHERE name IN ('Star', 'Flash') ) )
AND judge_id IN (
SELECT judge_id
FROM judge
WHERE name = 'Smith'
AND address = 'Melbourne');
Query: s5p04
SELECT name
FROM judge
WHERE address <> 'Melbourne'
AND judge_id IN (
SELECT judge_id
FROM event
WHERE event_id IN (
SELECT event_id
FROM entry
5
WHERE horse_id IN (
SELECT horse_id
FROM horse
WHERE gender = 'S')));
Query: s5p05
SELECT DISTINCT judge.name
FROM judge, event, entry, horse
WHERE judge.judge_id = event.judge_id
AND event.event_id = entry.event_id
AND entry.horse_id = horse.horse_id
AND judge.address <> 'Melbourne'
AND gender = 'S';
Query: s5p06
SELECT name, horse_id, 'Alive'
FROM horse
WHERE died IS NULL
UNION SELECT name, horse_id, 'Dead '
FROM horse
WHERE died IS NOT NULL
ORDER BY horse_id;
Query: s5p07
SELECT name, COUNT(*) AS number_of_wins
FROM horse, entry
WHERE horse.horse_id = entry.horse_id
AND place=1
GROUP BY name
UNION SELECT name, 0
FROM horse
WHERE horse_id NOT IN (
SELECT horse_id
FROM entry
WHERE place = 1)
ORDER BY number_of_wins DESC;
Query: s6p01
SELECT event_id, event_name, name AS judge_name
FROM event INNER JOIN judge ON event.judge_id = judge.judge_id;
Query: s6p02a
SELECT event.event_id, horse.horse_id, entry.place, prizemoney
FROM ((horse INNER JOIN entry ON horse.horse_id = entry.horse_id) INNER JOIN event ON
entry.event_id = event.event_id) INNER JOIN prize ON event.event_id = prize.event_code
WHERE entry.place = prize.place
ORDER BY event.event_id, horse.horse_id;
Query: s6p02b
SELECT entry.event_id, horse.horse_id, entry.place, prizemoney
FROM (horse INNER JOIN entry ON horse.horse_id = entry.horse_id) INNER JOIN prize ON
entry.event_id = prize.event_code AND entry.place = prize.place
ORDER BY entry.event_id, horse.horse_id;
Query: s6p03
SELECT event.event_id, event_name
FROM ((horse INNER JOIN entry ON horse.horse_id = entry.horse_id) INNER JOIN event ON
entry.event_id = event.event_id) INNER JOIN judge ON judge.judge_id = event.judge_id
6
WHERE horse.name IN ('Star','Flash')
AND judge.name = 'Smith'
AND judge.address = 'Melbourne';
Query: s6p04a
SELECT name, horse.horse_id, sum(prizemoney)
FROM ((horse INNER JOIN entry ON horse.horse_id = entry.horse_id) INNER JOIN event ON
entry.event_id = event.event_id) INNER JOIN prize ON event.event_id = prize.event_code
WHERE entry.place = prize.place
GROUP BY name, horse.horse_id
HAVING sum(prizemoney) >= 110;
Query: s6p04b
SELECT name, horse.horse_id, sum(prizemoney)
FROM (horse INNER JOIN entry ON horse.horse_id = entry.horse_id) INNER JOIN prize ON
entry.event_id = prize.event_code AND entry.place = prize.place
GROUP BY name, horse.horse_id
HAVING sum(prizemoney) >= 110;
Query: s6p05_join
SELECT horse.name, horse.born, sire.name
FROM horse INNER JOIN horse AS sire ON horse.sire = sire.horse_id
ORDER BY horse.born;
Query: s6p05_product
SELECT horse.name, horse.born, sire.name
FROM horse, horse AS sire
WHERE horse.sire = sire.horse_id
ORDER BY horse.born;
Query: s6p06_join
SELECT father.name AS father_of_flash
FROM horse AS father INNER JOIN horse AS child ON father.horse_id = child.sire
WHERE child.name = 'Flash';
Query: s6p06_product
SELECT sire.name
FROM horse, horse AS sire
WHERE horse.sire = sire.horse_id
AND horse.name = 'Flash';
Query: s6p07_join
SELECT horse.name, dam.name
FROM horse INNER JOIN horse AS dam ON horse.dam = dam.horse_id
WHERE horse.gender = 'M'
AND horse.colour = 'White'
AND dam.colour = 'White';
Query: s6p07_product
SELECT child.name, mother.name
FROM horse AS child, horse AS mother
WHERE child.dam = mother.horse_id
AND child.gender = 'M'
AND child.colour = 'White';
7
Query: s6p08_join
SELECT child.name, father.name, grandfather.name
FROM (horse AS child INNER JOIN horse AS father ON child.sire = father.horse_id) INNER JOIN
horse
AS grandfather ON father.sire = grandfather.horse_id;
Query: s6p08_product
SELECT horse.name, sire.name, grandsire.name
FROM horse, horse AS sire, horse AS grandsire
WHERE horse.sire = sire.horse_id
AND sire.sire = grandsire.horse_id;
Query: s6p09_join
SELECT first.event_code, first.prizemoney, second.prizemoney, third.prizemoney
FROM (prize AS [first] INNER JOIN prize AS [second] ON first.event_code =
second.event_code)
INNER JOIN prize AS third ON first.event_code = third.event_code
WHERE first.place = 1
AND second.place = 2
AND third.place = 3
ORDER BY first.event_code;
Query: s6p09_product
SELECT first.event_code, first.prizemoney, second.prizemoney, third.prizemoney
FROM prize AS [first], prize AS [second], prize AS third
WHERE first.event_code = second.event_code
AND first.event_code = third.event_code
AND first.place = 1
AND second.place = 2
AND third.place = 3
ORDER BY first.event_code;
Query: s6p10_join
SELECT child.name, father.name, event_name
FROM (((event INNER JOIN entry AS child_entry ON event.event_id = child_entry.event_id)
INNER
JOIN horse AS child ON child_entry.horse_id = child.horse_id) INNER JOIN entry AS
father_entry ON
event.event_id = father_entry.event_id) INNER JOIN horse AS father ON father_entry.horse_id
=
father.horse_id
WHERE child.sire = father.horse_id;
Query: s6p10_product
SELECT child.name, sire.name, event_name
FROM horse AS child, entry AS child_entry, event, entry AS sire_entry, horse AS sire
WHERE child.horse_id = child_entry.horse_id
AND child_entry.event_id = event.event_id
AND event.event_id = sire_entry.event_id
AND sire_entry.horse_id = sire.horse_id
AND child.sire = sire.horse_id;
Query: s6p11
SELECT judge.judge_id, name, event_id
FROM judge LEFT JOIN event ON judge.judge_id = event.judge_id;
