SQL Server 2008 习题2

-一.
create database WXK1
on primary
(name=WXK1,
filename=‘f:\08_17网络\WXK1.mdf’,
size=4MB,
maxsize=50MB,
filegrowth=10%
)
log on
(name=WXK1_log,
filename=‘f:\08_17网络\WXK1_log.ldf’,
size=1MB,
maxsize=3MB,
filegrowth=10%
)
–二.1.
use WXK1
create table xs
(学号 char (6) not null primary key,
姓名 char (8) not null,
专业名 char (10) null,
性别 char (1) not null,
出生时间 time(4) not null,
总学分 int not null
)
–2.
use WXK1
create table kc
(课程号 char (3) not null primary key,
课程名 nchar (16) not null,
开课学期 int not null,
学时 int not null,
学分 int null
)
–三。1.
use WXK1
insert into xs values(‘001101’, ‘王金华’, ‘软件技术’,‘1’, ‘1990-02-10’, ‘50’)
insert into xs values(‘001102’, ‘程周杰’, ‘软件技术’, ‘1’ ,‘1991-02-01’, ‘50’)
insert into xs values(‘001220’ ,‘吴莉丽’, ‘网络技术’, ‘0’ ,‘1989-11-12’, ‘42’)
–2.
use WXK1
insert into kc values (‘101’ ,‘计算机基础’ ,‘1’, ‘80’, ‘5’)
insert into kc values (‘102’, ‘C程序设计’, ‘2’, ‘68’ ,‘4’)
insert into kc values (‘206’ ,‘高等数学’ ,‘4’ ,‘68’, ‘4’)
–四,1.
use cjgl
select *
from xs
–2.
use cjgl
select 学号,姓名,出生时间
from xs
–3.
use cjgl
select top 6 学号
from xs
–4.
use cjgl
select *
from xs
where 专业名=‘软件技术’
–5.
use cjgl
select *
from xs
where 出生时间 like ‘1990’
–6.
use cjgl
select 学号,姓名
from xs
where 姓名 like ‘王%%’
–7.
use cjgl
select 出生时间
from xs
order by 出生时间 desc
–8.
use cjgl
select *
from xs jion cj
on xs.学号=cj.学号
cj join kc
on cj.课程号=kc.课程号
where 课程名 in (select *
from xs
where 姓名=‘王元’)
–9.
create view xscj
as
select 学号,课程名,成绩
from kc jion cj
on kc.课程号=cj.课程号
–10.
create view xs_info
as
select 学号,姓名,课程名,成绩
from xs jion cj
on xs.学号=cj.学号
cj join kc
on cj.课程号=kc.课程号