數(shù)據(jù)庫(kù)實(shí)驗(yàn)及答案

1、實(shí)驗(yàn)二 sql語言的基本操作實(shí)驗(yàn)?zāi)康暮鸵螅赫莆绽胹ql語句完成各種查詢操作的能力。重點(diǎn)掌握用select語句進(jìn)行各種查詢； 掌握insert語句的用法。 實(shí)驗(yàn)內(nèi)容：用sql語句完成一下的要求：1.查詢信息系（is）的所有學(xué)生信息select * from student where sdept=is2.查詢選修了“數(shù)學(xué)”課的所有學(xué)生名單select s.sno,sname from student s,course c,scwhere s.sno=sc.sno and o=o and cname=數(shù)學(xué)3.查詢至少選修了一門其直接先行課為5號(hào)課程的學(xué)生的姓名。select

2、sname from student s, sc, course cwhere s.sno=sc.sno and o=o and pcno=54.查詢?nèi)w學(xué)生的姓名和出生年份。select sname,year(now()-sage as 出生年份 from student5.查詢所有姓王的學(xué)生。select * from student where sname like 王%6.查詢選修了3號(hào)課程的學(xué)生姓名及成績(jī)，并按成績(jī)降序排序。select sname,gradefrom student s, scwhere s.sno=sc.sno and o=3order

3、 by grade desc7.查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號(hào)升序排列，同一系中的學(xué)生按年齡降序排列。select *from studentorder by sdept asc,age desc8.計(jì)算2號(hào)課程的平均成績(jī)。select avg(grade)from scwhere cno=29.查詢選修了2號(hào)課程的學(xué)生的最高成績(jī)。select max(grade) from sc where cno=210.求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。select cno as 課程號(hào),count(sno) as 人數(shù)from scgroup by cno11.查詢至少選修了3門課程以上的學(xué)生學(xué)

4、號(hào)。select snofrom sc group by sno having count(*)212.查詢“數(shù)據(jù)庫(kù)”的間接先行課。select amefrom course c1,course c2,course c3where c1.cpno=o and ame=數(shù)據(jù)庫(kù) and c2.cpno=o13.查詢平均成績(jī)最高的學(xué)生的學(xué)號(hào)和姓名。select top 1 sno,avg(grade)from scgroup by snoorder by avg(grade) desc14.查詢數(shù)學(xué)成績(jī)最高的學(xué)生的學(xué)號(hào)和姓名。select top 1 s.sn

5、o,sname,gradefrom student s,course c, scwhere s.sno=sc.sno and o=o and cname=數(shù)學(xué)order by grade desc15.查詢出成績(jī)最低學(xué)號(hào)最大的學(xué)生學(xué)號(hào)。select top 1 sc.sno,gradefrom scorder by grade asc,sno desc16.查詢成績(jī)高于學(xué)生平均成績(jī)的記錄。select *from scwhere grade(select avg(grade)from sc )17.查詢至少選修了1號(hào)課程和3號(hào)課程的學(xué)生學(xué)號(hào)。select sc1.snofro

6、m sc sc1,sc sc2where sc1.sno=sc2.sno and o=1 and o=318.查詢只選修了1號(hào)課程和3號(hào)課程的學(xué)生學(xué)號(hào)。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號(hào)課程的學(xué)生姓名。select distinct s.snamefrom student s, scwhere s.sno=sc.sn

7、o and o!=120.查詢選修了全部課程的學(xué)生姓名。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所選修的全部課程的學(xué)生學(xué)號(hào)。 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.查詢沒有不及格課程的學(xué)生的學(xué)號(hào)和姓名。 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.查詢沒有不及格學(xué)生的課程的課程號(hào)和課程名。 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.建立信息系學(xué)生視圖，并從視圖中查詢年齡最大的學(xué)生記錄。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的默認(rèn)參照完整性；為cno定義名為lcno的默認(rèn)參照完整性；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);另一組數(shù)據(jù)同上進(jìn)行插入。用sql語句向course表輸入如下元組：(1,數(shù)據(jù)庫(kù),5,4);(2,數(shù)學(xué),null,2);insert into coursevalues (1,數(shù)據(jù)庫(kù),5,4);另一組數(shù)據(jù)同上進(jìn)行插入。用s

13、ql語句向sc表輸入如下元組：(95001,1,92);(95001,2,85); (95002,2,90);insert into scvalues (95001,1,92);其它組數(shù)據(jù)同上進(jìn)行插入。5.執(zhí)行下列語句，并查看執(zhí)行結(jié)果。如果不能正確執(zhí)行給出錯(cuò)誤原因。insert into student values(95001,張力,男,20,cs);不能執(zhí)行，student中sno屬性為unique，student中已經(jīng)有學(xué)號(hào)為95001的學(xué)生信息了，所以不能再插入相同學(xué)號(hào)的學(xué)生信息。insert into student values(95003,李勇,男,20,cs);不能執(zhí)行，stu

14、dent中cname屬性為unique，student中已經(jīng)有姓名為李勇的學(xué)生信息了，所以不能再插入相同姓名的學(xué)生信息。insert into sc values(95004,1,92);不能執(zhí)行，根據(jù)參照完整性，在student表中沒有95004的信息，所以不能插入。delete from student where sno=95001;不能執(zhí)行，因?yàn)樵趕c表中有95001的信息。update course set cno=3 where cno=2;不能執(zhí)行，因?yàn)閟c表中有cno=2的信息。6.給student表的ssex列添加名為fm的約束，使其取值只能取男或女。alter table

15、studentadd constraint fm check (ssex in (男,女)執(zhí)行insert into student values(95005,張力,f,20,cs)，查看執(zhí)行結(jié)果。不能進(jìn)行插入，因?yàn)椋斎氲男畔⒅行詣e必須是男或女。7.給student表的sage列添加約束，使其年齡不得超過20歲。查看約束是否能正確添加，并分析其原因。alter table studentadd constraint age check (sage 20)不能正確添加，alter table 語句與 column check 約束 age 沖突。該沖突發(fā)生于數(shù)據(jù)庫(kù) 學(xué)生信息，表 student, column sage，因?yàn)楸頂?shù)據(jù)有sage 20的信息。8.刪除約束lsno和lcno。alter table scdrop constraint lsno,lcno9.為sc表添加在列sno上的外鍵約束lsno1，并定義為級(jí)聯(lián)刪除。執(zhí)行delete from student where sno=95001;查看執(zhí)行結(jié)果。alter table scadd constraint lsno1 foreign key (sno) references student(sno)on delete cascade;由于是級(jí)聯(lián)刪除，所以除