2006年10月25日 星期天 天气晴 Last Modify: Oct 25 2006
==================================================================================
下面是数据库试验所需的SQL语句
SQL Server test passed
create table Studios(
studio_id int primary key,
name varchar(20) unique not null,
city varchar(50) unique not null,
state char(2) not null,
)
create table People(
person_id int primary key,
person_fname varchar(10) not null,
person_lname varchar(10) not null,
person_address varchar(30),
person_city varchar(20),
person_state char(2),
person_zip char(10),
person_phone char(10),
person_ssn char(9) unique,
person_union bit default '1'
)
create table Movies(
movie_id int primary key,
movie_title varchar(20) unique,
studio_id int unique references studios(studio_id),
director_id int references people(person_id),
gross int,
budget int,
release_date datetime,
constraint gross_constraint
check (gross < 1000),
constraint budget_constraint
check (budget < 2000)
)
create table Cast_Movies(
movie_id int references Movies(movie_id),
person_id int references People(person_id),
role varchar(20),
payment int,
primary key(movie_id, person_id),
constraint minimum_pay
check (payment > 500)
)
create table Locations(
movie_id int references Movies (movie_id),
city varchar(20),
state char(2),
primary key(movie_id, state)
)
create index person_full_name on people(person_fname, person_lname)
create unique index movie_index on Movies(movie_title, studio_id)
insert into studios
(city, state, name, studio_id)
values('Burbank','CA','MPM',2)
insert into People(
person_id, person_fname, person_lname, person_ssn)
values(1,'Jeff','Price','543890123')
select movie_title, studio_id
from Movies
select movie_title, studio_id, gross, gross * 1.5 as kldjf
from Movies
select movie_title, gross, budget, gross-budget as Money
from Movies
--对于数字的比较加不加引号是一样的
select movie_title, gross
from movies
where gross < '20'
select movie_title, gross
from movies
where movie_title != 'Codependence Day'
--比较<>,!=是一样的
select movie_title, gross
from movies
where movie_title <> 'Codependence Day'
select movie_title, gross
from movies
where gross < budget
select name, city
from studios
where lower(state)='ca'
select movie_title
from Movies
where gross is null
select movie_title
from Movies
where gross is not null
select movie_title, gross
from Movies
where gross >= 20 or gross is null
select movie_title, gross
from Movies
order by movie_title DESC
select *
from Movies
order by budget DESC
select moVie_title, studio_id, budget, gross, gross - budget as profits
from Movies
order by profits DESC
--与上面的一样
select moVie_title, studio_id, budget, gross, gross - budget as profits
from Movies
order by 5
select moVie_title, studio_id, budget, gross, gross - budget as profits
from Movies
order by studio_id, movie_id
Rev 1.0 Creat Document Oct 25 2006