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

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

from

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,

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 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

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 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)

[1] All solutions tested on the sample database populated with data from http://www.moviefone.com/.