SQL 语句查询

题目1:
问题描述:
 S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名
 C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
 SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
 1.（1）找出选修过“李明”老师讲授课程的所有学生姓名
 --实现代码:法一
Select *
FROM SC,C,S
Where SC.CNO=C.CNO AND SC.SNO=S.SNO AND CTEACHER='李明'
---法二：-------------
Select *
FROM S
Where Sno IN( Select Sno
              FROM C,SC
              Where C.[Cno]=SC.[Cno] AND CTEACHER='李明')
 
（2）找出没有选修过“李明”老师讲授课程的所有学生姓名
 --实现代码:法一
Select SName
FROM S
Where [Sno] NOT IN( Select SC.[Sno]
                    FROM SC,C
                    Where SC.CNO=C.CNO AND CTEACHER='李明')
 
（3）找出没有选修过课程号为"1"的所有学生姓名
 --实现代码
Select SName
FROM S
Where [Sno] NOT IN( Select [Sno] FROM SC Where CNO='1')
 
------------------------------------------------------------------------------------------------------------------------------------------
 2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
 --实现代码:
Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM S,SC,( Select SNO
                     FROM SC
                     Where SCGRADE<60
                     GROUP BY SNO
                     HAVING COUNT(DISTINCT CNO)>=2 )A
Where S.SNO=A.SNO AND SC.SNO=A.SNO
GROUP BY S.SNO,S.SNAME
 --------------------------------------------------------------------------------------------------------------------------------------
 3. (1)列出既学过“语文”课程，又学过“数学”课程的所有学生姓名
 --实现代码:法一
Select S.SNO,S.SNAME
FROM S,(
   Select SC.SNO
   FROM SC,C
   Where SC.CNO=C.CNO AND C.CNAME IN('语文','数学')
   GROUP BY SNO
   HAVING COUNT(DISTINCT c.CNO)=2 )SC
Where S.SNO=SC.SNO
---法二：--------------------------
select *
from (select s.sno from s,sc,c where s.sno=sc.sno and sc.cno=c.cno and c.cname='语文')t1,
     (select s.sno from s,sc,c where s.sno=sc.sno and sc.cno=c.cno and c.cname='数学')t2,
      s
where t1.sno=t2.sno and t1.sno=s.sno
------------------
(2)使用标准SQL嵌套语句查询选修全部课程的学员姓名
法一：一句SQL语句即可。注意有下划线的部分！
 
Select S.SNO,S.SNAME
FROM S,(
        Select SNO
        FROM SC
        GROUP BY SNO
        HAVING COUNT(*)=(select count(*) from c) )SC
Where S.SNO=SC.SNO
 
(3)查询选修课程超过2门的学员学号
 --实现代码:
                     Select Sno  FROM SC GROUP BY Sno HAVING COUNT(Cno)>2
查询选修课程超过2门的学员学号及姓名
 --实现代码:
Select SNo,SNAME
FROM S
Where Sno IN(Select Sno  FROM SC GROUP BY Sno HAVING COUNT(Cno)>2)
 
（4）列出人数大于3的各科最高成绩的列表，显示科目号、成绩两个字段
---正解如下：(科目号为2、3的选课人数大于3人)
Select cno,max(scgrade)as'最高分'
FROM SC
Where cno IN (Select cno FROM SC GROUP BY cno HAVING COUNT(sno)>3)
group by cno
order by cno
-----------------------------------------------------------------------------------------------------------------------------------
4. 列出“语文”课比“数学”课同学该门课成绩高的所有学生的学号、姓名及分数
 --实现代码:法一 
select *
from (select s.sno,s.sname,sc.SCGRADE from s,sc,c
      where s.sno=sc.sno and sc.cno=c.cno and c.cname='语文' )t1,
       (select s.sno,s.sname,sc.SCGRADE from s,sc,c
      where s.sno=sc.sno and sc.cno=c.cno and c.cname='数学' )t2
where t1.sno=t2.sno and t1.SCGRADE>t2.SCGRADE
------------------------------------------------------------------------------------------------------------------------------------ 
5. 查询选修了课程的学员人数
 --实现代码:
 Select 学员人数=COUNT(DISTINCT [Sno]) FROM SC
 
总结：
1、select * from a,b where a.id=b.id 和
     select * from a inner join b on a.id=b.id 结果集相同；
2、在作“找出没有选修过“李明”老师讲授课程的所有学生姓名”这种题时，
   --用Where [Sno] NOT IN(在此可以是SQL语句生成的一列值)比较好。
   Select SName
   FROM S
   Where [Sno] NOT IN( Select SC.[Sno]
                                   FROM SC,C
                                   Where SC.CNO=C.CNO AND CTEACHER='李明')
3、可以将select count(*) from c直接代入SQL语句，而不是非要写成存储过程；
    Select S.SNO,S.SNAME
    FROM S,(
           Select SC.SNO
           FROM SC,C
           Where SC.CNO=C.CNO
           GROUP BY SNO
           HAVING COUNT(*)=(select count(*) from c) )SC
    Where S.SNO=SC.SNO

 

 

 

 

题名是这样的：
studentname    classname      grade  
 mike           english          65
 mike           math             75
 Jerry          english          80
 Jerry          math             68
 Lida           english          88
 Lida           chinese          77
 Lida           math             90
求出各科成绩都大于75分的学生。
即得到如下结果
studentname    classname      grade
 Lida             english          88
 Lida             chinese          77
 Lida             math             90
 
select t1.studentname,classname,grade
from # t1,(select studentname
           from #
           where grade>75
           group by studentname
           having count(*)=(select count(*) from (select distinct classname from #)A)) t2
where t1.studentname=t2.studentname
 
注：
1、from (select distinct classname from #)A 中的"A"一定要有，将(select distinct classname from #)得到的记录集看成一个新的表A。
2、在SQL语句中，用group by 语句时，一般都会用到count(),sum(),avg(),max(),min()等函数；
但，当不使用group by语句，而直接使用这些函数时，则表示将所有数据集看成一个组来处理。
 
第二题：
已知一个关系数据库的模式如下：
职工EMP（职工号，姓名，工资，所在部门）
部门DEPT（部门号，部门名，部门经理的职工号）
（1）、使用查询分析器建表，要求如下：
　　A、每个表的主外码。
　　B、职工姓名和部门名不能为空。
　　C、工资取800到5000这间整数。
（2）、插入如下数据：
职工（E01 赵 2500    D02      
E02 钱 3000    D02      
E03 孙 6000    D01      
E04 李 5500    D01      
E05 周 3500    D03      
E06 吴 2000    D04      
E07 郑 3000    D04   ）
部门（D01，人事，E04
　　　D02，信息，E01
　　　D03，销售，E05
　　　D04，财务，E06）
（3）、用SQL表达以下的查询：找出那些工资高于其所在部门经理工资的职工的姓名及其工资。
select *
from emp A,(select emp.姓名,emp.工资,dept.*
            from emp,dept
            where emp.职工号=dept.部门经理的职工号)B
where A.所在部门=B.部门号and A.工资>B.工资

（4）、用SQL完成：将D01号部门经理的工资改为该部门所有职工的平均工资。
update emp
set工资=(select avg(工资) from emp where 所在部门='D01')
from emp,dept
where emp.职工号=dept.部门经理的职工号AND所在部门='D01'
（5）、新增加一个广告部门，编号为D05。

insert into dept values('D05','广告','E08')

小结：不要急于答题，先审好题看清表结构，再下笔！

   在（3）题中，职工号（E01..）是主键，是标识字段。要先确定标识字段，再按照标识字段去理思路，用（E01..）将两个表相连即可得到部门表中各经理的姓名、工资等情况数据集B，此数据集的记录数=部门(dept)表的记录数。然后再将数据集B与职工(emp)表相连，即可得到“出那些工资高于其所在部门经理工资的职工的姓名及其工资”。

 

第一题：创建如下三个基本表，表结构如下：
borrower：
借书证号　　姓名　　系名　　 班级
03001        李垒    信息系   03-1     
03002        赵      信息     03-1     
03003        钱      计算机   03-2     
03004        孙      计算机   03-3     
......
loans:
借书证号　　图书登记号　　借书日期
03001        t01           2004     
03001        t02           2004     
03001        t03           2004     
03002        t01           2005     
03002        t02           2005     
03003        t03           2006     
03003        t04           2006     
03004        t05           2007     
.......
BOOKS:
索书号　　书名　　　　作者　　　图书登记号　　出版社　　价格
TP311.13　数据结构　　李卫　　 T01　　　　科学　　　19.00
TP311.13　数据结构　　李卫　　 T02　　　　科学　　　19.00
TP.065　　数据结构导论李卫　   T03　　　　北航　　　16.50
TP.1599　数据通信　　　杨志　　 T04　　　　清华　　　28.50

(1)、检索借了3本书以下的学生的借书证号，姓名，系名和借书数量。
---正解如下------------
select *
from borrower B,(select count(*) as 借书数量,借书证号 as 借书证号
                    from loans
                    group by 借书证号
                    having count(*)<3) A
where B.借书证号=A.借书证号

(2)、检索借书和赵垒同学所借书中的任意一本相同的学生的姓名，系名，书名，借书日期。
---正解如下------------
select姓名,系名,图书登记号,借书日期
from borrower bb,loans ll
where bb.借书证号=ll.借书证号 and 姓名<>'李垒'
and图书登记号 in (select 图书登记号
                    from borrower b,loans l
                    where b.借书证号=l.借书证号 and b.姓名='李垒')

(3)、建立信管系学生借书的视图SB，该视图的属性列由借书证号，姓名，班级，图书登记号，书号，出版社和借书日期组成
 
第二题：
现有一个学生选修课程的数据库，其中存放以下三个表：
学生（学号，姓名，性别，年龄，系别）
课程（课程号，课程名，任课教师）；
选修（学号，课程号，分数）；
请用SQL完成以下功能：
（1）、建表，在定义中要求声明：
　A、每个表的主外码。
　B、学生的年龄介于16到30。
　C、学生的姓名和课程名不能为空。
　D、选课成绩要么为空值，要么取0到100的整数。
（2）、插入如下数据：
　学生（101，张三，男，16，数学
　      102，李四，男，18，计算机
　      103，王玲，女，17，中文
　      105，李飞，男，19，计算机
　      109，赵四，女，18，历史
　      110，李平，男，20，化学）
课程（203，操作系统，程羽
　　　279，高等数学，王备
　　　210，现代文学，王林
　　　243，有机化学，沈同
　　　204，数据结构，张青)
选修（101,203,82
　　　105,203,59
　　　102,279,90
　　　101,279,88
　　　105,279,82
　　　110,279,68
　　　109,210,72
　　　103,210,90
　　　110,243,92
　　　101,204,85
　　　105,204,91
　　　102,204,56
101,210,77
101,243,88）
（3）、用SQL语然完成下列查询：
1、列出张三同学选修的所有课程的名称和成绩
------正解如下----------------
select课程名,分数
from学生,选修,课程
where学生.学号=选修.学号and选修.课程号=课程.课程号and姓名='张三'

2、列出所有课程都及格了的同学的名字
------正解如下----------------
select t1.姓名
from学生t1,(select 学号
           from 选修
           where 分数>=60
           group by 学号
           having count(*)=(select count(*) from (select distinct 课程号from课程)A)) t2
where t1.学号=t2.学号

3、列出在选修张青老师所教授课程的学生中，成绩最高的学生姓名和成绩
------正解如下----------------
select top 1 姓名,分数
from学生,选修
where学生.学号=选修.学号 and 学生.学号 in (select 学号 from 课程,选修 where 选修.课程号=课程.课程号 and 任课教师='张青')
order by 分数 desc

（4）、删除所有成绩不及格的选课记录
delete from 选修where分数<60

（5）、将105号同学选修203号课程的成绩改为该门课程的平均成绩
  
update选修
set分数=(select avg(分数) from 选修where课程号='203')
from选修
where学号='105' and 课程号='203'