--（一）创建教材学生-课程数据库
create database s_c
go
use s_c
go
--建立“学生”表Student,学号是主码,姓名取值唯一。
     
CREATE TABLE Student          
   (Sno   CHAR(9) PRIMARY KEY, /* 列级完整性约束条件*/                  
            Sname  CHAR(20) UNIQUE,     /* Sname取唯一值*/
            Ssex    CHAR(2),
            Sage   SMALLINT,
            Sdept  CHAR(20)
           )
go
--建立一个“课程”表Course
      CREATE TABLE  Course
               ( Cno       CHAR(4) PRIMARY KEY,
                 Cname  CHAR(40),            
                 Cpno     CHAR(4) ,                                    
                 Ccredit  SMALLINT,
                FOREIGN KEY (Cpno) REFERENCES  Course(Cno)
            )
go
--建立一个“学生选课”表SC
 CREATE TABLE  SC
        (Sno  CHAR(9),
        Cno  CHAR(4),  
        Grade    SMALLINT,
        PRIMARY KEY (Sno,Cno),  
                     /* 主码由两个属性构成,必须作为表级完整性进行定义*/
        FOREIGN KEY (Sno) REFERENCES Student(Sno),
                    /* 表级完整性约束条件,Sno是外码,被参照表是Student */
        FOREIGN KEY (Cno) REFERENCES Course(Cno)
                   /* 表级完整性约束条件, Cno是外码,被参照表是Course*/
  )
--（二）装载数据
--1、往学生表（student）插入数据
insert into student
values('200215121','李勇','男',20,'CS')
GO
insert into student
values('200215122','刘晨','女',19,'CS')
GO
insert into student
values('200215123','王敏','女',18,'MA')
GO
insert into student
values('200215125','张立','男',19,'IS')

go
--2、往course表插入数据
insert into course
values('1','数据库',null,4)
go
insert into course
values('2','数学',null,2)
go
insert into course
values('3','信息系统',null,4)
go
insert into course
values('4','操作系统',null,3)
go
insert into course
values('5','数据结构',null,4)
go
insert into course
values('6','数据处理',null,2)
go
insert into course
values('7','PASCAL语言',null,4)
go
update course
set cpno='5'
where cno='1'
go
update course
set cpno='1'
where cno='3'
go
update course
set cpno='6'
where cno='4'
go
update course
set cpno='7'
where cno='5'
go
update course
set cpno='6'
where cno='7'
go
select * from course
go
--3、往sc表插入数据
insert into sc
values('200215121','1',92)
go
insert into sc
values('200215121','2',85)
go
insert into sc
values('200215121','3',88)
go
insert into sc
values('200215122','2',90)
go
insert into sc
values('200215122','3',80)
go
select * from sc
go
--（三）数据查询

--查询指定列
--[例1]  查询全体学生的学号与姓名。
SELECT Sno,Sname
FROM Student
GO
--[例2]  查询全体学生的姓名、学号、所在系。
SELECT Sname,Sno,Sdept
FROM Student
go
--[例3]  查询全体学生的详细记录。
SELECT  Sno,Sname,Ssex,Sage,Sdept
FROM Student
--或
go
SELECT  *
FROM Student
go
--[例4]  查全体学生的姓名及其出生年份。
SELECT Sname,2013-Sage    /*假定当年的年份为2011年*/
FROM Student
go
--[例5]  查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名

SELECT Sname,'Year of Birth: ' as birth,2013-Sage birth_year,
                 LOWER(Sdept) sdept
FROM Student
go
--使用列别名改变查询结果的列标题:
 
SELECT Sname NAME,'Year of Birth:'  BIRTH,
       2000-Sage  BIRTHDAY,LOWER(Sdept)  DEPARTMENT
FROM Student
go
--指定DISTINCT关键词,去掉表中重复的行    
SELECT  DISTINCT Sno
FROM SC
go
--［例7］  查询计算机科学系全体学生的名单。
SELECT Sname
FROM Student
WHERE Sdept='CS'  
go
--[例8]  查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname,Sage
FROM    Student    
WHERE Sage < 20
go
--［例9］  查询考试成绩有不及格的学生的学号。
SELECT DISTINCT Sno
FROM  SC
WHERE Grade<60
go
--[例10] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的             姓名、系别和年龄
      SELECT Sname,Sdept,Sage
FROM     Student
WHERE   Sage BETWEEN 20 AND 23
go
--[例11]  查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM    Student
WHERE Sage NOT BETWEEN 20 AND 23
go
--[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。
SELECT Sname,Ssex
FROM  Student
WHERE Sdept IN ( 'IS','MA','CS' );
go
--[例13]查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。
SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN ( 'IS','MA','CS' )
go
--[例14]  查询学号为200215121的学生的详细情况。
SELECT *    
FROM  Student  
WHERE  Sno LIKE '200215121'
go
--[例15]  查询所有姓刘学生的姓名、学号和性别。
      SELECT Sname,Sno,Ssex
      FROM Student
      WHERE  Sname LIKE '李%'
go
--[例16]  查询姓"欧阳"且全名为三个汉字的学生的姓名。
      SELECT Sname
      FROM   Student
      WHERE  Sname LIKE '欧阳__'
go
--[例17]  查询名字中第2个字为"阳"字的学生的姓名和学号。
      SELECT Sname,Sno
      FROM Student
      WHERE Sname LIKE '__阳%'
go
--[例18]  查询所有不姓刘的学生姓名。
      SELECT Sname,Sno,Ssex
      FROM Student
      WHERE Sname NOT LIKE '刘%'
go
--[例19]  查询DB_Design课程的课程号和学分。
      SELECT Cno,Ccredit
      FROM Course
      WHERE Cname LIKE 'DB\_Design' ESCAPE '\'
go
--[例20]  查询以"DB_"开头,且倒数第3个字符为 i的课程的详细情况。
      SELECT  *
      FROM   Course
      WHERE  Cname LIKE  'DB\_%i_ _' ESCAPE '\'
go
--[例21]  某些学生选修课程后没有参加考试,所以有选课记录,但没
--有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
      SELECT Sno,Cno
      FROM  SC
      WHERE  Grade IS NULL
go
--[例22]  查所有有成绩的学生学号和课程号。
      SELECT Sno,Cno
      FROM  SC
      WHERE  Grade IS NOT NULL
go
--[例23]  查询计算机系年龄在20岁以下的学生姓名。
     SELECT Sname
       FROM  Student
       WHERE Sdept= 'CS' AND Sage<20
go
--[例24]  查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
        SELECT Sno,Grade
        FROM  SC
        WHERE  Cno= ' 3 '
        ORDER BY Grade DESC
go
--［例25］  查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
        SELECT  *
        FROM  Student
        ORDER BY Sdept desc,Sage DESC
go
--[例26]  查询学生总人数。
SELECT COUNT( * )
FROM Student
go
--[例27]  查询选修了课程的学生人数。
     SELECT COUNT(DISTINCT Sno)
     FROM SC
go
--[例28]  计算1号课程的学生平均成绩。
          SELECT AVG(Grade)
          FROM SC
          WHERE Cno='1'
go
--[例29]  查询选修1号课程的学生最高分数。
   SELECT MAX(Grade)
   FROM SC
   WHERE Cno='1'
go
--［例30］查询学生200215121选修课程的总学分数。
      SELECT SUM(Ccredit)
             FROM  SC, Course
             WHERE Sno='200215121' AND SC.Cno=Course.Cno
go
--[例31]  求各个课程号及相应的选课人数
SELECT Cno,COUNT(Sno)
     FROM    SC
     GROUP BY Cno
go
--[例32]  查询选修了3门以上课程的学生学号。
     SELECT Sno
     FROM  SC
     GROUP BY Sno
     HAVING  COUNT(*) >3
go
--[例33]  查询每个学生及其选修课程的情况
SELECT  Student.*,SC.*
FROM     Student,SC
WHERE  Student.Sno = SC.Sno
go
--[例34]     对[例33]用自然连接完成。
 SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
 FROM     Student,SC
 WHERE  Student.Sno = SC.Sno
go
--[例35]查询每一门课的间接先修课（即先修课的先修课）
    SELECT  FIRST.Cno,SECOND.Cpno
     FROM  Course  FIRST,Course  SECOND
     WHERE FIRST.Cpno = SECOND.Cno
go
--外连接
--标准SQL
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM  Student  LEFT OUT JOIN SC ON (Student.Sno=SC.Sno)
GO
--SQL SERVER 2000中的SQL
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM  Student  LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)
GO
--[例37]查询选修2号课程且成绩在90分以上的所有学生
  SELECT Student.Sno, Sname
  FROM    Student, SC
  WHERE Student.Sno = SC.Sno AND  
                                            /* 连接谓词*/
                         SC.Cno= '2' AND SC.Grade > 90      
     /* 其他限定条件 */
GO
--[例38]查询每个学生的学号、姓名、选修的课程名及成绩
  SELECT Student.Sno,Sname,Cname,Grade
   FROM    Student,SC,Course    /*多表连接*/
   WHERE Student.Sno = SC.Sno
                   and SC.Cno = Course.Cno
GO
--[例39]  查询与“刘晨”在同一个系学习的学生。
SELECT Sno,Sname,Sdept
     FROM Student
    WHERE Sdept  IN
                  (SELECT Sdept
                   FROM Student
                   WHERE Sname='刘晨')
GO
--[例40]查询选修了课程名为“信息系统”的学生学号和姓名
  SELECT Sno,Sname    --③ 最后在Student关系中取出Sno和Sname
   FROM    Student                                    
  WHERE Sno  IN
             (SELECT Sno     --② 然后在SC关系中找出选修了3号课程的学生学号
              FROM    SC                                    
              WHERE  Cno IN
                     (SELECT Cno  --① 首先在Course关系中找出“信息系统”的课程号,为3号
                       FROM Course                      
                       WHERE Cname= '信息系统'
                     )
              )
GO
--［例41］找出每个学生超过他选修课程平均成绩的课程号。
   SELECT Sno, Cno
    FROM  SC  x
    WHERE Grade >=(SELECT AVG(Grade)
                          FROM  SC y
                                   WHERE y.Sno=x.Sno)
GO
--[例42]  查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄
    SELECT Sname,Sage
    FROM    Student
    WHERE Sage < ANY (SELECT  Sage
                                         FROM    Student
                                         WHERE Sdept= 'CS')
           AND Sdept <> 'CS'
GO
--[例43]  查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

--方法一：用ALL谓词
    SELECT Sname,Sage
    FROM Student
    WHERE Sage < ALL
                           (SELECT Sage
                            FROM Student
                            WHERE Sdept= ' CS ')
           AND Sdept <> ' CS '
GO
--[例44]查询所有选修了1号课程的学生姓名。
--1、用嵌套查询
SELECT Sname
     FROM Student
     WHERE EXISTS
                   (SELECT *
                    FROM SC
                    WHERE Sno=Student.Sno AND Cno= ' 1 ')
go
--2、用连接运算
 SELECT Sname
 FROM Student, SC
 WHERE Student.Sno=SC.Sno AND SC.Cno= '1'
GO
--[例45]  查询没有选修1号课程的学生姓名。
     SELECT Sname
     FROM Student
     WHERE NOT EXISTS(SELECT *
                    FROM SC
                    WHERE Sno = Student.Sno AND Cno='1')
go
--[例39]查询与“刘晨”在同一个系学习的学生。
--可以用带EXISTS谓词的子查询替换：
     SELECT Sno,Sname,Sdept
     FROM Student S1
      WHERE EXISTS(SELECT *
                     FROM Student S2
                     WHERE S2.Sdept = S1.Sdept AND
                                   S2.Sname ='刘晨')
go
--[例46] 查询选修了全部课程的学生姓名。
        SELECT Sname
        FROM Student
        WHERE NOT EXISTS
                    (SELECT *
                        FROM Course
                        WHERE NOT EXISTS
                                     (SELECT *
                                       FROM SC
                                       WHERE Sno= Student.Sno
                                             AND Cno= Course.Cno
                                       )
)
go
--[例47]查询至少选修了学生200215122选修的全部课程的学生号码。
--用NOT EXISTS谓词表示：    
     SELECT DISTINCT Sno
       FROM SC SCX
       WHERE NOT EXISTS
                     (SELECT *
                      FROM SC SCY
                      WHERE SCY.Sno =' 200215122'  AND
                                    NOT EXISTS
                                    (SELECT *
                                     FROM SC SCZ
                                     WHERE SCZ.Sno=SCX.Sno AND
                                                   SCZ.Cno=SCY.Cno))
go
--[例48]  查询计算机科学系的学生及年龄不大于19岁的学生。
--方法一：
        SELECT *
        FROM Student
        WHERE Sdept= 'CS'
        UNION
        SELECT *
        FROM Student
        WHERE Sage<=19
go
--方法二：
       SELECT  DISTINCT  *
        FROM Student
        WHERE Sdept= 'CS' &nbs***bsp;Sage<=19
go
--[例49]  查询选修了课程1或者选修了课程2的学生。

        SELECT Sno
        FROM SC
        WHERE Cno=' 1 '
        UNION
        SELECT Sno
        FROM SC
        WHERE Cno= ' 2 '
go
--[例50]  查询计算机科学系的学生与年龄不大于19岁的学生的交集

SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19
go
--在SQL SERVER2000用EXISTS和NOT EXISTS查找交集与差集
go
SELECT *
FROM Student s1
WHERE s1.Sdept='CS' and exists(SELECT *
FROM Student s2
WHERE s2.Sage<=19 and s1.sno=s2.sno)
--[例51]  查询选修课程1的学生集合与选修课程2的学生集合的交集
     SELECT Sno
    FROM SC
    WHERE Cno='1'
    INTERSECT
    SELECT Sno
    FROM SC
    WHERE Cno='2'
--SQL SERVER2000中只有INTERSECT函数
go
SELECT sc1.Sno
    FROM SC sc1
    WHERE sc1.Cno='1' and exists(SELECT Sno
    FROM SC sc2
    WHERE sc2.Cno='2' and sc1.sno=sc2.sno)
 

go
--[例52]  查询计算机科学系的学生与年龄不大于19岁的学生的差集。

    SELECT *
    FROM Student
    WHERE Sdept='CS'
    EXCEPT
    SELECT  *
    FROM Student
    WHERE Sage <=19
go
SELECT *
    FROM Student s1
    WHERE  s1.Sdept='CS' and not exists(SELECT  *
    FROM Student s2
    WHERE s2.Sage <=19 and s1.sno=s2.sno)