题目一
设教学数据库Education有三个关系:
学生关系Student (SNO, SNAME, AGE, SEX, SDEPT);
学习关系SC (SNO, CNO, GRADE);
课程关系 Curriculum (CNO, CNAME, CDEPT, TNAME)。
查询问题:
(1) 检索计算机系的所有学生的学号、姓名和性别;
(2) 检索学习课程号为2的学生学号和姓名;
(3) 检索选修课程名为”Digital”的学生学号和姓名;(三表联合)
(4) 检索选修课程号为2或4的学生学号;
(5) 检索年龄在17-19岁间的学生信息;
(6) 检索每个院系有多少个学生;
(7) 检索选修课程号为2的年纪最大的学生;
(8) 检索选修每个课程学生的最大年纪;
(9) 检索每个学生的总成绩,并从高到低排序;
(10) 检索每门课程的平均成绩;
(11) 检索java成绩低于2级的学生信息;
(12) 检索同时选修课程1和课程2的学生信息;
(13) 检索以”j”开头的学生的信息以及所选课程
首先创建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)
);
解答(按序号)
(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%";
题目二
设教学数据库Education_2有四个关系:
学生关系Student (S, SNAME, SAGE, SSEX);
学习关系SC (S, C, SCORE);
课程关系 Course (C, CNAME, T);
老师关系 Teacher (T, TNAME)。
问题:
(1) 创建数据库并插入响应数据;
(2) 检索01课程比02课程成绩高的学生的信息及课程分数;
(3) 检索平均成绩大于等于60分的同学编号和学生姓名和平均成绩;
(4) 检索所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩;
(5) 检索“李”姓老师数量;
(6) 检索上过“Zhang”老师课的同学信息;
(7) 检索没上过“Zhang”老师课的同学;
(8) 检索学过编号1课程并且也学过编号2课程的学生信息
首先创建表
(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
)
);
网友评论