实验六 使用T-SQL语句查询数据

一.实验目的:

熟练掌握使用SELECT语句进行数据查询。

二.实验内容:(所有题写到实验报告中)

1.对数据库stuinfo使用T-SQL命令进行如下操作:

  1.  查询student表中的学号、姓名和年龄并为列设置别名,结果按学号升序排。
    
  2.  查询班级(要求不重复)。
    
  3.  查询姓“王”的学生信息。
    
  4.  查询成绩在80-100之间的学号、课程号及成绩,结果先按课程号升序排,课程号一样的再按成绩降序排。
    
  5.  查询所有缺考的学生的学号及课程号。
    
  6.  查询 ‘3-105’课的选课人数、最高分、最低分和平均分。
    
  7.  查询每位同学的平均成绩,包括学号和平均成绩两列,结果按学号升序排。
    
  8.  查询各班各门课的平均成绩,包括班号、课程号和平均成绩三列,结果先按班升序排,班一样的再按课程号升序排。
    
  9.  查询score表中至少有5名学生选修的课程号及平均分。
    
  10. 查询其平均分高于80的学生的学号,姓名和平均分。

  11. 查询“95031”班所选课程的平均分,包括课程名和平均分。

  12. 查询所有教师的任课情况,包括教师姓名和课程名两列,如果某位教师没有任课则课程名列显示NULL。

  13. 查询其最低分大于70,最高分小于90的学生的学号、所选课程号及其分数。

  14. 查询成绩高于所选课平均分的学生学号、姓名、课程号和成绩。

  15. 查询每门课最高分的课程名、学生姓名和成绩。

  16. 查询选修其课程的学生人数多于5人的教师姓名。

  17. 查询没选“张旭”教师课的学生成绩,并按成绩递增排列。

  18. 查询没有任课的教师的姓名。

  19. 查询没有选修"6-166"课程的学生的学号和姓名。

  20. 查询出所有男生信息放入NS表中。

  21. 删除没人选的课程。

  22. 将“95031”班学生的成绩全部减去10分。

\2. 对订单管理库ordermanagement使用T-SQL命令进行下列查询。

ordermanagement数据库中有三个表,其结构如下:(加下划线的为主键)

客户表customer(客户号,客户名,地址,电话)

订单表order_list(订单号,客户号,订购日期)

订单明细表Order_detail(订单号,器件号,器件名,单价,数量)

使用SELECT语句完成下列查询:

  1. 查询2001年的所有订单信息(订单号,客户号,订购日期)。

  2. 查询订单明细中有哪些器件(即查询不重复的器件号和器件名)。

  3. 查询客户名为“三益贸易公司”的订购单明细(订单号、器件号、器件名、单价和数量),

查询结果先按“订单号”升序排,同一订单的再按“单价”降序排。

  1. 查询目前没有订单的客户信息。

  2. 查询客户名中有“科技”字样的客户信息。

  3. 查询每笔订单的订单号和总金额,查询结果按订单号升序排,查询结果存入表ZJE中。

  4. 查询订单数量超过5笔的客户号及订单数量,查询结果按订单数量降序排。

  5. 查询每种器件中单价最低的订单明细(订单号、器件号、器件名、单价和数量)。

  6. 对表order_detail建立查询,把“订单号”的尾部字母相同且“器件号”相同的订单合并

成一张订单,新的“订单号”取原来订单号的尾部字母,器件号不变,“单价”取最低价,

“数量”取合计,查询结果先按新的“订单号”升序排,再按“器件号”升序排。

  1. 查询销售总数量最多的三种器件及其总数量。

use stuinfo

select sno as '学号',sname as '姓名',DateName(year,GetDate())-DATEPART(YYYY,sbirthday) as '年龄' from student order by sno asc;

select sno as '学号',sname as '姓名', DateName(year,GetDate())-DATEPART(YYYY,sbirthday) as '年龄' from student order by sno desc

---2

select distinct sclass from student

---3

select sname from student where substring(sname,1,1)='王'

---4

select sno,cno,degree from score where degree>=80 order by cno asc,degree desc

---5

select sno,cno from score where degree is null

---6

select COUNT(*) as '选课人数',MAX(degree) as 'max',MIN(degree) as 'min',AVG(degree) as 'avg' from score

---7

select sno,AVG(degree)as 'avg' from score group by sno order by sno desc

---8

select sclass,cno,AVG(degree) as 'AVG' from student,score group by sclass,cno order by sclass,cno

---9

select cno,AVG(degree) from score group by cno having COUNT(*) >= 5

---10

select student.sno as '学号', sname as '姓名' ,AVG(degree) as 'AVG' 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,course,score where student.sno=score.sno and sclass='95031' group by course.cno,course.cname

---12

select tname as '教师姓名',cname as '课程名' from teacher left join course on teacher.tno = course.tno group by tname,cname

---13

select sno as '学号',cno as '课程号',degree as '分数' 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 score.sno,sname,cno,degree from student,score where student.sno=score.sno and degree>(select AVG(degree) from score,course where score.cno=course.cno)

---15

select cname '课程名',sname '姓名',degree '成绩' from student,course,score where student.sno=score.sno and course.cno=score.cno and degree=(select MAX(degree)from score where score.cno=course.cno)

---16

select tname as 教师姓名 from teacher,course where teacher.tno=course.tno and (select COUNT(*) from score where score.cno=course.cno)>5

---17

select score.degree from score,teacher,course where teacher.tno=course.tno and course.cno=score.cno and tname!='张旭' order by degree

---18

select tname from teacher except select tname from teacher,course where teacher.tno=course.tno

---19

select student.sno,student.sname from student,course,score where score.sno not in (select sno from score where cno='1-166') group by student.sno,student.sname

---20

select student.sno as 学号,student.sname as 学生姓名,ssex as 性别,sbirthday as 生日,sclass as 班级,cname as 课程,degree as 成绩 into NS from student,score,course where student.sno=score.sno and ssex='男' and course.cno=score.cno order by student.sno

---21

delete course where course.cno not in (select cno from score)

---22

update score set degree = degree - 10 from student,score where student.sno=score.sno and student.sclass='95031'

第二题

use OrderManagement

---1

select * from order_list

---2

select distinct 器件号, 器件名 from order_detail

---3

select order_detail.订单号, order_detail.器件号, order_detail.器件名, order_detail.单价, order_detail.数量 from Customer, order_detail, order_list

where Customer.客户名 = '三益贸易公司' and Customer.客户号 = order_list.客户号 and order_list.订单号 = order_detail.订单号

order by 订单号, 单价 desc

---4

select * from Customer where Customer.客户号 not in (select 客户号 from order_list)

---5

select * from Customer where 客户名 like '%科技%'

---6

select 订单号, sum(单价 * 数量) as '总金额' 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 单价 in (select MIN(单价) from order_detail b where a.器件名=b.器件名)

---9

select RIGHT(订单号, 1) as 订单号, 器件号, MIN(单价)as 最低价,SUM(数量) as 数量 from order_detail group by 订单号, 器件号 order by 订单号, 器件号

---10

select top 3 器件号, 器件名, SUM(数量) as 数量 from order_detail group by 器件号, 器件名 order by 数量 desc

发布了50 篇原创文章 · 获赞 12 · 访问量 6万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览