如果你的資料資料庫老師叫李健，請關閉網頁

實驗一

學生表：Student(Sno,Sname,Ssex,Sage,Sdept)

課程表：Course(Cno,Cname,Cpno,Ccredit)

學生選課表：SC(Sno,Cno,Grade)

注：性别為enum('男'or'女')，請使用KingBase語句進行答題，語句後要添加分号，否則語句視為錯誤

題目連結

1. 查詢全體學生的詳細資訊!（要求輸出全部列）

SELECT *
FROM Student;

2. 查詢課程的詳細資訊（要求輸出全部列）

SELECT *
FROM Course;

3. 查詢所有女生的姓名（輸出列為：SNAME）

SELECT Sname SNAME
FROM Student
WHERE Ssex='女';

4. 查詢至少選修了一門課程的學生學号(請在結果中除去重複值，輸出列為：SNO)。

SELECT DISTINCT Sno SNO
FROM SC
GROUP BY Sno
HAVING COUNT(*)>=1;

5. 查詢年齡不在20~23之間的學生姓名、系别和年齡，并按照年齡降序排列（輸出列為：SNAME, SDEPT,SAGE）

SELECT Sname SNAME,Sdept SDEPT,Sage SAGE
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;

6. 查詢所有姓曹的學生的姓名、學号和性别（輸出列為：SNAME,SNO,SSEX)。

SELECT Sname SNAME,Sno SNO,Ssex SSEX
FROM Student
WHERE Sname LIKE '曹%';

7. 查詢“資訊學院”、“理學院”和“經濟學院”學生的姓名和性别（輸出列為：SNAME,SSEX)

SELECT Sname SNAME,Ssex SSEX
FROM Student
WHERE Sdept='資訊學院' OR Sdept='理學院' OR Sdept='經濟學院';

8. 查詢每個同學選課的數目，并按學号升序排列，（輸出列為SNO，選課數目SUM）

SELECT Sno SNO,COUNT(*) SUM
FROM SC
GROUP BY Sno
ORDER BY Sno DESC;

9. 統計不同系的人數（輸出列為：SDEPT、人數）

SELECT Sdept SDEPT,COUNT(*) 人數
FROM Student
GROUP BY Sdept;

實驗二

1. 查詢學生姓名，對應的課程名和成績（輸出列為：SNAME,CNAME,GRADE）

SELECT Sname SNAME,Cname CNAME,Grade GRADE
FROM SC JOIN Student S ON SC.Sno=S.Sno JOIN Course C ON C.Cno=SC.Cno;

2. 查詢每個學生的學号、姓名及其選修課程的名稱和成績(包括沒有選修課程的學生情況（輸出列為：SNO, SNAME, CNAME, GRADE)。

SELECT S.Sno SNO,Sname SNAME,Cname CNAME,Grade GRADE
FROM SC  RIGHT JOIN Student S ON SC.Sno=S.Sno JOIN Course C ON C.Cno=SC.Cno;

3. 查詢其他系中比“資訊學院”某一學生年齡小的學生姓名和年齡（輸出列為：SNAME, SAGE）。

SELECT Sname SNAME,Sage SAGE
FROM Student
WHERE Sdept<>'資訊學院' AND Sage<SOME(SELECT Sage FROM Student WHERE Sdept='資訊學院');

4. 查詢選修了全部課程的學生姓名（輸出列為：SNAME）

SELECT Sname
FROM Student
WHERE NOT EXISTS(
					SELECT *
          FROM Course
          WHERE NOT EXISTS(
								SELECT *
                FROM SC
                WHERE Sno= Student.Sno
                AND Cno= Course.Cno));

資料庫實驗 MOOC如果你的資料資料庫老師叫李健，請關閉網頁實驗一實驗二實驗三實驗四

EXISTS如果子查詢結果不為空，目前行被選擇

NOT EXISTS如果子查詢結果為空，目前行被選擇

5. 查詢選了經濟控制論的學生中，成績比名字為趙古韻的學生好的學生資訊（輸出列為STUDENT表全部列）

SELECT *
FROM Student
WHERE Sno IN(SELECT Sno 
							FROM SC JOIN Course C ON SC.Cno=C.Cno 
							WHERE Cname='經濟控制論' AND Grade>(SELECT Grade 
																								FROM SC JOIN Student S ON SC.Sno=S.Sno JOIN Course C ON C.Cno=SC.Cno
																								WHERE Sname='趙古韻' AND Cname='經濟控制論'));

6. 統計每個學生所選課程的平均成績（輸出列為：SNAME，AVGSCORE）

SELECT Sname SNAME,AVG(Grade) AVGSCORE
FROM SC JOIN Student S ON SC.Sno=S.Sno
GROUP BY Sname;

·7. 查詢資訊學院年齡在21歲以下的男生每個人所修課程的總學分，并按總學分進行升序排序（輸出列為學号SNO、總學分CREDITSUM）

下面做法判錯

SELECT SC.Sno SNO,SUM(Ccredit) CREDITSUM
FROM SC JOIN Student S ON SC.Sno=S.Sno JOIN Course C ON C.Cno=SC.Cno
WHERE Sdept='資訊學院' AND Sage<21 AND Ssex='男' 
GROUP BY SC.Sno
ORDER BY CREDITSUM;

8. 查詢各門課程的最高成績的學生的姓名及其成績（輸出列為CNO,SNAME,GRADE）

SELECT SC.Cno CNO,S.Sname SNAME,SC.grade GRADE
FROM SC, student S,
(select Cno, MAX(grade) grade FROM SC GROUP BY CNO) t1
WHERE SC.Sno = S.Sno
AND SC.Cno = t1.Cno
AND SC.grade = t1.grade;

9. 查詢選修了2012001001号學生所選修的全部課程的學生的姓名（輸出列SNAME）

SELECT Sname SNAME 
FROM Student x 
WHERE NOT EXISTS(
						SELECT* 
						FROM SC y
						WHERE Sno = '2012001001' AND 
						NOT EXISTS(
								SELECT * 
								FROM SC z 
								WHERE z.Sno = x.Sno AND 
								z.Cno = y.Cno ));

實驗三

寫出建立Student、Course和SC表的SQL語句，并建立資料庫

1、Student：

Sno 學号字元串長度7 主鍵

Sname 姓名字元串長度10 非空

Ssex 性别字元串長度2

Sage 年齡整數

Sdept 系字元串長度20

2、Course：

Cno 課程号字元串長度6

Cname 課程名字元串長度20 主鍵

Credit 學分整數

Semster 學期整數

3、SC：

Sno 學号字元串長度7 主鍵，引用student外碼

Cno 課程号字元串長度6 主鍵，引用Course外碼

Grade 成績實數

CREATE TABLE Student(
	Sno CHAR(7) PRIMARY KEY,
	Sname CHAR(10) NOT NULL,
	Ssex CHAR(2),
	Sage INTEGER,
	Sdept CHAR(20)
);

CREATE TABLE Course(
	Cno CHAR(6),
	Cname CHAR(20),	
	Credit INTEGER,
	Semster INTEGER,
	PRIMARY KEY(Cname)
);

CREATE TABLE SC(
	Sno CHAR(7),
	Cno CHAR(6),
	Grade DOUBLE,
	PRIMARY KEY(Sno,Cno),
	FOREIGN KEY(Sno) REFERENCES TEST.Student(Sno),
	FOREIGN KEY(Cno) REFERENCES TEST.Course(Cno)
);

實驗四

上文的Student、Course和SC表結構實作如下操作。

1、查詢學生選課表中的全部資料。

SELECT * FROM SC;

2、查詢計算機系的學生的姓名、年齡。

SELECT Sname,Sage
FROM Student
WHERE Sdept='CS';

3、查詢成績在70-80分之間的學生的學号、課程名和成績。

SELECT Sno,Cname,Grade
FROM Course C,SC S
WHERE C.Cno=S.Cno AND Grade BETWEEN 70 AND 80;

4、查詢計算機系年齡在18-20,包括18、20，之間且性别為“男”的學生的姓名、年齡。

SELECT Sname,Sage
FROM	Student
WHERE Sdept='CS' AND Ssex='男' AND Sage BETWEEN 18 AND 20;

5、查詢課程号為“c01”的課程的最高的分數。

SELECT MAX(Grade)
FROM SC
WHERE Cno='c01';

6、查詢計算機系學生的最大年齡和最小年齡。

SELECT MAX(Sage),MIN(Sage)
FROM Student
WHERE Sdept='CS';

7、統計每個系的學生人數。

SELECT Sdept,COUNT(Sno)
FROM Student
GROUP BY Sdept;

8、統計每門課程的選課人數和考試最高分。

SELECT Cno,COUNT(Sno),MAX(Grade)
FROM SC
GROUP BY Cno;

9、統計每個學生的選課門數和考試總成績，并按選課門數升序顯示結果。

SELECT Sno,COUNT(Cno) num,SUM(Grade)
FROM SC
GROUP BY Sno
ORDER BY num;

10、查詢總成績超過100分的學生，要求列出學号、總成績。

SELECT Sno,SUM(Grade) SumGrade
FROM SC
GROUP BY Sno
HAVING SumGrade>100;

WHERE子句與HAVING 短語的差別在于作用對象不同。WHERE子句作用于基本表或視圖，從中選擇滿足條件的元組。HAVING短語作用于組，從中選擇滿足條件的組。

［例3.48] 查詢平均成績大于等于90 分的學生學号和平均成績。

下面的語句是不對的：

SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;

因WHERE子句中是不能用聚集函數作為條件表達式的，正确的查詢語句應該是：

SELECT Sno,AVG(Grade)
FROM SC
GROUPBYSno
HAVING AVG(Grade)>=90;

11、查詢選修了“c02”号課程的學生的姓名和所在系。

SELECT Sname,Sdept
FROM Student S JOIN SC ON SC.Sno=S.Sno
WHERE Cno='c02';

12、查詢成績80分以上的學生的姓名、課程名和成績，并按成績降序排列結果。

SELECT Sname,Cname,Grade
FROM Student,Course,SC
WHERE SC.Sno=Student.Sno AND SC.Cno=Course.Cno AND Grade>80
ORDER BY Grade DESC;

13、查詢哪些學生沒有選課，要求列出學号、姓名和所在系。

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sno NOT IN(SELECT Sno FROM SC);

14、查詢與VB在同一學期開設的課程的課程名和開課學期。

SELECT Cname,Semster
FROM Course
WHERE Semster IN(SELECT Semster FROM Course WHERE Cname='VB');

SELECT C1.Cname,C1.Semster
FROM Course C1,Course C2
WHERE C1.Semster=C2.Semster AND C2.Cname='VB';
#注意Cname和Semster全都要加C1、C2

15、查詢查詢與李勇年齡相同的學生的姓名、所在系和年齡。

SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage IN(SELECT Sage FROM Student WHERE Sname='李勇');

16、用子查詢實作如下查詢：

（1）查詢選修了“c01”号課程的學生的姓名和所在系。

SELECT Sname,Sdept
FROM Student
WHERE Sno IN(SELECT Sno FROM SC WHERE Cno='c01');

（2）查詢數學系MA成績80分以上的學生的學号、姓名、課程号、成績。

SELECT Student.Sno,Sname,Cno,Grade
FROM Student JOIN SC ON Student.Sno=SC.Sno
WHERE Sdept='MA' AND Student.Sno IN(SELECT Sno FROM SC WHERE Grade>80);

A JION B ON 條件結果A在前，B在後加入

SELECT *
FROM SC JOIN  Student ON Student.Sno=SC.Sno;

（3）查詢計算機系考試成績最高的學生的姓名。

SELECT Sname
FROM SC JOIN Student S ON SC.Sno=S.Sno
WHERE Sdept='CS' AND Grade>=ALL(SELECT Grade
																FROM SC JOIN Student S ON SC.Sno=S.Sno
																WHERE Sdept='CS');

（4）查詢VB考試成績最高的學生的姓名、所在系、性别和成績。

SELECT Sname,Sdept,Ssex,Grade
FROM SC JOIN Student S ON Sc.Sno=S.Sno JOIN Course C ON SC.Cno=C.Cno
WHERE Cname='VB' AND Grade>=ALL(SELECT Grade
																FROM SC JOIN Student S ON Sc.Sno=S.Sno JOIN Course C ON SC.Cno=C.Cno
																WHERE Cname='VB');

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-FUP8YP9J-1587826816289)(/Users/xcsxchen/Library/Application Support/typora-user-images/截屏2020-04-25 上午10.55.00.png)]

17、查詢沒有選修VB課程的學生的姓名和所在系。

SELECT Sname,Sdept
FROM SC JOIN Student S ON SC.Sno=S.Sno JOIN Course C ON SC.Cno=C.Cno
WHERE Cname<>'VB';

18、查詢計算機系沒有選課的學生的姓名和性别。

SELECT Sname,Ssex
FROM Student
WHERE Sdept='CS' AND Sno NOT IN(SELECT Sno FROM SC);

19、建立一個新表，表名為test_t，其結構為：（COL1,COL2,COL3），其中：

COL1:整型，允許空值。

COL2：字元型，長度為10，不允許空值。

COL3:字元型，長度為10，允許空值。

試寫出按行插入如下資料的語句（空白處表示空值）。

COL1	COL2	COL2
B1
1	B2	C2
2	B3

CREATE TABLE test_t(COL1 INTEGER,COL2 char(10) NOT NULL,COL3 char(10));
INSERT INTO test_t(COL2) VALUES('B1');
INSERT INTO test_t VALUES(1,'B1','C2');
INSERT INTO test_t VALUES(2,'B3',NULL);

20、删除考試成績低于50分的學生的選課記錄。

DELETE
FROM SC
WHERE Grade<50;

21、删除沒有人選的課程記錄。

DELETE
FROM Course
WHERE Cno NOT IN(SELECT Cno FROM SC);

22、删除計算機系VB成績不及格學生的VB選課記錄。

DELETE
FROM SC
WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept='CS') AND 
			Cno IN(SELECT Cno FROM Course WHERE Cname='VB') AND 
			Grade<60;

23、将第2學期開設的所有課程的學分增加2分。

UPDATE Course SET Credit=Credit+2
WHERE Semster=2;

24、将計算機系學生的年齡增加1歲。

UPDATE Student SET Sage=Sage+1
WHERE Sdept='CS';

25、将計算機系學生的“計算機文化學”課程的考試成績加5分。

UPDATE SC SET Grade=Grade+5
WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept='CS') AND
			Cno IN(SELECT Cno FROM Course WHERE Cname='計算機文化學');

資料庫實驗 MOOC如果你的資料資料庫老師叫李健，請關閉網頁實驗一實驗二實驗三實驗四