SQL语句练习(Student,Course,SC表)

• Create table Student 主码，姓名（唯一），性别（男、女），年龄（18—25）

  CREATE TABLE Student          
  	(Sno CHAR(9) PRIMARY KEY,	/* 列级完整性约束条件,Sno是主码*/
       Sname CHAR(20) UNIQUE,		/* Sname取唯一值*/
       Ssex CHAR(2) check (Ssex in ('男','女')),
       Sage SMALLINT check (Sage between 18 and 25),
       Dept CHAR(20)
      );
  

• Create table Course

  CREATE TABLE Course
       (Cno CHAR(4) PRIMARY KEY,
        Cname CHAR(40),            
        Cpno CHAR(4),              	                      
        Ccredit SMALLINT，
        Semester INT,
        FOREIGN KEY (Cpno) REFERENCES  Course(Cno) 
        ); 	
  

• Create table 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*/
        );
  

• 查询“计算机系”学生的详细信息，并按性别升序排列，相同性别按年龄降序排列

  SELECT * FROM Student 
   WHERE Dept = '计算机系'
   ORDER BY Ssex ASC, Sage DESC
  

• 查询年龄在18-20岁之间的学生详细情况

  SELECT * FROM Student 
   WHERE Sage BETWEEN 18 AND 20
  

• 查询所有选修了Java课程的学生情况，输出学生的姓名和系别

  SELECT Sname,Dept FROM Student S 
   JOIN SC ON S.Sno = SC.Sno
   JOIN Course C ON C.Cno = SC.cno
   WHERE Cname = 'Java'
  

• 与刘晨在同一个系学习的学生（自连接；嵌套 IN）

  SELECT S2.Sno,S2.Sname,S2.Dept FROM Student S1 
   JOIN Student S2 ON S1.Dept = S2.Dept
   WHERE S1.Sname = '刘晨' AND S2.Sname != '刘晨'
  

  SELECT Sno,Sname,Dept FROM Student
   WHERE Dept IN ( 
     SELECT Dept FROM Student 
       WHERE Sname = '刘晨')
   AND Sname != '刘晨'
  

• 查询人数在50人以上的系，输出系别，人数，按照人数降序排列Count（sno）

  SELECT Dept,COUNT(Sno) FROM student
   GROUP By Dept
   HAVING COUNT(Sno)>50
   ORDER by COUNT(Sno) DESC /* order by 在 having 子句下方 */
  

• 查询总成绩600分以上的学生学号，平均成绩Sum（）

  SELECT s.Sno FROM Student s /* 须指明是哪个表的 Sno */
   JOIN SC ON s.Sno = SC.Sno /* 两个表都有 Sno */
   GROUP By s.Sno
   HAVING SUM(Grade)>600
  

• 查询每个学生的平均成绩，输出学号，学生姓名，平均成绩

  SELECT s.Sno,Sname,avg(Grade)平均成绩 FROM Student s
   JOIN SC ON s.Sno = SC.Sno
   GROUP By s.Sno,Sname /* 需要学号、姓名两个因素分组 */
  

• 查询计算机系没有选课的学生信息

  SELECT Sname,Dept,Cno,Grade FROM Student S 
   LEFT JOIN SC ON S.Sno = SC.Sno
   WHERE Dept = '计算机系' AND SC.Sno IS NULL /* 判空条件和连接条件是同属性 */
  

• 查询选修了全部课程的学生信息

  SELECT s.Sno,Sname,Dept FROM Student s
   WHERE NOT EXISTS(	/* 2.不存在这样一个课程c.Cno，它没有被s.Sno选 */
     SELECT * FROM Course c 
      WHERE NOT EXISTS(	/* 1.不存在该学生没选的课程 */
        SELECT * FROM SC
  	   WHERE SC.Cno = c.Cno AND SC.Sno = s.Sno))
  

• 将“计算机系”学生选修课程的成绩置为0 / 成绩加5分

  UPDATE SC 
   SET Grade = 0 /* SET Grade = Grade + 5 */
   WHERE Sno IN
     (SELECT Sno FROM Student
       WHERE Dept = '计算机系')
  

• 删除“计算机系”全体学生“第2学期”的选课记录

  DELETE FROM SC 
   JOIN Student ON SC.Sno = Student.Sno
   WHERE Dept = '计算机系' AND Semester = 2
  

  更正：

  DELETE FROM SC 
   FROM SC JOIN Student ON SC.Sno = Student.Sno
   JOIN Course ON Course.Cno = SC.Cno
   WHERE Dept = '计算机系' AND Semester = 2
  

• 创建一个“计算机系”学生选课的视图V1，包括学号，姓名，课程名称，成绩

  将查询视图V1的权限授予用户user1

  CREATE VIEW	V1(Sno,Sname,Cname,Grade)
  AS
  SELECT s.Sno,Sname,Cname,Grade FROM Student s 
  JOIN SC ON s.Sno = SC.Sno
  JOIN Course c ON c.Cno = SC.Cno
  
  GRANT SELECT 
   ON VIEW V1 
   TO User1
  /* WITH GRANT OPTION 允许已经获得权限的用户把这种权限再授予其他用户 */
  

• 将查询每门课程号和平均成绩的权限授权给用户Wang

  GRANT SELECT 
   ON TABLE Course,SC 
   TO Wang
  

• 将对Student的全部访问权限授予所有用户

  GRANT ALL PRIVILEGES 
   ON TABLE Student 
   TO PUBLIC