数据库实验及答案

1、实验二 sql语言的基本操作实验目的和要求：掌握利用sql语句完成各种查询操作的能力。重点掌握用select语句进行各种查询； 掌握insert语句的用法。 实验内容：用sql语句完成一下的要求：1.查询信息系（is）的所有学生信息select * from student where sdept=is2.查询选修了“数学”课的所有学生名单select s.sno,sname from student s,course c,scwhere s.sno=sc.sno and o=o and cname=数学3.查询至少选修了一门其直接先行课为5号课程的学生的姓名。select

2、sname from student s, sc, course cwhere s.sno=sc.sno and o=o and pcno=54.查询全体学生的姓名和出生年份。select sname,year(now()-sage as 出生年份 from student5.查询所有姓王的学生。select * from student where sname like 王%6.查询选修了3号课程的学生姓名及成绩，并按成绩降序排序。select sname,gradefrom student s, scwhere s.sno=sc.sno and o=3order

3、 by grade desc7.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。select *from studentorder by sdept asc,age desc8.计算2号课程的平均成绩。select avg(grade)from scwhere cno=29.查询选修了2号课程的学生的最高成绩。select max(grade) from sc where cno=210.求各个课程号及相应的选课人数。select cno as 课程号,count(sno) as 人数from scgroup by cno11.查询至少选修了3门课程以上的学生学

4、号。select snofrom sc group by sno having count(*)212.查询“数据库”的间接先行课。select amefrom course c1,course c2,course c3where c1.cpno=o and ame=数据库 and c2.cpno=o13.查询平均成绩最高的学生的学号和姓名。select top 1 sno,avg(grade)from scgroup by snoorder by avg(grade) desc14.查询数学成绩最高的学生的学号和姓名。select top 1 s.sn

5、o,sname,gradefrom student s,course c, scwhere s.sno=sc.sno and o=o and cname=数学order by grade desc15.查询出成绩最低学号最大的学生学号。select top 1 sc.sno,gradefrom scorder by grade asc,sno desc16.查询成绩高于学生平均成绩的记录。select *from scwhere grade(select avg(grade)from sc )17.查询至少选修了1号课程和3号课程的学生学号。select sc1.snofro

6、m sc sc1,sc sc2where sc1.sno=sc2.sno and o=1 and o=318.查询只选修了1号课程和3号课程的学生学号。select snofrom sc where cno=1 and sno in(select sno from sc where cno=3)and sno in(select sno from sc group by sno having count(cno)=2)19.查询没有选修1号课程的学生姓名。select distinct s.snamefrom student s, scwhere s.sno=sc.sn

7、o and o!=120.查询选修了全部课程的学生姓名。select snamefrom student swhere not exist (select * from course c where not exist (select * from sc where s.sno=sc.sno and o=o)21.查询至少选修了95002所选修的全部课程的学生学号。 select sc1.sno from sc sc1 where not exist (select * from sc sc2 where sc2.sno=95002 and not exist( s

8、elect * from sc sc3 where o=o and sc1.sno=sc3.sno)22.查询没有不及格课程的学生的学号和姓名。 select distinct sc.sno,s.snamefrom sc,student swhere sc.sno=s.sno and not exists (select * from sc sc2 where sc.sno=sc2.sno and sc2.grade60)23.查询没有不及格学生的课程的课程号和课程名。 select distinct o,amefrom sc ,course cwhe

9、re o=o and not exists (select * from sc sc2 where o=o and sc2.grade60)24.建立信息系学生视图，并从视图中查询年龄最大的学生记录。gocreate view is_student(sno,sname,sage)as select sno,sname,sagefrom s where sdept=isselect max(sage)from is_student1.用sql语句定义表student(sno,sname,ssex,sage，sdept)，并加入如下约束：主键：sno；sna

10、me有唯一约束；sname,ssex,sage都不允许空；create table student (sno char(10) not null unique, sname char(20) not null unique, ssex char(2) not null, sage int not null, sdept char(20) not null, primary key (sno)2.用sql语句定义表course(cno,cname,cpno,credit)，并加入如下约束：主键：cno；cname不允许空；create table course(cno char(10) not

11、null unique, cname char(20) not null, cpnochar(10), credit char(10), primary key (cno)3.用sql语句定义表sc(sno,cno,cj)，并加入如下约束：主键：sno,cno；为sno定义名为lsno的默认参照完整性；为cno定义名为lcno的默认参照完整性；create table sc(snochar(10) not null, cnochar(10) not null, gradeint, primary key (sno,cno), constraint lsno foreign key (sno)

12、references student(sno), constraint lcno foreign key (cno) references course(cno);4.用sql语句向student表输入如下元组：(95001,李勇,男,20,cs); (95002,刘晨,女,21,is);insert into studentvalues (95001,李勇,男,20,cs);另一组数据同上进行插入。用sql语句向course表输入如下元组：(1,数据库,5,4);(2,数学,null,2);insert into coursevalues (1,数据库,5,4);另一组数据同上进行插入。用s

13、ql语句向sc表输入如下元组：(95001,1,92);(95001,2,85); (95002,2,90);insert into scvalues (95001,1,92);其它组数据同上进行插入。5.执行下列语句，并查看执行结果。如果不能正确执行给出错误原因。insert into student values(95001,张力,男,20,cs);不能执行，student中sno属性为unique，student中已经有学号为95001的学生信息了，所以不能再插入相同学号的学生信息。insert into student values(95003,李勇,男,20,cs);不能执行，stu

14、dent中cname属性为unique，student中已经有姓名为李勇的学生信息了，所以不能再插入相同姓名的学生信息。insert into sc values(95004,1,92);不能执行，根据参照完整性，在student表中没有95004的信息，所以不能插入。delete from student where sno=95001;不能执行，因为在sc表中有95001的信息。update course set cno=3 where cno=2;不能执行，因为sc表中有cno=2的信息。6.给student表的ssex列添加名为fm的约束，使其取值只能取男或女。alter table

15、studentadd constraint fm check (ssex in (男,女)执行insert into student values(95005,张力,f,20,cs)，查看执行结果。不能进行插入，因为，所输入的信息中性别必须是男或女。7.给student表的sage列添加约束，使其年龄不得超过20岁。查看约束是否能正确添加，并分析其原因。alter table studentadd constraint age check (sage 20)不能正确添加，alter table 语句与 column check 约束 age 冲突。该冲突发生于数据库 学生信息，表 student, column sage，因为表数据有sage 20的信息。8.删除约束lsno和lcno。alter table scdrop constraint lsno,lcno9.为sc表添加在列sno上的外键约束lsno1，并定义为级联删除。执行delete from student where sno=95001;查看执行结果。alter table scadd constraint lsno1 foreign key (sno) references student(sno)on delete cascade;由于是级联删除，所以除