pta mysql训练题集(341-360)

10-341 把“李小鹏”同学的成绩全部删除

delete from sc
where sno in (select sno from students where sname = '李小鹏');

10-342 spj-查询没有使用天津供应商生产的红色零件的工程（MSSQL）

select jno 工程项目号
from j 
where jno not in 
(
    select jno
    from spj,s,p 
    where spj.sno = s.sno and spj.pno = p.pno and city = N'天津' and color = N'红'
);

10-343 查询编号为“Dp02”系部的系部名称和系部主任

select DepartmentName,DepartmentHeader
from Department
where DepartmentID = 'Dp02';

10-344 动态更新员工薪水

update Salary
set count = 
(
    case
    when count >= 300000 then count*0.9
    when count between 250000 and 280000 then count*1.2
    else count
    end
)

10-345 显示出所有学生的学号，课号以及提高1分后的成绩。

select sno,cno,ifnull(null,grade+1) as grade
from score

10-346 检索出参加选课的学生学号。

select distinct sno from score

10-347 检索出被学生选修的课程编号，结果按着课程编号降序排列。

select distinct cno
from score
order by cno desc;

10-348 找出音乐学院或体育学院的学生基本信息

select * from student
where dept in ('音乐学院','体育学院');

10-349 检索选修40008课程并且成绩在60到80之间的学生学号和选修的学期。

select sno,term
from score
where cno = '40008' and grade between 60 and 80;

10-350 显示所有学生的学号、姓名和出生年份。

select sno as 学号,sname as 姓名,year(birth) as 出生年份
from student;

10-351 检索出姓“王”的学生姓名和联系电话。（采用系统函数实现）

select sname,phone
from student
where sname like '王%';

10-352 显示所有课程的基本信息，课程编号不足6位的，左侧补0。

select lpad(cno,6,0) as 课程编号,cname as 课程名称,credit as 学分,attribute as 类别
from course;

10-353 显示出所有选课学生的学号，课号以及提高1%后的成绩，要求成绩四舍五入保留整数。

select sno,cno,ifnull(null,round(grade*1.01)) as grade
from score;

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

select *
from student
where timestampdiff(year,birth,'2021-09-01') >= 24 and sex='男';
--  timestampdiff 日期详细计算

10-355 查看商品表中的所有关键词

SELECT distinct keyword from sh_goods;

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

select name,category_id,price
from sh_goods
order by category_id asc,price desc;

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

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

10-358 为商品表所有字段添加数据

insert into sh_goods
values
(2,3,'钢笔','001','练字必不可少',15.00,300,3.90,500);

10-359 为商品表部分字段添加数据

insert into sh_goods
(id,category_id,name,keyword,content)
values
(1,3,'2B铅笔','001','考试专用');

10-360 查询商品表中全部数据

select * from sh_goods;