Wang Ziwei's age.

Use master

go to

Create database jwgl

During the primary election

(-master file

name='jwgl 1 '，

filename = ' D:\ jwgl \ jwgl 1 . MDF '，

Size =3MB,

maxsize= 10MB，

File growth = 1MB

),

File group jwgl_group

(-auxiliary file 1

name='jwgl2 '，

filename='D:\jwgl\jwgl2.ndf '，

Size =3MB,

maxsize= 10MB

),

(

name='jwgl3 '，

filename='D:\jwgl\jwgl3.ndf '

)

log in

(

name='jwgllog 1 '，

filename = ' D:\ jwgl \ jwgllog 1 . ldf '，

size= 1mb，

maxsize= 10mb，

File growth = 1mb

),

(

name='jwgllog2 '，

filename='D:\jwgl\jwgllog2.ldf '，

size= 1mb，

File growth = 10%

)

Second,

Using jwgl

go to

- 1、( 1)

Create table xsxx

(

Xh char( 10) primary key,

xm nvarchar(6)，

Xb char(2) default "female" check (xb= "male" or xb= "female"),

nl int，

Xibe matriarch (20)

)

- (2)、

Create table kcxx

(

Kcbh char( 10) primary key,

kcmc nvarchar( 10)，

Xf tinyint inspection (xf>0 and xf<7)

)

- (3)、

Create table xkxx

(

xh char( 10)，

kcbh char( 10)，

Chj tinyint inspection (chj & gt=0 and chj<= 100),

Constraint pk_xh_kcbh primary key (xh, kcbh),

The foreign key (xh) refers to xsxx(xh),

Foreign key reference

)

- 2、

-insert into xsxx

Insert the value of xsxx ('9952 10 1',' Liu Xuan',' Male', 23,' Department of Software Engineering').

Insert into xsxx value ('9952 102',' Li',' Female', 20,' Information Department')

Insert into xsxx value ('9952 103',' Dou Lijie',' Female', 2 1,' Mathematics Department').

Insert into xsxx value ('9952 104',' Wang Ziwei',' female', 19,' Network Department').

Insert into xsxx value ('9952 105',' Zhang Hui',' Male', 25,' Department of Software Engineering').

Insert into xsxx value ('9952 106',' Jia Dong',' Male', 26,' Information Department').

Insert into xsxx value ('9952 107',' Zhou Dandan',' Female', 24,' Network Department').

-insert kcxx

Insert kcxx value ('1',' network basis', 6)

Insert kcxx value ('2',' language programming', 6)

Insert kcxx value ('3',' database management', 6)

-insert xkxx

Insert xkxx value ('9952 10 1',' 1', 69).

Insert xkxx value ('9952 10 1',' 2', 73).

Insert xkxx value ('9952 10 1',' 3', 86).

Insert xkxx values ('9952 102',' 1', 9 1).

Insert xkxx values ('9952 103',' 3', 56).

Insert xkxx value ('9952 104',' 1', 56).

Insert xkxx value ('9952 104',' 2', 69)

Insert xkxx value ('9952 105',' 1', 73).

Insert xkxx value ('9952 106',' 2', 86).

- 3、

- ( 1)

Select * from xsxx where xib<& gt Department of Software Engineering and Beijing University of Technology.

- (2)

Select xsxx.xh, xm from xsxx inner join xkxx on xsxx.xh=xkxx.xh

Where nl & gt=25 and xkxx.chj & gt=60.

- (3)

select * from xsxx where XM like ' Li[Yang]% '

- (4)

Select xsxx.xm, kcxx.kcmc, xkxx.chj from xkxx inner join xsxx on xkxx.xh inner join kcxx on xkxx.kcbh=kcxx.kcbh

Where kcxx.kcbh=' 1' is ordered by xkxx.chj desc.

- (5)

Select kcxx. Kcmc, count (kcxx. Kcmc) as the number of elective courses, join KCXX on xkxx. KCBH = KCXX. KCBH from within XKXX.

Group by kcxx.kcmc

- (6)

select xh，xm，xib from xsxx where xh in(select xh from xkxx group by xh having count(xh)>=3)

-three,

Create a unique index index_xm on xsxx(xm)

Four,

-Use tables xsxx, xkxx and kcxx to create a view view_ssc with columns such as name, course name and grade.

If it exists (select * from sysobjects where name =' view _ SSC')

Delete view view_ssc

go to

Create view view_ssc

be like

Select xsxx.xm, kcxx.kcmc, xkxx. chj. xh = xkxx. xh from xsxx inner join xkxx on kcxx. kcbh = kcxx. kcbh on inner join kcxx.

go to

Select * from view_ssc-test the query view.