Solutions to homework 1, CSE444, fall 2003[1]
1.1 The best one:
select distinct t.name, t.phone
from theaters as t
join movies2theaters as m2t1 on t.theatercode = m2t1.theatercode
join movies2theaters as m2t2 on t.theatercode = m2t2.theatercode
join movies as m1 on m1.moviecode = m2t1.moviecode
join movies as m2 on m2.moviecode = m2t2.moviecode
where m1.title = 'girlfight' and m2.title = 'place vendome'
1.2 Less optimal one:
select distinct t.name, t.phone
from theaters as t
join movies2theaters as m2t on t.theatercode = m2t.theatercode
join movies as m on m.moviecode = m2t.moviecode
where m.title = 'Girlfight' and exists
(select * from movies2theaters as m2t_sub1
join movies as m_sub1 on m_sub1.moviecode = m2t_sub1.moviecode
where m2t_sub1.theatercode = m2t.theatercode
and m_sub1.title = 'Place Vendome')
Output:
Landmark Metro Cinemas (206) 633-0055
(1 row(s) affected)
2.1 The best one
select t.name, t.address
from theaters as t
join theaters2districts as t2d1 on t2d1.theatercode = t.theatercode
join theaters2districts as t2d2 on t2d2.theatercode = t.theatercode
join districts as d1 on t2d1.districtcode = d1.districtcode
join districts as d2 on t2d2.districtcode = d2.districtcode
where d1.name = 'CAPITOL HILL' and d2.name = 'DOWNTOWN'
2.2 Less optimal one
select distinct set1.Name, set1.Address
from
(select distinct t.Name, t.Address, t.TheaterCode
from theaters as t, theaters2districts as t2d, districts as d
where t.TheaterCode = t2d.TheaterCode and t2d.DistrictCode = d.DistrictCode and d.Name = 'capitol hill') as set1,
(select distinct t.Name, t.Address, t.TheaterCode
from theaters as t, theaters2districts as t2d, districts as d
where t.TheaterCode = t2d.TheaterCode and t2d.DistrictCode = d.DistrictCode and d.Name = 'downtown') as set2
where set1.TheaterCode = set2.TheaterCode
Output:
Landmark Broadway Market Cinemas 425 Broadway East | Seattle, WA 98102
Landmark Harvard Exit Theatre 807 E.Roy | Seattle, WA 98102
Cineplex Odeon Uptown 511 Queen Anne Avenue North | Seattle, WA 98109
Boeing Imax Theater 200 Second Ave North | Seattle, WA 98109
Cineplex Odeon City Centre 1420 Fifth Avenue, Suite 375 | Seattle, WA 98101
General Cinema Pacific Place 11 600 Pine St. #406 | Seattle, WA 98101
Cineplex Odeon Meridian 1501 7th Avenue | Seattle, WA 98101
Omnidome Pier 59 | Seattle, WA 98101
General Cinema Cinerama 2100 4th Avenue | Seattle, WA 98121
Landmark Neptune Theatre 1303 NE 45th | Seattle, WA 98105
Landmark Metro Cinemas 4500 9th Avenue NE, Suite 200 | Seattle, WA 98105
Landmark Egyptian Theatre 801 E. Pine Street | Seattle, WA 98105
Landmark Seven Gables Theatre 911 NE 50th | Seattle, WA 98105
Landmark Varsity Theatre 4329 University Way, NE | Seattle, WA 98105
Landmark Guild 45th 2115 N. 45th | Seattle, WA 98103
(15 row(s) affected)
3.1 The best one
select t.name, t.address
from theaters as t
join theaters2districts as t2d on t.theatercode = t2d.theatercode
join districts as d on t2d.districtcode = d.districtcode
where
d.name = 'CAPITOL HILL'
and not exists
(select t2d_sub1.theatercode
from theaters2districts as t2d_sub1
join districts as d on t2d_sub1.districtcode = d.districtcode
where d.name = 'THE U DISTRICT'
and t2d_sub1.theatercode = t.theatercode)
Output
General Cinema Cinerama 2100 4th Avenue | Seattle, WA 98121
(1 row(s) affected)
4.1 The best one
select m.title
from movies m
where (19*60 + 1) <= all (select datepart(hh, m2t.movietime)*60 + datepart(mm, m2t.movietime)
from movies2theaters as m2t
where m.moviecode = m2t.moviecode)
4.2 Less optimal one
select distinct m.title
from movies as m, movies2theaters as m2t
where m.MovieCode = m2t.MovieCode
and datepart(hh, m2t.MovieTime) >= 19
and m.MovieCode not in (select distinct m2.MovieCode
from movies as m2, movies2theaters as m2t_sub1
where m2.MovieCode = m2t_sub1.MovieCode
and datepart(hh, m2t_sub1.MovieTime) < 19)
Output:
Dark Days
Everest
Spike And Mikes Festival Of Animation '92
(3 row(s) affected)
5.1 The best one
select t.name, min(m.length) as ShortestMovie, max(m.length) as LongestMovie
from theaters as t
join movies2theaters as m2t on m2t.theatercode = t.theatercode
join movies as m on m.moviecode = m2t.moviecode
where m.length is not null
group by t.theatercode, t.name
Output:
Cineplex Odeon City Centre 93 94
Cineplex Odeon Northgate 113 113
Cineplex Odeon Uptown 85 102
Landmark Varsity Theatre 84 102
Landmark Seven Gables Theatre 140 140
Landmark Neptune Theatre 90 90
Landmark Metro Cinemas 88 147
Landmark Harvard Exit Theatre 94 140
Landmark Guild 45th 107 120
Landmark Broadway Market Cinemas 90 103
Cineplex Odeon Meridian 84 147
General Cinema Pacific Place 11 107 126
General Cinema Cinerama 132 132
(13 row(s) affected)
6.1 The best one
select m.title, convert(varchar(255), max(m2t.movietime), 8) as TheLatest
from movies2theaters as m2t
join movies as m on m.moviecode = m2t.moviecode
group by m.moviecode, m.title
Output:
Almost Famous 23:35:00
Amazon 22:40:00
Barenaked in America 21:30:00
Beautiful 21:40:00
Best in Show 22:00:00
Bring It On 23:40:00
Chicken Run 17:30:00
Dancer In The Dark 22:00:00
Dark Days 20:00:00
Digimon: The Movie 23:50:00
Dr. T and the Women 22:00:00
Everest 19:15:00
Extreme 20:30:00
Get Carter 23:20:00
Girlfight 22:15:00
Human Resources 21:15:00
Into The Deep in 3D 23:45:00
Lost Souls 22:40:00
Meet The Parents 23:15:00
Michael Jordan to the Max 20:45:00
Mysteries of Egypt 19:45:00
Nurse Betty 23:10:00
Nutty Professor 2: with captions 21:45:00
One 20:45:00
Place Vendome 21:20:00
Remember the Titans 23:30:00
Rififi 21:30:00
Santitos 21:30:00
Space Cowboys 23:40:00
Spike And Mikes Festival Of Animation '92 20:00:00
The Art of War 22:00:00
The Broken Hearts Club 21:30:00
The Cell 22:35:00
The Contender 23:10:00
The Eruption Of Mount St.Helens 23:30:00
The Exorcist: The Version You've Never Seen 22:00:00
The Ladies Man 23:30:00
The Tao of Steve 21:30:00
The Watcher 23:00:00
Urban Legends: Final Cut 21:00:00
Urbania 21:15:00
Woman On Top 21:30:00
movie1 22:00:00
movie2 21:00:00
(44 row(s) affected)
7.1 The best one
select t.name
from theaters as t
join movies2theaters as m2t on m2t.theatercode = t.theatercode
group by t.theatercode, t.name
having count(distinct m2t.moviecode) >=4
7.2 Less optimal one
select theaters.name
from theaters, (select movies2theaters.theatercode
from movies2theaters
group by movies2theaters.theatercode
having count(distinct movies2theaters.moviecode) >= 4) as code
where theaters.theatercode=code.theatercode
Output
Cineplex Odeon Uptown
Landmark Metro Cinemas
Cineplex Odeon Meridian
General Cinema Pacific Place 11
Omnidome
(5 row(s) affected)
8.1 The best one
insert into movies(moviecode, title, rating, length)
values ('Movie-100', 'movie1', 'R', '120')
insert into movies(moviecode, title, rating, length)
values ('Movie-110', 'movie2', 'R', '110')
insert into movies2theaters(theatercode, moviecode, movietime)
values ('Theater-code-027', 'Movie-100', '10/20/2002 9:00:00 PM')
insert into movies2theaters(theatercode, moviecode, movietime)
values ('Theater-code-027', 'Movie-100', '10/20/2002 10:00:00 PM')
insert into movies2theaters(theatercode, moviecode, movietime)
values ('Theater-code-029', 'Movie-100', '10/20/2002 6:00:00 PM')
insert into movies2theaters(theatercode, moviecode, movietime)
values ('Theater-code-029', 'Movie-100', '10/20/2002 7:00:00 PM')
insert into movies2theaters(theatercode, moviecode, movietime)
values ('Theater-code-053', 'Movie-110', '10/20/2002 5:00:00 PM')
insert into movies2theaters(theatercode, moviecode, movietime)
values ('Theater-code-053', 'Movie-110', '10/20/2002 9:00:00 PM')
9.1 The best one
update movies2theaters
set movietime = dateadd(hh, -1, movietime)
from movies2theaters as m2t
join theaters as t on m2t.theatercode = t.theatercode
join theaters2districts as t2d on t.theatercode = t2d.theatercode
join districts as d on t2d.districtcode = d.districtcode
where d.name='THE U DISTRICT'
9.2 Less optimal one
update movies2theaters
set movietime = dateadd(hh, -1, movietime)
where movies2theaters.theatercode in
(select t2d.theatercode
from districts as d
join theaters2districts as t2d on d.districtcode = t2d.districtcode
where d.name = 'the u district')
10.1 Using “top” keyword (not discussed in class)
update theaters set NewFeature =
(
select top 1 m.moviecode
from movies as m
join movies2theaters as m2t on m2t.moviecode = m.moviecode
join theaters as t on m2t.theatercode = t.theatercode
where t.theatercode = theaters.theatercode
order by m.title desc
)
10.2 Without “top” keyword
update theaters set newfeature =
(select distinct mm.moviecode
from movies as mm
join movies2theaters as mm2tt on mm.moviecode = mm2tt.moviecode
where mm.title =
(select max(m.title)
from movies as m
join movies2theaters as m2t on m2t.moviecode = m.moviecode
where m2t.theatercode = theaters.theatercode
)
and mm2tt.theatercode = theaters.theatercode
)
Theaters after modification:
Newfeature Title Theatercode
Movie-042 Woman On Top Theater-Code-009
Movie-026 Remember the Titans Theater-Code-015
Movie-024 One Theater-Code-019
Movie-030 Spike And Mikes Festival Of Animation '92 Theater-Code-027
Movie-008 Dancer In The Dark Theater-Code-028
Movie-100 movie1 Theater-Code-029
Movie-038 The Tao of Steve Theater-Code-030
Movie-032 The Broken Hearts Club Theater-Code-031
Movie-019 Meet The Parents Theater-Code-032
Movie-027 Rififi Theater-Code-033
Movie-041 Urbania Theater-Code-035
Movie-039 The Watcher Theater-Code-053
Movie-034 The Contender Theater-Code-059
Movie-036 The Exorcist: The Version You've Never Seen Theater-Code-065
Movie-035 The Eruption Of Mount St.Helens Theater-Code-XXX
Movie-017 Into The Deep in 3D Theater-Code-YYY
(16 row(s) affected)