在线编程在线课堂在线测评Anycodes在线编程

编程论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

How to use bs4??
本帖最后由 carry0987 于
Double Queue 问题描述 : The new founded Balkan Investment Group Bank (
John 问题描述 : Little John is playing very funny game
linux-command Linux命令大全搜索工具,内容包含Linux命令
Coati 是一款跨平台的代码查看工具,适用于 C/C++ 和 Java。商业软件。特性:1. 索引
系统可承载海量并发,消息收发确认机制 保障消息必达 系统采用动态智
全平台视频监控,支持安卓苹果以及pcweb,支持海康大华等主流dvr,全部源码以及文档 单聊、群聊、商
如何访问类的私有属性? 下面以 TPathData 为例,
问题:从 XE4 以来,Firemonkey 曲线绘图在移动平台不平滑的问题一直令人诟病,提交到官方的 QC 也是族繁不及备载,官方似乎有意的
操作数据库(RODBC)   odbcConnect(dsn, uid="", p
数据模式:mode函数显示任何对象的模式。常见的单个的
系统可承载海量并发,消息收发确认机制 保障消息必达 系统采用动态智
RabbitMQ与PHP(一) 项
Iease团队扩编预备中,盼望能有Ruby或者java工程师加盟。全职兼职都可以。有爱好的伴侣请与我接洽。 邮件:i
ruby 怎么设置装备摆设GTK2,求教指导下!
#include #include #include #include using namespace std; int main() {
标题如图所示: 有n盏灯,编号1~n。一开端灯都是关着的
成熟的消息收发确认机制,支持万人大群 支持开发自定义的消息sdk接口,扩展性超强 支持单/
成熟的消息收发确认机制,支持万人大群 支持开发自定义的消息sdk接口,扩展性超强 支持单/
1. 注意列表和集合的区别 set 列表表现形式: list_1
Ajax   Ajax即“Asynchronous Javascript And
大师好,我比来在做布谷鸟优
分辨提取A和B图像Harris角点,接下来须要对

[精彩分享] SQL面试笔试经典题(Part 1)

[复制链接]
发表于 2016-12-24 13:19:00 | 显示全部楼层 |阅读模式
本文是在Cat Qi的原贴的基础之上,经本人逐题分别在MySql数据库中实现的笔记,持续更新...
参考原贴:http://www.cnblogs.com/qixuejia/p/3637735.html
01 表结构

  Student(Sno,Sname,Sage,Ssex)    学生表
  Course(Cno,Cname,Tno)       课程表
  SC(Sno,Cno,score)          成绩表
  Teacher(Tno,Tname)        教师表
02 建表及插入测试数据

  (1) 建表:
  1. 1 DROP TABLE IF EXISTS student ;
  2. 2 DROP TABLE IF EXISTS course ;
  3. 3 DROP TABLE IF EXISTS sc ;
  4. 4 DROP TABLE IF EXISTS teacher ;
  5. 5
  6. 6 CREATE TABLE Student
  7. 7 (
  8. 8 Sno int,
  9. 9 Sname varchar(32),
  10. 10 Sage int,
  11. 11 Ssex varchar(8)
  12. 12 )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  13. 13
  14. 14 CREATE TABLE Course
  15. 15   (
  16. 16      Cno    INT,
  17. 17      Cname varchar(32),
  18. 18      Tno    INT
  19. 19   )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  20. 20
  21. 21 CREATE TABLE Sc
  22. 22 (
  23. 23 Sno    INT,
  24. 24 Cno   INT,
  25. 25 score INT
  26. 26 )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  27. 27
  28. 28 CREATE TABLE Teacher
  29. 29   (
  30. 30      Tno    INT,
  31. 31      Tname varchar(16)
  32. 32   )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
复制代码
View Code
  【注】MySQL数据库建表时需要添加“ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci”命令,否则中文会发生乱码。
  (2) 插入测试数据:
  1. 1  insert into Student select 1,'刘一',18,'男' union all
  2. 2  select 2,'钱二',19,'女' union all
  3. 3  select 3,'张三',17,'男' union all
  4. 4  select 4,'李四',18,'女' union all
  5. 5  select 5,'王五',17,'男' union all
  6. 6  select 6,'赵六',19,'女'
  7. 7  
  8. 8  insert into Teacher select 1,'叶平' union all
  9. 9  select 2,'贺高' union all
  10. 10  select 3,'杨艳' union all
  11. 11  select 4,'周磊';
  12. 12  
  13. 13  insert into Course select 1,'语文',1 union all
  14. 14  select 2,'数学',2 union all
  15. 15  select 3,'英语',3 union all
  16. 16  select 4,'物理',4;
  17. 17  
  18. 18  insert into SC
  19. 19  select 1,1,56 union all
  20. 20  select 1,2,78 union all
  21. 21  select 1,3,67 union all
  22. 22  select 1,4,58 union all
  23. 23  select 2,1,79 union all
  24. 24  select 2,2,81 union all
  25. 25  select 2,3,92 union all
  26. 26  select 2,4,68 union all
  27. 27  select 3,1,91 union all
  28. 28  select 3,2,47 union all
  29. 29  select 3,3,88 union all
  30. 30  select 3,4,56 union all
  31. 31  select 4,2,88 union all
  32. 32  select 4,3,90 union all
  33. 33  select 4,4,93 union all
  34. 34  select 5,1,46 union all
  35. 35  select 5,3,78 union all
  36. 36  select 5,4,53 union all
  37. 37  select 6,1,35 union all
  38. 38  select 6,2,68 union all
  39. 39  select 6,4,71;
复制代码
View Code
03 问题及实现代码

  (1)查询“1”课程比“2”课程成绩高的所有学生的学号;
  1. select a.sno from
  2. (select sno,score from sc where cno=1) a,
  3. (select sno,score from sc where cno=2) b
  4. where a.sno=b.sno and a.score>b.score;
复制代码
  (2)查询平均成绩大于60分的同学的学号和平均成绩;
  1. select Sno,AVG(Score) as AvgScore
  2. from SC
  3. group by Sno
  4. having AVG(Score)>60
复制代码
  (3)查询所有同学的学号、姓名、选课数、总成绩;
  1. select student.sno,student.sname,count(sc.cno),sum(sc.score) from
  2. student left outer join sc
  3. on student.sno = sc.sno
  4. group by student.sno
  5. order by student.sno;
复制代码
  (4)查询姓“李”的老师的个数;
  1. select count(distinct tname) as count
  2. from teacher
  3. where tname like '李%';
复制代码
  (5)查询没学过“叶平”老师课的同学的学号、姓名;
  1. select s.sno,s.sname
  2. from student s
  3. where s.sno not in
  4. (
  5.         select distinct(sc.sno) from sc ,course c,teacher t
  6.         where sc.cno = c.cno and c.tno = t.tno and t.tname = '叶平'
  7. )
复制代码
  (6)查询学过“1”并且也学过编号“2”课程的同学的学号、姓名;
  1. select s.sno,s.sname from
  2. student s,
  3. (select sno from sc where cno=1) a,
  4. (select sno from sc where cno=2) b
  5. where s.sno = a.sno and a.sno = b.sno;
复制代码
  方法二 用exist函数
  1. select s.Sno,s.Sname
  2. from Student s,SC sc
  3. where s.Sno=sc.Sno and sc.Cno=1 and exists
  4. (
  5.      select * from SC sc2 where sc.Sno=sc2.Sno and sc2.Cno=2
  6. )
复制代码
  (7)查询学过“叶平”老师所教的所有课的同学的学号、姓名;
  1. select s.sno,s.sname
  2. from student s,teacher t,
  3. course c left outer join sc
  4. on c.cno = sc.cno
  5. where t.tname="叶平" and t.tno = c.cno and s.sno = sc.sno ;
复制代码
  或者:
  1. 1  insert into Student select 1,'刘一',18,'男' union all
  2. 2  select 2,'钱二',19,'女' union all
  3. 3  select 3,'张三',17,'男' union all
  4. 4  select 4,'李四',18,'女' union all
  5. 5  select 5,'王五',17,'男' union all
  6. 6  select 6,'赵六',19,'女'
  7. 7  
  8. 8  insert into Teacher select 1,'叶平' union all
  9. 9  select 2,'贺高' union all
  10. 10  select 3,'杨艳' union all
  11. 11  select 4,'周磊';
  12. 12  
  13. 13  insert into Course select 1,'语文',1 union all
  14. 14  select 2,'数学',2 union all
  15. 15  select 3,'英语',3 union all
  16. 16  select 4,'物理',4;
  17. 17  
  18. 18  insert into SC
  19. 19  select 1,1,56 union all
  20. 20  select 1,2,78 union all
  21. 21  select 1,3,67 union all
  22. 22  select 1,4,58 union all
  23. 23  select 2,1,79 union all
  24. 24  select 2,2,81 union all
  25. 25  select 2,3,92 union all
  26. 26  select 2,4,68 union all
  27. 27  select 3,1,91 union all
  28. 28  select 3,2,47 union all
  29. 29  select 3,3,88 union all
  30. 30  select 3,4,56 union all
  31. 31  select 4,2,88 union all
  32. 32  select 4,3,90 union all
  33. 33  select 4,4,93 union all
  34. 34  select 5,1,46 union all
  35. 35  select 5,3,78 union all
  36. 36  select 5,4,53 union all
  37. 37  select 6,1,35 union all
  38. 38  select 6,2,68 union all
  39. 39  select 6,4,71;0
复制代码
  (8)查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名;
  1. 1  insert into Student select 1,'刘一',18,'男' union all
  2. 2  select 2,'钱二',19,'女' union all
  3. 3  select 3,'张三',17,'男' union all
  4. 4  select 4,'李四',18,'女' union all
  5. 5  select 5,'王五',17,'男' union all
  6. 6  select 6,'赵六',19,'女'
  7. 7  
  8. 8  insert into Teacher select 1,'叶平' union all
  9. 9  select 2,'贺高' union all
  10. 10  select 3,'杨艳' union all
  11. 11  select 4,'周磊';
  12. 12  
  13. 13  insert into Course select 1,'语文',1 union all
  14. 14  select 2,'数学',2 union all
  15. 15  select 3,'英语',3 union all
  16. 16  select 4,'物理',4;
  17. 17  
  18. 18  insert into SC
  19. 19  select 1,1,56 union all
  20. 20  select 1,2,78 union all
  21. 21  select 1,3,67 union all
  22. 22  select 1,4,58 union all
  23. 23  select 2,1,79 union all
  24. 24  select 2,2,81 union all
  25. 25  select 2,3,92 union all
  26. 26  select 2,4,68 union all
  27. 27  select 3,1,91 union all
  28. 28  select 3,2,47 union all
  29. 29  select 3,3,88 union all
  30. 30  select 3,4,56 union all
  31. 31  select 4,2,88 union all
  32. 32  select 4,3,90 union all
  33. 33  select 4,4,93 union all
  34. 34  select 5,1,46 union all
  35. 35  select 5,3,78 union all
  36. 36  select 5,4,53 union all
  37. 37  select 6,1,35 union all
  38. 38  select 6,2,68 union all
  39. 39  select 6,4,71;1
复制代码
  (9)查询有课程成绩小于60分的同学的学号、姓名;

  1. 1  insert into Student select 1,'刘一',18,'男' union all
  2. 2  select 2,'钱二',19,'女' union all
  3. 3  select 3,'张三',17,'男' union all
  4. 4  select 4,'李四',18,'女' union all
  5. 5  select 5,'王五',17,'男' union all
  6. 6  select 6,'赵六',19,'女'
  7. 7  
  8. 8  insert into Teacher select 1,'叶平' union all
  9. 9  select 2,'贺高' union all
  10. 10  select 3,'杨艳' union all
  11. 11  select 4,'周磊';
  12. 12  
  13. 13  insert into Course select 1,'语文',1 union all
  14. 14  select 2,'数学',2 union all
  15. 15  select 3,'英语',3 union all
  16. 16  select 4,'物理',4;
  17. 17  
  18. 18  insert into SC
  19. 19  select 1,1,56 union all
  20. 20  select 1,2,78 union all
  21. 21  select 1,3,67 union all
  22. 22  select 1,4,58 union all
  23. 23  select 2,1,79 union all
  24. 24  select 2,2,81 union all
  25. 25  select 2,3,92 union all
  26. 26  select 2,4,68 union all
  27. 27  select 3,1,91 union all
  28. 28  select 3,2,47 union all
  29. 29  select 3,3,88 union all
  30. 30  select 3,4,56 union all
  31. 31  select 4,2,88 union all
  32. 32  select 4,3,90 union all
  33. 33  select 4,4,93 union all
  34. 34  select 5,1,46 union all
  35. 35  select 5,3,78 union all
  36. 36  select 5,4,53 union all
  37. 37  select 6,1,35 union all
  38. 38  select 6,2,68 union all
  39. 39  select 6,4,71;2
复制代码

  (10)查询没有学全所有课的同学的学号、姓名;
  1. 1  insert into Student select 1,'刘一',18,'男' union all
  2. 2  select 2,'钱二',19,'女' union all
  3. 3  select 3,'张三',17,'男' union all
  4. 4  select 4,'李四',18,'女' union all
  5. 5  select 5,'王五',17,'男' union all
  6. 6  select 6,'赵六',19,'女'
  7. 7  
  8. 8  insert into Teacher select 1,'叶平' union all
  9. 9  select 2,'贺高' union all
  10. 10  select 3,'杨艳' union all
  11. 11  select 4,'周磊';
  12. 12  
  13. 13  insert into Course select 1,'语文',1 union all
  14. 14  select 2,'数学',2 union all
  15. 15  select 3,'英语',3 union all
  16. 16  select 4,'物理',4;
  17. 17  
  18. 18  insert into SC
  19. 19  select 1,1,56 union all
  20. 20  select 1,2,78 union all
  21. 21  select 1,3,67 union all
  22. 22  select 1,4,58 union all
  23. 23  select 2,1,79 union all
  24. 24  select 2,2,81 union all
  25. 25  select 2,3,92 union all
  26. 26  select 2,4,68 union all
  27. 27  select 3,1,91 union all
  28. 28  select 3,2,47 union all
  29. 29  select 3,3,88 union all
  30. 30  select 3,4,56 union all
  31. 31  select 4,2,88 union all
  32. 32  select 4,3,90 union all
  33. 33  select 4,4,93 union all
  34. 34  select 5,1,46 union all
  35. 35  select 5,3,78 union all
  36. 36  select 5,4,53 union all
  37. 37  select 6,1,35 union all
  38. 38  select 6,2,68 union all
  39. 39  select 6,4,71;3
复制代码
  (11)查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名;
  1. 1  insert into Student select 1,'刘一',18,'男' union all
  2. 2  select 2,'钱二',19,'女' union all
  3. 3  select 3,'张三',17,'男' union all
  4. 4  select 4,'李四',18,'女' union all
  5. 5  select 5,'王五',17,'男' union all
  6. 6  select 6,'赵六',19,'女'
  7. 7  
  8. 8  insert into Teacher select 1,'叶平' union all
  9. 9  select 2,'贺高' union all
  10. 10  select 3,'杨艳' union all
  11. 11  select 4,'周磊';
  12. 12  
  13. 13  insert into Course select 1,'语文',1 union all
  14. 14  select 2,'数学',2 union all
  15. 15  select 3,'英语',3 union all
  16. 16  select 4,'物理',4;
  17. 17  
  18. 18  insert into SC
  19. 19  select 1,1,56 union all
  20. 20  select 1,2,78 union all
  21. 21  select 1,3,67 union all
  22. 22  select 1,4,58 union all
  23. 23  select 2,1,79 union all
  24. 24  select 2,2,81 union all
  25. 25  select 2,3,92 union all
  26. 26  select 2,4,68 union all
  27. 27  select 3,1,91 union all
  28. 28  select 3,2,47 union all
  29. 29  select 3,3,88 union all
  30. 30  select 3,4,56 union all
  31. 31  select 4,2,88 union all
  32. 32  select 4,3,90 union all
  33. 33  select 4,4,93 union all
  34. 34  select 5,1,46 union all
  35. 35  select 5,3,78 union all
  36. 36  select 5,4,53 union all
  37. 37  select 6,1,35 union all
  38. 38  select 6,2,68 union all
  39. 39  select 6,4,71;4
复制代码
  (12)查询至少学过学号为“1”同学所有一门课的其他同学学号和姓名;
  1. 1  insert into Student select 1,'刘一',18,'男' union all
  2. 2  select 2,'钱二',19,'女' union all
  3. 3  select 3,'张三',17,'男' union all
  4. 4  select 4,'李四',18,'女' union all
  5. 5  select 5,'王五',17,'男' union all
  6. 6  select 6,'赵六',19,'女'
  7. 7  
  8. 8  insert into Teacher select 1,'叶平' union all
  9. 9  select 2,'贺高' union all
  10. 10  select 3,'杨艳' union all
  11. 11  select 4,'周磊';
  12. 12  
  13. 13  insert into Course select 1,'语文',1 union all
  14. 14  select 2,'数学',2 union all
  15. 15  select 3,'英语',3 union all
  16. 16  select 4,'物理',4;
  17. 17  
  18. 18  insert into SC
  19. 19  select 1,1,56 union all
  20. 20  select 1,2,78 union all
  21. 21  select 1,3,67 union all
  22. 22  select 1,4,58 union all
  23. 23  select 2,1,79 union all
  24. 24  select 2,2,81 union all
  25. 25  select 2,3,92 union all
  26. 26  select 2,4,68 union all
  27. 27  select 3,1,91 union all
  28. 28  select 3,2,47 union all
  29. 29  select 3,3,88 union all
  30. 30  select 3,4,56 union all
  31. 31  select 4,2,88 union all
  32. 32  select 4,3,90 union all
  33. 33  select 4,4,93 union all
  34. 34  select 5,1,46 union all
  35. 35  select 5,3,78 union all
  36. 36  select 5,4,53 union all
  37. 37  select 6,1,35 union all
  38. 38  select 6,2,68 union all
  39. 39  select 6,4,71;5
复制代码
  (13)把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
  1. 1  insert into Student select 1,'刘一',18,'男' union all
  2. 2  select 2,'钱二',19,'女' union all
  3. 3  select 3,'张三',17,'男' union all
  4. 4  select 4,'李四',18,'女' union all
  5. 5  select 5,'王五',17,'男' union all
  6. 6  select 6,'赵六',19,'女'
  7. 7  
  8. 8  insert into Teacher select 1,'叶平' union all
  9. 9  select 2,'贺高' union all
  10. 10  select 3,'杨艳' union all
  11. 11  select 4,'周磊';
  12. 12  
  13. 13  insert into Course select 1,'语文',1 union all
  14. 14  select 2,'数学',2 union all
  15. 15  select 3,'英语',3 union all
  16. 16  select 4,'物理',4;
  17. 17  
  18. 18  insert into SC
  19. 19  select 1,1,56 union all
  20. 20  select 1,2,78 union all
  21. 21  select 1,3,67 union all
  22. 22  select 1,4,58 union all
  23. 23  select 2,1,79 union all
  24. 24  select 2,2,81 union all
  25. 25  select 2,3,92 union all
  26. 26  select 2,4,68 union all
  27. 27  select 3,1,91 union all
  28. 28  select 3,2,47 union all
  29. 29  select 3,3,88 union all
  30. 30  select 3,4,56 union all
  31. 31  select 4,2,88 union all
  32. 32  select 4,3,90 union all
  33. 33  select 4,4,93 union all
  34. 34  select 5,1,46 union all
  35. 35  select 5,3,78 union all
  36. 36  select 5,4,53 union all
  37. 37  select 6,1,35 union all
  38. 38  select 6,2,68 union all
  39. 39  select 6,4,71;6
复制代码
  (14)查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名;
  1. 1  insert into Student select 1,'刘一',18,'男' union all
  2. 2  select 2,'钱二',19,'女' union all
  3. 3  select 3,'张三',17,'男' union all
  4. 4  select 4,'李四',18,'女' union all
  5. 5  select 5,'王五',17,'男' union all
  6. 6  select 6,'赵六',19,'女'
  7. 7  
  8. 8  insert into Teacher select 1,'叶平' union all
  9. 9  select 2,'贺高' union all
  10. 10  select 3,'杨艳' union all
  11. 11  select 4,'周磊';
  12. 12  
  13. 13  insert into Course select 1,'语文',1 union all
  14. 14  select 2,'数学',2 union all
  15. 15  select 3,'英语',3 union all
  16. 16  select 4,'物理',4;
  17. 17  
  18. 18  insert into SC
  19. 19  select 1,1,56 union all
  20. 20  select 1,2,78 union all
  21. 21  select 1,3,67 union all
  22. 22  select 1,4,58 union all
  23. 23  select 2,1,79 union all
  24. 24  select 2,2,81 union all
  25. 25  select 2,3,92 union all
  26. 26  select 2,4,68 union all
  27. 27  select 3,1,91 union all
  28. 28  select 3,2,47 union all
  29. 29  select 3,3,88 union all
  30. 30  select 3,4,56 union all
  31. 31  select 4,2,88 union all
  32. 32  select 4,3,90 union all
  33. 33  select 4,4,93 union all
  34. 34  select 5,1,46 union all
  35. 35  select 5,3,78 union all
  36. 36  select 5,4,53 union all
  37. 37  select 6,1,35 union all
  38. 38  select 6,2,68 union all
  39. 39  select 6,4,71;7
复制代码
  (15)删除学习“叶平”老师课的SC表记录;
  1. 1  insert into Student select 1,'刘一',18,'男' union all
  2. 2  select 2,'钱二',19,'女' union all
  3. 3  select 3,'张三',17,'男' union all
  4. 4  select 4,'李四',18,'女' union all
  5. 5  select 5,'王五',17,'男' union all
  6. 6  select 6,'赵六',19,'女'
  7. 7  
  8. 8  insert into Teacher select 1,'叶平' union all
  9. 9  select 2,'贺高' union all
  10. 10  select 3,'杨艳' union all
  11. 11  select 4,'周磊';
  12. 12  
  13. 13  insert into Course select 1,'语文',1 union all
  14. 14  select 2,'数学',2 union all
  15. 15  select 3,'英语',3 union all
  16. 16  select 4,'物理',4;
  17. 17  
  18. 18  insert into SC
  19. 19  select 1,1,56 union all
  20. 20  select 1,2,78 union all
  21. 21  select 1,3,67 union all
  22. 22  select 1,4,58 union all
  23. 23  select 2,1,79 union all
  24. 24  select 2,2,81 union all
  25. 25  select 2,3,92 union all
  26. 26  select 2,4,68 union all
  27. 27  select 3,1,91 union all
  28. 28  select 3,2,47 union all
  29. 29  select 3,3,88 union all
  30. 30  select 3,4,56 union all
  31. 31  select 4,2,88 union all
  32. 32  select 4,3,90 union all
  33. 33  select 4,4,93 union all
  34. 34  select 5,1,46 union all
  35. 35  select 5,3,78 union all
  36. 36  select 5,4,53 union all
  37. 37  select 6,1,35 union all
  38. 38  select 6,2,68 union all
  39. 39  select 6,4,71;8
复制代码
  (16)向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“2”课程的同学学号作为学号;②将“2”号课程的平均成绩作为其成绩;
  1. 1  insert into Student select 1,'刘一',18,'男' union all
  2. 2  select 2,'钱二',19,'女' union all
  3. 3  select 3,'张三',17,'男' union all
  4. 4  select 4,'李四',18,'女' union all
  5. 5  select 5,'王五',17,'男' union all
  6. 6  select 6,'赵六',19,'女'
  7. 7  
  8. 8  insert into Teacher select 1,'叶平' union all
  9. 9  select 2,'贺高' union all
  10. 10  select 3,'杨艳' union all
  11. 11  select 4,'周磊';
  12. 12  
  13. 13  insert into Course select 1,'语文',1 union all
  14. 14  select 2,'数学',2 union all
  15. 15  select 3,'英语',3 union all
  16. 16  select 4,'物理',4;
  17. 17  
  18. 18  insert into SC
  19. 19  select 1,1,56 union all
  20. 20  select 1,2,78 union all
  21. 21  select 1,3,67 union all
  22. 22  select 1,4,58 union all
  23. 23  select 2,1,79 union all
  24. 24  select 2,2,81 union all
  25. 25  select 2,3,92 union all
  26. 26  select 2,4,68 union all
  27. 27  select 3,1,91 union all
  28. 28  select 3,2,47 union all
  29. 29  select 3,3,88 union all
  30. 30  select 3,4,56 union all
  31. 31  select 4,2,88 union all
  32. 32  select 4,3,90 union all
  33. 33  select 4,4,93 union all
  34. 34  select 5,1,46 union all
  35. 35  select 5,3,78 union all
  36. 36  select 5,4,53 union all
  37. 37  select 6,1,35 union all
  38. 38  select 6,2,68 union all
  39. 39  select 6,4,71;9
复制代码
  (17)按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分; 【此处已补回15题中被删除的数据】
  1. select a.sno from
  2. (select sno,score from sc where cno=1) a,
  3. (select sno,score from sc where cno=2) b
  4. where a.sno=b.sno and a.score>b.score;
  5. 0
复制代码
  (18)查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
  1. select a.sno from
  2. (select sno,score from sc where cno=1) a,
  3. (select sno,score from sc where cno=2) b
  4. where a.sno=b.sno and a.score>b.score;
  5. 1
复制代码
  (19)按各科平均成绩从低到高和及格率的百分数从高到低顺序;
  1. select a.sno from
  2. (select sno,score from sc where cno=1) a,
  3. (select sno,score from sc where cno=2) b
  4. where a.sno=b.sno and a.score>b.score;
  5. 2
复制代码
  (20)查询如下课程平均成绩和及格率的百分数(备注:需要在1行内显示): 企业管理(2),OO&UML (3),数据库(4)
  1. select a.sno from
  2. (select sno,score from sc where cno=1) a,
  3. (select sno,score from sc where cno=2) b
  4. where a.sno=b.sno and a.score>b.score;
  5. 3
复制代码
  (21)查询不同老师所教不同课程平均分从高到低显示;
  1. select a.sno from
  2. (select sno,score from sc where cno=1) a,
  3. (select sno,score from sc where cno=2) b
  4. where a.sno=b.sno and a.score>b.score;
  5. 4
复制代码
  (22)查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(1),马克思(2),UML (3),数据库(4)
  1. select a.sno from
  2. (select sno,score from sc where cno=1) a,
  3. (select sno,score from sc where cno=2) b
  4. where a.sno=b.sno and a.score>b.score;
  5. 5
复制代码
  
在线编程(http://www.anycodes.cn)&编程论坛(http://www.52exe.cn)感谢您的支持!
回复

使用道具 举报

发布主题 上个主题 下个主题 快速回复 返回列表 官方QQ群
在线客服
客 服 中 心
群 机 器 人
网站二维码
收 起 客 服

QQ|Archiver|手机版|小黑屋|Anycodes ( ICP14002806Anycodes在线编程

GMT+8, 2018-11-15 04:45 , Processed in 1.851307 second(s), 63 queries .

Powered by Anycodes

© 2001-2013 吉林市群龙科技有限公司 Inc.

快速回复 返回顶部 返回列表