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