数据库 ----- 实验三：SQL的查询

2022-04-09 05:58:21

实验三 SQL的查询

[实验目的和要求]
1．掌握SQL Server Management Studio中SQL 查询操作；
2．掌握SQL 的单表查询命令；
3．掌握SQL 的连接查询操作；
4．掌握SQL 的嵌套查询操作；
5．掌握SQL 的集合查询操作。
[实验内容]

对学生-课程数据库，应用SQL 语句实现以下查询要求：

查询数学系学生的学号和姓名；

查询选修了课程的学生学号；
查询选修了1号课程的学生学号和成绩，并要求结果按成绩降序排列，如果成绩相同，则按学号升序排列；

查询选修了1号课程且成绩在80-90分之间的学生学号和成绩，并将成绩乘以系数0.8输出；

查询数学系或计算机系姓张的学生的信息；

查询缺少了成绩的学生的学号和课程号；
查询每个学生的情况以及他（她）所选的课程；
法一：

法二：

查询学生的学号、姓名、选修的课程名称及成绩；
法一：

法二：

查询选修了“数据库”课程且成绩在90分以上的学生学号、姓名和成绩；
法一：

法二：

查询每门课程的间接先行课的课程名称。

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;

码农公寓

实验三 SQL的查询

相关文章