實(shí)驗(yàn)三創(chuàng)建數(shù)據(jù)庫(kù)和數(shù)據(jù)表實(shí)驗(yàn)報(bào)告

1、實(shí)驗(yàn)三1、 建立數(shù)據(jù)庫(kù)university2、 建表：1) Student: USE universityGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.student(snum nchar(6) COLLATE Chinese_PRC_CI_AS NOT NULL,sname nchar(10) COLLATE Chinese_PRC_CI_AS NULL,sex nchar(2) COLLATE Chinese_PRC_CI_AS NULL,dept nchar(20) COLLATE Chinese_PRC_

2、CI_AS NULL,birthday datetime NULL,telephone nchar(12) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT PK_student PRIMARY KEY CLUSTERED (snum ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON PRIMARY) ON PRIMARYGOALTER TABLE dbo.student WITH CHECK ADD CONSTRAINT CK_sex CHECK (sex=女 OR sex=男)GOALTER TABL

3、E dbo.student CHECK CONSTRAINT CK_sexGOALTER TABLE dbo.student WITH CHECK ADD CONSTRAINT CK_snum CHECK (substring(snum,(1),(1)=S)GOALTER TABLE dbo.student CHECK CONSTRAINT CK_snumGOALTER TABLE dbo.student WITH CHECK ADD CONSTRAINT CK_tel CHECK (isnumeric(substring(telephone,(1),(3)=(1) AND isnumeric

4、(substring(telephone,(5),(8)=(1)GOALTER TABLE dbo.student CHECK CONSTRAINT CK_tel視圖：2) Course: USE universityGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.course(cnum nchar(6) COLLATE Chinese_PRC_CI_AS NOT NULL,cname nchar(20) COLLATE Chinese_PRC_CI_AS NULL,credits smallint NULL,de

5、scr nchar(10) COLLATE Chinese_PRC_CI_AS NULL,dept nchar(20) COLLATE Chinese_PRC_CI_AS NULL,textbook nchar(40) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT PK_course PRIMARY KEY CLUSTERED (cnum ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON PRIMARY) ON PRIMARYGOALTER TABLE dbo.course WITH CHECK AD

6、D CONSTRAINT FK_course_course FOREIGN KEY(cnum)REFERENCES dbo.course (cnum)GOALTER TABLE dbo.course CHECK CONSTRAINT FK_course_courseGOALTER TABLE dbo.course WITH CHECK ADD CONSTRAINT CK_course CHECK (substring(cnum,(1),(1)=c)GOALTER TABLE dbo.course CHECK CONSTRAINT CK_courseGOALTER TABLE dbo.cours

7、e WITH CHECK ADD CONSTRAINT CK_course_1 CHECK (credits(0) AND credits=(0) AND score=(100)GOALTER TABLE dbo.sc CHECK CONSTRAINT CK_Table_score4) Section: USE universityGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.section(secnum nchar(8) COLLATE Chinese_PRC_CI_AS NOT NULL,cnum nchar

8、(6) COLLATE Chinese_PRC_CI_AS NULL,pnum nchar(6) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT PK_section PRIMARY KEY CLUSTERED (secnum ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON PRIMARY) ON PRIMARYGOALTER TABLE dbo.section WITH CHECK ADD CONSTRAINT FK_section_course FOREIGN KEY(cnum)REFERENCE

9、S dbo.course (cnum)GOALTER TABLE dbo.section CHECK CONSTRAINT FK_section_courseGOALTER TABLE dbo.section WITH CHECK ADD CONSTRAINT CK_secnum CHECK (isnumeric(secnum)=(1) AND len(secnum)=(5)GOALTER TABLE dbo.section CHECK CONSTRAINT CK_secnumGOALTER TABLE dbo.section WITH CHECK ADD CONSTRAINT CK_Tabl

10、e_pnum CHECK (substring(pnum,(1),(1)=p)GOALTER TABLE dbo.section CHECK CONSTRAINT CK_Table_pnum實(shí)驗(yàn)四select sname From student,scwhere student.snum=sc.snum and score=80select sname as 獲獎(jiǎng)學(xué)金學(xué)生From student,scwhere student.snum=sc.snum group by sname Having min(score)=80 and avg(score)=90select sname,score

11、From student,sc,section,coursewhere student.snum=sc.snum and sc.secnum=section.secnum and um=um and cname=大學(xué)英語(yǔ)order by score descselect um as 課程號(hào),count(snum) as 人數(shù)From sc,section,course where sc.secnum=section.secnum and um=umgroup by umselect Distinct sname, student.deptFrom student,sc,section,cour

12、se where student.snum=sc.snum and sc.secnum=section.secnum and um=um and ame=數(shù)據(jù)庫(kù)技術(shù)and sname not in( select snameFrom student,sc,section,course where student.snum=sc.snum and sc.secnum=section.secnum and um=um and ame=高等數(shù)學(xué))select cnamefrom coursewhere textbook like%高等教育出版社%select cname,max(score) as 最高分,min(score) as 最低分,avg(score) as 平均分From sc,section,course where sc.secnum=section.secnum and um=umgroup by