【数据库期末复习】查询、更新、视图、索引、约束触发器、存储过程语句复习

2021/6/14 2:23:22

编程Tag： title 视图 name Insert 触发器 dbo Values 复习 movies

本文主要是介绍【数据库期末复习】查询、更新、视图、索引、约束触发器、存储过程语句复习，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

数据库复习

脚本文件在最后面，下载运行即可，基于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
--@Studio_name nchar(30) ,@year int
--as
--select Movies.title
--from Movies
--where Movies.studioName=@Studio_name
--and Movies.year=@year
--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 
--@movies_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
--@person 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 =@person and MovieStar.address = @addr);
--set @isExec=(	
--	select COUNT(*)
--	from MovieExec
--	where MovieExec.name =@person 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','TaiWan 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
--@addr varchar(255),@star nchar(30) output
--as
--begin
--	set @star=NULL;
--	if 1 = (select COUNT(*) from MovieStar where address=@addr)
--		set @star=(select name from MovieStar where address = @addr);
--end;

--DECLARE @yewei nchar(30)
--EXEC findStar'TaiWan 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'TaiWan 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分。也有学弟学妹们要复习数据库，就把实验贴了上来，希望能够在有限的时间里能帮助大家通过期末考试。

这篇关于【数据库期末复习】查询、更新、视图、索引、约束触发器、存储过程语句复习的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！