经典ＳＱＬ

USE master 
GO 
IF EXISTS(SELECT * FROM sysdatabases WHERE name='DB_StudentInfoManagement')
DROP DATABASE DB_StudentInfoManagement
GO
CREATE DATABASE DB_StudentInfoManagement
GO 
USE DB_StudentInfoManagement
GO 
IF EXISTS(SELECT * FROM sysobjects WHERE name='Student')
DROP TABLE Student
GO 
CREATE TABLE Student
(
S# INT  PRIMARY KEY ,--学号
Sname NVARCHAR(100) NOT NULL,--姓名
Sage INT NOT NULL,--年龄
Ssex  NVARCHAR(10) NOT NULL--性别

)
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name='Teacher')
DROP TABLE Teacher
GO
CREATE TABLE Teacher
(
 T# INT PRIMARY KEY ,--教师编号
 Tname NVARCHAR(50) NOT NULL,--教师名
  
)
GO 

IF EXISTS(SELECT * FROM sysobjects WHERE name='Course')
DROP TABLE Course
GO
CREATE TABLE Course
(
 C# INT PRIMARY KEY ,--课程号
 Cname NVARCHAR(50) NOT NULL,--课程名
 T# INT NOT NULL REFERENCES Teacher(T#)--教师编号
)
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name='SC')
DROP TABLE SC
GO
CREATE TABLE SC
(
 C# INT  NOT NULL REFERENCES dbo.Course(C#),--课程号
 S# INT NOT NULL REFERENCES dbo.Student(S#),--学号
 Score NVARCHAR(50) NOT NULL,--分数
 CONSTRAINT PK_SC PRIMARY  KEY(C#,S#)
)

 

--插入测试数据

INSERT  INTO dbo.Student
        SELECT  '1' ,
                N'刘一' ,
                '18' ,
                N'男'
        UNION ALL  --不会去除重复数据，Union会自动去除重复数据
        SELECT  '2' ,
                N'钱二' ,
                '19' ,
                '女'
        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”课程成绩高的所有学生的学号； 

--先查出001号，002号课程的学号和成绩
--在使用子查询连表查询 

SELECT  a.S#
FROM    ( SELECT    S# ,
                    Score
          FROM      dbo.SC
          WHERE     C# = '001'
        ) AS a , --001号课程的学号和成绩
        ( SELECT    S# ,
                    Score
          FROM      dbo.SC
          WHERE     C# = '002'
        ) AS b ----002号课程的学号和成绩
WHERE   a.S# = b.S#
        AND a.Score > b.Score; 
       

验证：

SELECT S# ,
Score
FROM dbo.SC
WHERE C# = '001'

SELECT S# ,
Score
FROM dbo.SC
WHERE C# = '002'

 

 

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

--Having子句中，不能使用聚合函数的别名【错误答案：列名 'AvgScore' 无效。】
SELECT S#,AVG(Score)AS AvgScore FROM dbo.SC
GROUP BY S# HAVING AvgScore>60   

--正确答案：
SELECT S#,AVG(Score)AS AvgScore FROM dbo.SC
GROUP BY S# HAVING AVG(Score)>60  

 

 

--3、查询所有同学的学号、姓名、选课数、总成绩；

--这里要注意的是，并不是所有学生都选课了，所以要用到外连接，
--因为是要查询所有学生的学号，姓名，，也就是不管该学生，选没选课，都要查出来，所以左表是Student，用左连接

SELECT  S.S# ,
        S.Sname ,
        COUNT(SC.C#) AS SelectCourseCount ,
        SUM(SC.Score) AS TotalScore
FROM    dbo.Student AS S
        LEFT JOIN dbo.SC AS SC ON SC.S# = S.S#
        GROUP BY S.S#,S.Sname;

 

--4、查询姓“李”的老师的个数；

--分析，用到模糊查询,只要姓李就行，后面几个字的名，不管
--'%代表任意字符的字符串，_代表单个字符'
--同名的也算，这是明显的。网上的答案是错误的。

--自己的答案
SELECT COUNT(T#)  FROM dbo.Teacher  WHERE Tname LIKE '李%';
 
 --网上的答案：同名的也算，网上的答案是错误的了
 SELECT COUNT(DISTINCT ( Tname ))
 FROM   Teacher
 WHERE  Tname LIKE '李%'; 

 

验证：

--测试
--SELECT * FROM dbo.Teacher WHERE Tname LIKE '李%';

 

--5、查询没学过“叶平”老师课的同学的学号、姓名； 

--分析：先查出学过叶平老师课的学生学号，再求出Student表中学号不在这其中的就是了

SELECT S#,Sname FROM dbo.Student WHERE S# NOT IN 
(
--学过叶平老师课的学生学号
SELECT DISTINCT( SC.S#) FROM dbo.Course AS C 
INNER JOIN dbo.Teacher AS T ON T.T# = C.T#
INNER  JOIN dbo.SC AS SC   ON SC.C# = C.C#
WHERE T.Tname='叶平'
)

 

 

 

--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； 

--查出学过001号课程的                                                

SELECT  s.S# ,
        Sname
FROM    dbo.Student AS s
        RIGHT JOIN dbo.SC ON SC.S# = s.S#
WHERE   C# = '001'
        AND EXISTS ( SELECT *
                     FROM   SC AS SC2
                          LEFT JOIN dbo.Student AS s ON s.S# = SC2.S#
                     WHERE  SC2.C# = '002' );

 

 未完待整理...........