题阶段二啊，加油

标号       标题       分数       提交通过率

10-1      查询没有选修'C语言'课程的学生     10   121/363(33.33%)

10-2      查询选修某两门课程的学生      10   82/263(31.18%)

10-3      查询选修了“C语言”课程，但是没有选修“数据结构”课程的学生  10

10-4      查询选修了3门以上课程的学生学号      5     50/397(12.59%)

10-5      查询平均成绩高于75分的学生 10   75/178(42.13%)

10-6      查询S001学生选修而S003学生未选修的课程      10   59/127(46.46%)

10-7      批量插入学生记录      10   59/84(70.24%)

10-8      删除成绩为空的学生选课记录   5     57/123(46.34%)

10-9      C1-2新增订单统计信息     20   53/104(50.96%)

10-10    C2-1修改订单运费     20   52/70(74.29%)

10-11    C2-2修改特定职工的订单运费 20   52/96(54.17%)

10-12    修改学生数据表。      10   40/139(28.78%)

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

10-14    获取每个分类下商品的最高价格      10   47/102(46.08%)

10-15    创建视图，包含商品打折前后的价格信息      10   44/91(48.35%)

10-16    查询每门必修课的课程编号，课程名称和选修人数。   10   40/139(28.78%)

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

10-18    查询选课门数大于3门并且平均分大于70分的学生信息。 10   41/113(36.28%)

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

10-20    查询商品表中指定条件的商品信息（多条件查询）      10   36/47(76.60%)

10-21    查询商品表中指定条件的商品信息（多条件查询）      10   32/41(78.05%)

10-22    查询没有任何评论信息的商品id和name（多表查询） 10   39/120(32.50%)

10-23    查询五星商品对应的商品分类信息（多表查询）   10   30/86(34.88%)

10-24    查询年龄18-20之间的学生信息      5     41/114(35.96%)

10-25    查询平均成绩最高的前3名同学      10   32/186(17.20%)

10-26    检索出年龄大于23岁的男同学的基本信息，查询结果按学号升序排列。 10       48/119(40.34%)

10-27    查询同专业的学生（MSSQL）  10   41/177(23.16%)

10-28    查询没有选课的学生学号和姓名      5     33/163(20.25%)

10-29    查询商品分类id为10或11的商品对应的商品信息（多表查询）     10       35/110(31.82%)

10-30    查询姓刘的员工信息   10   43/113(38.05%)

10-31    查询’A01’仓库中的职工中比’A02’所有职工薪水都高的职工编号与姓名     10       29/84(34.52%)

10-32    查询’A02’仓库中薪水最高的员工编号与姓名  20   26/205(12.68%)

10-33    😊检索出没有先行课的课程全部信息     2     29/83(34.94%)

10-1 查询没有选修'C语言'课程的学生

select sno  学号,sname  姓名 

from stu

where not exists(select * from sc 

   where cno=( select cno from cou

     where cname = 'C语言')

                 and stu.sno=sc.sno )

order by sno asc;

10-2 查询选修某两门课程的学生

select a.sno 学号

from sc a

where a.cno='C001' and b.cno='C002'

group by a.sno

10-3 查询选修了“C语言”课程，但是没有选修“数据结构”课程的学生

select stu.sname from sc

join cou on cou.cno=sc.cno

join stu on stu.sno=sc.sno

where cou.cname='C语言'

and sname not in(

select stu.sname from sc

join cou on cou.cno=sc.cno

join stu on stu.sno=sc.sno

where cou.cname='数据结构'

)

10-4 查询选修了3门以上课程的学生学号

select sno 学号,count(sno) 选课门数

from sc

group by sno

having count(sno)>3;

10-5 查询平均成绩高于75分的学生

SELECT

    sno AS 学号 ,sum(grade) / COUNT(sno) AS 平均成绩

FROM

    sc

GROUP

    BY sno

HAVING

sum(grade) / COUNT(sno) > 75

10-6 查询S001学生选修而S003学生未选修的课程

select cno  课程号

from sc where

sno='S001'and cno not in(select cno

from sc where

sno='S003');

10-7 批量插入学生记录

insert into softstu

select sno 学号,sname 姓名

from stu

where mno='02';

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

delete

from sc

where grade is NULL;

10-9 C1-2新增订单统计信息

insert into results

select orders.CustomerID,count(orders.CustomerID) OrderCount

from orders

group by CustomerID

10-10 C2-1修改订单运费

update orders set freight = freight*1.5

10-11 C2-2修改特定职工的订单运费

update orders

set freight = freight *0.95

where EmployeeID =3 or EmployeeID = 4;

10-12 修改学生数据表。

update student

set avg_grade=(

select s.avg_grade from(

select student.sno,round(avg(grade),2) as avg_grade

from score,student

where score.sno = student.sno

group by student.sno

)s

where student.sno = s.sno)

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

select sno,term

from score

where cno="40008" and grade  between 60 and 80

10-14 获取每个分类下商品的最高价格

select category_id,max(price) max_price

from sh_goods

group by category_id

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

create view view_goods

as

select id,name ,price old_price,price*0.5 new_price

from sh_goods

10-16 查询每门必修课的课程编号，课程名称和选修人数。

select course.cno,cname,count(sno) total

from course left join score on course.cno=score.cno

where attribute='必修'

group by course.cno,cname

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

select e.empno 员工编号,e.ename 员工姓名,e.mgr 上司编号,m.ename 上司姓名

from emp as e left outer join emp as  m on(e.mgr=m.empno);

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

select StudentID,count(*) course_num,avg(grade) avg_score

from grade as a

group by StudentID

having count(*)>3 and avg(Grade)>70    

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

create view customer_male(会员编号,会员姓名,性别,电话)

as

select *

from customer

where sex='男';

10-20 查询商品表中指定条件的商品信息（多条件查询）

select id ,name,price

from sh_goods

where category_id=3 and score=5.00

10-21 查询商品表中指定条件的商品信息（多条件查询）

select name,price,score

from sh_goods

where score=4.5 or price < 10

10-22 查询没有任何评论信息的商品id和name（多表查询）

select id,name

from sh_goods

where id not in(select distinct goods_id from sh_goods_comment);

10-23 查询五星商品对应的商品分类信息（多表查询

select sh_goods.id gid,sh_goods_category.id cid,sh_goods_category.name cname,score

from sh_goods

join sh_goods_category

on sh_goods.category_id=sh_goods_category.id

where score=5;

10-24 查询年龄18-20之间的学生信息

分数 5

SELECT sno AS 学号,sname AS 姓名,sex AS 性别,mno AS 专业,

2020-year(birdate) AS 年龄,memo AS 备注

FROM stu

WHERE 2020-year(birdate) between 18 and 20

10-25 查询平均成绩最高的前3名同学

SELECT stu.sno as 学号, stu.sname as 姓名,stu.sex as 性别,

(year(now())-year(stu.birdate)-2) as 年龄,avg(sc.grade)as 平均成绩

from stu join sc on stu.sno=sc.sno

GROUP BY sc.sno

ORDER BY avg(sc.grade) DESC

limit 3

10-26 检索出年龄大于23岁的男同学的基本信息，查询结果按学号升序排列。

select *

from student

where timestampdiff(YEAR,birth,"2020-12-8")>=23

and sex='男'

order by sno

10-27 查询同专业的学生（MSSQL

select sno  学号,sname 姓名

from stu

where mno  in (select mno  from stu  where sname=N'张三')  and sname<>N'张三'

10-28 查询没有选课的学生学号和姓名

select sno,sname

from

students

where

sno not in

(

    select sno

    from sc

)

10-29 查询商品分类id为10或11的商品对应的商品信息（多表查询）

select a.id as gid,a.name as gname,b.id as cid,b.name as cname

from sh_goods as a

right join sh_goods_category as b on a.category_id = b.id

where b.id in (10,11)

10-30 查询姓刘的员工信息

select *

from employee

where EName like "刘%"

10-31 查询’A01’仓库中的职工中比’A02’所有职工薪水都高的职工编号与姓名

select Eid,EName from employee

where Salary = (select max(Salary) from employee where Wno='A01')

10-32 查询’A02’仓库中薪水最高的员工编号与姓名

select Eid,EName from employee

where salary = (select max(Salary) from employee where Wno = 'A02')

10-33 😊检索出没有先行课的课程全部信息

select *

from course

where PCid is NULL;

10-1 查找所有的仓库信息

分数 5

select *

from warehouse;

10-2 查询‘张三’的员工编号

select Eid

from employee

where EName = '张三';

10-3 查询商品表中指定价格范围的商品信息

select id,name,price

from sh_goods

where price between 2000 and 6000;

10-4 A1-3查询顾客表中所有不重复的城市

select Distinct City

from customers

10-5 查询商品表中指定条件的商品信息（多条件查询）

select name,price,score

from sh_goods

where score = 4.5 or price < 10

10-6 查询重量大于60的产品编号及产品名，并按照产品名升序排

select Pid, PName

from product

where Weight>60

order by PName asc

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

select id,name,price

from goods

where name like '%pad%'

10-8 查询商品表中指定条件的商品信息（多条件查询）

select id,name,price

from sh_goods

where category_id = 3 and score = 5

10-9 删除没有确定工作仓库的员工信息

delete from employee

where Wno is null;

10-10 将’B01’号仓库的面积扩大20%

分数 5

update warehouse

set Size  = Size * 1.2

where Wno = 'B01' ;

10-11 统计各班男、女生人数

SELECT class as 班级, ssex as 性别,count(*) as 人数

from students

GROUP BY class,ssex;

10-12 查询每个仓库的信息及其相关的订单编号

分数 10

select d.Wno , d.City , d.Size , c.OrdNo

from  warehouse d , employee a , orders c

where a.Eid = c.Eid and d.Wno = a.Wno

10-13 查询’A01’仓库中的职工中比’A02’所有职工薪水都高的职工编号与姓名

select Eid , EName

from employee

where Salary >

(select Salary

from employee

 where Wno = 'A02'

order by Salary desc

limit 1)

and Wno = 'A01';

10-14 查询每个员工编号和姓名及其工作仓库的信息

SELECT e.Eid,e.EName,e.Wno,w.City,w.Size

from employee e inner join warehouse w

where e.Wno=w.Wno

10-15 查询姓刘的员工信息及所销售的订单编号，单价和数量信息

select a.Eid , a.EName , a.Wno , a.Salary , b.OrdNo , b.Price , b.QTY

from employee a inner join  orders b

where a.Eid = b.Eid and a.EName like ('刘%');