pta mysql训练题集(241-260)

10-241 添加销售记录

insert into recorder
values
(null,C008,G001,3,null),
(null,C008,G002,1,null);

10-242 删除马齐的购物记录

delete from recorder
where cid=(select cid from customer where cname=马齐);

10-243 修改杰克的购物记录。

update recorder set quantity = 1
where cid = (
    select cid from customer where cname=杰克
) and gid=G006;

10-244 查询单价少于500的货品信息。

select gid 商品编号,gname 商品名称,price 单价,stock 库存
from good
where price<=500
order by 商品编号 asc;

10-245 找出所有姓“张”的学生学号、姓名、院部和联系电话

select sno 学号, sname 姓名,dept 院部,phone 电话
from student
where sname like 张%;

10-246 查看1998年出生的学生信息，查询结果以学号升序排列

select sno 学号,sname 姓名,pnum 身份证号码
from student
where year(birth) = 1998
order by 学号 asc;

10-247 统计每个民族的学生总人数。

select nation as 民族,count(*) as 总人数
from student
group by nation

10-248 检索王林选课的情况。

select a.sno,sname,cname,grade
from student as a,score as b,course as c 
where a.sno=b.sno and b.cno=c.cno and sname=王林;

10-249 修改学生基本信息

update student
set dept = 数计学院
where pnum = 152221199710182012

10-250 修改商品库存数量。

update good
set stock = stock-10
where gname like %爱国者%;

10-251 检索2019级云计算1班所有学生的姓名、选修课号和成绩。

select sname,cno,grade
from student,score
where student.sno = score.sno and class = 2019级云计算1班;

10-252 查询学生表所有学生记录（MSSQL）

select * from stu

10-253 查询学生表中部分信息(MSSQL)

select sno,sname,(case when sex=1 then 男 else 女 end) as sex
from stu

10-254 查询学生表中的女生信息(MSSQL)

select sno 学号,sname 姓名 from stu where sex = 0;

10-255 查询学生表中大于19岁的女生（MSSQL）

select sno 学号,sname 姓名,sex 性别,mno 专业,(year(2020-03-01)-year(birdate)) as 年龄,memo 备注
from stu
where sex=0 and year(2020-03-01)-year(birdate)>19;

10-256 查询年龄18-20之间的学生信息（MSSQL）

select sno 学号,sname 姓名,sex 性别,mno 专业,(year(2020-03-01)-year(birdate)) as 年龄,memo 备注
from stu
where year(2020-03-01)-year(birdate) between 18 and 20;

10-257 查询姓‘李’的学生记录(MSSQL)

select sno as 学号,sname as 姓名,sex as 性别,mno as 专业,birdate as 出生日期,memo as 备注 
from stu
where sname like N李%
order by 学号 asc;

10-258 查询部分专业的学生(MSSQL)

select sno as 学号,sname as 姓名,sex as 性别,mname as 专业
from stu as a,major as b
where a.mno = b.mno and mname in (N计算机工程,N软件工程)
order by sno asc

10-259 查询所有学生的平均成绩(MSSQL)

select a.sno as 学号,isnull(avg(grade),0) as 平均成绩
from stu as a
left join sc on a.sno = sc.sno
group by a.sno;

10-260 查询各专业学生的平均成绩(MSSQL)

select mname as 专业,isnull(avg(grade),0) as 平均成绩
from major
left join stu on major.mno = stu.mno
left join sc on stu.sno = sc.sno 
group by mname,major.mno
order by major.mno;