PTA SQL部分练习题集

10-1 找出所有姓“李”的学生姓名、民族和联系电话。

``````select sname, nation, phone
from student
where sname like "李%"``````

10-2 查询选修了2门以上课程的学生学号和平均成绩。

``````select sno 学号, round(avg(grade),1) 平均成绩
from score
group by sno
having count(sno) >= 2``````

10-3 统计每种商品的销售数量

``````select gid 商品编号, sum(quantity) 销售总数量
from recorder
group by gid``````

10-4 查询前3门课程的课号及课程名称

``````select cno, cname
from course
order by cno limit 3``````

10-5 查询名字中含有“明”字的男生的学生姓名和班级

``````select sname, class
from students
where sname like "%明%"``````

10-6 查询姓名是两个字的学生信息

``````select *
from students
where sname like "__"``````

10-7 计算“0000001”课程的平均分、最高分和最低分

``````select avg(score) 平均分, max(score) 最高分, min(score) 最低分
from sc
where cno = 0000001``````

10-8 统计每个学生的选课门数和考试总成绩，并按选课门数升序排列

``````select sno 学号, count(*) 选课门数, sum(score) 考试总成绩
from sc
group by sno
order by sno``````

10-9 显示截止到2021年9月1日为止年满24周岁的男学生信息。

``````SELECT *
FROM student
WHERE timestampdiff(year,birth,'2021-09-01') >= 24
AND sex = '男'``````

10-10 查询商品表，先按商品分类升序排列，再按商品价格降序排列

``````select name, category_id, price
from sh_goods
order by category_id, price desc``````

10-11 查询商品表中每项关键词对应的商品数量

``````select keyword, count(*) goodscount
from sh_goods
group by keyword``````

10-12 查询商品表中部分字段

``````select id, category_id, name
from sh_goods``````

10-13 获取每个分类下商品的最高价格

``````select category_id, max(price) max_price
from sh_goods
group by category_id``````

10-14 查询商品表中商品库存的最高和最低值

``````select max(stock) stock1, min(stock) stock2
from sh_goods``````

10-15 获取指定条件商品的平均价格

``````select category_id, avg(price) average
from sh_goods
group by category_id
having count(*) > 2``````

10-16 商品表查询语句中运算符的使用

``````select name, price old_price, stock old_stock, price*0.75 new_price, stock+850 new_stock
from sh_goods
where score = 5``````

10-17 查询商品表中指定价格范围的商品信息

``````select id, name, price
from sh_goods
where price between 2000 and 6000``````

10-18 商品表中判断字段是否为NULL

``````select id, name, price
from goods
where price is NULL``````

``````select id, name, price
from goods

10-20 查询商品表中指定条件的商品信息（多条件查询）

``````select id, name, price
from sh_goods
where category_id = 3 and score = 5``````

10-21 查询商品表中指定条件的商品信息（多条件查询）

``````select name, price, score
from sh_goods
where score = 4.5 or price < 10``````

10-22 统计每个学院的学生总人数，并按人数降序排列。

``````select dept 院部, count(*) 总人数
from student
group by dept
order by count(*) desc``````

10-23 查询选修了2门以上课程的学生学号和平均成绩。

``````select sno 学号, avg(grade) 平均分
from score
group by sno
having count(*) >= 2``````

10-24 在教师表中查询出所有教师所在部门编号，并消除重复记录

``````select DepartmentID
from Teacher
group by DepartmentID``````

10-25 查询所有教师信息,按教师编号升序排列

``````select *
from Teacher
order by TeacherID``````

10-26 查询出出生日期最大（即年龄最小）的学生姓名及出生日期。

``````select StudentName, Birth
from Student
where Birth =(
select max(Birth)
from Student
)``````

10-27 查询学生人数大于5人的班级编号

``````select ClassID
from Class
where StudentNum >= 5``````

10-28 查询课程“Dp010001”的最高分

``````select max(Grade) max_grade
where CourseID = 'Dp010001'``````

10-29 查询课程“Dp010004”的学生学号和成绩，并按成绩降序排列，成绩相同按学号升序排列

``````select StudentID, Grade
where CourseID = 'Dp010004'

10-30 查询选修课名中含有“数据库”三个字的课程且成绩在80～90分之间的学生学号及成绩

``````select sno, score
from sc
where score between 80 and 90
and cno in(
select cno
from course
where cname like '%数据库%'
)``````

10-31 查询课程成绩最高二人

``````select stu.sno,stu.sname,sc.grade from stu
join sc on
stu.sno=sc.sno
and sc.cno='C002'
group by sno
limit 2``````

10-32 修改女生成绩

``````update sc
where grade < 75 and sno in (
select sno
from stu
where sex = 0
)``````

10-33 删除选修C语言课程的女生成绩记录

``````delete from sc
where sno in(
select sno
from stu
where sex = 0
)and cno in(
select cno
from cou
where cname = 'C语言'
)``````

10-34 A1-7在产品表中找出库存量小于订购量的产品信息

``````select ProductID, ProductName
from products
where UnitsInStock < UnitsOnOrder
10-35 B1-7查找每位领导的直接下属数量
select b.EmployeeID, count(*) countSub
from employees a, employees b
where a.ReportsTo = b.EmployeeID
group by b.EmployeeID ``````

10-36 查询比“网络工程”专业所有学生年龄都小的学生姓名

``````select sname
from stu
where birdate > (
select max(birdate)
from stu, major
where mname = '网络工程' and stu.mno = major.mno
)``````

10-37 查询软件工程专业中年龄最大的同学姓名

``````select sname
from stu, major
where major.mname = '软件工程' and birdate = (
select min(birdate)
from stu
)``````

10-38 查询选修人数超过2人且成绩都在60分以上的课程

``````select cou.cno 课程号,cname 课程名,max(grade) 最高成绩,min(grade) 最低成绩,avg(grade) 平均成绩
from stu,cou,sc
where stu.sno = sc.sno and cou.cno = sc.cno
group by cou.cno
having count(*) > 2 and min(grade) >= 60 and count(*) = count(grade); ``````

10-39 将student表中的数计学院的学生信息插入到stu表中

``````Insert into stu
select * from student
where dept='数计学院'``````

10-40 没有被购买过的商品信息

``````select gid, gname, price, stock
from good
where gid not in(
select gid
from recorder
)``````

10-41 查询S001学生选修而S003学生未选修的课程（MSSQL）

``````select cno 课程号
from sc
where sno = 'S001' and cno not in(
select cno
from sc
where sno = 'S003'
)``````

10-42 查询选修课程2门以上且成绩都在80分以上的学生（MSSQL）

``````select max(sname) 姓名, max(mname) 专业名, sum(credit) 总学分
from cou, stu, sc, major
where sc.sno = stu.sno and cou.cno = sc.cno and major.mno = stu.mno
group by stu.sno
having count(cou.cno) >= 2 and min(grade) >= 80;	``````

10-43 统计有学生选修的课程的门数,多人选修同一门只算一门

``````select count(distinct cno) 门数
from sc``````

10-44 统计选修人数最多的3门课

``````select cno 课程号, count(*) 选修人数
from sc
group by cno
order by count(cno) desc
limit 3``````

10-45 查询与“陆毅”同一个系的同学姓名

``````select sname
from students
where sdept = (
select sdept
from students
where sname = '陆毅'
)and sname != '陆毅'``````

10-46 查询没有任何评论信息的商品id和name（多表查询）

``````select id, name
from sh_goods
where id not in(
select goods_id
from sh_goods_comment
)``````

10-47 查询用户评分为5星的商品的评论信息（多表查询）

``````select sg.name, sgc.content
from sh_goods sg, sh_goods_comment sgc
where sg.id = sgc.goods_id and score = 5``````

10-48 查询五星商品对应的商品分类信息（多表查询）

``````select sg.id gid, sgc.id cid, sgc.name cname, score
from sh_goods sg join sh_goods_category sgc on sg.category_id = sgc.id
where score = 5``````

10-49 查询商品价格小于500的商品分类名称（多表查询）

``````select name
from sh_goods_category
where id in (
select category_id
from sh_goods
where price < 500
)``````

10-50 检索选修了三门课以上的学生学号、姓名、院部。

``````select sno, sname, dept
from student
where sno in(
select sno
from score
group by sno
having count(*) >= 3
)``````

10-51 查询每门必修课的课程编号，课程名称和选修人数

``````select c.cno, c.cname, count(sno) total
from course c left join score s on c.cno = s.cno
where attribute = '必修'
group by c.cno``````

10-52 查找所有“大学语文”分数在80以上的学生的姓名，所在院系

``````select sname,dept from student,score,course
where student.sno = score.sno
and course.cno = score.cno
and cname='大学语文'

10-53 显示每位员工的编号，姓名，工资及工资级别。

``````select empno,ename,sal,grade from emp,salgrade
where sal between losal and hisal ``````

10-54 显示每位员工的编号，姓名及其上级领导的编号和姓名（要求显示出所有的员工）。

``````select  a.empno 员工编号, a.ename 员工姓名, a.mgr 上司编号, b.ename 上司姓名
from emp a left join emp b on a.mgr = b.empno``````

10-55 查询编号‘dep01001’教师的系主任名称

``````select DepartmentHeader from Teacher,Department
where Teacher.DepartmentID = Department.DepartmentID and TeacherID = 'dep01001'``````

10-56 查询没有课程成绩的学生学号、姓名、性别

``````select StudentID, StudentName, Sex
from Student s
where StudentID not in(
select StudentID
)``````

10-57 查询出学生的选课情况，结果集包括学号、姓名、课号、课名、开设学期和成绩。

``````select student.sno, sname, course.cno, cname, term, grade
from student, course, score
where student.sno = score.sno and course.cno = score.cno``````

10-58 查询平均成绩以上的课程

``````select sno 学号, cname 课程名, grade 成绩
from cou, sc
where cou.cno = sc.cno and (
from sc b
where sc.sno = b.sno)
)``````

10-59 查询选修张老师讲授所有课程的学生

``````select sname
from stu
where sno in (
select sno
from sc
where cno in (
select cno
from cou
where teacher='张老师'
)
group by sno
having count(sno)=(
select count(cno)
from cou
where teacher='张老师'
)
)``````

10-60 检索没被学生选修的课程编号和课程名称

``````select cno, cname
from course
where cno not in(
select cno
from score
)``````

10-61 spj-查询比p6零件供应数量都高的零件

``````select distinct pno
from spj x
where pno not in(
select pno
from spj y
where y.qty<=(
select max(qty)
from spj
where pno='p6'
)
)``````

10-62 6-7 查询生产三种不同型号的PC的厂商

``````select maker
from product, pc
where product.model = pc.model
group by maker
having count(*) >= 3``````

10-63 列出所有学生的选课情况（包括学号，姓名，课号，成绩），结果中包括没有选课的学生

``````select students.sno, sname, cno, score
from students left join sc on students.sno = sc.sno``````

10-64 查询所有产品名中包含’螺母’的产品种类数

``````select count(*)
from product
where PName = '螺母'``````

10-65 查询所有员工中最高工资和最低工资

``````select max(Salary) max_Salary, min(Salary) min_Salary
from employee``````

10-66 查询每个仓库的编号及员工数量

``````select Wno, count(Eid) Count_Eid
from employee
where Wno is not null
group by Wno``````

10-67 查询’A01’仓库中的职工中比’A02’所有职工薪水都高的职工编号与姓名

``````select Eid, EName
from employee
where Salary > (
select max(Salary)
from employee
where Wno = 'A02'
)``````

10-68 查询销售数量最多的供应商编号

``````select Sid
from orders
group by Sid
having sum(QTY) >=all (
select sum(QTY)
from orders
group by Sid
)``````

10-69 查询销售过’0011’号员工销售的所有产品的员工编号和姓名

``````select Eid, EName
from employee
where Eid in(
select Eid
from orders
where Pid in(
select Pid
from orders
where Eid = '0011'
)and Eid != '0011'
)``````

10-70 4-6 查询在具有最小内存容量的所有PC中具有最快处理器的PC制造商

``````select maker
from product,pc
where product.model = pc.model
order by ram , speed desc
limit 1;``````

10-71 5-2 查询至少生产两种不同的计算机(PC或便携式电脑)且机器速度至少为133的厂商

``````select maker
from product left join pc on product.model = pc.model
left join laptop on product.model = laptop.model
and pc.speed >= 133 and laptop.speed >= 133
group by maker
having count(*) >= 2
order by maker;``````

10-72 查询’A01’仓库中的职工中比’A02’任意一个职工薪水少的职工编号与姓名

``````select Eid,EName from employee
where Wno ='A01' and Salary < (
select max(Salary)
from employee
where wno = 'A02'
)``````

10-73 86.删除所有期末成绩小于60分的选课记录

``````delete from sc
where SCScore3 < 60``````

THE END