0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for linguists, teachers, and students wanting to discuss the finer points of the Latin language

create table #ACTOR (name varchar(max), age int, salary int)
create table #MOVIE (title varchar(max), studioname varchar(max),
directorname varchar(max), budget int, movieyear int)
create table #APPEARS (actorname varchar(max), movietitle varchar(max))
insert into #ACTOR values
('Tom', 45, 10000000), ('Denzel', 45, 11000000), ('Ryan', 27, 8000000),
('Seth', 31, 4000000), ('Brad', 45, 15000000), ('George', 45, 15000000),
('Meryl Streep', 55, 12000000), ('Jim', 45, 9000000)
insert into #MOVIE values
('MovieA', 'StudioA', 'DirectorA', 100000000, 2000),
('MovieB', 'StudioA', 'DirectorA', 10000000, 2002),
('MovieC', 'StudioA', 'DirectorA', 100000000, 2003),
('MovieD', 'StudioB', 'DirectorB', 100000000, 2004),
('MovieE', 'StudioB', 'DirectorB', 100000000, 2006),
('MovieF', 'StudioB', 'DirectorA', 100000000, 2007),
('MovieG', 'StudioC', 'DirectorC', 100000000, 2008),
('MovieH', 'StudioC', 'DirectorB', 100000000, 2010),
('MovieI', 'StudioD', 'DirectorD', 100000000, 2011),
('MovieJ', 'StudioD', 'DirectorA', 100000000, 2012),
('MovieK', 'StudioE', 'DirectorC', 100000000, 2012),
('MovieL', 'StudioC', 'DirectorC', 10000000, 2009),
('MovieM', 'StudioD', 'DirectorA', 101000000, 2009),
('MovieN', 'StudioE', 'DirectorB', 101000000, 2009),
('MovieO', 'StudioE', 'DirectorD', 90000000, 2009),
('MovieP', 'StudioF', 'John Huston', 90000000, 2009),
('MovieQ', 'StudioF', 'John Huston', 90000000, 2010)
insert into #APPEARS values
('Tom', 'MovieA'), ('Tom', 'MovieB'), ('Tom', 'MovieC'), ('Denzel', 'MovieB'),
('Denzel', 'MovieC'), ('Denzel', 'MovieD'), ('Ryan', 'MovieD'),
('Meryl Streep', 'MovieE'),
('Meryl Streep', 'MovieB'),
('Meryl Streep', 'MovieC'),
('Jim', 'MovieJ'), ('Jim', 'MovieK'),
('Brad', 'MovieG'), ('Brad', 'MovieL'), ('Brad', 'MovieM'),
('George', 'MovieP'), ('Brad','MovieP'), ('George', 'MovieQ')





select *--m.title
from
(
select title, sum(salary) as payroll
from #actor join #appears on name = actorname
join #movie on movietitle = title
group by title
) temp join #movie m on temp.title = m.title
where payroll > budget





















drop table #actor
drop table #movie
drop table #appears

view current version

edited feb 27 12

created feb 27 12 blinksumgreen