SQLBase综合测试

---

提示：以下是本篇文章正文内容，下面案例可供参考

SQLBase综合测试

数据库以及题目文件下载地址：

http://139.224.194.29:81点击文件即可下载

脚本创建方式

使用方法：
例如
1、新建数据库StudentDB
2、打开StudentSql.sql文件》》 Ctrl+A全选 》》》》F5执行

综合测试1（StudentDB数据库）：

--(2)使用sql语句更新姓名为张三的学生的年龄为21岁；
select * from Userinfo
update Userinfo set SA='21' where SN='张三'

--(3)删除课程名为“药学原理”的记录
select * from Course
delete from Course where CN='药学原理'

--(4)查询学生年龄在18岁到22岁之间的学生信息；
select * from Userinfo where SA between 18 and 22

--(5)统计每门课程的平均分数；
select CId 课程id,avg(SC) 平均分数 from Score group by CId

--(6)查询学习成绩最好五个的学生信息；
select top 5 * from Score  order by SC desc

--(7)查询课程名称有‘基础’字的所有课程信息；
select * from Course where CN like '%基础%'

--(8)统计学生的人数，并要求列出列名；
select count(*) 学生人数 from Userinfo

--(9)使用左外接查询学生的学号，姓名，所属学院，成绩，并要求列出列名；
select * from Userinfo
select * from Score
select u.UId 学号,u.SN 姓名,u.SD 所属学院,s.SC 成绩 from Userinfo u left join Score s on u.UId=s.UId 

综合测试2（school数据库）：

--1、查询“CS”系学生的基本信息；
select * from Student where stu_adress='CS'
--2、查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；
select stu_num 学号,stu_name 姓名 from Student where stu_adress='CS'and stu_birth not between 19  and 21
---3、查询学生中的最大年龄；
select top 1 stu_birth 最大年龄 from Student order by stu_birth desc
select MAX(stu_birth) from Student 
--4、找出“计算机”系年龄最大的学生，显示其学号、姓名；
select top 1 stu_num 学号,stu_name 姓名,stu_birth 年龄 from Student where stu_adress='计算机' order by stu_birth desc

--5、统计各系学生的人数，结果按升序排列；
select stu_adress 系,count(*) 人数 from Student GROUP  BY stu_adress order by count(*) desc
--6、按系统计各系学生的平均年龄，结果按降序排列；
select stu_adress 系,AVG(stu_birth) 平均年龄 from Student GROUP  BY stu_adress order by AVG(stu_birth) desc
--7、查询选修了“1”或“2”号课程的学生学号和姓名；
select s.stu_num 学号,s.stu_name 姓名  from Student s,StuChoose s1 where s.stu_num=s1.stu_num and (s1.C_num=1 or s1.C_num=2)
---8、查询选修了课程名为“数据库”且成绩在60分以下的学生的学号、姓名和成绩；
SELECT s.stu_num 学号,s.stu_name 姓名 ,sc_score 成绩  FROM Student s JOIN StuChoose s1 ON s.stu_num=s1.stu_num join course c on s1.C_num=c.C_num where C_name='数据库' and sc_score<60
--9、查询选修了3门以上课程的学生学号；
SELECT s.stu_num 学号,s.stu_name 姓名 ,count(*)  FROM Student s JOIN StuChoose s1 ON s.stu_num=s1.stu_num join course c on s1.C_num=c.C_num group by s.stu_num,s.stu_name
SELECT *  FROM Student s JOIN StuChoose s1 ON s.stu_num=s1.stu_num
--10、查询选修课程成绩至少有一门在80分以上的学生学号；
SELECT s.stu_num 学号,s.stu_name 姓名 ,sc_score 成绩  FROM Student s JOIN StuChoose s1 ON s.stu_num=s1.stu_num join course c on s1.C_num=c.C_num group by s.stu_num,s.stu_name,sc_score having sc_score>80
---11、查询选修课程成绩均在80分以上的学生学号；
SELECT s.stu_num 学号  FROM  StuChoose s join student s1 on s.stu_num=s1.stu_num  group by s.stu_num having min(sc_score)>80
--12、查询选修课程平均成绩在80分以上的学生学号
select stu_num,AVG(sc_score) from StuChoose group by stu_num having AVG(sc_score)>80
--13、找出各系年龄最大的学生，显示其学号、姓名；

select  * from student s ,(select stu_adress,max(stu_birth) 最大值 from student group by stu_adress) s1 where s1.stu_adress=s.stu_adress and s.stu_birth=s1.最大值

综合测试3（shop数据库）：

use shop
--1、用insert插入数据
--2、查询小明的基本信息；
select * from subscriber where User_name='小明'
--3、查询订单状态在待餐中的用户
select * from [order] where Order_status='待餐中' 
---4、查询商品表中的最大价格的商品；
select max(price) from Goods
--5、找出会员为A的用户，显示其姓名和消费价格；
select User_name 姓名,cost 消费价格 from subscriber where Member_type='A'
---6、分组商品类型，结果按升序排列；
  --思路
  --1.查询
  select * from Goods
  --2.分组
  select * from Goods group by Goods_type
  --3.排序以及放字段
  select Goods_name,[Goods_type] from Goods group by Goods_type,Goods_name order by Goods_type asc

--7、查询地址为“红菱路”或“茭菱路”的用户和地址；
select * from [order] where Receive_addres='红菱路' or Receive_addres='茭菱路' --典型的条件查询

--8、查询状态为骑手接单且订单时间在10.30以前的用户和住址
  --方法一
  select * from [order] where  Order_time<'2021-04-25 10:30'
  --方法二
  select * from [order] where DATEDIFF(n,'2021-04-25 10:30',Order_time)<0
  --注意：如果开始时间比结束时间晚，返回的值是正值

综合测试4（School）：

—（1）查询自己的信息，显示学号，姓名，性别，电话，地址，出生日期，民族，家长电话1，家长电话2

select no 学号,name 姓名,sex 性别,phone 电话,address 地址,birthdady 出生日期, nation 民族,parent_phone_1 家长电话1,parent_phone_2 家长电话2 from Student where id=1

—（2）查询自己和所在班级的信息，显示班级，学号，姓名，性别，电话，地址，出生日期，民族，家长电话1，家长电话2

select class.name 班级, no 学号,Student.name 姓名,sex 性别,phone 电话,address 地址,birthdady 出生日期, nation 民族,parent_phone_1 家长电话1,parent_phone_2 家长电话2 from Student join Class on student.class_id=class.id where Student.name=‘杨有山’

—（3）查询所有讲师的带班信息，显示，讲师姓名，班级，班级人数
select Teacher.name 讲师姓名,Class.name 班级,count(Student.class_id) 班级人数 from Student,Teacher,Class where Teacher.id=Class.lecturer_id and class.id=student.class_id group by Teacher.name,Class.name

—（4）查询班级信息，显示班级名称、讲师姓名、班主任姓名，班级人数
select c.name 班级,t.name 班主任姓名,t2.name 讲师姓名,count(s.class_id) 班级人数 from Teacher t join Class c on t.id=c.class_teacher_id join Teacher t2 on t2.id=c.lecturer_id join Student s on c.id=s.class_id group by c.name,t.name,t2.name

—（5）查询我们班的成绩信息，要求显示，姓名，卷面成绩，项目成绩，平时成绩，综合成绩,按综合成绩降序排序
select name 姓名,exam 卷面成绩,project 项目成绩,daily 平时成绩,result 综合成绩 from Score join Student on Score.id=Student.id order by result desc

—（6）查询每个讲师带了多少学生，显示讲师姓名，学生数
select t.name 讲师姓名,count(*) 学生数 from Student s left join Class c on c.id=s.class_id left join Teacher t on c.lecturer_id=t.id group by t.name,t.type having type=‘讲师’

—（7）查询各班H5卷面成绩，显示班级、平均分，最高分，最低分
select * from class select * from Subject select * from Score select Class.name 班级, avg(exam) 平均分,max(exam) 最高分,min(exam) 最低分 from class ,Score,student,Subject where Class.id=Student.class_id and Student.id=Score.sub_id and Subject.id=Score.sub_id and Subject.name=‘HTML’ group by Class.name,Subject.name

—（8）查询你自己各科成绩，显示，姓名、卷面成绩，项目成绩，平时成绩，综合成绩
select Student.name 姓名,exam 卷面成绩,project 项目成绩,daily 平时成绩,result 综合成绩 from Student,Score where Student.id=stu_id group by Student.name,exam,project,daily ,result having Student.name=‘自己’

—（9）查询各地州的学生人数，显示，地址州，学生人数，按人数排序
select address 地址,count() 学生人数 from Student group by address order by count() desc

—（10）查询自己所有成绩的平均分，显示姓名 卷面成绩，项目成绩，平时成绩，综合成绩

select Student.name 姓名,avg(exam) 卷面成绩,avg(project) 项目成绩,avg(daily) 平时成绩,avg(result) 综合成绩 from Student join Score on Student.id=stu_id group by Student.name having Student.name=‘自己’

—（11）查询各地州的学生平均成绩，显示，地址州，学生人数，平均卷面成绩，平均项目成绩，平均平时成绩，平均综合成绩，按综合成绩排序从高到低排序
select address 地址,count(*) 学生人数,avg(exam) 卷面成绩,avg(project) 项目成绩,avg(daily) 平时成绩,avg(result) 综合成绩 from student join Score on Student.id=Score.stu_id group by address order by 综合成绩 desc

—（12）查询所有学生的成绩信息，显示，学生姓名，班级，讲师，班主任，课程名，卷面成绩，项目成绩，平时成绩，综合成绩，按卷面成绩排序从高到低排序
select s.name 学生姓名,c.name 班级,t2.name 讲师名称,t.name 班主任名称 ,s2.name 课程名称,exam 卷面成绩,project 项目成绩,daily 平时成绩, result 综合成绩 from Teacher t join Class c on t.id=c.class_teacher_id join Teacher t2 on t2.id=c.lecturer_id join Student s on c.id=s.class_id join Score s1 on s.id=s1.stu_id join Subject s2 on s2.id=s1.sub_id group by c.name,t.name,t2.name,s.name,s2.name,exam,project,daily,result order by exam desc

—（13）查询各讲师各科目的平均分，显示讲师，科目，卷面成绩，项目成绩，平时成绩，综合成绩，选择合适的排序字段和排序方式
select t.name 讲师,s.name 科目,avg(exam) 卷面成绩,avg(project) 项目成绩,avg(daily) 平时成绩,avg(result) 综合成绩 from Teacher t,Subject s,Score s1 where s.id=s1.sub_id group by t.name,s.name,s1.exam,s1.project,s1.daily,s1.result,t.type having t.type=‘讲师’ order by 综合成绩 desc

—（14）查询各讲师各班各科目的平均分，显示讲师，班级，科目，卷面成绩，项目成绩，平时成绩，综合成绩, 选择合适的排序字段和排序方式
select t.name 讲师,c.name 班级,s2.name 科目,exam 卷面成绩,project 项目成绩,daily 平时成绩,result 综合成绩 from Class c left join Teacher t on c.id=c.lecturer_id or c.id=c.class_teacher_id left join Student s on c.id=s.class_id left join Score s1 on s.id=s1.stu_id left join Subject s2 on s2.id=s1.sub_id group by c.name,t.name,s.name,s2.name,exam,project,daily,result,t.id having t.id=1 order by 卷面成绩 desc

---（1）查询自己的信息，显示学号，姓名，性别，电话，地址，出生日期，民族，家长电话1，家长电话2
  select no 学号,name 姓名,sex 性别,phone 电话,address 地址,birthdady 出生日期,
   nation 民族,parent_phone_1 家长电话1,parent_phone_2 家长电话2 from Student where id=1
---（2）查询自己和所在班级的信息，显示班级，学号，姓名，性别，电话，地址，出生日期，民族，家长电话1，家长电话2
  select class.name 班级, no 学号,Student.name 姓名,sex 性别,phone 电话,address 地址,birthdady 出生日期,
   nation 民族,parent_phone_1 家长电话1,parent_phone_2 家长电话2 from Student join Class on student.class_id=class.id
   where Student.name='杨有山'
---（3）查询所有讲师的带班信息，显示，讲师姓名，班级，班级人数
  select Teacher.name 讲师姓名,Class.name 班级,count(Student.class_id) 班级人数 from Student,Teacher,Class where 
   Teacher.id=Class.lecturer_id and class.id=student.class_id group by Teacher.name,Class.name
---（4）查询班级信息，显示班级名称、讲师姓名、班主任姓名，班级人数
  select c.name 班级,t.name 班主任姓名,t2.name 讲师姓名,count(s.class_id) 班级人数  from Teacher t join Class c on t.id=c.class_teacher_id join
   Teacher t2 on t2.id=c.lecturer_id  join Student s on c.id=s.class_id group by c.name,t.name,t2.name
---（5）查询我们班的成绩信息，要求显示，姓名，卷面成绩，项目成绩，平时成绩，综合成绩,按综合成绩降序排序
  select name 姓名,exam 卷面成绩,project  项目成绩,daily 平时成绩,result 综合成绩 from Score join Student on Score.id=Student.id order by result desc
---（6）查询每个讲师带了多少学生，显示讲师姓名，学生数
  select t.name 讲师姓名,count(*) 学生数 from Student s left join Class c on  c.id=s.class_id left join Teacher t on c.lecturer_id=t.id group by 
  t.name,t.type having type='讲师'
---（7）查询各班H5卷面成绩，显示班级、平均分，最高分，最低分
select * from class 
select * from Subject
select * from Score
  select Class.name 班级, avg(exam) 平均分,max(exam) 最高分,min(exam) 最低分 from class ,Score,student,Subject where 
  Class.id=Student.class_id and Student.id=Score.sub_id and Subject.id=Score.sub_id and Subject.name='HTML' group by Class.name,Subject.name
---（8）查询你自己各科成绩，显示，姓名、卷面成绩，项目成绩，平时成绩，综合成绩
  select Student.name 姓名,exam 卷面成绩,project  项目成绩,daily 平时成绩,result 综合成绩 from Student,Score where Student.id=stu_id 
   group by Student.name,exam,project,daily ,result having Student.name='杨有山'
---（9）查询各地州的学生人数，显示，地址州，学生人数，按人数排序
  select address 地址,count(*) 学生人数 from Student group by address  order by count(*) desc 
---（10）查询自己所有成绩的平均分，显示姓名 卷面成绩，项目成绩，平时成绩，综合成绩

  select Student.name 姓名,avg(exam) 卷面成绩,avg(project)  项目成绩,avg(daily) 平时成绩,avg(result) 综合成绩 from Student join Score on Student.id=stu_id
   group by Student.name having Student.name='杨有山'
---（11）查询各地州的学生平均成绩，显示，地址州，学生人数，平均卷面成绩，平均项目成绩，平均平时成绩，平均综合成绩，按综合成绩排序从高到低排序
  select address 地址,count(*) 学生人数,avg(exam) 卷面成绩,avg(project)  项目成绩,avg(daily) 平时成绩,avg(result) 综合成绩 from student join Score on
   Student.id=Score.stu_id group by address order by 综合成绩 desc
---（12）查询所有学生的成绩信息，显示，学生姓名，班级，讲师，班主任，课程名，卷面成绩，项目成绩，平时成绩，综合成绩，按卷面成绩排序从高到低排序
     select s.name 学生姓名,c.name 班级,t2.name 讲师名称,t.name 班主任名称 ,s2.name 课程名称,exam 卷面成绩,project  项目成绩,daily 平时成绩,
   result 综合成绩 from Teacher t join Class c on t.id=c.class_teacher_id join Teacher t2 on t2.id=c.lecturer_id  join Student s on c.id=s.class_id  join Score s1 on 
   s.id=s1.stu_id  join Subject s2 on s2.id=s1.sub_id group by c.name,t.name,t2.name,s.name,s2.name,exam,project,daily,result order by exam desc
---（13）查询各讲师各科目的平均分，显示讲师，科目，卷面成绩，项目成绩，平时成绩，综合成绩，选择合适的排序字段和排序方式
  select t.name 讲师,s.name 科目,avg(exam) 卷面成绩,avg(project)  项目成绩,avg(daily) 平时成绩,avg(result) 综合成绩 from Teacher t,Subject s,Score s1 where
   s.id=s1.sub_id group by  t.name,s.name,s1.exam,s1.project,s1.daily,s1.result,t.type having t.type='讲师' order by 综合成绩 desc
   
---（14）查询各讲师各班各科目的平均分，显示讲师，班级，科目，卷面成绩，项目成绩，平时成绩，综合成绩, 选择合适的排序字段和排序方式
  select t.name 讲师,c.name 班级,s2.name 科目,exam 卷面成绩,project  项目成绩,daily 平时成绩,result 综合成绩 from Class c left join Teacher t on
   c.id=c.lecturer_id or c.id=c.class_teacher_id left join Student s on c.id=s.class_id left join Score s1 on s.id=s1.stu_id left join Subject s2 on 
   s2.id=s1.sub_id group by c.name,t.name,s.name,s2.name,exam,project,daily,result,t.id having t.id=1 order by 卷面成绩 desc