You have just become the database manager for your bookie. He keeps
records on horse races for statistical purposes, and his basic table looks
like this:
CREATE TABLE RacingResults
(track_id CHAR(3) NOT NULL,
race_date DATE NOT NULL,
race_nbr INTEGER NOT NULL,
win_name CHAR(30) NOT NULL,
place_name CHAR(30) NOT NULL,
show_name CHAR(30) NOT NULL,
PRIMARY KEY (track_id, race_nbr_date, race_nbr));
The track_id column is the name of the track where the race was
held, race_date is when it was held, race_nbr is number of the each
race, and the other three columns are the names of the horses that won,
placed, or showed for that race. If you do not know these terms, won
means that the horse was in first place; placed means that the horse was
in first or second place, and showed means the horse was in first,
second, or third place.
Your bookie comes to you one day and wants to know how many
times each horse was in the money. What
SQL query do you write for
this?
sql like this:
select a.win_name, count(a.*) from RacingResults r,
(select win_name from RacingResults
union
select place_name from RacingResults
union
select show_name from RacingResults)
as a
where r.win_name = a.win_name
or
r.place_name =a.win_name
or
r.show_name = a.win_name
group by (a.win_name);