【期末冲刺SQL例题】

实验四
2、建表use s_ T
create table student (
Sno char ( 11)not null primary key,Sname char (8) not null,
ssex char (2) not null,sage Tinyint not null,sdept char (10) not null
check (ccredit >=l and ccredit <=6)(学分取1-6的整数)
check (Grade >=l and Grade <=100)(成绩在0-100之间));

3、将course表的cpno的长度改为3
alter table Course alter column cpno char (3)

4、给student表中的ssex列的增加默认约束，默认值为“男”alter table student add constraint df_student_ssexdefault '男’for ssex;

5、为course表增加一列teacher,char(8)
alter table Course add teacher char i8j;

6、删除course表的teacher列
alter table Course drop column teacher;

7、插入数据
insert into s (SNO , SNAME, STATUS , CITY)values
 ( 's1','精益'，'20'，'天津'），
( 'S2','盛锡， '10','北京')，
( 'S3’，'东方红'，'30'，'北京')，
( 's4'，'丰泰盛','20'，'天津')，
( 'S5','为民·， '50','上海');

8、为s表设一个主键
alter table s add primary key(sNO) ;

9、为s表增加一个唯一索引s_sno
create unique index s_sno on s (SNO) ;

为sP表增加索引spji_sno_pno_jno,要求索引列 sno用降序,pno、jno用升序
create index spj_sno_pno_jno on SPJ (sNo desc,pno,jno)

为HISD的Doctor表按医生ID(dID）升序建立唯一索引
create unique index Dindex on Doctor (dID)

为HISD 的 Doctor表按医生ID (dID)降序建立唯一索引create unique index Dindex on Doctor (dID desc)


10、为SP」表加外键约束（若建表时已经建立外键，可以先删除某个已有外键，重新建立alter table sPJ add constraint FK_SNO foreign key (sNO) references s (SNO) ;
alter table SPJ add constraint FK_PNO foreign key (PNO) references P(PNO);
alter table sPJ add constraint FK_JNO foreign key (JNO) references J (JNO) ;

11、为sPJ表增加列qty的检查约束,要求qty>o
alter table SPJ add constraint ck_SPJ__qty check (qty>0) ;

12、删除SPJ表的检查约束CK_SPJ_qty
alter table sPJ drop constraint CK_sPJ_gty

13、查询全体学生的学号与姓名
select Sno，Sname FROM student

14、查询全体学生的基本信息
select * FROM student

15、查询全体学生的姓名、出生年份及所在系
use s_t
SELECT Sname, year(getdate ()) -sage birthyear, sdept FROM student

16、查询选修了课程的学生学号。(掌握distinct 的用法)
SELECT distinct sno FROM sc

17、查询年龄在18-20岁间的06级的学生姓名及学号
SELECT Sname , sno FROM
student WHERE sage BETWEEN18 AND 20 AND
sNO like '2006%'

18、查询信息系（Is)、数学系(MA）和计算机科学系(Cs）学生的姓名和性别
SELECT Sname，Ssex FROM student WHERE Sdept IN ( 'IS'，'MA '，'CS');

19、查找所有姓李的学生的信息
SELECT * FROM student where Sname like '李%'

20、查找所有已选修但没有成绩的学生学号。
select Sno
from sc
where Grade is nu11

21、对所有已选课的学生按学号进行升序排列，同时要求每个学生按成绩降序排列。(掌握order by的用法)
select sno , Gradefrom sc
order by Sno,Grade desc
22、统计每门课程的选课人数。(掌握group by用法)
select course.Cname , cOUNT (sc.Cno) as 人数from course , sc
where course.Cnomsc. cnogroup by Cname

23、统计重名的学生姓名及人数。
select Sname , cOUNT (*)as 人数from student
group by Snamehaving COUNT(*)>1

24、统计男生与女生的人数。
select ssex,COUNT (sno) cntfrom student
group by ssex

25、查询“计算机系”年龄最大的学生的基本信息。
select *
from student a
where sagee ( select MAX (Sage)
from student b
where sdept'计算机系’)

实验五

26、打出所有供应商的姓名和所在城市。
use SPJ
select SNAME ,CITY from s

27、找出所有零件的名称及重量。
use SPJ
select PNAME，WEIGHT from p

28、统计每个供应商供应的各种零件数量。
select PNAME,COUNT (PNAME) cnt from P,SPJ
where P.PNO=SPJ.PNO group by PNAME
29、求供应工程1零件的供应商号码SNO.
use SPJ
select distinct sno from spj
where jno='j1'

30、求供应工程J1零件P1的的供应商号码SNO.
use SPJ
select distinct sno 
from spj
where jno='j1' and pno='p1'


实验六
31、求所在的城市为直辖市的供应商的名称。
select Sname from s where city in('北京'，'天津'，'重庆'，'上海')

32、求供应红色零件的供应商号,零件号和数量。
select sNO,PNO ,QTY FROM SPJ
where Pno IN
(SELECT pno FROM p where color= '红')

33、没有使用天津供应商生产的红色零件的工程号JNO。
select distinct JNo from sPJ where JNo not in (select JNO from SPJ,P,S where sPJ.PNO-P.PNO
and SPJ.SNO=S.sNo and P.COLOR='红'and s.CITY='天津')

34、求供应数量超过300的供应信息,包括供应商名,零件名,项目名和供应数量。
select SNAME，PNAME,JNAME,QTY from SPJ,S,P,J
where s.sNO=SPJ.sNO and P.PNO uSPJ.PNO
and J .JNOSPJ .JNO and QTY>300

35、至少使用了供应商s1所供应的全部零件的工程号JNO。
select PNO from SPJ where sNO='S1';
select JNO from SPJ where (PNO'P1'and sNO's1')intersect select JNO from SPJ where (PNO=m'P2'
and SNOo's1')

36、找出使用供应商S1所提供零件的工程号码。
select distinct JNo from SPJ where sNO='s1'

37、找出工程项目2使用的各种零件的名称及其重量。
select PNAME,WEIGHT from P,SPJ
where sPJ.PNO=P. PNO and JNOa'J2'

38、找出上海厂商供应的所有零件号码。
select PNO from s,SPJ where s.sNO=-SPJ.SNO
and s.cITY='上海'group by PNO

39、找出使用上海产的零件的工程名称。
select JNO from s, sPJ
where s.sNOSPJ . SNO and s.cITY’上海’group by JNO

40、找出没有使用天津产的零件的工程号码。
select JNO from SPJ where sPJ.JNO not in (
select JNO from s,sPJ where s.sNO=SPJ.sNO and s,CITY='天津')



41、查询‘IS’系学生的学号、所选课程名称及该门课程的成绩.
select sc.sno ,Cname , Grade from sc, course , student
where student. Sno=sC.Sno and Course.Cno=Sc.Cno and sdept= '数学系'

42、查询‘CS’系成绩不及格的学生姓名。
select distinct Sname from student , sc
where student.Sno=sC.Sno and Grade<60 and sdept= '计算机系'

43、查询每一门的课程的间接先修课程。
select first.Cno , second. Cpnofrom Course first,course second 
where first.Cpnosecond.Cno

44、查询所有的学生的选课情况,要求没有选课的情况也能在结果显示出来。
select student.* , sc.Cno FROM Student LEfT Join sc sno=student.Sno

45、查询每个学生超过他选修课程平均成绩的课程号，课程名称及成绩。
select x.cno,Cname , Grade
from sc x, Course where x.Cno=Course.Cno and Grade(select AVG(Grade) from sc y where x.Sno=y.Sno)

48、查询‘IS’系的学生以及‘数据库系统原理’成绩在70~80之间的学生
select Sname from student , Course , sc
where student.Sno=sc.sno and course . Cno-sc, Crio
and Course.Cname='数据库系统原理and Grade between ‘70’and'80'
and sdept='数学系·

47、用两种方法实现:选修了001课程和002课程的学生学号。
方法一: select Sno from sc where Cno='1' intersect
select sno from sc where Cno='2'
方法二:select Sno from sc where Cno='2' and Sno in
( select sno from sc where Cno='1')

48、查询至少选修了学生001课程和002课程的学生学号。
select distinct Sno from sc A where not exists (select from sc B (where Cno=’1' or Cno='2')and select * from sc c where c.sno=A. Snoan c.Sno=B .Sno ))

实验七
49、建一新表，并将所有学生的学生的学号、平均成绩增加到其中。
create table avgrade
( sno char ( 11) primary key not null,
avgra numeric (3,1))
insert into avgrade
( sno, avgra)
select sno, avg (Grade) from sc group by sno
50、假设所有的学生都选了‘008‘课程，如何将记录播入选修表中。
insert into sc (sno ,cno)
select sno ,' 008'
from student

51、将所有cS系的成绩不及格学生的成绩加5分。
update sc
set grade=grade+5
where '计算机系'= ( select sdeptfrom student ,sc
where student.Sno=sc.sno and grade<60)

52、删除选了‘数据库’数据库的学生的选课记录。
delete
from sc
where cno= (select cno from course where Cname =‘数据库’)

53、删除所有的学生记录。
delete
from student

54、把全部红色零件的颜色改成蓝色。
update p
set color= '蓝'where color='红'

55、由S5供给4的零件6改为由S3供应。
update spj
set snom's3'
where sno='s5' and jno'j4' and pno='p6'

56、从供应商关系中删除供应商号是s2的记录，并从供应情况关系中删除相应的记录。
delete from s
where sno='s2'
delete from SPJ
where sno's2'

57、请将(S2，J6,P4，200)插入供应情况关系。
insert into spj(sno , pno ,jno, gty)
values ( 's2','j6', 'p4', '200')






实验八
58、为“图书”的“分类号”建立一个索引 ts_flh.
create index ts_flh on tushu ( fenleihao)

59、为“借阅”表建立一唯一索引 jycx，要求按“借书证号”升序，“借书日期”降序。
create index jycx on jieyue (cardID ,jdate desc)

60、为“管理员”表建立一检查约束。要求“基本工资”在（100，10000)之间。alter table administrator add constraint cK_administrator_gongzi
check (gongzi between 100 and 10000)

61、建立“借阅”与“图书”及“读者”表之间的参照关系。(增加借阅表的外德约束)alter table jieyue add constraint FK_IDforeign
key (ID) references tushu(ID)
alter table jieyue add constraint FK_cardID foreign
key (cardID)references duzhe (cardID)

61、查询出“图书”数据库中作者姓“刘”的所有图书。
SELECT* FROM tushuwhere writer like '刘%'

63、查询出“图书”数据库中高等教育出版社出版的、单价低于25元的所有种类的图书。select * from tushu where address'高等教育出版社' and price<25

64、求出“读者”数据库中的总人数。
select COUNT (*) from duzhe

65、求出“图书”数据库中的所有图书的最高价、最低价和平均价。
select MAX (price) as最高价,MIN(price)as最低价,aVG (price) as平均价from tushu

66、求出“借阅”库中借书证号为“112”的所借图书的册数。
select cardID,COUNT (*) as 册数from jieyue where cardID ='112' group
by cardID
67、按分类号降序显示“图书”库中各种图书的分类号、书名合作者。
select fenleihao , bookname , writer from tushu order by fenleihao desc

68、按单价升序显示“图书”库中的高等教育出版社出版的所有图书。
select * from tushu where address='高等教育出版社'order by price

69、按单价升序显示出“图书”库中由清华大学出版社和电子工业出版社出版的所有图书
select * from tushu where (address ='高等教育出版社’or address'电子工业出版社')order by price

70、按书名和作者分组统计出“图书”库中每种图书的数太。
select bookname , writer ,COUNT( *) as 数量from tushu group by bookname ， writer
71、统计出“图书”敷据库中15至25元之间的图书数量。
select count(* ) as 数量from tushu where price between 15 and 25

72、查询出“图书”数据库中书名中含有“应用基础”字串的所有图书。
select *from tushu where bookname like '应用基础'

73、分组统计出“借阅”数据库中每一种借书证号所借图书的册数。
select cardID,COUNT(*) as册数from jieyue group by cardID

74、按单位分组统计出“读者”数据库中每个单位的人数。
select danwei, COUNT( *) as 人数from duzhe group by danwei

75、分组统计出1997年底以前借阅不低于2本图书的借书证号和数量。
select cardID,COUNT( *) as 人数from jieyue where jdate<'1997-12-31'
group by cardID having count(*) >2

76、联接查询“借阅”库和“图书”，得到借阅每一本书的信息。
select * from tushu ,jieyue where tushu. ID =jieyue ID

77、联按查询“借阅”库和“读者”库，得到每一个以借阅者的借书证号姓名、单位。
select duzhe.cardID, name , danwei from duzhe,jieyue 
Where duzhe0 . .cardID = jieyue.cardID

78、联接查询“借阅”、“读者”、“图书”三个库，得到每一本所借图书的读者的借书证号、姓名、单位、书名。
select duzhe.cardID, name , danwei , bookname from duzhe
jieyue ,tushu
where duzhe.cardID jieyue.cardID and tushu. ID=jieyue . ID

79、从“图书”数据库中（通过对“借阅”库的嵌套）查询出所有被借图书的信息。
select * from tushu where ID in (select ID from jieyue)

80、按单位分组统计出被借图书的数量。
select danwei , COUNT (*) as数量from duzhe ,jieyue
where duzhe .cardID jieyue .cardID group by danwei

81、按单位分组统计出借阅图书的人数。
select danwei, COUNT (*) as人数from duzhe where cardID in (select distinct cardID from jieyue ) group by danwei

82、从读者库中查询出每个借阅图书的读者的情况。
select*from duzhe where cardID in (select distinct cardID from jieyue )


83、查询‘管理员’表中职称为教授或副教授，并且在1950年(含该年）以后出生的所有职工记录。
select *from administrator where zhicheng ='教授' or
Zhicheng= '副教授’and birth <=1950

84、查询‘管理员’表中基本工资在250到380元之间的职工记录。
select *from administrator where gongzi between 250 and 380

85、将基本工资小于500的管理员,工资加上 100元.
update administrator set gongzi=gongzi+100 where gongzi<500

86、列出藏书在十本以上的图书（书名、作着、出版社)。
select bookname , writer , address from tushu where cangshuliang >10

87、“数据库系统原理教程，王珊编。清华大学出版社，1998年出版”还有几本?
select kucun from tushu where bookname='数据库系统’ and
writer =’王瑞' and address ='清华大学出版社'

实验九
88、请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。针对该视图完成下列查询:
CREATE VIEW vSP AS SELECT SNO, PNO,QTY PROM SPJ,J
WHERE
SPJ.JNO=J.JNO AND J.JNAM='三建'

89、找出三建工程项目使用的各种零件代码及其数量。
SELECT PNO , sum (QTY) FROM vsP group by PNO

90、找出供应商s1的供应情况。
SELECT distinct * FROM vSP WHERE SNO= 'S1'

91、建立信息系学生的视图（用with check option子句)，并向该视图中插入类似以下的语句:
insert into is_student1 (sno,sname,sage)values ('20051100101,王一,20)
是否合理?如何更改?
create view Is studentas
select sno , sname , sage from studentwhere sdept='IS'
with check option
不合理(sno,sname,sage)不用
92、建立信息系选修了0o1号课程的学生的视图。create view Is_s1
as
select student.sno , sname , grade from student , sc
where sdept='IS 'and student.sno#sc.sno and cno= '1'

93、在第(2）题创建的视图的基础上建立信息系选修了1号课程且成绩在90分以上的学生的视图。
create view Is_s2as
select student.sno , sname , grade from student, sc
where sdeptm 'IS'and student.snosc.sno and cno='2
and Grade>-90

94、试图删除“选修”表(Sc表)。看看第(2题与第（3）题建立的视图是否存在。
delete from sc
不存在