pta mysql训练题集(401-431)

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

select max(grade) as max_grade
from Grade
where CourseID = 'Dp010001'

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

select StudentID,a.Grade
from Grade as a
where CourseID = 'Dp010004' 
order by a.grade desc,studentID asc

10-403 检索李玉敏选修的课程编号及成绩

SELECT cno,grade
from score
where sno = (select sno from student where sname = '李玉敏' )

10-404 检索数计学院的叶凡选修课程的平均分。(分数四舍五入取整)

-- select sname,cast(avg(grade) as decimal(10,0)) as avggrade
select sname,round(avg(grade),0) as avggrade
from score as a
inner join student as b on a.sno = b.sno
where b.sname = '叶凡'
group by a.sno

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

select student.sno,sname,course.cno,cname,term,grade 
from score 
left join course on score.cno = course.cno 
left join student on score.sno = student.sno;

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

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

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

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

10-408 查询各个系部的教师人数

select DepartmentID,count(*) as count_pro
from Teacher
group by DepartmentID;

10-409 查询选课门数大于3门并且平均分大于70分的学生信息

select StudentID,count(*) as course_num,avg(grade) as avg_score
from Grade as a
group by StudentID
having count(*) > 3 and avg(grade) > 70

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

select DepartmentHeader
from Department
where DepartmentID = 
(
    select DepartmentID
    from Teacher
    where TeacherID = 'dep01001'
)

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

select a.StudentID,StudentName,Sex
from Student as a
left join Grade as b on a.StudentID=b.StudentID
where Grade is null;

10-412 创建一个有关男会员信息的视图customer_male，字段名分别是会员编号,会员姓名,性别,电话。

create view customer_male(会员编号,会员姓名,性别,电话)
as select *
from customer
where sex='男';

10-413 创建一个每种货物的销售数量的视图good_total，要求是在2010年04月01日到2010年7月31日之间销售的货品，字段包括（gid,total）

create view good_total
as select
gid,sum(quantity) as total
from sale_recorder
where sale_date between '2010-04-01' and '2010-07-31'
group by gid

10-414 创建视图，统计每个学生的课程数量，其中包含学生学号和对应的课程数量。

create view v1
as select
StudentID,count(CourseID) as total from Grade
group by StudentID

10-415 创建视图，用于统计每个学生的修课总学分，列出学生学号和总学分。

create view v2
as select
StudentID,sum(credit) as total_credit
from Grade as a
left join Course as b on a.CourseID = b.CourseID
where grade > 60
group by StudentID

10-416 修改商品表库存数据

update sh_goods
set stock=100 where id=8;

10-417 删除商品表数据

delete from sh_goods where keyword = '003'

10-418 使用CASE语句对商品价格进行分级

select name,price,
(
    case
    when price <50 then 'level1'
    when price >=50 and price <100 then 'level2'
    when price >=100 and price <500 then 'level3'
    else 'level4'
    end
) as price_level
from sh_goods
order by price

10-419 使用CASE语句基于用户评分对商品进行分级

select name,score,
(
    case 
    when score = 5 then 'Golden'
    when score >4.5 and score < 5 then 'Silver'
    when score >4 and score <= 4.5 then 'Copper'
    else 'Normal'
    end
) as score_level
from sh_goods order by score desc;

10-420 创建视图，包含部分商品打折前后的价格信息

create view view_goods
as select
id,name,price as old_price,price*0.5 as new_price
from sh_goods
where price > 1000

10-421 查询'CS'系有哪些学生

select *
from student
where Sdept = 'CS'

10-422 😊统计每门课程总评成绩的平均分（保留2位小数），显示课程号，课程名和平均成绩。

select sc.CId,CName,round(avg(SCScore),2) as Score
from sc left join course on course.CId = sc.CId
group bY CId,CName

10-423 建立’A01’仓库的员工信息视图vEmp

create view vEmp
as select *
from employee
where Wno = 'A01'

10-424 建立’天津’的供应商视图vSup

create view vSup
as select *
from supplier
where City = '天津'

10-425 建立供应商的销售记录视图vSupStatics

create view vSupStatics
as select
Sid,count(*) as ordcount,sum(Price*QTY) as ordtotalPrice
from orders
group by Sid
order by Sid asc

10-426 建立产品的销售记录视图vProductStatics

create view vProductStatics
as select 
Pid,sum(QTY) as totalQTY,max(Price) as maxPrice,min(Price) as minPrice
from orders
group by Pid

10-427 创建视图图统计没有选课的学生信息

create view V_StudentsWithoutCourse
as select *
from Student
where Sno not in (select Sno from SC)

10-428 创建视图计算学生平均绩点

create view V_average_point
as select Sdept,Sno,avg(Grade) as Average_point
from 
(
    select a.Sdept,a.Sno,(case when Grade<60 then 0 else (Grade-50)/10 end) as Grade
    from Student as a
    right join SC as b on a.Sno = b.Sno
) as c
group by Sno

10-429 创建视图计算学生课程平均分

create view V_average_grade
as select
Sdept,Student.Sno,Sname,avg(Grade) as Average_grade
from Student 
left join SC on Student.Sno = SC.Sno
group by Student.Sno
having avg(Grade)>80

10-430 创建视图查找不及格学生

create view V_FailedCourseStudent
as select
Sdept,a.Sno,Sname,Cname,Grade
from (select * from SC where Grade < 60 or Grade is null) as a
left join Course on a.Cno = Course.Cno
left join Student on Student.Sno = a.Sno

10-431 创建视图统计需要重修的名单

create view V_RestudyList
as select
Sdept,a.Sno,Sname,Cname
from (select * from SC where Grade < 60 or Grade is null) as a
left join Course on a.Cno = Course.Cno
left join Student on Student.Sno = a.Sno