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

10-19 获取商品表中商品名称含有“pad”的商品

select id, name, price
from goods
where name like '%pad%'

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
from Grade
where CourseID = 'Dp010001'

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

select StudentID, Grade
from Grade
where CourseID = 'Dp010004'
order by Grade desc, StudentID

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
order by grade desc
limit 2

10-32 修改女生成绩

update sc
set grade = grade*1.05
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='大学语文' 
and grade >80

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
    from Grade
)

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 (
    sc.grade >(
        select avg(b.grade)
        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