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'
参考以上代码进行调试，发现错误并处理。