SQL语句训练题

刷博主的训练题应对笔试面试

博主博客：http://www.cnblogs.com/edisonchou/p/3878135.html

下面的代码复制到数据库管理工具sqlyog之类就可以用了，都有注释的了

另外：数据库系统原理及应用教程（苗雪兰 刘瑞新）这本大学教材p133页有很多例子，需要的时候，多翻一翻

-- 博客链接：http://www.cnblogs.com/edisonchou/p/3878135.html
-- 备注1：以下数据全部没有分号';'，不可直接复制黏贴，可单个复制
-- 备注2：原博主建表含有字段S#,C#这种带'#'号的，在mysql貌似不能作为字段，我删减了'#'号，下面建表是修改过博主的代码，可以运行
-- 数据库名：sql_demo
-- 创建表 --
CREATE TABLE Student 
  ( 
     S    INT, 
     Sname NVARCHAR(32), 
     Sage  INT, 
     Ssex  NVARCHAR(8) 
  ) 

CREATE TABLE Course 
  ( 
     C    INT, 
     Cname NVARCHAR(32), 
     T    INT 
  ) 

CREATE TABLE Sc 
  ( 
     S    INT, 
     C    INT, 
     score INT 
  ) 

CREATE TABLE Teacher 
  ( 
     T    INT, 
     Tname NVARCHAR(16) 
  )
  
  -- 测试数据 --
  
 INSERT INTO Student SELECT 1,N'刘一',18,N'男' UNION ALL
 SELECT 2,N'钱二',19,N'女' UNION ALL
 SELECT 3,N'张三',17,N'男' UNION ALL
 SELECT 4,N'李四',18,N'女' UNION ALL
 SELECT 5,N'王五',17,N'男' UNION ALL
 SELECT 6,N'赵六',19,N'女' 
 
 INSERT INTO Teacher SELECT 1,N'叶平' UNION ALL
 SELECT 2,N'贺高' UNION ALL
 SELECT 3,N'杨艳' UNION ALL
 SELECT 4,N'周磊'
 
 INSERT INTO Course SELECT 1,N'语文',1 UNION ALL
 SELECT 2,N'数学',2 UNION ALL
 SELECT 3,N'英语',3 UNION ALL
 SELECT 4,N'物理',4
 
 INSERT INTO SC 
 SELECT 1,1,56 UNION ALL 
 SELECT 1,2,78 UNION ALL 
 SELECT 1,3,67 UNION ALL 
 SELECT 1,4,58 UNION ALL 
 SELECT 2,1,79 UNION ALL 
 SELECT 2,2,81 UNION ALL 
 SELECT 2,3,92 UNION ALL 
 SELECT 2,4,68 UNION ALL 
 SELECT 3,1,91 UNION ALL 
 SELECT 3,2,47 UNION ALL 
 SELECT 3,3,88 UNION ALL 
 SELECT 3,4,56 UNION ALL 
 SELECT 4,2,88 UNION ALL 
 SELECT 4,3,90 UNION ALL 
 SELECT 4,4,93 UNION ALL 
 SELECT 5,1,46 UNION ALL 
 SELECT 5,3,78 UNION ALL 
 SELECT 5,4,53 UNION ALL 
 SELECT 6,1,35 UNION ALL 
 SELECT 6,2,68 UNION ALL 
 SELECT 6,4,71
 
 
 -- 开始练习 --
 -- （1）查询“001”课程比“002”课程成绩高的所有学生的学号；
 
 -- 方法1:博主
SELECT s1.S 
FROM 
(SELECT S,score FROM sc WHERE c = '001')s1,
(SELECT S,score FROM sc WHERE c = '002')s2
WHERE  s1.S = s2.S AND s1.score > s2.score
-- 方法2：me
SELECT *
FROM
sc s1,sc s2
WHERE s1.S = s2.S AND s1.C = '001' AND s2.C = '002' AND s1.score > s2.score

-- （2） 查询平均成绩大于60分的同学的学号和平均成绩；

-- 官方
SELECT s,AVG(score) AS AvgScore
FROM 
sc
GROUP BY
S
HAVING
AVG(score) > 60

 -- （3）查询所有同学的学号、姓名、选课数、总成绩；
 -- 笔记：count如果不跟group by一起用，最后只会有一条结果
 -- left outer join 和 inner join 两种用法都是相同结果，但是题目是所有同学，所以下意识肯定是让student全部显示~~
 -- 联合查询的on替换了where
 SELECT s1.S,Sname,SUM(score) AS SumScore,COUNT(C) AS SumSubject
 FROM 
 student s1 LEFT OUTER JOIN sc s2
 ON s1.s = s2.s
 GROUP BY
 s1.s
 ORDER BY s1.s;
 
 -- （4）查询姓“李”的老师的个数
 -- 考点：like ，姓李，必须'李'开头
 SELECT COUNT(DISTINCT Tname) AS COUNT 
 FROM 
 Teacher
 WHERE 
 Tname LIKE '李%'
 
 -- （5）查询没学过“叶平”老师课的同学的学号、姓名；
 -- 考点：no in
 SELECT s.S,s.Sname 
 FROM
 Student s
 WHERE 
 s.S NOT IN
 (
SELECT DISTINCT(S)
FROM 
sc scc,Teacher t,Course c
WHERE
c.T = t.T AND scc.C = c.C AND t.Tname = '叶平'
 )
 
--  （6）查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
-- 难点：并且
-- 错误用法：使用了union且并集
-- mysql没有实现intersect方法--求交集

-- 使用exits

-- 正确方法：博主
SELECT s.S,s.Sname
FROM Student s,SC sc
WHERE s.S=sc.S AND sc.C='001' AND EXISTS
(
    SELECT * FROM SC sc2 WHERE sc.S=sc2.S AND sc2.C='002'
)

-- 错误示范：me
SELECT s.S,s.Sname
FROM
Student s,Sc scc
WHERE 
s.S = scc.S AND scc.C = 1
AND EXISTS
(
SELECT s.S,s.Sname
FROM
Student s,Sc scc
WHERE 
s.S = scc.S AND scc.S = 1 AND scc.C = 2
-- s.S = scc.S AND scc.C = 2
)

-- （7）查询学过“叶平”老师所教的所有课的同学的学号、姓名；

-- 方法1：博主
-- 博主方法统计了叶平老师的课数，跟学生修的课数对比，无论叶平老师有多少课，都成功
SELECT s.S,s.Sname 
FROM Student s
WHERE s.S IN 
(
    SELECT sc.S 
    FROM SC sc,Course c,Teacher t
    WHERE c.C=sc.C AND c.T=t.T AND t.Tname='叶平'
    GROUP BY sc.S
    HAVING COUNT(sc.C)=
    (
        SELECT COUNT(c1.C) 
        FROM Course c1,Teacher t1 
        WHERE c1.T=t1.T AND t1.Tname='叶平'
    )
)


-- 错误方法：me
-- 先查找出叶平老师教过所有课的表，再让学生上过的课跟这个表联合
-- 如果叶平老师有两节课以上，则会把修过一节以上的都包含在内，但题目明显不是这个意思
SELECT s.S,s.Sname
FROM 
Student s,
Sc scc,
(
SELECT t.T,t.Tname,c.C
FROM
Teacher t,Course c
WHERE
t.T = c.T AND t.Tname = '叶平'
)k
WHERE
s.S = scc.S AND scc.C = k.C

-- 未完待续 练习到第8条