上机练习3 - 数据查询(2)

17.

select 所在系,count(*) 学生总数,avg(成绩) 平均成绩 from student st join sc on st.学号=sc,学号 group by 所在系

18.

select 学号,count(课程号) 选课门数,avg(成绩) 平均成绩 from sc group by 学号 having count(课程号)>2

19.

select 学号,sum(成绩) 总成绩 from sc group by 学号 having sum(成绩)>200 20.

use pubs

select type ,avg(price) 平均价格,max(price) 最高价格

from titles group by type having avg(price)>12.0

21.

select avg(price) 平均价格 from titles where royalty=10 group by type 22.

select sum(price) 总价格 from titles group by type having count(type)>3 23.

use sqllx

select 姓名,所在系 from student st,sc where st.学号=sc.学号 and sc.课程号='c02'

24.

select 姓名,课程号,成绩 from student st,sc where st.学号=sc.学号 and sc.成绩>80

order by 成绩 desc

25.

select 姓名,性别,成绩 from student st , course co,sc where st.学号=sc.学号 and

sc.课程号=co.课程号 and st.所在系='计算机系'and co.课程名='数据库基础' and st.性别='男'

26.

select student.学号,姓名,课程号,成绩 from student left join sc on student.学号=sc.学号 27.

select top 3 st.学号,姓名,所在系,成绩 from student st,course co ,sc

where st.学号=sc.学号 and co.课程号=sc.课程号 and 课程名='数据库基础' order by 成绩 desc 28.

select distinct s1.学号, s1.课程号 from sc s1 join sc s2 on s1.课程号=s2.课程号 order by s1.课程号

29.

select distinct s1.姓名,s1.年龄 from student s1 join student s2 on s1.年龄=s2.年龄 order by s1.年龄

30.

select co.课程号,co.课程名 from course co , sc

where co.课程号=sc.课程号 and co.课程号 not in(select 课程号 from sc) 31.

select 姓名 student_name,课程名 course_name,成绩 grade into new_sc

from student st join sc on st.学号=sc.学号 join course co on co.课程号=sc.课程号 where sc.成绩 is not null

32.

select st.所在系,st.姓名,st.性别,co.课程名,sc.成绩 from student st,course co,sc where sc. 学号=st.学号 and sc.课程号=co.课程号 and st.所在系='计算机系' union

select st.所在系,st.姓名,st.性别,co.课程名,sc.成绩 from student st,course co,sc where sc. 学号=st.学号 and sc.课程号=co.课程号 and st.所在系='信息系'

33.

select distinct city from Employees union

select distinct city from Customers order by city asc 34.

select ProductID, ProductName, case

when UnitPrice <=10 then '很便宜'

when UnitPrice >10 and UnitPrice<=20 then '较便宜' when UnitPrice >20 and UnitPrice<=30 then '中等' when UnitPrice >30 and UnitPrice<=40 then '较贵' when UnitPrice >40 and UnitPrice<=100 then '很贵' when UnitPrice >100 then '价格过高' end

from products

where UnitsInStock>10 35.

select s.sno 学号,sname 姓名, case sdept

when '计算机系' then 'CS' when '信息系' then 'IS' when '数学系' then 'MA' else 'OTHER'

end 所在系,grade 成绩

from student s join sc on s.sno = sc.sno join course c on sc.cno=c.cno where cname='vb'

参考以上代码进行调试，发现错误并处理。