数据库 实验二 查询

一、实验内容

1、用SQL语句表示下列操作，在学生库中实现数据查询。
（1）求数学系学生的学号和姓名。
（2）求选修了课程的学生学号。
（3）求选修001号课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。
（4）求选修课程001且成绩在80～90分之间的学生学号和成绩，并将成绩乘以系数0.8 输出。
（5）求数学系或计算机系姓张的学生的信息。
（6）查看选修了课程、但没有成绩学生的学号、姓名、课程号和所在是系部。
（7）查询学生的学号、姓名、课程名和成绩。
（8）分别实现学生和系的交叉连接、内连接、外连接。
2、在SQL Server查询分析器中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。具体内容如下：
用SQL语句表示，在学生选课库中实现其数据嵌套查询操作。
（1）求选修了高等数学的学生学号和姓名。
（2）求001课程的成绩高于张力的学生学号和成绩。
（3）求其他系中年龄小于计算机系年龄最大者的学生。
（4）求其他系中比计算机系学生年龄都小的学生。
（5）求选修了001课程的学生姓名。
（6）求没有选修001课程的学生姓名。
（7）查询选修了全部课程的学生的姓名。

二、实验代码

-- theme 数据库实验二
--author dahua
-- data by 2022.6.11

--创建一个学生数据库

create database Student;

--1.创建第一个表：学生表
create table student1(

sno char(9) primary key,   --将学号设置为主码
sname char(20),
ssex char(2),
sage int,
sdept char(20)
);

--插入元组
insert 
into student1(sno,sname,ssex,sage,sdept)
values('01','张力','男',20,'计算机系'), 
      ('02','雷杰多','男',19,'体育系'),
      ('03','赛迦','男',19,'电子工程系'),
	  ('04','诺亚','男',19,'计算机系'),
	  ('05','玛丽','女',19,'数学系'),
	  ('06','盖亚','男',20,'化工化学系'),
	  ('07','张三','男',20,'计算机系'),
	  ('08','赛罗','男',19,'数学系'),
	  ('09','雷欧','男',20,'数学系'),
	  ('10','张四','男',18,'数学系');


--修改表中数据
update student1 set sage=18 where sno='09';


--创建索引
create unique index stusno1 on student1(sno);

--2.创建第二个表：课程表
create table course1(

cno char(9) primary key,   --将课程号设置为主码
cname char(30),
cpno char(30),             --先行课
cpoint int                 --学分
);

--插入元组
insert 
into course1(cno,cname,cpno,cpoint)
values('001','高等数学A1','无',4.5),
      ('002','大学英语1','无',3),
	  ('003','C++程序设计A1','无',3),
	  ('004','计算机导论','无',1.5),
	  ('005','军事理论','无',1),
	  ('006','思想道德修养','无',3),
	  ('007','高等数学A2','001',4),
	  ('008','大学英语2','002',2),
	  ('009','大学物理C','无',4),
	  ('010','C++程序设计A2','003',2);

--创建索引
create unique index coucno1 on course1(cno);

--3.创建第三个表：选课表
create table sc1(

sno char(9),    
cno char(9),    
grade int                   
primary key(sno,cno),
foreign key(sno) references student1(sno),  --外码
foreign key(cno) references course1(cno),   --外码
);

--插入元组
insert 
into sc1(sno,cno,grade)
values('01','001',92),
      ('01','002',95),
	  ('01','003',90),
	  ('01','004',92),
      ('01','005',94),
	  ('01','006',90),
	  ('01','007',92),
      ('01','009',96),
	  ('01','010',91),
	  ('02','001',86),
	  ('02','002',97),
	  ('02','003',86),
	  ('02','004',99),
	  ('02','005',97),
	  ('02','006',92),
	  ('02','007',86),
	  ('02','008',89),
	  ('02','009',90),
	  ('02','010',86),
	  ('03','001',96),
	  ('04','001',96),
	  ('04','004',97),
	  ('04','007',85),
	  ('04','008',86),
	  ('04','003',96),
	  ('05','001',84),
	  ('06','001',84),
	   ('06','003',null),
	  ('06','007',97);

--4.创建第四个表：系部表
create table department1(

dno char(9) primary key,    
dname char(20),    
manager char(20)                   
);


--插入元组
insert 
into department1(dno,dname,manager)
values('202001','机械工程系','萧峰'),
      ('202002','电子工程系','段誉'),
	  ('202003','自动化系','虚竹'),
	  ('202004','化学与化工系','王语嫣'),
	  ('202005','计算机系','阿朱'),
	  ('202006','环境与安全系','阿紫'),
	  ('202007','材料工程系','木婉清'),
	  ('202008','理学系','瑛姑'),
	  ('202009','外语系','慕容复'),
	  ('202010','设计艺术系','游坦之');

--创建索引
create unique index departmentdno1 on department1(dno);

--1.(1)求数学系学生的学号和姓名。
select sno,sname from student1 where sdept='数学系';

--1.(2)求选修了课程的学生学号。
select distinct sno from sc1;

--1.(3)求选修001号课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。
select sno,grade from sc1 where cno='001' order by grade desc,sno asc;

--1.(4)求选修课程001且成绩在80～90分之间的学生学号和成绩，并将成绩乘以系数0.8 输出。
select sno,0.8*grade from sc1 where cno='001' and grade between 80 and 90;

--1.(5)求数学系或计算机系姓张的学生的信息。
select * from student1 where sdept in('数学系','计算机系') or sname like '张_%';

--1.(6)查看选修了课程、但没有成绩学生的学号、姓名、课程号和所在是系部。
select sc1.sno,student1.sname,cno,student1.sdept from student1,sc1 where sc1.cno is not null and grade is null and sc1.sno=student1.sno;
 
 
--1.(7)查询学生的学号、姓名、课程名和成绩。
select  sc1.sno,sname,cname,sc1.grade from student1,sc1,course1 where sc1.sno=student1.sno and sc1.cno=course1.cno;

--1.(8)分别实现学生和系的交叉连接、内连接、外连接。
select student1.*,department1.* from department1 cross join student1;
select student1.*,department1.* from student1 inner join department1 on student1.sdept = department1.dno;
select student1.*,department1.* from  student1 left outer join department1 on student1.sdept = department1.dno;


--2.(1)求选修了高等数学A1的学生学号和姓名。
select sno,sname from student1 where sno in(select sno from sc1 where cno=(select cno from course1 where cname='高等数学A1'));

--2.(2)求001课程的成绩高于张力的学生学号和成绩。
select a.sno,a.grade from sc1 a,sc1 b where a.cno=001 and b.cno=001 and a.grade>b.grade and a.sno!=b.sno and b.sno=(select sno from student1 where sname='张力');


--2.(3)求其他系中年龄小于计算机系年龄最大者的学生。
select sname,sage,sdept from student1 where Sage < any(select max(sage) from student1 where sdept='计算机系' ) and sdept <> '计算机系';

--2.(4)求其他系中比计算机系学生年龄都小的学生。
select sname,sage,sdept from student1 where Sage < all(select sage from student1 where sdept='计算机系' ) and sdept <> '计算机系';

--2.(5)求选修了001课程的学生姓名。
select sname from student1,sc1 where student1.sno = sc1.sno and cno='001';

--2.(6)求没有选修001课程的学生姓名。
select sname from student1 where not exists (select * from sc1 where sno=student1.sno and cno='1');

--2.(7)查询选修了全部课程的学生的姓名。
select sname from student1 where not exists(select * from course1 where not exists(select * from sc1 where sno=student1.sno and CNo = course1.cno));

三、运行结果

3.1问题一的运行结果图
3.1.1

3.1.2

3.1.3

3.1.4

3.1.5

3.1.6

3.1.7

3.1.8

3.2问题二的运行结果图
3.2.1

3.2.2

3.2.3

3.2.4

3.2.5

3.2.6

3.2.7