題目一

設(shè)教學(xué)數(shù)據(jù)庫(kù)Education有三個(gè)關(guān)系：
學(xué)生關(guān)系Student (SNO, SNAME, AGE, SEX, SDEPT)；
學(xué)習(xí)關(guān)系SC (SNO, CNO, GRADE)；
課程關(guān)系 Curriculum (CNO, CNAME, CDEPT, TNAME)。

查詢問題：  
(1) 檢索計(jì)算機(jī)系的所有學(xué)生的學(xué)號(hào)、姓名和性別；  
(2) 檢索學(xué)習(xí)課程號(hào)為2的學(xué)生學(xué)號(hào)和姓名；  
(3) 檢索選修課程名為”Digital”的學(xué)生學(xué)號(hào)和姓名；(三表聯(lián)合)  
(4) 檢索選修課程號(hào)為2或4的學(xué)生學(xué)號(hào)；  
(5) 檢索年齡在17-19歲間的學(xué)生信息；  
(6) 檢索每個(gè)院系有多少個(gè)學(xué)生；  
(7) 檢索選修課程號(hào)為2的年紀(jì)最大的學(xué)生；  
(8) 檢索選修每個(gè)課程學(xué)生的最大年紀(jì)；  
(9) 檢索每個(gè)學(xué)生的總成績(jī)，并從高到低排序；  
(10) 檢索每門課程的平均成績(jī)；  
(11) 檢索java成績(jī)低于2級(jí)的學(xué)生信息；  
(12) 檢索同時(shí)選修課程1和課程2的學(xué)生信息；  
(13) 檢索以”j”開頭的學(xué)生的信息以及所選課程  

首先創(chuàng)建Student、SC以及Curriculum表:

CREATE table Student (  
SNO int NOT NULL AUTO_INCREMENT,  
SNAME VARCHAR(10) NOT NULL,  
AGE int NOT NULL,  
SEX VARCHAR(6) NOT NULL,  
SDEPT VARCHAR(20) NOT NULL,  
PRIMARY KEY (SNO)
     );  

CREATE table SC (
SNO int NOT NULL,
CNO int NOT NULL,
GRADE int NOT NULL
);

CREATE table Curriculum(
CNO int NOT NULL AUTO_INCREMENT,
CNAME VARCHAR(20) NOT NULL,
CDEPT VARCHAR(20) NOT NULL,
TNAME VARCHAR(20) NOT NULL,
PRIMARY KEY (CNO)
);

解答（按序號(hào)）

(1) SELECT Student.SNO, Student.SNAME,Student.SEX
FROM Student
WHERE SDEPT="CS";

(2) SELECT Student.SNO, Student.SNAME
FROM Student INNER JOIN SC
ON Student.SNO=SC.SNO
WHERE SC.CNO=2;

(3) SELECT Student.SNAME, Student.AGE 
FROM (Student INNER JOIN SC ON Student.SNO=SC.SNO) 
INNER JOIN Curriculum ON SC.CNO=Curriculum.CNO
WHERE Curriculum.CNAME="digital";

(4) SELECT Student.SNAME, Student.AGE 
FROM (Student INNER JOIN SC ON Student.SNO=SC.SNO) 
INNER JOIN Curriculum ON SC.CNO=Curriculum.CNO
WHERE SC.CNO IN (4,5);

(5) SELECT * FROM Student
WHERE Student.AGE BETWEEN 17 AND 19;
 
(6) SELECT SDEPT, COUNT(SNO)
FROM Student
GROUP BY SDEPT;

(7) SELECT Student.SNAME, MAX(Student.AGE)
FROM Student INNER JOIN SC
ON Student.SNO=SC.SNO
WHERE SC.CNO=2;

(8) SELECT CNAME, MAX(Student.AGE)
FROM (Student INNER JOIN SC ON Student.SNO=SC.SNO)
INNER JOIN Curriculum ON SC.CNO=Curriculum.CNO
GROUP BY CNAME;

(9) SELECT Student.SNO, Student.SNAME, SUM(SC.GRADE)
FROM Student INNER JOIN SC
ON Student.SNO=SC.SNO
GROUP BY SNAME
ORDER BY GRADE DESC;

(10)    SELECT Curriculum.CNO, Curriculum.CNAME, AVG(SC.GRADE)
FROM SC INNER JOIN Curriculum
ON SC.CNO=Curriculum.CNO
GROUP BY CNAME
ORDER BY SC.CNO;

(11)    SELECT Student.*
FROM (Student INNER JOIN SC ON Student.SNO=SC.SNO)
INNER JOIN Curriculum ON SC.CNO=Curriculum.CNO
WHERE (CNAME="java" AND GRADE<2);

(12)    SELECT a.SNO
FROM SC a, SC b
WHERE a.CNO = 1 and b.CNO = 2;

SELECT Distinct Student.*
FROM Student INNER JOIN SC
ON Student.SNO = SC.SNO
WHERE SC.SNO = (
SELECT a.SNO from SC a, SC b where a.CNO=1 and b.CNO=2 and a.SNO=b.SNO);

(13)    SELECT Student.*, SC.* 
FROM Student INNER JOIN SC
ON Student.SNO=SC.SNO
WHERE SNAME LIKE "j%";

題目二

設(shè)教學(xué)數(shù)據(jù)庫(kù)Education_2有四個(gè)關(guān)系：
學(xué)生關(guān)系Student (S, SNAME, SAGE, SSEX)；
學(xué)習(xí)關(guān)系SC (S, C, SCORE)；
課程關(guān)系 Course (C, CNAME, T);
老師關(guān)系 Teacher (T, TNAME)。

問題：
(1) 創(chuàng)建數(shù)據(jù)庫(kù)并插入響應(yīng)數(shù)據(jù)；
(2) 檢索01課程比02課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)；
(3) 檢索平均成績(jī)大于等于60分的同學(xué)編號(hào)和學(xué)生姓名和平均成績(jī)；
(4) 檢索所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)；
(5) 檢索“李”姓老師數(shù)量；
(6) 檢索上過“Zhang”老師課的同學(xué)信息；
(7) 檢索沒上過“Zhang”老師課的同學(xué)；
(8) 檢索學(xué)過編號(hào)1課程并且也學(xué)過編號(hào)2課程的學(xué)生信息

首先創(chuàng)建表

(1) CREATE table Student (
S Int NOT NULL AUTO_INCREMENT,
SNAME Varchar(10) NOT NULL,
SAGE Int NOT NULL,
SSEX Varchar(10) NOT NULL,
PRIMARY KEY (S)
);

CREATE table Course (
C Int NOT NULL AUTO_INCREMENT,
CNAME Varchar(10) NOT NULL,
T Varchar(10) NOT NULL,
PRIMARY KEY (C)
);

CREATE table Teacher (
T Int NOT NULL AUTO_INCREMENT,
TNAME Varchar(10) NOT NULL,
PRIMARY KEY (T)
);

CREATE table SC (
S Int NOT NULL,
C Int NOT NULL,
SCORE Int NOT NULL
);

解答正式開始

(2) SELECT a.*, b.SCORE as “score1”, c.SCORE as “score2”
FROM Student a
INNER JOIN SC b
ON a.S = b.S and b.C=1
INNER JOIN SC b
ON a.S = c.S and c.C=2
WHERE b.SCORE > c.SCORE;

(3) SELECT a.S, a.SNAME, AVG(b.SCORE)
FROM Student a
INNER JOIN SC b
ON a.S = b.S
GROUP BY a.S
HAVING AVG (b.SCORE)>60;

(4) SELECT Student.S, Student.SNAME, COUNT(SC.C), AVG(SC.SCORE)
FROM Student
INNER JOIN SC
ON Student.S = SC.S
GROUP BY SC.S;

(5) SELECT COUNT(T) 
FROM Teacher
WHERE Teacher.TNAME LIKE ‘李%’;

(6) SELECT Student.* 
FROM Student
INNER JOIN SC
ON Student.S = SC.S
INNER JOIN Course
ON SC.C = Course.C
INNER JOIN Teacher
ON Course.T = Teacher.T
WHERE Teacher.TNAME=”Zhang”;

(7) SELECT Student.*
FROM Student
INNER JOIN SC 
ON Student.S = SC.S
WHERE NOT EXISTS (
  SELECT * 
  FROM Course
  INNER JOIN Teacher
  ON Course.T = Teacher.T
  INNER JOIN SC
  ON Course.C = SC.C
  WHERE Teacher.TNAME=’Zhang’ and SC.S = Student.S
);

(8) SELECT DISTINCT Student.*
FROM Student
INNER JOIN SC
ON Student.S = SC.S
WHERE SC.S = any (
   SELECT a.S FROM SC a, SC b WHERE a.C=1 AND b.C=2 AND a.S=b.S
);

(9) SELECT DISTINCT Student.*
FROM Student 
INNER JOIN SC
ON Student.S = SC.S
WHERE SC.S = any (
   SELECT S FROM SC WHERE C = 1 and S NOT IN (
SELECT S FROM SC WHERE C =2
)
);