【数据库】简单查询、多表查询和子查询，连接表达式的使用、聚合操作

实验过程：
新建查询文件：学号.sql，保存本次实验的所有SQL语句。
任务1：Simple Queries and Multiple Relations Queries in SQL
（一）Simple Queries
(1) Find the address of‘81 Film’studios.
SELECT statement:

select address from Studio where name = '81 Film'

(2) Find Sandra_Bullock’s birthdate.
SELECT statement:

select birthdate from MovieStar where name='Sandra_Bullock'

(3) Find all the stars that appeared either in a movie made in 1980 or a movie with “Love” in the title.
SELECT statement:

select starName from StarsIn where movieYear = 1980 OR movieTitle like '%Love%'

(4) Find all executives worth at least $10,000,000.
SELECT statement:

select name from MovieExec where netWorth > 10000000

(5) Find all the stars who either are male or live in Malibu(have string Malibu as a part of their address)
SELECT statement:

select name from MovieStar where gender='M' OR address like '%Malibu%'

（二）Multiple Relations Queries
(1) Who are the male stars in Titanic?
SELECT statement:

select name from MovieStar where gender='M' and name in(select starName from StarsIn where movieTitle='Titanic' )

(2) Which stars appeared in movies produced by 81 Film in 2018?
SELECT statement:

select starName from StarsIn
where movieTitle in(select title from Movies where year =2018 and studioName='81 Film')

(3) Who is the executive of ‘The Faithful’ Film?
SELECT statement:

select MovieExec.name
from Movies,MovieExec
where Movies.title='The Faithful'and Movies.producerC#=MovieExec.cert#

(4) Who had executed Harrison Ford’s movies?
SELECT statement:

select distinct MovieExec.name
from StarsIn,MovieExec,Movies
where StarsIn.starName = 'Harrison Ford' and StarsIn.movieTitle=Movies.title and Movies.producerC#=MovieExec.cert#

(5) Who had performed films more than two?
SELECT statement:

select distinct a.starName
from StarsIn a,StarsIn b
where a.movieTitle <> b.movieTitle and a.starName=b.starName

（三）Subquery
(1) Find movies which are longer than The Faithful.
SELECT statement：

select title  from Movies
where length>(select length from Movies where title='The Faithful')

(2) Find executives who has the greatest worth.
SELECT statement:

SELECT name FROM MovieExec where netWorth=(SELECT max(netWorth) FROM MovieExec) 

(3) Find executives who had executed Harrison Ford’s movies.
SELECT statement:

select name from MovieExec
where cert# in(
select producerC# from Movies
where title in(
select movieTitle from StarsIn
where starName='Harrison Ford'
)
)

(4) Find the stars who had not performed in the film Star Wars and its sequels.
SELECT statement:

select distinct starName from StarsIn
where movieTitle not in(
select title from Movies
where title like '%Star Wars%'
)

(5) Find the stars who had performed more than two films.
SELECT statement:

select distinct starName from StarsIn a
where exists(
select *
from StarsIn b
where a.starName=b.starName and a.movieTitle <> b.movieTitle
)

任务2：Join Expressions And Aggregate Operation
（一）Join Expressions
(1) Find the stars who appeared in movies produced by 81 Film in 2018.
SELECT statement:

select starName from StarsIn
where movieTitle =(select title 
from dbo.Movies 
	Inner JOIN dbo.Studio 
	ON producerC# = presC#
where year = 2018)

(2) Find the stars who are male in The Faithful.
SELECT statement:

select name from MovieStar
INNER JOIN StarsIn on movieTitle='The Faithful' and name=starName
where gender ='M'

(3) Find the studios which had produced more than two films.
SELECT statement:

select distinct a.studioName from Movies a inner join Movies b on
a.studioName=b.studioName and a.title <> b.title

(4) Find the stars who not appeared in Titanic.
SELECT statement:

select distinct a.starName
from StarsIn a
	LEFT JOIN StarsIn b
	ON b.movieTitle='Titanic' AND a.starName=b.starName
where b.movieTitle IS NULL;   

(5) Find the stars who never appeared in Movies.
SELECT statement:

select name
from MovieStar
	LEFT JOIN StarsIn
	ON name = starName
where movieTitle IS NULL;

（二）Aggregate Operation
(1) Find the number of movies which had produced by various studios.
SELECT statement:

select studioName,count(title) total from Movies
where studioName is not null
group by studioName

(2) Find the studios which had produced more than two films.
SELECT statement:

select studioName from Movies
where studioName is not null
group by studioName
having COUNT(title) >1

(3) Find the sum of the lengths of all movies for each studio.
SELECT statement:

select studioName,SUM(length) sumlength  from Movies
where studioName is not null
group by studioName

(4) Find the number of movie stars have appeared in films which had produced by Fox studio.
SELECT statement:

select COUNT(distinct starName) sum_star from Movies
right join StarsIn
on title=movieTitle
where studioName='Fox'

(5) Which studio has the largest number of films.
SELECT statement:

select TOP 1 studioName, COUNT(title) total
	from Movies
	where studioName IS NOT NULL
GROUP BY studioName
ORDER BY total desc;