2006年10月25日数据库试验

        2006年10月25日 星期天 天气晴         Last Modify： Oct 25 2006

==================================================================================

 

下面是数据库试验所需的SQL语句

建立各种表格及索引和相应的限制等

 

SQL Server 2000 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        Create Document      Oct 25 2006

        Rev 1.1    整理文档         Jan 25, 2008