sql实验四
use stuinfo /*1.查询 student 表中的学号、姓名和年龄并为列设置别名，结果按学号升序排*/ select sno as 学号,sname as 姓名, year(getdate())-YEAR(sbirthday)as 年龄 from student order by sno /*2.查询班级（要求不重复）*/ select distinct sclass from student /*3.在 student 表查询姓“王”的学生信息。*/ select * from student where sname like "王%" /*4．查询成绩在-100 之间的学号、课程号及成绩，结果先按课程号升序排，课程号一样的再按成绩降序排。*/ select * from score WHERE degree between 80 and 100 order by cno,degree desc /*5.查询所有缺考的学生的学号及课程号。*/ select sno,cno from score where degree is NULL /*6.查询‘-105’课的选课人数、最高分、最低分和平均分。*/ select count(*)as 选课人数,max(degree)as 最高分,min(degree)as 最低分,avg(degree)as平均分 from score where cno="3-105" /*7．查询每位同学的平均成绩，包括学号和平均成绩两列，结果按学号升序排。*/ select sno as 学号,avg(degree)as平均成绩 from score group by sno/*以学号分组*/--8.查询各班各门课的平均成绩，包括班号、课程号和平均成绩三列，结果先按班升序排，班一样的再按课程号升序排。
select sclass,cno,avg(degree)as平均成绩 from student,score group by sclass,cno order by sclass,cno--9.查询 score 表中至少有名学生选修的课程号及平均分。
select cno as 课程号,avg(degree)as平均分 from score group by cno having count(*)>=5 /*10.查询其平均分高于的学生的学号，姓名和平均分。*/ select student.sno as 学号,sname as 姓名,AVG(degree)AS "平均成绩" FROM student,score where student.sno=score.sno GROUP BY student.sno,student.sname HAVING AVG(degree)>=80--11.查询“”班所选课程的平均分，包括课程名和平均分。
select course.cname as 课程名,avg(degree)as平均分 from student,score,course
where student.sno=score.sno and student.sclass="95031" and score.cno=course.cno group by course.cno,course.cname--12.查询所有教师的任课情况，包括教师姓名和课程名两列，如果某位教师没有任课则课程名列显示 NULL。
select teacher.tname,course.cname from teacher LEFT JOIN course ON teacher.tno=course.tno--外链接 P145--13.查询其最低分大于，最高分小于的学生的学号、所选课程号及其分数。
select sno,cno,degree from score a WHERE(SELECT MIN(degree)FROM score b where a.sno=b.sno)>70 and(SELECT MAX(degree)FROM score c where a.sno=c.sno)<90 and degree is not NUll--14.查询成绩高于所选课平均分的学生学号、姓名、课程号和成绩。
select 学号=student.sno,姓名=sname,课程号=cno,成绩=degree from student INNER JOIN score a on student.sno=a.sno where a.degree>(select AVG(degree)from score b where a.cno=b.cno)--15.查询每门课最高分的课程名、学生姓名和成绩。
select 课程名=cname,学号=student.sno,姓名=sname,成绩=degree from student,course,score where student.sno =score.sno and score.cno =course.cno and degree=(select MAX(degree)from score where score.cno =course.cno)--16.查询选修其课程的学生人数多于人的教师姓名。
select tname from score a,teacher,course where course.tno=teacher.tno and(select count(*)from score where score.cno=course.cno)>5 group by tname--17.查询没选“张旭”教师课的学生成绩，并按成绩递增排列。
select score.degree from score,course,teacher where course.tno=teacher.tno and score.cno=course.cno and tname!="张旭" order by degree--18.查询没有任课的教师的姓名。
select tname from teacher a except select tname from course,teacher b where course.tno=b.tno--19.查询没有选修"6-166"课程的学生的学号和姓名。
select sno,sname from student where sno not in(select sno from score where cno="6-166")--20.查询出所有男生信息放入 NS 表中。
--第一种 select 学号=student.sno,姓名=sname,性别=ssex,年龄=datediff(year,sbirthday,getdate()),班级=sclass,课程号=score.cno,课程名=cname,成绩=degree into NS from student,course,score where ssex="男" and student.sno=score.sno and score.cno=course.cno order by student.sno
--第二种 SELECT * INTO NS FROM student where ssex="男" SELECT * FROM NS--21 删除没人选的课程。
DELETE course WHERE course.cno NOT IN(SELECT cno FROM score)--22.将“”班学生的成绩全部减去分。
UPDATE score SET degree=degree-10 FROM score JOIN student ON student.sno=score.sno where student.sclass="95031" use OrderManagement--1.查询年的所有订单信息（订单号，客户号，订购日期）。
SELECT * FROM order_list WHERE year(定购日期)=2001--2.查询订单明细中有哪些器件（即查询不重复的器件号和器件名）。
select DISTINCT 器件名,器件号 from order_detail--3．查询客户名为“三益贸易公司”的订购单明细（订单号、器件号、器件名、单价和数量），查询结果先按“订单号”升序排，同一订单的再按“单价”降序排。
select 订单号,器件号,器件名,单价,数量 from order_detail,order_list,customer where 客户名="三益贸易公司" and customer.客户号=order_list.客户号 and order_detail.[ 订单号]=order_list.订单号 ORDER BY 订单号,单价 DESC--4．查询目前没有订单的客户信息。
select * from customer where 客户号 not in(select 客户号 from order_list)--5．查询客户名中有“科技”字样的客户信息。
select * from customer where 客户名 like "%科技%"--6.查询每笔订单的订单号和总金额，查询结果按订单号升序排，查询结果存入表 ZJE 中。
select [ 订单号],总金额=sum(数量*单价)into ZJE from order_detail group by [ 订单号] order by [ 订单号]--7.查询订单数量超过笔的客户号及订单数量，查询结果按订单数量降序排。
select 客户号,订单数量=count(订单号)from order_list group by 客户号 having count(订单号)>5 ORDER BY count(订单号)DESC--8.查询每种器件中单价最低的订单明细（订单号、器件号、器件名、单价和数量）。
select * from order_detail a where 单价=(select min(单价)from order_detail b
where a.器件名=b.器件名)/*9.对表 order_detail 建立查询，把“订单号”的尾部字母相同且“器件号”相同的订单合并 成一张订单，新的“订单号”取原来订单号的尾部字母，器件号不变，“单价”取最低价，“数量”取合计，查询结果先按新的“订单号”升序排，再按“器件号”升序排。*/ select RIGHT([ 订单号],1)AS "订单号", 器件号, MIN(单价)AS 单价, SUM(数量)as 数量 FROM order_detail GROUP BY RIGHT([ 订单号],1), 器件号 ORDER BY 订单号, 器件号--10.查询销售总数量最多的三种器件及其总数量。
select top 3 器件号,器件名,SUM(数量)AS 总数量 from order_detail group by 器件号,器件名 order by 总数量 desc
