【Transact-SQL】教材3.4“数据查询”的部分例题（SELECT）

• 莫偷懒，切勿“复制-粘贴”，亲自敲一遍代码效果好。
• 自己输入后，如果运行不正确，可以参考一下这里的代码。

--[例3.16]  查询全体学生的学号与姓名。
SELECT Sno,Sname
FROM Student;
--[例3.17]  查询全体学生的姓名、学号、所在系。
SELECT Sname,Sno,Sdept
FROM Student;

--[例3.18]  查询全体学生的详细记录
SELECT  Sno,Sname,Ssex,Sage,Sdept 
FROM Student; 

--或

SELECT  *
FROM Student; 

--[例3.19]  查全体学生的姓名及其出生年份。
SELECT Sname,2016-Sage          /*假设当时为2016年*/
FROM Student;

--[例3.20] 查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。
SELECT Sname,'Year of Birth: ',2014-Sage,LOWER(Sdept)
FROM Student;

--[例3.21]  查询选修了课程的学生学号。
SELECT Sno   FROM SC;
--等价于：
SELECT ALL  Sno  FROM SC;

--[例3.22] 查询计算机科学系全体学生的名单。
SELECT Sname
FROM     Student
WHERE  Sdept='CS'; 

--[例3.23]查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname,Sage 
FROM     Student    
WHERE  Sage < 20;

--[例3.24]查询考试成绩有不及格的学生的学号。
SELECT DISTINCT Sno
FROM  SC
WHERE Grade<60; 

--[例3.25] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM     Student
WHERE   Sage BETWEEN 20 AND 23; 

--[例3.26]  查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname, Sdept, Sage
FROM    Student
WHERE Sage NOT BETWEEN 20 AND 23; 

--[例3.27]查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。
SELECT Sname, Ssex
FROM  Student
WHERE Sdept IN ('CS','MA','IS');

--[例3.28]查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept NOT IN ('IS','MA','CS');

 

--[例3.29]  查询学号为201215121的学生的详细情况。
      SELECT *    
     FROM  Student  
     WHERE  Sno LIKE '201215121';

--等价于： 
       SELECT  * 
      FROM  Student 
      WHERE Sno = ' 201215121 ';

--[例3.30]  查询所有姓刘学生的姓名、学号和性别。
       SELECT Sname, Sno, Ssex
      FROM Student
      WHERE  Sname LIKE '刘%';

--[例3.31]  查询姓"欧阳"且全名为三个汉字的学生的姓名。
       SELECT Sname
      FROM   Student
      WHERE  Sname LIKE '欧阳_ _';
--【注】数据库字符集为ASCII时，一个汉字需要两个_
--      数据库字符集为 GBK 时，一个汉字需要一个_

--[例3.32]  查询名字中第2个字为"阳"字的学生的姓名和学号。
      SELECT Sname,Sno
      FROM     Student
      WHERE  Sname LIKE '_ _阳%';

--[例3.33]  查询所有不姓刘的学生姓名、学号和性别。
      SELECT Sname, Sno, Ssex
      FROM     Student
      WHERE  Sname NOT LIKE '刘%';
	 

 

 --[例3.34]  查询DB_Design课程的课程号和学分。
      SELECT Cno,Ccredit
      FROM     Course
      WHERE  Cname LIKE 'DB\_Design' ESCAPE '\' ;

--[例3.35]  查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。
      SELECT  *
      FROM    Course
      WHERE  Cname LIKE  'DB\_%i__' ESCAPE '\' ;

--ESCAPE '＼' 表示“ ＼” 为换码字符

--[例3.36]  某些学生选修课程后没有参加考试，所以有选课记录，但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
	  SELECT Sno,Cno
      FROM    SC
      WHERE  Grade IS NULL
--[例3.37]  查所有有成绩的学生学号和课程号。
      SELECT Sno,Cno
      FROM     SC
      WHERE  Grade IS NOT NULL;
--[例3.38]  查询计算机系年龄在20岁以下的学生姓名。
      SELECT Sname
       FROM  Student
       WHERE Sdept= 'CS' AND Sage<20;

--改写[例3.27]
--[例3.27]  查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。
SELECT Sname, Ssex
FROM     Student
WHERE  Sdept IN ('CS ','MA ','IS');
--可改写为：
SELECT Sname, Ssex
FROM     Student
WHERE  Sdept= ' CS' OR Sdept= ' MA' OR Sdept= 'IS ';

--[例3.39]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
        SELECT Sno, Grade
        FROM    SC
        WHERE  Cno= '3'
        ORDER BY Grade DESC;

【例3.40】

SELECT  *
FROM  Student
ORDER BY Sdept, Sage DESC;  

SELECT  *
FROM  Student

--[例3.41]  查询学生总人数。
SELECT COUNT(*)
FROM  Student; 
--[例3.42]  查询选修了课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC;
--[例3.43]  计算1号课程的学生平均成绩。
SELECT AVG(Grade)
FROM    SC
WHERE Cno= '1';
--[例3.44]  查询选修1号课程的学生最高分数。
SELECT MAX(Grade)
FROM SC
WHERE Cno='1';
--[例3.45] 查询学生201215012选修课程的总学分数。
SELECT SUM(Ccredit)
FROM  SC,Course
WHERE Sno='201215012' AND SC.Cno=Course.Cno; 

【例3.46】

SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno; 

SELECT * FROM SC

【例3.47】

SELECT Sno
FROM  SC
GROUP BY Sno
HAVING  COUNT(*) >2;

SELECT * FROM SC

【例3.48】

SELECT  Sno, AVG(Grade)
FROM  SC
GROUP BY Sno
HAVING AVG(Grade)>=86;

SELECT * FROM SC

--错误的使用：

--SELECT Sno, AVG(Grade)
--FROM  SC
--WHERE AVG(Grade)>=90
--GROUP BY Sno;

【例3.49】

--等值连接
SELECT  Student.*, SC.*
FROM     Student, SC
WHERE  Student.Sno = SC.Sno;


--自然连接
SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM     Student,SC
WHERE  Student.Sno = SC.Sno;


--等值连接
SELECT  *
FROM     Student, SC
WHERE  Student.Sno = SC.Sno;

--[例 3.50]  对[例 3.49]用自然连接完成。
 SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
 FROM     Student,SC
 WHERE  Student.Sno = SC.Sno;

【例3.51】

SELECT Student.Sno,Sname
FROM Student, SC
WHERE  Student.Sno=SC.Sno  AND  SC.Cno='2' AND SC.Grade>86;

【例3.52】

SELECT  FIRST_TABLE.Cno, SECOND_TABLE.Cpno
FROM  Course  FIRST_TABLE, Course  SECOND_TABLE
WHERE FIRST_TABLE.Cpno = SECOND_TABLE.Cno;

【例3.53】

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM  Student  LEFT OUTER JOIN SC ON  (Student.Sno=SC.Sno);

【例3.54】

SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course    --多表连接
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

SELECT *
FROM Student, SC, Course    --多表连接
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

【例3.54-3.55】

--嵌套查询
--一个SELECT-FROM-WHERE语句称为一个查询块
--将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

SELECT *	                           /*外层查询/父查询*/
FROM Student
WHERE Sno IN
	(SELECT Sno        /*内层查询/子查询*/
	FROM SC
	WHERE Cno= '2');


--不相关子查询
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept  IN
	(SELECT Sdept
	FROM Student
	WHERE Sname= '刘晨');


--相关子查询
SELECT Sno, Cno
FROM    SC  x
WHERE Grade >=(	SELECT AVG(Grade) 
				FROM  SC y
				WHERE y.Sno=x.Sno);

【例3.55】

--不相关子查询
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept  IN
	(SELECT Sdept
	FROM Student
	WHERE Sname= '刘晨');

--连接查询（自身连接）
SELECT S1.Sno, S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept = S2.Sdept AND S2.Sname = '刘晨';

--可以用带EXISTS谓词的子查询替换：
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
      (SELECT *
        FROM Student S2
         WHERE S2.Sdept = S1.Sdept AND S2.Sname = '刘晨');

【例3.56】

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= '信息系统'                      
		                  )
              );

SELECT Student.Sno,Sname
FROM    Student,SC,Course
WHERE Student.Sno = SC.Sno  AND
	 SC.Cno = Course.Cno AND
	 Course.Cname='信息系统';

【例3.57】

SELECT Sno, Cno
FROM    SC  x
WHERE Grade >=(	SELECT AVG(Grade) 
		FROM  SC y
		WHERE y.Sno=x.Sno);

【例3.57+】

SELECT Sno, Cno
FROM SC, (SELECT  Sno, Avg(Grade) 
		FROM   SC 
		GROUP BY   Sno)
		AS   Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno and SC.Grade >=Avg_sc.avg_grade


SELECT Sname
FROM Student,  
    (SELECT Sno FROM SC WHERE Cno='1') AS SC1
WHERE  Student.Sno=SC1.Sno;

【例3.58】

SELECT Sname,Sage
FROM    Student
WHERE Sage < ANY (	SELECT  Sage
			FROM    Student
			WHERE Sdept= 'CS')
     AND Sdept <> 'CS';           /*父查询块中的条件 */

SELECT Sname,Sage
FROM   Student
WHERE Sage < 
		(SELECT MAX(Sage)
		 FROM Student
		 WHERE Sdept= 'CS ')
       AND Sdept <> 'CS';

SELECT * FROM Student

【例3.59】

SELECT Sname,Sage
FROM    Student
WHERE Sage < ALL (	SELECT  Sage
			FROM    Student
			WHERE Sdept= 'CS')
     AND Sdept <> 'CS';           /*父查询块中的条件*/

SELECT Sname,Sage
FROM   Student
WHERE Sage < 
		(SELECT MIN(Sage)
		 FROM Student
		 WHERE Sdept= 'CS ')
       AND Sdept <> 'CS';

SELECT * FROM Student

【例3.60】

SELECT Sname
FROM Student
WHERE EXISTS
	(SELECT *
	FROM SC
	WHERE Sno=Student.Sno AND Cno= '1');

【例3.61】

SELECT Sname
FROM     Student
WHERE NOT EXISTS
	(SELECT *
	FROM SC
	WHERE Sno = Student.Sno AND Cno='1');

【例3.62】

SELECT Sname
FROM Student
WHERE NOT EXISTS
	(SELECT *
	FROM Course
	WHERE NOT EXISTS
		(SELECT *
		FROM SC
		WHERE Sno= Student.Sno
		AND Cno= Course.Cno
		)
	);

【例3.63】

SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
                     (SELECT *
                      FROM SC SCY
                      WHERE SCY.Sno = '201215122'  AND
                                    NOT EXISTS
                                    (SELECT *
                                     FROM SC SCZ
                                     WHERE SCZ.Sno=SCX.Sno AND
                                                   SCZ.Cno=SCY.Cno));

【例3.64】

SELECT *
FROM Student
WHERE Sdept= 'CS'

UNION
        
SELECT *
FROM Student
WHERE Sage<=19;