1、Create Table Student( Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),Sname VARCHAR(20) constraint uq_sname unique,Sage SMALLINT,Ssex CHAR(2) DEFAULT 男 CHECK (Ssex= OR Ssex=女),Sdept CHAR(2);课程表Course,由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成,记作:Course(Cno,Cname,Cpno,Ccredit),其中主码为Cno。Cname取值唯一,Cpno参照c
2、no。Create Table Course ( Cno CHAR(2) NOT NULL PRIMARY KEY(Cno),Cname VARCHAR(20) UNIQUE,Cpno CHAR(2) references course(cno),Ccredit SMALLINT);学生SC,由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成,记作:SC(Sno,Cno,Grade)其中主码为(SNO,CNO),sno参照student表的sno列,cno参照course表的cno列。grade取值范围0-100。Create Table SC( Sno CHAR(5) NOT
3、 NULL CONSTRAINT S_F FOREIGN KEY REFERENCES Student(Sno),Cno CHAR(2) NOT NULL Constraint fk_cno FOREIGN KEY(Cno) REFERENCES Course(Cno),Grade SMALLINT CHECK (Grade IS NULL) OR (Grade BETWEEN 0 AND 100),PRIMARY KEY(Sno,Cno),);3、向Student表增加“入学时间”列,其数据类型为日期型。Alter table studentAdd 入学时间 smalldatetime4、将
4、年龄的数据类型改为smallint。Alter column sage smallint5、删除学生姓名必须取唯一值的约束。Drop uq_sname6、向course表中增加一个约束,以限制Ccredit的取值范围为1-4。Alter table courseAdd constraint ck_ccredit check(Ccredit between 1 and 4)7、向student表中插入值(98006,欧阳姗姗,18,女,CS)。INSERT INTO student VAlues(98006,欧阳姗姗,18,,CS8、从student表中删除姓名为李四的学生。Delete fro
5、m studentWhere sname=李四9、在SC表中将2号课程的成绩加5。Update scSet grade=grade+5Where cno=210、在SC表中将2号课程的成绩减5。Set grade=grade-5实验三 单表查询(一)学号 姓名 成绩:1、掌握投影查询。2、掌握选择查询。对JXGL数据库进行查询。1、查询全体学生的学号与姓名。select sno,snamefrom student2、查询全体学生的姓名、学号、所在系。select sname,sno,sdept3、查询全体学生的详细记录。select *4、查全体学生的姓名及其出生年份。select sname
6、,year(getdate()-sage 出生年份5、查询全体学生的姓名、出生年份和所在系,要求用小写字母表示所有系名。,lower(sdept) sdept6、查询选修了课程的学生学号。select distinct snofrom sc7、查询所有年龄在20岁以下的学生姓名及其年龄。select sname,sagewhere sage=9014、查询所有姓刘学生的姓名、学号和性别。Select sname,sno,ssexWhere sname like 刘%15、查询姓欧阳且全名为三个汉字的学生的姓名。Select snameWhere sname like 欧阳_16、查询名字中第2
7、个字为阳字的学生的姓名和学号。Select sname,snoWhere sname like _阳%17、查询所有不姓刘的学生姓名。Where sname not like 刘%18、某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。Select sno,cnoFrom scWhere grade is null19、查所有有成绩的学生学号和课程号。Where grade is not null实验四 单表查询(二)3、掌握使用集函数的单表查询。4、掌握对查询结果排序与分组。1、查询学生总人数。select count(*) 总人数2、查
8、询选修了课程的学生人数。select count(distinct sno) 人数3、计算1号课程的学生平均成绩。select avg(grade) 平均分where cno=14、查询选修1号课程的学生最高分数。select max(grade) 最高分5、计算选修1号课程的学生人数、最高成绩、最低成绩及平均成绩。,max(grade) ,min(grade) 最低分,avg(grade) 6、求各个课程号及相应的选课人数。select cno,count(*) 选课人数group by cno7、查询选修了3门以上课程的学生学号。select snogroup by snohaving c
9、ount(*)38、查询有3门以上课程是90分以上的学生的学号。where grade909、查询各专业的专业名及学生人数。select sdept,COUNT(*) 学生人数group by sdept10、查询平均成绩在80分以上的学生的学号及平均成绩select sno,AVG(grade) as 平均成绩having AVG(grade)8011、查询至少有2个人选修的课程的课程号,选课人数。、select cno,COUNT(*) having COUNT(*)=212、查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。select sno,grade3order by
10、 grade desc13、查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。order by sdept asc,sage desc实验五 多表查询(一)系名 专业及班级 5、掌握连接查询的一般格式。6、熟练掌握连接查询的各种方法。1.查询选修2号课程的学生名字。select snamefrom student,scwhere student.sno=sc.sno and cno=22.查询选修了这样课程的学生学号,其直接先行课是5号课程。from sc,coursewhere o=o and cpno=53.查询选修的课程成绩为90分以上的学生姓名与课程名和成
11、绩。select sname,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and o=o and grade4.查询每一门课的间接先修课(即先修课的先修课)select o,b.cpnofrom course a,course bwhere a.cpno=o5.查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。select student.sno,sname6.查询每个学生的学号、姓名、选修的课程名及成绩。select student.sno,sname,cname,grade7.查询性别为男、课程成绩及格的学生信
12、息及课程号、成绩。select student.*,cno,gradewhere student.sno=sc.sno and grade=60 and ssex=8.查询平均成绩大于85分的学号、姓名、平均成绩。select student.sno,sname,avg(grade) where student.sno=sc.snogroup by student.sno,snamehaving avg(grade)859.查询选修了数据库系统的学生的姓名及成绩。select sname,gradefrom student,course,scwhere student.sno=sc.sno a
13、nd o=o and cname=数据库系统10.查询每个系的学生选修的1号课程的平均成绩,最高分,最低分,选课人数。,count(*) 实验六 多表查询(二)7、掌握子查询语句的一般格式。8、熟练掌握子查询的各种方法。11.查询不选1号课程的学生名字。where sno not in(select sno from sc where cno=12.查询至少被2个学生选修的课程的课程名。select cnamefrom coursewhere cno in (select cno from sc group by cno having count(*)=2)13.查询计算机系(CS)选修了2门
14、及以上课程的学生的学号。where sno in(select sno from student where sdept=14.查询与“钱横”在同一个系学习的学生信息。where sname!=钱横 and sdept= (select sdept from student where sname=15.查询选修了课程名为“数学分析”的学生学号、姓名和所在系。select sno,sname,sdeptwhere sno in(select sno from sc where cno in(select cno from course where cname=数学分析)16.查询同时选修了“1
15、”课程和“2”课程的学生的姓名。where sno in (select sno where cno= and sno in (select sno from sc where cno=17.查询至少选修了3门课程的学生的姓名。 group by sno=3)18.查询平均成绩在80分以上的学生的姓名。 having avg(grade)80)实验七 视图1.掌握视图定义方法和使用方法。2.掌握对视图数据的增,删,改操作。19.创建计算机系(CS)的学生信息视图V_1。create view v_1aswhere sdept=20.建立计算机系(CS)选修了1号课程的学生信息视图V_2。cre
16、ate view v_2 AND SNO IN(SELECT SNO FROM SC WHERE CNO=21.建立计算机系选修了1号课程且成绩在90分以上的学生的视图V_3。create view v_3 AND SNO IN (SELECT SNO FROM SC WHERE CNO= AND GRADE90)22.定义一个反映学生学号,姓名,出生年份的视图V_4。CREATE VIEW V_4(学号,姓名,出生年份)ASSELECT SNO,SNAME,YEAR(GETDATE()-SAGEFROM STUDENT23.将学生的学号及其平均成绩定义为一个视图V_5。CREATE VIEW
17、 V_5(学号,平均成绩)SELECT SNO,AVG(GRADE)FROM SCGROUP BY SNO24.创建计算机专业(CS)学生的平均成绩视图V_6,包括学号和平均成绩.CREATE VIEW V_6(学号,平均成绩)WHERE SNO IN(SELECT SNO FROM STUDENT WHERE SDEPT=25.通过V_2视图查询计算机系选修了1号课程的学生姓名,性别,年龄。SELECT SNO,SNAME,SAGEFROM V_226.在V_5视图中查询平均成绩在90分以上的学生学号和平均成绩。SELECT 学号,平均成绩FROM V_5WHERE 平均成绩27.将计算机系
18、学生视图V_1中学号98002的学生姓名改为“刘辰”。UPDATE V_1SET SNAME=刘辰WHERE SNO=9800228.向计算机系学生视图V_1中插入一个新的学生记录:95029,赵新,20岁INSERT INTO V_1(SNO,SNAME,SAGE,SDEPT)VALUES(95029赵新,20,29.删除视图V_1中学号为95029的记录。DELETE V_1实验八 索引9、掌握索引的类型10、掌握使用界面方式和SQL语句创建索引,查看索引,删除索引的方法。分别使用界面方式和SQL语句完成下面题。1、为student表的sname列创建索引。Create index ind
19、_sname on student(sname)2、为student表按sname的升序,sage的降序建立索引。Create index ind_student on student(sname asc,sgae desc)3、根据course表的cname列降序创建唯一索引。Create unique index ind_cname on course(cname)4、根据student表中sno列创建唯一聚集索引。Create unique clustered index ind_sno on student(sno)5、删除course表cname列上的索引。Drop index ame6、使用存储过程查看student表上所有的索引。Exec Sp_helpindex student
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1