[实验目的和要求]
1.掌握SQL Server Management Studio中SQL 查询操作;
2.掌握SQL 的单表查询命令;
3.掌握SQL 的连接查询操作;
4.掌握SQL 的嵌套查询操作;
5.掌握SQL 的集合查询操作。
[实验内容]
查询选修了课程的学生学号;
查询选修了1号课程的学生学号和成绩,并要求结果按成绩降序排列,如果成绩相同,则按学号升序排列;
查询缺少了成绩的学生的学号和课程号;
查询每个学生的情况以及他(她)所选的课程;
法一:
法二:
法二:
法二:
2.对学生-课程数据库,应用嵌套查询实现以下查询要求:
1) 查询选修了“高等数学”的学生学号和姓名;
法一:
法二:
2) 查询“高等数学”的成绩高于小红的学生学号和成绩;
法一:
法二:
3) 查询其他系中年龄小于计算机系年龄最大者的学生;
4) 查询其他系中比计算机系学生年龄都小的学生;
5) 查询选修了“信息系统”课程的学生姓名;
法一: 多表连接
法二:两个嵌套子查询
6) 查询没有选修“信息系统”课程的学生姓名;
7) 查询选修了全部课程的学生姓名;
8) 查询至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。
3.对图书读者数据库,应用SQL 语句实现以下查询要求:
1) 查询计算机类和机械工业出版社出版的图书;
2) 查询“机械工业出版社”出版的各类图书的平均定价,用GROUP BY 表示;
3) 查找这样的图书类别:要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的2倍;
4) 列出计算机类图书的书号、名称及价格,最后给出总册数和总价格;(提示:可以使用Compute 子句最简便地实现);
5) 列出各出版社计算机类图书的具体书号、名称、价格和销售数量,并求出各出版社这类书的平均价格和销售总量。(提示:可以使用Compute by子句最简便地实现)。
[实验思考题]
对上述查询要求的实现进行总结,并对单表查询、连接查询、嵌套查询以及集合查询进行比较。
1.单表查询:
选择表中的若干列,选择表中的若干元组,order by子句,聚类函数,group by子句
2.连接查询
等值与非等值连接查询,自身查询,嵌套查询,集合查询,基于派生表的查询,
各有优点,具体情况具体使用
【附录代码】
create database Db2 create table Student (Sno char(9) primary key, Sname char(20) unique, Ssex char(2), Sage smallint, Sdept char(20) ); create table Course ( Cno char(4) primary key, Cname char(40) not null, Cpno char(4), Ccredit smallint, foreign key(Cpno) References Course(cno) ); create table SC ( Sno char(9), Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key(Sno) references Student(Sno), foreign key(Cno) references Course(Cno) ); INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('95001','李勇','男',30,'CS'); INSERT INTO Student VALUES('95002','刘晨','女','19','IS'); INSERT INTO Student VALUES('95003','王敏','女','18','MA'); INSERT INTO Student VALUES('95004','小美','女','126','IS'); INSERT INTO Student VALUES('95005','张立','男','23','IS'); INSERT INTO Student VALUES('95006','张三','男','19','IS'); INSERT INTO Student VALUES('95007','李四','男','18','MA'); INSERT INTO Student VALUES('95008','王五','男','20','IS'); INSERT INTO Student VALUES('95009','小红','女','23','MA'); INSERT INTO Student VALUES('95010','小丽','女','20','MA'); INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('95011','闫冠希','男',30,'CS'); INSERT INTO Student VALUES('95012','隔壁老王','女','19','CS'); INSERT INTO Student VALUES('95013','鹏于晏','女','18','MA'); INSERT INTO Student VALUES('95014','瓶子','男','22','MA'); INSERT INTO Student VALUES('95015','阿广','女','25','IS'); INSERT INTO Student VALUES('95016','乔彦祖','男','19','CS'); INSERT INTO Student VALUES('95017','金城武','男','18','MA'); INSERT INTO Student VALUES('95018','刘德华','男','20','CS'); INSERT INTO Student VALUES('95019','张艺谋','女','19','MA'); INSERT INTO Student VALUES('95020','华强','男','20','IS'); INSERT INTO Student VALUES('95021','小风','男','20','CS'); INSERT INTO Student VALUES('95022','小刘','女','19','MA'); INSERT INTO Student VALUES('95023','小马','男','20','IS'); INSERT INTO Student VALUES('95024','张1','男','20','CS'); INSERT INTO Student VALUES('95025','刘2','女','26','MA'); INSERT INTO Student VALUES('95026','马3','女','23','IS'); ---------------------------------------------------------------------------- insert into Course values(2,'数学',null,2); insert into Course values(6,'数据处理',null,2); insert into Course values(4,'操作系统',6,3); insert into Course values(7,'C语言',6,4); insert into Course values(5,'数据结构',7,4); insert into Course values(1,'数据库',5,4); insert into Course values(3,'信息系统',1,4); -------------------------------------------------------------------------------- insert into SC(Sno,Cno,Grade) values('95001',1,32); insert into SC values('95002',2,85); insert into SC values('95003',3,88) insert into SC values('95004',1,92); insert into SC values('95005',3,100) insert into SC values('95006',1,98); insert into SC values('95007',2,85); insert into SC values('95008',3,91) insert into SC values('95009',2,41) insert into SC values('95010',3,94) insert into SC(Sno,Cno,Grade) values('95011',3,63); insert into SC values('95012',1,58); insert into SC values('95013',3,86) insert into SC values('95014',2,50) insert into SC values('95015',1,73) insert into SC values('95016',3,86) insert into SC(Sno,Cno,Grade) values('95017',1,92); insert into SC values('95018',2,65); insert into SC values('95019',3,88) insert into SC values('95020',1,99); insert into SC values('95021',1,81); insert into SC values('95022',1,88); insert into SC values('95023',1,89); insert into SC values('95024',1,null); insert into SC values('95025',2,null); insert into SC values('95009',3,89); insert into SC values('95009',1,66); insert into SC values('95009',4,null); insert into SC values('95009',5,30); insert into SC values('95009',6,71) insert into SC values('95009',7,90) insert into SC values('95021',3,89); insert into SC values('95021',2,66); insert into SC values('95021',4,99); insert into SC values('95021',5,73); insert into SC values('95021',6,71) insert into SC values('95021',7,90) ------------------------------------------------------------------ select Sno,Sname from Student where Sdept = 'MA'; select Sno from SC select Sno,Grade from SC where Cno = 1 order by Grade desc, Sno; select Sno,Grade*0.8 '成绩乘0.8' from SC where Cno = 1 and Grade between 80 and 90 ; select * from Student where Sdept = 'MA' or Sname LIKE '张%'; select Sno , Cno from SC where Grade is null; 法一: select Student.*,Cname from Student,SC,Course where Student.Sno = SC.Sno and SC.Cno = Course.Cno; 法二: select Student.Sno,Sname,Sage,Sdept,Course.Cno,Grade,Cname from Student left outer join SC on(Student.Sno = SC.Sno) join Course on (SC.Cno = Course .Cno); 法一: select Student.Sno,Sname,Grade,Cname from Student,SC,Course where Student.Sno = SC.Sno and SC.Cno = Course.Cno; 法二: select Student.Sno,Sname,Grade,Cname from Student left outer join SC on(Student.Sno = SC.Sno) join Course on (SC.Cno = Course .Cno); 法一: select Student.Sno,Sname,Cname,Grade from Student left outer join SC on(Student.Sno = SC.Sno) join Course on (SC.Cno = Course .Cno) where Cname = '数据库' and Grade >=90; 法二: select Student.Sno,Sname,Grade from Student,SC,Course where Student.Sno = SC.Sno and SC.Cno = Course.Cno and Course.Cname = '数据库' and SC.Grade > = 90 select first.Cno,second.Cpno from Course first,Course second where first.Cpno = second.Cno; 法一: select Student.Sno,Sname from Student left outer join SC on(Student.Sno = SC.Sno) join Course on (SC.Cno = Course .Cno) where Cname = '数学'; 法二: select Student.Sno,Sname from Student where Sno in ( select Sno from SC where Cno in ( select Cno from Course where Cname = '数学' ) ); 法一: select Student.Sno,Grade from Student left outer join SC on(Student.Sno = SC.Sno) join Course on (SC.Cno = Course .Cno) where Course.Cname = '数学' and SC.Grade >( select Grade from SC where Cno = 2 and Sno = ( select Sno from Student where Sname = '小红') ) 法二: select Sno,Grade from SC,Course where SC.Cno = Course.Cno and Cname = '数学' and Grade > ( select Grade from SC,Course where SC.Cno = Course.Cno and Cname = '数学' and SC.Sno =( select Sno from Student where Sname = '小红' ) ) select Student.* from Student where Sdept != 'CS' and Sage<( select MAX(Sage) from Student where Sdept = 'CS' ) select Student.* from Student where Sdept != 'CS' and Sage<ANY( select MIN(Sage) from Student where Sdept = 'CS' ) 法一: select Sname from Course,SC,Student where Student.Sno = SC.Sno and SC.Cno = Course.Cno and Cname = '信息系统' 法二: select Sname from Student where Sno in ( select Sno from SC where Cno = ( select Cno from Course where Cname = '信息系统' ) ) select Sname from Student where not exists ( select * from SC where Sno =Student.Sno and Cno = ( select Cno from Course where Cname='信息系统' ) ) select Sname from Student where not exists( select * from Course where not exists( select * from SC where Sno = Student.Sno and Cno = Course.Cno ) ) select distinct Sno from SC SCX where not exists( select * from SC SCY where SCY.Sno = '95002' and not exists( select * from SC SCZ where SCZ.Sno = SCX.Sno and SCZ.Cno = SCY.Cno ) ) ================================================================================================ ================================================================================================ create database Library2 create table book ( book_number char(20) primary key, classes char(20), press char(30), writer char(20), title char(30), pricing money, number bigint, sold bigint ); create table person ( Sno char(11) primary key, name char(20), work_address char(30), Ssex char(2), phone bigint ); create table look ( book_number char(20), Sno char(11), date date, primary key(book_number,Sno), foreign key(book_number) references book(book_number), foreign key(Sno) references person(Sno) ); insert into person values('20211104227','闫冠希','计科2102班','男',18335850028); insert into person values('20211104225','小丽','计科2102班','女',12374196301); insert into person values('20211104217','凯子','计科2102班','男',19635741286); insert into person values('20211104221','小刘子','计科2101班','女',15934712486); insert into person values('20211104205','董卓','计科2102班','男',15642893651); insert into person values('20211104233','泽华','计科2102班','男',17634852176); insert into person values('20211104201','刘1','计科2102班','女',15835411496); insert into person values('20211104202','李4','计科2101班','男',13478900123); insert into person values('20211104203','张3','计科2101班','女',19336411234); insert into person values('20211104204','隔壁老王','计科2103班','男',13888482153); insert into person values('20211104256','隔壁老樊','计科2102班','女',18664722189); insert into person values('20211104206','瓶子','计科2101班','男',17334758194); insert into person values('20211104331','乔彦祖','计科2102班','女',14334899516); insert into person values('20211104333','金城武','计科2103班','男',17684262796); insert into person values('20211104347','阿广','计科2103班','女',14614893346); insert into book values('K565.41 24','小说','上海译文出版社','威廉·多伊尔','法国大革命',58.98,200,100); insert into book values('K231.04 4','历史','上海古籍出版社','刘向','战国策',99.8,20,10); insert into book values('TP3121036','计算机','机械工业出版社','黑马程序员','Java面向对象程序设计',78.568,60,32); insert into book values('I216.2 483','文学','上海译文出版社','林徽因','林徽因文学精品选',68.46,400,186); insert into book values('I216.2 484','文学','上海译文出版社','林因','林因文学选',500,400,356); insert into book values('I210.1 1:16 ','计算机','机械工业出版社','鲁迅文学爱好者','鲁迅全集',86.79,500,321); insert into book values('k565.08 6','小说','清华大学出版社','简·奥斯汀','傲慢与偏见',46.27,80,25); insert into book values('K242.09 7','历史','新星出版社','陈舜臣','大唐帝国',54.31,150,114); insert into book values('J222.49 23','计算机','机械工业出版社','高样萍','办公软件应用',125.63,350,289); insert into book values('K835.655.2 10','历史','机械工业出版社','阿三','拿破仑传',54.31,150,68); insert into book values('I512.4 175','小说','机械工业出版社','李武','变色龙',125.63,350,71); insert into book values('I247.8795','历史','机械工业出版社','王伟','乾隆皇帝',54.31,150,22); insert into book values('I247.8796','历史','机械工业出版社','王伟3','清朝清宁',600,260,210); insert into book values('I111.8417 424','小说','机械工业出版社','芳孔','新农村',125.63,350,100); insert into book values('I111.8418 427','小说','机械工业出版社','芳孔1','新世界',600,350,50); insert into look values('K565.41 24',20211104221,'2021-11-10'); insert into look values('J222.49 23',20211104221,'2021-11-11'); insert into look values('K231.04 4',20211104227,'2021-10-06'); insert into look values('TP3121036',20211104233,'2021-09-25'); insert into look values('I216.2 483',20211104233,'2021-10-25'); insert into look values('J222.49 23',20211104233,'2021-10-21'); insert into look values('I210.1 1:16',20211104205,'2021-11-16'); insert into look values('k565.08 6',20211104217,'2021-11-18'); insert into look values('TP3121036',20211104217,'2021-11-18'); insert into look values('I210.1 1:16 ',20211104217,'2021-11-18'); insert into look values('K242.09 7',20211104227,'2021-10-14'); insert into look values('I210.1 1:16 ',20211104227,'2021-11-15'); insert into look values('J222.49 23',20211104225,'2021-09-25'); insert into look values('TP3121036',20211104225,'2021-09-25'); insert into look values('I210.1 1:16 ',20211104225,'2021-11-18'); insert into look values('J222.49 23',20211104201,'2021-10-21'); insert into look values('I210.1 1:16',20211104201,'2021-11-16'); insert into look values('k565.08 6',20211104202,'2021-11-18'); insert into look values('TP3121036',20211104201,'2021-11-18'); insert into look values('I210.1 1:16 ',20211104203,'2021-11-18'); insert into look values('K242.09 7',20211104204,'2021-10-14'); insert into look values('I210.1 1:16 ',20211104206,'2021-11-11'); insert into look values('J222.49 23',20211104256,'2021-09-24'); insert into look values('k565.08 6',20211104331,'2021-09-27'); insert into look values('I210.1 1:16 ',20211104331,'2021-11-30'); insert into look values('J222.49 23',20211104333,'2021-09-14'); insert into look values('TP3121036',20211104204,'2021-09-15'); insert into look values('I210.1 1:16 ',20211104204,'2021-11-11'); select title from book where classes = '计算机' and press = '机械工业出版社'; select classes , AVG(pricing) '图书平均定价' from book where press = '机械工业出版社' group by classes; select classes,MAX(pricing) 最高图书定价,AVG(pricing) 图书平均定价 from book group by classes having MAX(pricing)>= all( select AVG(pricing)*2 价格 from book group by classes ) select book_number,title,pricing from book where classes='计算机' compute count(book_number),sum(pricing) select book_number,title,pricing,number,sold from book where classes='计算机' order by number select * from book order by press compute avg(pricing),sum(sold) by press;