3.15 作业
下面是一个学生与课程的数据库，三个关系表为： 学生表A（Sid，SNAME,AGE,
SEX） 成绩表B（Sid，Cid，GRADE） 课程表C（Cid，CNAME，TEACHER） 其中Sid为学号，SNAME为学生名字，AGE为年龄，SEX为性别， Cid为课程号，GRADE为成绩，CNAME为课程名字，TEACHER为教师姓名。 SQL脚本如下： #学生表
#create table A(Sid int(1),SNAME char(20),AGE int(1),SEX int(1));
#成绩表
#create table B(Sid int(1),Cid char(5),GRADE char(20)) ;
#课程表
#create table C(Cid char(5),CNAME char(20),TEACHER char(20));
学生表A（Sid，SNAME,
AGE,
SEX） 成绩表B（Sid，Cid，GRADE） 课程表C（Cid，CNAME，TEACHER） CREATE TABLE A (
Sid INT (1),
SNAME CHAR (20),
AGE INT (1),
SEX INT (1)
);
CREATE TABLE B (
Sid INT (1),
Cid CHAR (5),
GRADE CHAR (20)
);
CREATE TABLE C (
Cid CHAR (5),
CNAME CHAR (20),
TEACHER CHAR (20)
);
INSERT INTO A (Sid, SNAME, AGE, SEX)
VALUES
(1111, 'David', 28, 1),
(1112, 'Dany', 23, 0),
(1113, 'TOM', 38, 1),
(1114, 'Lily', 20, 0);
INSERT INTO B (Sid, Cid, GRADE)
VALUES
(1113, 'C1', '38'),
(1112, 'C2', '80'),
(1112, 'C3', '67'),
(1111, 'C4', '88'),
(1114, 'C4', '66');
INSERT INTO C (Cid, CNAME, TEACHER)
VALUES
('C4', 'Maths', 'zhangsan'),
('C3', 'English', 'lisi'),
('C2', 'Chinese', 'wangwu'),
('C1', 'Computer', 'zhaoliu');
SELECT
*
FROM
A;
SELECT
*
FROM
B;
SELECT
*
FROM
C;
请用SQL完成以下的操作：
1.查询出学习成绩及格以上的学生姓名与成绩； SELECT
*
FROM
A,
B
WHERE
A.sid = B.sid
AND B.Grade > 60
2.查询姓名以ny结尾的学生姓名及其任课老师姓名； SELECT
*
FROM
A,
B,
C
WHERE
A.sid = B.sid
AND C.cid = B.cid
AND A.Sname LIKE '%ny';
3.选修课名为Maths的学生学号与姓名;
SELECT
A.SID,
A.SNAME
FROM
A,
B,
C
WHERE
A.sid = B.sid
AND C.cid = B.cid
AND C.cname = 'Maths'
4.选修课号为C2和C4的学生学号； SELECT
A.sid
FROM
A,
B
WHERE
A.sid = B.sid
AND B.cid IN ('C2', 'C4');
5.请问没有参加考试的学生的姓名及其学号； SELECT
*
FROM
A
WHERE
A.Sid NOT IN (SELECT Sid FROM B)
6.请问总分在80分以上的学生姓名； SELECT
*
FROM
A
RIGHT JOIN (
SELECT
*
FROM
B
GROUP BY
sid
HAVING
SUM(grade) > 80
) newB ON A.SID = newB.sid
7.请问考试不及格的考生姓名以及科目名称； SELECT
*
FROM
A
RIGHT JOIN (SELECT * FROM B WHERE grade < 60) newB ON A.SID = newB.SID
LEFT JOIN C ON newB.cid = C.cid
8.找出Dany的所有考试后成绩中最高的科目的任课老师姓名； SELECT
*
FROM
C
WHERE
C.Cid IN (
SELECT
B.Cid
FROM
A
LEFT JOIN B ON A.Sid = B.Sid
WHERE
A.SNAME = 'Dany'
AND B.GRADE = (
SELECT
MAX(B.GRADE)
FROM
A,
B
WHERE
A.Sid = B.Sid
AND A.SNAME = 'Dany'
)
)
9.各个科目的平均成绩各是多少； SELECT
Cid,
AVG(GRADE) AS 平均分
FROM
B
GROUP BY
Cid;
10.由于录入员的失误，导致TOM同学有成绩不及格的科目，请帮忙将其合理修正； （1）查询语句 SELECT
*
FROM
B
WHERE
sid = (
SELECT
Sid
FROM
A
WHERE
A.sName = 'TOM'
) （2）修改语句 UPDATE B
SET Grade = 60
WHERE
sid = (
SELECT
Sid
FROM
A
WHERE
A.sName = 'TOM'
)
AND GRADE < 60
11.显示出参加考试的学生的学号和姓名； SELECT
*
FROM
A
WHERE
A.Sid IN (SELECT Sid FROM B)
12.请问TOM一共参加了几门科目的考试； SELECT
COUNT(Cid)
FROM
B
WHERE
sid = (
SELECT
Sid
FROM
A
WHERE
SNAME = 'TOM'
);
13.期末，DBA发现学生表中没有班级字段，请帮忙将其合理修正； ALTER TABLE A ADD Class VARCHAR (50) DEFAULT '未录入' AFTER SNAME;
SELECT
*
FROM
A;
14.学校新招了1位美术老师叫“白雪”，请在课程表中增加一条相应的记录，课程编号为C5； INSERT INTO C (CID, CNAME, TEACHER)
VALUES
('C5', 'MeiShu', 'baixue');
SELECT
*
FROM
C;
15.赵姓的老师很早就离职了，然而课程表中却还有其上课的记录，请帮忙删除； DELETE
FROM
C
WHERE
TEACHER LIKE 'zhao%'
16.请求出TOM同学所有任课老师的名字及其所教的课程名； SELECT
C.Cid,
C.CNAME,
C.TEACHER
FROM
A,
B,
C
WHERE
A.Sid = B.Sid
AND B.Cid = C.Cid
AND A.SNAME = 'TOM'
17.求每个同学的各科 (总分或者平均) 成绩，并以每一个人的成绩的先大后小的顺序显示； SELECT
*
FROM
A,
B
WHERE
A.Sid = B.Sid
ORDER BY
A.Sid ASC,
B.GRADE DESC;
18.请问张老师教什么课程，以及他班上所有学生的姓名； SELECT
*
FROM
A
LEFT JOIN B ON A.Sid = B.Sid
RIGHT JOIN C ON B.Cid = C.Cid
WHERE
C.TEACHER LIKE 'zhang%'
页:
[1]