SQL陳述句習題總練習

SQL陳述句習題總練習

SQL陳述句總結詳解點這里

接下來為練習：

預建表

------------------------------------------------------------------------
--先建表
CREATE TABLE 學生
(
Sno varchar(20),
Sname varchar(20),
Ssex char(2),
Sage smallint,
Sdept varchar(20)
);
CREATE TABLE 課程
(
Cno varchar(20),
Cname varchar(20),
Ccredit smallint,
);
CREATE TABLE 選課
(
Sno varchar(20),
Cno varchar(20),
Grade smallint
);
------------------------------------------------------------------------

第一大題：采用SQL陳述句修改表的結構

------------------------------------------------------------------------
--第一大題：采用SQL陳述句修改表的結構，并要求記錄系統的相關提示界面，題目如下：
--題目1：修改學生表的結構
--（1）增加“專業”欄位，默認為“計算機科學與應用”
ALTER TABLE 學生
ADD 專業 char(20) DEFAULT '計算機科學與技術'
--（2）增加約束：定義性別只能是”男”或”女”的檢查約束
ALTER TABLE 學生
ADD CONSTRAINT CK_Ssex CHECK(Ssex IN ('男','女'))
--（3）為學生表創建主鍵約束
ALTER TABLE 學生
ALTER COLUMN Sno VARCHAR(20) NOT NULL
ALTER TABLE 學生
ADD CONSTRAINT PK_Sno PRIMARY KEY(Sno)
--（4）洗掉“姓名（sname）”欄位
ALTER TABLE 學生
DROP COLUMN Sname

--題目2：修改課程表的結構
--（1）增加“課程類別”欄位，默認為“軟體系統類”
ALTER TABLE 課程
ADD 課程類別 char(20) DEFAULT '軟體系統類'
--（2）增加約束：定義“Ccredit ”欄位取值范圍在1到10之間
ALTER TABLE 課程
ADD CONSTRAINT CK_Credit CHECK ( Ccredit >= 1 AND Ccredit <= 10)
--（3）為課程表創建主鍵約束
ALTER TABLE 課程
ALTER COLUMN Cno VARCHAR(20) NOT NULL
ALTER TABLE 課程
ADD CONSTRAINT PK_Cno PRIMARY KEY(Cno)
--（4）洗掉“課程名（Cname）”欄位
ALTER TABLE 課程
DROP COLUMN Cname

--題目3：修改選課表的結構
--（1）增加“任課教師級別”欄位，默認為“講師以上”
ALTER TABLE 選課
ADD 任課教師級別 VARCHAR(20) DEFAULT '講師以上'
--（2）增加約束：定義“Grade”欄位取值范圍在0到100之間
ALTER TABLE 選課
ADD CONSTRAINT CK_Grade CHECK(Grade <=100 AND Grade >=0)
--（3）為選課表創建外鍵約束
ALTER TABLE 選課
ADD CONSTRAINT FK_Sno FOREIGN KEY(Sno) REFERENCES 學生(Sno)
ALTER TABLE 選課
ADD CONSTRAINT FK_Cno FOREIGN KEY(Cno) REFERENCES 課程(Cno)
--（4）洗掉“成績（grade）”欄位
ALTER TABLE 選課
DROP CONSTRAINT CK_GRADE
ALTER TABLE 選課
DROP COLUMN Grade 
------------------------------------------------------------------------

第二大題：采用SQL陳述句實作資料的維護操作

------------------------------------------------------------------------
--第二大題：采用SQL陳述句實作資料的維護操作
--題目1：學生表資料的操作
--（1）	添加以下資料
/*
Sno	    專業	  Ssex	Sage Sdept
99001	軟體工程   男	 20	   CS 
*/
INSERT 
INTO 學生(Sno,專業,Ssex,Sage,Sdept) 
VALUES('99001','軟體工程','男','20','Cs')=
--（2）將所有學生的年齡減少1歲
UPDATE 學生
SET Sage = Sage - 1
--（3）將學號為95003的學生洗掉
DELETE 
FROM 學生
WHERE Sno='95003'

--題目2：課程表資料的操作
--（1）	添加以下資料
--Cno	課程類別	Ccredit
--001	軟體系統      4
INSERT 
INTO 課程(Cno,課程類別,Ccredit) 
VALUES('001','軟體系統','4')
--（2）將課程號為002的課程學分（Ccredit）減少1分
UPDATE 課程
SET Ccredit = Ccredit - 1
WHERE Cno = '002'
--（3）將課程號為002的課程洗掉
DELETE
FROM 課程
WHERE Cno = '002'

--題目3：選課表資料的操作
--（1）	添加以下資料
--Sno	Cno	Grade	任課教師級別
--95001	001	87	副教授
INSERT 
INTO 選課(Sno,Cno,Grade,任課教師級別) 
VALUES('95001','001','87','副教授')
--（2）將選了課程號為002的學生成績加5%
UPDATE 選課
SET Grade = Grade + Grade * 0.05
WHERE Cno = '002'
--（3）將學號為95001的選課資訊洗掉
DELETE 
FROM 選課
WHERE Sno = '95001'
------------------------------------------------------------------------

第三大題：采用SQL陳述句實作查詢統計功能

------------------------------------------------------------------------
--第三大題：采用SQL陳述句實作查詢統計功能
--題目1：查詢各學院各門課程的平均成績和選修人數，輸出平均成績在60以上的課程，選課人數和平均成績，并按平均成績排序
SELECT Sdept,Cname,COUNT(*)選課人數,AVG(Grade)平均成績
FROM 學生,課程,選課 
WHERE 學生.Sno=選課.Sno AND 選課.Cno=課程.Cno
GROUP BY Sdept,Cname,選課.Cno having AVG(Grade) > 60
ORDER BY 平均成績 DESC

--題目2：統計各學院學生選課總學分和平均成績，輸出選課總學分在20分以上學生的選課數量和平均成績，并按平均成績排序
SELECT Sdept,SUM(Ccredit)選課總學分,AVG(Grade)平均成績
FROM 學生,課程,選課 
WHERE 學生.Sno=選課.Sno AND 選課.Cno=課程.Cno 
GROUP BY Sdept HAVING SUM(Ccredit)>20
ORDER BY 平均成績 DESC

--題目3：統計各門課程各分數段的分布情況， 
SELECT 選課.Cno,COUNT(CASE WHEN 選課.Grade>80 THEN 1 ELSE NULL END)成績大于80的人數
FROM 學生,課程,選課 
WHERE 學生.Sno=選課.Sno AND 選課.Cno=課程.Cno 
GROUP BY 選課.Cno
------------------------------------------------------------------------

第四大題：采用SQL陳述句實作查詢功能

------------------------------------------------------------------------
--第四大題：采用SQL陳述句實作查詢功能:
--題目1：創建“物電學院”所有學生的成績視圖，包括：學號，姓名，選修課程名，成績，并對視圖作查詢，
CREATE VIEW 物電學院學生成績
AS 
SELECT 學生.Sno 學號,Sname 姓名,Cname 選修課程名,Grade 成績
FROM 學生,課程,選課 
WHERE 學生.Sno=選課.Sno AND 選課.Cno=課程.Cno AND Sdept = '物電學院'
WITH CHECK OPTION;

SELECT *
FROM 物電學院學生成績

--題目2：創建“學生選課”視圖，包括：學號，姓名，選修課程名，成績，要求沒有選修任何課程的學生也在內，并對視圖作查詢，
CREATE VIEW 學生選課
AS 
SELECT 學生.Sno 學號,Sname 姓名,Cname 選修課程名,Grade 成績
FROM 學生  
LEFT OUTER JOIN 選課
ON 學生.Sno=選課.Sno  
LEFT OUTER JOIN 課程
ON 選課.Cno=課程.Cno
WITH CHECK OPTION;

SELECT *
FROM 學生選課

--題目3：創建“課程選修”視圖，包括課程號、課程名、學號、姓名、成績，要求沒被任何學生選修的課程也在內，并對視圖作查詢，
CREATE VIEW 課程選修
AS 
SELECT 課程.Cno 課程號,Cname 課程名,學生.Sno 學號,Sname 姓名,Grade 成績
FROM 課程  
LEFT OUTER JOIN 選課
ON 選課.Cno=課程.Cno
LEFT OUTER JOIN 學生
ON 學生.Sno=選課.Sno  
WITH CHECK OPTION;

SELECT *
FROM 課程選修
------------------------------------------------------------------------

第五大題：采用SQL陳述句實作存盤程序:

------------------------------------------------------------------------
--第五大題：采用SQL陳述句實作存盤程序:
--題目1：設計并呼叫存盤程序，輸出學生選修總學分 
CREATE PROC pro1
AS
SELECT 學生.Sno 學生學號,SUM(課程.Ccredit) 總學分
FROM 學生,課程,選課 
WHERE 學生.Sno=選課.Sno AND 選課.Cno=課程.Cno
GROUP BY 學生.Sno

EXEC pro1

--題目2：設計并呼叫存盤程序，輸出課程選修總人數， 
CREATE PROC pro2
AS 
SELECT 課程.Cno 課程號,COUNT(*) 選修人數
FROM 學生,課程,選課 
WHERE 學生.Sno=選課.Sno AND 選課.Cno=課程.Cno 
GROUP BY 課程.Cno

EXEC pro2

--題目3：設計并呼叫存盤程序，輸出課程成績平均分、最高分和最低分
CREATE PROC pro3
AS 
SELECT 課程.Cno 課程號,AVG(Grade) 成績平均分 ,MAX(Grade)最高分,MIN(Grade) 最低分
FROM 學生,課程,選課 
WHERE 學生.Sno=選課.Sno AND 選課.Cno=課程.Cno 
GROUP BY 課程.Cno
EXEC pro3
------------------------------------------------------------------------