3.29 实践课

SQL 练习： 针对 student/sc/course/teacher

1、统计每门课程的平均分，显示课程号、课程名称、平均分
select x.cid,cname,avg(source) pjcj from course x,sc where x.cid=sc.cid group by x.cid,cname;

2、统计课程号为 1 和 2，成绩在 70 分以上的人数，显示课程号、课程名称、人数
select x.cid,cname,count(source) from course x,sc where x.cid=sc.cid and (x.cid=1 or x.cid=2) and source>70 group by x.cid,cname;

3、统计年龄在 20~30 之间的男女生人数 ,性别。
select ‘男’ ssex,count(sage) from student where sage between 20 and 30 and ssex=’男’ union select ‘女’ ssex,count(sage) from student where sage between 20 and 30 and ssex=’女’;

4、统计每个人不同等级的数量，显示学号，姓名，等级，数量
select x.sid 学号,sname 姓名,’A’ 等级,count(*) 数量 from student x,sc y where x.sid=y.sid and source>=90 group by x.sid,sname
union select x.sid 学号,sname 姓名,’B’ 等级,count(*) 数量 from student x,sc y where x.sid=y.sid and source between 75 and 90 group by x.sid,sname
union select x.sid 学号,sname 姓名,’C’ 等级,count(*) 数量 from student x,sc y where x.sid=y.sid and source between 60 and 75 group by x.sid,sname
union select x.sid 学号,sname 姓名,’D’ 等级,count(*) 数量 from student x,sc y where x.sid=y.sid and source<60 group by x.sid,sname;

select sid,sname,dj,count(*) from dj group by sid,sname,dj;

5、查询学过某个老师所带的所有课程的学生，显示学号，姓名
select x.sid,sname,y.cid from student x,course y ,sc where tid=1 and x.sid=sc.sid and sc.cid=y.cid group by x.sid,sname,y.cid;
Select distinct sc.sid,sname from sc,student x where sc.sid=x.sid and cid in
(select cid from course where tid=1) group by sc.sid,sname having count(*)=(select count(tid) from course where tid=1 group by tid);
6、找出需要补考的人，显示学号，姓名，补考课程名称
select x.sid,sname,cname from student x,course y,sc where x.sid=sc.sid and sc.cid=y.cid and source<60 group by x.sid,sname,cname;

1、查询价格在 10～20 之间药品编码，名称和类别
select itemno,itemname,itempety from drug where price between 20 and 30;
2、查询药品数量大于 50 或单价大于 30 的药品名称、数量和价格，按库存金 额从高到底显示
select itemo,quantity,price from drug where quantity>50 or price>30;
3、将数量超过 30 的所有药品按类别分类，求每类的数量总和，库存金额总和
select sum(quantity),sum(quantity*buyprice) from drug where quantuty>30 group by itemtype;
4、查询账单号为 200812010001 的药品编码、名称，销售数量，销售单价
select x.itemno,itemname,y.quantity,y.price from drug x,detiail y where x.itemno=y.itemno and billid=200812010001;