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