sql查询练习

例 34  找出年龄超过平均年龄的学生姓名。
SELECT SNAME
FROM STUDENTS
WHERE AGE ＞
      (SELECT  AVG(AGE)
        FROM   STUDENTS)
例 35  找出各课程的平均成绩，按课程号分组，且只选择学生超过 3 人的课程的成绩。（ GROUP BY 与 HAVING
        GROUP BY 子句把一个表按某一指定列（或一些列）上的值相等的原则分组，然后再对每组数据进行规定的操作。
        GROUP BY 子句总是跟在 WHERE 子句后面，当 WHERE 子句缺省时，它跟在 FROM 子句后面。
        HAVING 子句常用于在计算出聚集之后对行的查询进行控制。）
         SELECT CNO, AVG(GRADE), STUDENTS ＝ COUNT(*)
         FROM ENROLLS
         GROUP BY CNO
         HAVING COUNT(*) >= 3
 
相关子查询
 
例 37  查询没有选任何课程的学生的学号和姓名。（当一个子查询涉及到一个来自外部查询的列时，称为相关子查询（ Correlated Subquery) 。相关子查询要用到存在测试谓词 EXISTS 和 NOT EXISTS ，以及 ALL 、 ANY （ SOME ）等。）
        SELECT SNO, SNAME
        FROM   STUDENTS
        WHERE  NOT EXISTS
              (SELECT *
              FROM ENROLLS
              WHERE ENROLLS.SNO=STUDENTS.SNO)
例 38   查询哪些课程只有男生选读。
        SELECT DISTINCT CNAME
        FROM   COURSES C
        WHERE ' 男 ' ＝ ALL
               (SELECT SEX
                FROM  ENROLLS ， STUDENTS
                WHERE ENROLLS.SNO=STUDENTS.SNO AND
                      ENROLLS.CNO=C.CNO)
例 39  要求给出一张学生、籍贯列表，该表中的学生的籍贯省份，也是其他一些学生的籍贯省份。
        SELECT  SNAME, BPLACE
        FROM   STUDENTS A
        WHERE  EXISTS
               (SELECT *
                 FROM STUDENTS B
                 WHERE A.BPLACE=B.BPLACE AND
                       A.SNO < > B.SNO)
例 40  找出选修了全部课程的学生的姓名。
        本查询可以改为：查询这样一些学生，没有一门课程是他不选修的。
        SELECT  SNAME
        FROM   STUDENTS
        WHERE  NOT EXISTS
               (SELECT *
                FROM COURSES
                WHERE  NOT EXISTS
                        (SELECT *
                         FROM ENROLLS
                         WHERE ENROLLS.SNO ＝ STUDENTS.SNO
                             AND ENROLLS.CNO ＝ COURSES.CNO))
关系代数运算
 
例 41  设有某商场工作人员的两张表：营业员表 SP_SUBORD 和营销经理表 SP_MGR ，其关系数据模式如下：
        SP_SUBORD (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)
        SP_MGR (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)
        其中，属性 SALPERS_ID 为工作人员的编号 , SALPERS_NAME 为工作人员的姓名 , MANAGER_ID 为所在部门经理的编号 , OFFICE 为工作地点。
若查询全部商场工作人员，可以用下面的 SQL 语句：
        (SELECT * FROM SP_SUBORD)
        UNION
        (SELECT * FROM SP_MGR)
        或等价地用下面的 SQL 语句：
        SELECT *
        FROM (TABLE SP_SUBORD UNION TABLE SP_MGR)
（ 2 ） INTERSECT
         (SELECT * FROM SP_SUBORD)
         INTERSECT
         (SELECT * FROM SP_MGR)
        或等价地用下面的 SQL 语句：
        SELECT *
        FROM (TABLE SP_SUBORD INTERSECT TABLE SP_MGR)
        或用带 ALL 的 SQL 语句：
        (SELECT * FROM SP_SUBORD)
        INTERSECT ALL
        (SELECT * FROM SP_MGR)
        或
        SELECT *
        FROM (TABLE SP_SUBORD INTERSECT ALL TABLE SP_MGR)
（ 3 ） EXCEPT
        (SELECT * FROM SP_MGR)
        EXCEPT
        (SELECT * FROM SP_SUBORD)
        或等价地用下面的 SQL 语句：
        SELECT *
        FROM (TABLE SP_MGR EXCEPT TABLE SP_ SUBORD)
        或用带 ALL 的 SQL 语句：
        (SELECT * FROM SP_MGR)
        EXCEPT  ALL
        (SELECT * FROM SP_SUBORD)
例 42  查询籍贯为四川、课程成绩在 80 分以上的学生信息及其成绩。（自然连接）
        (SELECT * FROM STUDENTS
         WHERE BPLACE=‘ 四川 ')
        NATURAL JOIN
        (SELECT * FROM ENROLLS
         WHERE GRADE >=80)
例3.43          列出全部教师的姓名及其任课的课程号、班级。
（外连接与外部并外连接允许在结果表中保留非匹配元组，空缺部分填以 NULL 。外连接的作用是在做连接操作时避免丢失信息。
        外连接有 3 类：
（ 1 ）左外连接（ Left Outer Join ）。连接运算谓词为 LEFT [OUTER] JOIN ，其结果表中保留左关系的所有元组。
（ 2 ）右外连接（ Right Outer Join ）。连接运算谓词为 RIGHT [OUTER] JOIN ，其结果表中保留右关系的所有元组。
（ 3 ）全外连接（ Full Outer Join ）。连接运算谓词为 FULL [OUTER] JOIN ，其结果表中保留左右两关系的所有元组。）
          SELECT TNAME, CNO, CLASS
          FROM TEACHERS LEFT OUTER JOIN TEACHING USING (TNO)
 
SQL 的数据操纵
 
例 44  把教师李映雪的记录加入到教师表 TEACHERS 中。（插入）
        INSERT INTO TEACHERS
        VALUES(1476 ， ' 李映雪 ' ， 44 ， ' 副教授 ')
例 45  成绩优秀的学生将留下当教师。
        INSERT INTO TEACHERS (TNO ， TNAME)
        SELECT DISTINCT SNO ， SNAME
        FROM   STUDENTS ， ENROLLS
        WHERE STUDENTS.SNO ＝ ENROLLS.SNO AND GRADE ＞＝ 90
例 47  把所有学生的年龄增加一岁。（修改）
        UPDATE STUDENTS
        SET AGE ＝ AGE+1
例 48  学生张春明在数据库课考试中作弊，该课成绩应作零分计。
        UPDATE ENROLLS
        SET GRADE ＝ 0
        WHERE CNO ＝ 'C1' AND
             ' 张春明 ' ＝
             (SELECT SNAME
             FROM STUDENTS
             WHERE STUDENTS.SNO=ENROLLS.SNO)
例 49  从教师表中删除年龄已到 60 岁的退休教师的数据。（删除）
        DELETE FROM TEACHERS
        WHERE  AGE ＞＝ 60
 
SQL 的数据控制
 
例 50  授予 LILI 有对表 STUDENTS 的查询权。（表／视图特权的授予
        一个 SQL 特权允许一个被授权者在给定的数据库对象上进行特定的操作。授权操作的数据库对象包括：表 / 视图、列、域等。授权的操作包括： INSERT 、 UPDATE 、 DELETE 、 SELECT 、 REFERENCES 、 TRIGGER 、 UNDER 、 USAGE 、 EXECUTE 等。其中 INSERT 、 UPDATE 、 DELETE 、 SELECT 、 REFERENCES 、 TRIGGER 有对表做相应操作的权限，故称为表特权。）
        GRANT SELECT ON STUDENTS
        TO LILI
        WITH GRANT OPTION
例 51  取消 LILI 的存取 STUDENTS 表的特权。
        REVOKE ALL
        ON STUDENTS
        FROM LILI CASCADE