课堂练习答案.doc

一、电影数据库：6Movie (title: string,year: integer,length: integer, genre: string, 电影所属的流派studioName: string,producerC#: integer 电影制片人的授权证书号码)MovieStar (name: string,address: string,gender: char, 性别：F或Mbirthdate: date)StarsIn (movieTitle: string,movieYear: integer,starName: string)MovieExec (电影制片人name:string,address: string,cert#: integer, 授权证书号码netWorth: integer资产净值)Studio (name: string,address: string,presC#: integer 公司总裁的授权证书号码)每一个电影制片人有一个授权证书号码，这个授权号码是具有惟一性的整型数。假设电影公司总裁也一定是电影制片人。用下划线表示主键。课堂练习一：1 写出关系Movie的关系模式定义，并定义主键。CREATE TABLE Movies (title CHAR(100),year INT,length INT,genre CHAR(10),studioName CHAR(30),producerC# INT,PRIMARY KEY(title, year);2 写出关系StarsIn的关系模式定义，并定义主键。CREATE TABLE StarsIn (movieTitle CHAR(100),movieYear INT,starName CHAR(30),PRIMARY KEY(movieTitle, movieYear, starName);课堂练习二：已知关系R 关系SAB1234BCD256478910111求RS的结果2求R和S的自然连接3求R和S的等值连接，连接条件：R.B=S.B4. 求R和S的q连接，连接条件：R.AS.B5求R和S的自然外连接课堂练习三：Movies (title, year, length, genre, studioName, producerC#) StarsIn (movieTitle, movieYear, starName) MovieStar (name, address, gender, birthdate) MovieExec (name, address, cert#, netWorth) Studio (name, address, presC#)（1）找出Harrison Ford演过的电影的制片人的姓名SELECT nameFROM MovieExecWHERE cert# IN(SELECT producerC# FROM Movies WHERE (title,year) IN (SELECT movieTitle,movieYear FROM StarsIn WHERE starname=Harrison Ford; ) );SELECT DISTINCT name FROM MovieExec, Movies, StarsInWHERE cert#=producerC# AND title=movieTitle AND year=movieYear ANDstarname= Harrison Ford;（2）找出被两部或两部以上电影使用过的电影名SELECT x.titleFROM Movies xWHERE x.yearANY(SELECT y.year FROM Movies y WHERE y.title=x.title);SELECT x.titleFROM Movies xWHERE x.yearANY(SELECT y.year FROM Movies y WHERE y.title=x.title);SELECT x.titleFROM Movies xWHERE x.yearANY(SELECT y.year FROM Movies y WHERE y.title=x.title);SELECT x.titleFROM Movies xWHERE x.year(SELECT MIN(y.year) FROM Movies y WHERE y.title=x.title);SELECT x.titleFROM Movies xWHERE EXISTS(SELECT * FROM Movies y WHERE y.title=x.title AND y.yearx.year);找出最高分在90分以上的学生的学号（HAVING短语可以有子查询）SELECT Sno FROM SC x GROUP BY Sno HAVING 9090 ;课堂练习四：Movies (title, year, length, genre, studioName, producerC#) StarsIn (movieTitle, movieYear, starName) MovieStar (name, address, gender, birthdate) MovieExec (name, address, cert#, netWorth) Studio (name, address, presC#)用SQL语句写出下面的查询：1 找出电影公司MGM的地址。SELECT address FROM StudioWHERE name=MGM;2 找出那些在1980年制作的，或者电影名中包括“Love”单词的电影中出现的所有电影明星。SELECT starNameFROM StarsIn WHERE movieYear=1980 OR movieTitle LIKE %Love%;3 哪些男影星出演了电影Titanic？SELECT nameFROM MovieStar, StarsInWHERE MovieS= StarsIn.starName AND gender=M AND movieTitle= Titanic;4 哪些影星在MGM电影公司于1995年制作的电影里演出？SELECT starNameFROM StarsIn, MoviesWHERE StarsIn.movieTitle= Movies.title and StarsIn.movieYear= Movies.year andmovieYear=1995 and studioName=MGM;5 谁是MGM电影公司的总裁？SELECT MovieEFROM Studio,MovieExecWHERE Studio.presC#= MovieExec.cert# and S= MGM;6 哪些电影的时间比Gone with the wind长？SELECT title FROM Movies WHERE length (SELECT length FROM MoviesWHERE title= Gone with the wind);7 找出最富有的制片人的姓名及资产。SELECT name, netWorthFROM MovieExecWHERE networth = ALL (SELECT networthFROM MovieExec);8 找出每个制片人的姓名及制作的电影的数量。SELECT name,COUNT(title)FROM MovieExec,MoviesWHERE MovieExec.cert#= MducerC#GROUP BY name课堂练习七：对于电影数据库，包括电影、电影明星、电影制片人和电影公司的信息。电影的信息包括名字