pta MYSQL 训练题集(41-60)

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

select a.sno,sname,grade
from
(
    select sno,grade
    from sc
    where cno = 'C002'
    order by grade desc
    limit 2
) as a,stu
where a.sno = stu.sno

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

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(*) = (select count(*) from cou where teacher = '张老师')
) 

10-43 sql-insert-sample

insert into Student
values
(99,'test')

10-44 sql-delete-sample

delete from Student
where id > 50

10-45 添加一条学生记录

insert into stu
values
('S012','周强',1,null,null,null)

10-46 添加学生成绩记录

insert into sc
values
('S012','C001',90),
('S012','C002',null)

10-47 批量插入学生记录

insert into softstu
(
    select sno,sname
    from stu
    where mno= '02'
)

10-48 检索部分教师并插入新表

insert into faculty
(
select teacher as tname
from cou
where cno in 
(
    select cno from sc
    group by cno
    having avg(grade)>70
)
group by teacher
having count(*) = (select count(*) from cou as b where cou.teacher = b.teacher)
)

10-49 插入学生总学分表

insert into totalcredit(sno,totalcredit)
select sno,ifnull(t.score,0) as totalcredit
from stu
left join 
(
    select sc.sno as id,sum(credit) as score
    from sc,cou
    where grade>60 and sc.cno = cou.cno
    group by sc.sno
) as t on stu.sno=t.id
order by stu.sno;

10-50 单表查询：根据运费查询订单信息

select orderid,customerid,employeeid from orders
where Freight between 10 and 50;

10-51 修改高数不及格的学生成绩

update sc
set grade = 60
where grade < 60 and cno = (select cno from cou where cname = '高等数学')

10-52 修改女生成绩

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

10-53 修改学生选课成绩(俩种都行)

update sc 
-- set grade= if(grade<70,grade*1.05,grade*1.04) 
set grade = (case when grade < 70 then grade*1.05 else grade*1.04 end)
where cno='C001';

10-54 添加成绩等级rank字段

update sc 
set rank= 
(
    case
    when grade>=0 and grade<60 then 'E'
    when grade>=60 and grade<=69 then 'D'
    when grade>=70 and grade<=79 then 'C'
    when grade>=80 and grade<=89 then 'B'
    when grade>=90 then 'A'
    end
);

10-55 计算并填写学生获得的总学分

update stu,
(
select sno,sum(credit) as credit
from sc,cou
where grade >=60 and cou.cno = sc.cno
GROUP BY sno
) as a
set stu.totalcredit = a.credit
where stu.sno = a.sno

10-56 删除成绩为空的学生选课记录

delete from sc where grade is null;

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

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

10-58 spj-查询供应工程 j1 的供应商

select distinct sno
from spj
where jno = 'j1'

10-59 spj-查询供应工程 j1 零件 p1 的供应商

select sno
from spj
where jno = 'j1' and pno = 'p1';

10-60 2-1(a) 查询st1制片公司的地址

select address
from Studio
where name = 'st1';