【数据库期末复习】查询、更新、视图、索引、约束触发器、存储过程语句复习
数据库复习
脚本文件在最后面，下载运行即可，基于SqlServer2019.
多表查询和子查询
Simple Queries and Multiple Relations Queries in SQL
(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
)
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;
数据库更新操作
Insert Operation
(1) Insert new tuples into movieStar.
(“Yang Zi”,“Fangshan District, Beijing”,“F”，“1992-11-6”)
(“Sun Yi”,“F”，“Jilin Province”)
SELECT statement:
insert MovieStar(name,address,gender,birthdate)
values('Yang Zi','Fangshan District, Beijing','F','1992-11-6')
insert MovieStar(name,address,gender)
values('Sun Yi','Jilin Province','F')
(2) Insert tuples into new table.
Create a new table named nunpart, which has two attributes: one is ID integer, which grows automatically in steps of 1, and the other is used to store actors’ names.
Insert the name of the actor who has not acted in the movie.
SELECT statement:
create table nunpart (IDinteger int identity(1,1) primary key,name varchar(20))
insert into nunpart(name)
(select distinct name
from MovieStar)
except
(select distinct name
from MovieStar,StarsIn
where MovieStar.name=StarsIn.starName)
(3) Create a new table from query results
Find the three studios that had produced the most movies between 1990 and 2000, and insert the name and number of movies into the table named temp。
SELECT statement:
create table temp (name varchar(20) not Null,number int)
insert into temp(name,number)
select top(3) studioName,count(title) number
from Movies
where studioName is not Null and year>1989 and year<2001
group by studioName
order by number desc
Update Operation
(1) Increase the length of all cartoon movies by 5% .
SELECT statement:
update Movies
set length=length*1.05
where genre='cartoon'
(2) Studio Disney buys studio Fox. Change all movies produced by Fox so they are now produced by Disney.
SELECT statement:
update Movies
set studioName='Disney'
where studioName='Fox'
(3) Subtract 5 minutes from the length of the movies directed by Sanford Panitch .
SELECT statement:
update Movies
set length=length-5
where title=(select title
from MovieExec
where cert#=producerC# and name='Sanford Panitch')
(4) Add a column named hot to the Movies relation, with the default value of 0. It is used to store the hot degree of the movie. Use 1 to indicate the hot, and 0 to indicate the general. If the movie has more than 3 actors, the value of this column is 1 .
SELECT statement:
alter table Movies add hot int not null default 0
update Movies
set hot=1
where title in
(select movieTitle
from StarsIn
group by movieTitle
having count(starName)>=3)
Delete Operation
(1) Delete all movies with null value for studioName, do not forget table Starsin.
SELECT statement:
delete from StarsIn
where movieTitle in (
select title
from Movies
where studioName is null)
delete from Movies where studioName is null
(2) Delete actors who have never been in a movie from the MovieStars.
SELECT statement:
delete from MovieStar
where name in (
(select name
from MovieStar)
except
(select starName
from StarsIn)
)
(3) In the MovieStars , delete actors who have only appeared in 2 movies or less .
SELECT statement:
delete from MovieStar
where name in(
select starName
from StarsIn
group by starName
having count(starName)<=2
)
(4) In the Movies , delete those movies whose names do not appear in the StarsIn.
SELECT statement:
delete from Movies
where title in(
(select title
from Movies)
except
(select movieTitle
from StarsIn)
)
视图和索引
Create view
（1）A view StudioPres giving the name, address, certificate number of all executives who are studio presidents.
SELECT statement:
create view StudioPres(name,address,certificate_number) as
select MovieExec.name,MovieExec.address,cert#
from MovieExec,Studio
where presC# = cert#
（2）A view LongMovies giving the title of all movies which are long than the movies “Star Wars” in the title.
SELECT statement:
create view LongMovies(title) as
--select title
from Movies
where length >all(select length from Movies where title like '%Star Wars%')
（3）A view SumLength giving the name, total film length for only those producers who made at least one file prior to 1950.
SELECT statement:
create view SumLength as
select MovieExec.name ,sum(Movies.length) length
from MovieExec,Movies
where MovieExec.cert# = Movies.producerC# and Movies.producerC# in
(select producerC# from Movies where year<1950)
group by MovieExec.name
（4）A view AvgLength giving the name, average length of films which are made before 1950 by every producer.
SELECT statement:
create view AvgLength(name,title,avelength) as
select MovieExec.name,Movies.title, avg(Movies.length) avglength
from MovieExec inner join Movies on cert#=producerC#
where year <1950
group by MovieExec.name,Movies.title
（5）A view StarsName giving the name of stars who appeared in more than two films.
SELECT statement:
create view StarName(name) as
select distinct a.starName
from StarsIn a,StarsIn b
where a.movieTitle <> b.movieTitle and a.starName=b.starName
Create indexes and nonclustered indexes
（1）Declare index on the studioName attribute of Studio.
SELECT statement:
create index nameIndex on Movies(studioName);
（2）Declare index on the combination of genre and length attributes of Movies.
SELECT statement:
create index genreIndex on Movies(genre,length);
约束和触发器
Add referential integrity constraint
（1）Require the referential integrity constraint in StarsIn that the value of movieTitle should appear in Movies.
SELECT statement:
alter table StarsIn
add constraint StarsIn_movieTitle
foreign key (movieTitle,movieYear) references Movies(title,year)
（2）Require the referential integrity constraint that the producer of every movie appear in MovieExec.
SELECT statement:
alter table Movies
add constraint MovieExec_cert#
foreign key(producerC#) references MovieExec(cert#)
Add check constraint
（1）Require that no movie length be less than 60 nor greater than 250.
SELECT statement:
alter table Movies
add constraint Movies_length
check (length<=250 and length>= 60)
（2）Require the constraint in MovieStar that the value of any gender component must in set (‘F’,‘M’).
SELECT statement:
alter table MovieStar
add constraint MovieStar_gender
check (gender in ('F','M'))
Write the following as triggers
（1）Assure that at all times, any stars appearing in StarsIn also appears in MoviesStar.（insert）
SELECT statement:
create trigger trig_StarsInInsert
on StarsIn after insert
as
begin
declare @StrarName nchar(30)
select @StrarName =starName from inserted
update MovieStar set name = @StrarName
end
（2）Assure that the average length of all movies made in any year is no more than 120.（insert）
SELECT statement:
create trigger trig_Movielength
on Movies for insert
as
begin
declare @movienumber int,@totallength int,@new_year int,@new_length int
declare @movies_title nchar(100)
select @new_length=length from inserted
select @movienumber=count(title) from Movies where year=@new_year
select @totallength=sum(length) from Movies where year=@new_year
if((@totallength+@new_length)/(@movienumber+1) >120 )
delete from Movies where(Movies.title=@movies_title and Movies.year=@new_year)
end
（3）When a tuple is deleted in Movies, also delete tuple(s) in StarsIn with the same value of title.（delete）
SELECT statement:
create trigger trig_deletemovie
on movies after delete
as
begin
declare @title nchar(100)
select @title=title from deleted
delete from starsin
where movietitle=@title
end
（4）When the title attribute of Movies is changed, also change tuple(s) in StarsIn with the same value of title.（update）
SELECT statement:
create trigger trigger_updatemovie
on movies after update
as
begin
declare @oldtitle nchar(100),@newtitle nchar(100)
select @oldtitle=title from deleted
select @newtitle=title from inserted
update StarsIn set movieTitle=@newtitle
where movieTitle=@oldtitle
end
存储过程
Write the following stored procedures based on MoviesDB.
（1）Given a studio name and year, find the all comedy which the studio produced in this year.
SELECT statement:
--create procedure SPM1
[email protected]_name nchar(30) ,@year int
--as
--select Movies.title
--from Movies
--where [email protected]_name
--and [email protected]
--and Movies.genre='comedy'
--execute SPM1'Paramount',1992
（2）Given the name of a star, delete them from MovieStar and delete all their movies from StarsIn and Movies.
SELECT statement:
--CREATE PROCEDURE delete_name(@input_starname nchar(30))
--AS
--DECLARE
[email protected]_starin nchar(110)
--BEGIN
--SELECT @movies_starin = (STR(movieYear) + movieTitle) FROM StarsIn WHERE starName = @input_starname
--DELETE Movies WHERE (STR(year) + title) in (@movies_starin)
--DELETE StarsIn WHERE (STR(movieYear) + movieTitle) in (@movies_starin)
--DELETE MovieStar WHERE name = @input_starname
--END
– EXEC delete_name ‘Huang ShaoQi’
（3）Given a name and address, return 1 as output parameters if the person is a movie star but not an executive, 2 if the person is an executive but not a star, 3 if both, and 4 if neither.
SELECT statement:
--create procedure status
[email protected] nchar(30),@addr varchar(255),@number int output
--as
--begin
--declare @isStar int;
--declare @isExec int;
--set @isStar=(
-- select count(*)
-- from MovieStar
-- where MovieStar.name [email protected] and MovieStar.address = @addr);
--set @isExec=(
-- select COUNT(*)
-- from MovieExec
-- where MovieExec.name [email protected] and MovieExec.address = @addr);
-- if @isStar + @isExec = 0 set @number=4
-- else set @number=(@isStar+2*@isExec)
--end;
--DECLARE @goulu int
--EXEC status'Huang ShaoQi','* China',@goulu output
--SELECT @goulu
（4）Given an address, find the name of the unique star with that address if there is exactly one, and return NULL if there is none or more than one.
SELECT statement:
--create procedure findStar
[email protected] varchar(255),@star nchar(30) output
--as
--begin
-- set @star=NULL;
-- if 1 = (select COUNT(*) from MovieStar where [email protected])
-- set @star=(select name from MovieStar where address = @addr);
--end;
--DECLARE @yewei nchar(30)
--EXEC findStar'* China',@yewei output
--SELECT @yewei
以下是创建数据库的sql脚本，复制后直接运行即可启动实验学习。
USE [master]
GO
/****** Object: Database [MoviesDB] Script Date: 2020/10/17 22:27:28 ******/
CREATE DATABASE [MoviesDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'MoviesDB', FILENAME = N'd:\MoviesDB.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MoviesDB_log', FILENAME = N'd:\MoviesDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [MoviesDB] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MoviesDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [MoviesDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MoviesDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MoviesDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MoviesDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MoviesDB] SET ARITHABORT OFF
GO
ALTER DATABASE [MoviesDB] SET AUTO_CLOSE ON
GO
ALTER DATABASE [MoviesDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [MoviesDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MoviesDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MoviesDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MoviesDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MoviesDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MoviesDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MoviesDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MoviesDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MoviesDB] SET DISABLE_BROKER
GO
ALTER DATABASE [MoviesDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MoviesDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MoviesDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [MoviesDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [MoviesDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MoviesDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [MoviesDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [MoviesDB] SET RECOVERY SIMPLE
GO
ALTER DATABASE [MoviesDB] SET MULTI_USER
GO
ALTER DATABASE [MoviesDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [MoviesDB] SET DB_CHAINING OFF
GO
ALTER DATABASE [MoviesDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [MoviesDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [MoviesDB]
GO
/****** Object: Table [dbo].[MovieExec] Script Date: 2020/10/17 22:27:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MovieExec](
[name] [nchar](30) NULL,
[address] [varchar](255) NULL,
[cert#] [int] NOT NULL,
[netWorth] [int] NULL,
PRIMARY KEY CLUSTERED
(
[cert#] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Movies] Script Date: 2020/10/17 22:27:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Movies](
[title] [nchar](100) NOT NULL,
[year] [int] NOT NULL,
[length] [int] NULL,
[genre] [nchar](10) NULL,
[studioName] [nchar](50) NULL,
[producerC#] [int] NULL,
CONSTRAINT [PK__Movie__5D23B98A40AB8AD5] PRIMARY KEY CLUSTERED
(
[title] ASC,
[year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[MovieStar] Script Date: 2020/10/17 22:27:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MovieStar](
[name] [nchar](30) NOT NULL,
[address] [varchar](255) NULL,
[gender] [char](1) NULL,
[birthdate] [datetime] NULL,
CONSTRAINT [PK_MovieStar] PRIMARY KEY CLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[StarsIn] Script Date: 2020/10/17 22:27:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[StarsIn](
[movieTitle] [nchar](100) NULL,
[movieYear] [int] NULL,
[starName] [nchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Studio] Script Date: 2020/10/17 22:27:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Studio](
[name] [nchar](50) NOT NULL,
[address] [varchar](255) NULL,
[presC#] [int] NULL,
PRIMARY KEY CLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[MovieExec] ([name], [address], [cert#], [netWorth]) VALUES (N'Yang Hu ', N'Shang Hai', 90001, 20000000)
INSERT [dbo].[MovieExec] ([name], [address], [cert#], [netWorth]) VALUES (N'Rupert Murdoch ', N'New York', 90064, 130000000)
INSERT [dbo].[MovieExec] ([name], [address], [cert#], [netWorth]) VALUES (N'Gary Barber ', N'Los Angeles, California, U.S', 90067, 300000000)
INSERT [dbo].[MovieExec] ([name], [address], [cert#], [netWorth]) VALUES (N'Sanford Panitch ', N'Culver City, California, United States', 90232, 800000000)
INSERT [dbo].[MovieExec] ([name], [address], [cert#], [netWorth]) VALUES (N'Robert Allen Iger ', N'500 South Buena Vista Street,
Burbank, California, United States', 91521, 560000000)
INSERT [dbo].[MovieExec] ([name], [address], [cert#], [netWorth]) VALUES (N'Kevin Tsujihara ', N'Petaluma, California, U.S', 91522, 20000000)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'aa ', 1997, 100, NULL, NULL, NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'ab%aaaa% ', 2000, 20, NULL, NULL, NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Galaxy Quest ', 1999, 104, N'comedy ', N'DreamWorks ', 90067)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Gone With the Wind ', 1939, 231, N'drama ', N'MGM ', NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'King Kong ', 2005, 187, N'action ', N'Universal Picture ', NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Love Story ', 1970, 99, N'Romance ', N'Paramount Pictures ', NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Love, Lies and Murder ', 1991, 240, N'Drama ', N'Republic Pictures Corporation ', NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Pretty woman ', 1990, 119, N'comedy ', N'Disney ', 91521)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Return of the Jedi ', 1983, 134, N'action ', N'Fox ', NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Romance ', 1990, 119, N'comedy ', N'Disney ', 90064)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Showgirls ', 1995, 150, NULL, N'MGM ', NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Star Wars: Episode IV - A New Hope ', 1977, 124, N'sciFi ', N'Fox ', 91522)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Star Wars: Episode V - The Empire Strikes Back ', 1980, 124, N'action ', N'Fox ', NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'The Amazing Spider-Man ', 2012, 141, N'cartoon ', N'Columbia Pictures ', NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'The Faithful ', 2018, 104, N'historical', N'81 Film ', 90001)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'The Maltese Falcon ', 1942, 101, N'Mystery ', N'Warner ', NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Titanic ', 1997, 194, N'Romance ', N'Fox ', NULL)
INSERT [dbo].[Movies] ([title], [year], [length], [genre], [studioName], [producerC#]) VALUES (N'Wayne''s World ', 1992, 95, N'comedy ', N'Paramount ', 90232)
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Carrie Fisher ', N'123 Maple St.,Hollywood', N'F', CAST(0x8E3A0000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Clancy Brown ', N'Urbana, Ohio, U.S', N'M', CAST(0x54310000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Elizabeth Berkeley ', N'
Farmington Hills, Michigan ', N'F', CAST(0x676C0000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Emma Stone ', N'Los Angeles, California, U.S', N'F', CAST(0x7EC30000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Harrison Ford ', N'789 Palm Dr.,Beverly Hills', N'M', CAST(0x6E970000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Huang ShaoQi ', N'* China', N'M', CAST(0x66060000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Jane Fonda ', N'316 Alta Ave., Santa Monica, CA 90402 ', N'M', CAST(0x362C0000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Joanne Woodward ', N'WestPort,Connecticut', N'M', CAST(0x2AFC0000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Kate Winslet ', N'West Wittering, West Sussex', N'F', CAST(0x6C160000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Leonardo DiCaprio ', N'Los Angeles, California', N'M', CAST(0x6ACE0000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'MacLaine Shirley ', N'Old Malibu Rd Malibu', N'F', CAST(0x30F10000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Mark Hamill ', N'456 Oak Rd.,Brentwood', N'M', CAST(0x7E690000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Paul Newman ', N'WestPort,Connecticut', N'F', CAST(0x23C40000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Sandra_Bullock ', N'
New Orleans, Louisiana ', N'F', CAST(0x5C1C0000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Sheryl Lee ', N'Augsburg, West Germany', N'F', CAST(0x60060000 AS SmallDateTime))
INSERT [dbo].[MovieStar] ([name], [address], [gender], [birthdate]) VALUES (N'Wang LiKe ', N'Jinan Shandong', N'F', CAST(0x7C590000 AS SmallDateTime))
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Star Wars: Episode IV - A New Hope', 1977, N'Carrie Fisher ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Star Wars: Episode IV - A New Hope', 1977, N'Mark Hamill ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Star Wars: Episode IV - A New Hope', 1977, N'Harrison Ford ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Wayne''s World', 1992, N'Dana Carvey ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Wayne''s World', 1992, N'Mike Meyers ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Gone With the Wind', 1939, N'Vivien Leigh ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Star Wars: Episode V - The Empire Strikes Back', 1980, N'Carrie Fisher ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Star Wars: Episode V - The Empire Strikes Back', 1980, N'Mark Hamill ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Star Wars: Episode V - The Empire Strikes Back', 1980, N'Harrison Ford ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Titanic', 1997, N'Leonardo DiCaprio ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Titanic', 1997, N'Kate Winslet ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'The Amazing Spider-Man', 2012, N'Emma Stone ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Love Story', 1970, N'Clancy Brown ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Love Story', 1970, N'Sheryl Lee ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'Showgirls', 1995, N'Elizabeth Berkeley ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'The Faithful ', 2018, N'Wang LiKe ')
INSERT [dbo].[StarsIn] ([movieTitle], [movieYear], [starName]) VALUES (N'The Faithful ', 2018, N'Huang ShaoQi ')
INSERT [dbo].[Studio] ([name], [address], [presC#]) VALUES (N'81 Film ', N'Changqing Road, Fengtai District, Beijing', 90001)
INSERT [dbo].[Studio] ([name], [address], [presC#]) VALUES (N'Columbia ', N' 10202 W. Washington Blvd.,Culver City, CA 90232', 90232)
INSERT [dbo].[Studio] ([name], [address], [presC#]) VALUES (N'Disney ', N'500 S. Buena Vista St. Burbank CA 91521', 91521)
INSERT [dbo].[Studio] ([name], [address], [presC#]) VALUES (N'Fox ', N'10201 West Pico Boulevard, Los Angeles, CA 90064 ', 90064)
INSERT [dbo].[Studio] ([name], [address], [presC#]) VALUES (N'MGM ', N'10250 Constellation Boulevard,Los Angeles, CA 90067', 90067)
INSERT [dbo].[Studio] ([name], [address], [presC#]) VALUES (N'Universal ', N'100 Universal City Plaza, Universal City, CA 91608', 91608)
INSERT [dbo].[Studio] ([name], [address], [presC#]) VALUES (N'Warner ', N' 4000 Warner Boulevard, Burbank, CA 91522-0001', 91522)
USE [master]
GO
ALTER DATABASE [MoviesDB] SET READ_WRITE
GO
最后
这是浙水院软工数据库的五个实验，里面的句子基本能运行，而且查询结果也是可以保证的。掌握这些句子的用法就基本上考试的语法上没问题了。
数据库是英文的，所以实验也都是英文，当时做的挺苦的，每次拿到实验都愁眉苦脸的，一来是我不喜欢看英文，二来是数据库这块自己并不是非常好。寿老师讲的很生动，最后期末也给我们透了题型，期末考也拿了94分。也有学弟学妹们要复习数据库，就把实验贴了上来，希望能够在有限的时间里能帮助大家通过期末考试。