2006年10月25日 SQL语句

  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