一 使用CREATE TABLE语句和INSERT INTO语句创建表和插入数据。
student表
course表
sc表
二 操作数据库
1.分别查询学生表和学生修课表中的全部数据。
SELECT * FROM student;
SELECT * FROM course;
2.查询成绩在70到80分之间的学生的学号、课程号和成绩。
SELECT sno,cno,grade FROM sc WHERE grade BETWEEN 70 AND 80;
3.查询100号课程成绩最高的分数。
SELECT MAX(grade) FROM sc WHERE cno = 100;
4.查询学生都选修了哪些课程,要求列出课程号。
SELECT DISTINCT c.cno,cname FROM course c,sc WHERE c.cno = sc.cno;
SELECT DISTINCT c.cno,cname FROM course c INNER JOIN sc on c.cno = sc.cno;
5.查询修了200号课程的所有学生的平均成绩、最高成绩和最低成绩。
SELECT AVG(grade),MAX(grade),MIN(grade) FROM sc WHERE cno = 200;
6.统计每个系的学生人数。
SELECT sdept,count(*) FROM student GROUP BY sdept;
7.统计每门课程的修课人数和考试最高分。
SELECT cno,count(*),MAX(grade) FROM sc GROUP BY cno ORDER BY cno;
8.统计每个学生的选课门数,并按选课门数的递增顺序显示结果。
SELECT sno,count(*) FROM sc GROUP BY sno ORDER BY count(*) ASC;
9.统计选修课的学生总数和考试的平均成绩。
SELECT COUNT(DISTINCT sno) stu_count,AVG(grade) FROM sc;
10.查询选课门数超过2门的学生的平均成绩和选课门数。
SELECT AVG(grade),COUNT(*) FROM sc GROUP BY cno HAVING count(*)>2;
11.列出总成绩超过200分的学生,要求列出学号、总成绩。
SELECT sno,SUM(grade) FROM sc GROUP BY sno HAVING SUM(grade) > 200;
12.查询选修了c02号课程的学生的姓名和所在系。
SELECT sname,sdept FROM student WHERE sno IN(SELECT sno FROM sc WHERE cno = 200);
13.查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。
SELECT sname,cno,grade FROM student,(SELECT * FROM sc WHERE grade > 80) scc
WHERE student.sno = scc.sno ORDER BY scc.grade DESC;
SELECT sname,cno,grade FROM student INNER JOIN(SELECT * FROM sc WHERE grade > 80) scc
ON student.sno = scc.sno ORDER BY scc.grade DESC;
14.查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。
SELECT sname,ssex,grade FROM student,(SELECT sno,grade FROM sc WHERE cno IN(SELECT cno FROM course WHERE cname = '高等数学')) newsc
WHERE student.sno = newsc.sno AND sdept = '計算机系' AND ssex = '男';
SELECT sname,ssex,grade FROM (SELECT sno,sname,ssex FROM student WHERE sdept = '計算机系' AND ssex = '男') newstu,
(SELECT sno,grade FROM sc WHERE cno IN(SELECT cno FROM course WHERE cname = '高等数学')) newsc
WHERE newstu.sno = newsc.sno;
15.查询哪些课程没有人选,要求列出课程号和课程名。
SELECT cname,cno FROM course WHERE cno NOT IN(SELECT cno FROM sc GROUP BY cno);
16.查询有考试成绩的所有学生的姓名、修课名称及考试成绩,要求将查询结果放在一张新的永久表(假设新表名为new-sc)中。
CREATE TABLE new_sc AS
SELECT sname,cname,grade FROM course,student,(SELECT * FROM sc WHERE grade is not NULL) nsc WHERE
course.cno = nsc.cno AND student.sno = nsc.sno;
17.分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,并要求将这两个查询结果合并成一个结果集,并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。
SELECT sdept,sname,ssex,cname,grade FROM course,(SELECT * FROM sc,(SELECT * FROM student WHERE sdept = '計算机系') nsc WHERE
sc.sno = nsc.sno) sc_stu WHERE course.cno = sc_stu.cno
UNION
SELECT sdept,sname,ssex,cname,grade FROM course,(SELECT * FROM sc,(SELECT * FROM student WHERE sdept = '情報系') nsc WHERE
sc.sno = nsc.sno) sc_stu WHERE course.cno = sc_stu.cno;
18.用子查询实现如下查询:
(1) 查询选修了100号课程的学生的姓名和所在系。
SELECT sname,sdept FROM student WHERE sno IN(SELECT sno FROM sc WHERE cno = 100);
(2) 查询数学系成绩80分以上的学生的学号、姓名。
SELECT sno,sname FROM student WHERE sno IN(SELECT sno FROM sc WHERE grade >80) AND sdept = '数学系';
(3) 查询计算机系学生所选的课程名。
SELECT cname FROM course WHERE cno IN(SELECT cno FROM sc WHERE sno IN(SELECT sno FROM student WHERE sdept = '計算机系'));
19.将计算机系成绩高于80分的学生的修课情况插入到另一张表中,分两种情况实现:
(1) 在插入数据过程中建表。
CREATE TABLE sc_info1 AS
SELECT * FROM sc WHERE sno IN(SELECT sno FROM student WHERE sdept = '計算机系') AND grade > 80;
(2) 先建一个新表,然后再插入数据。 创建表SC_Info1 往表SC_info2插入查询得到的结果
CREATE TABLE sc_info2(
sno NUMBER,
cno NUMBER,
grade NUMBER
);
INSERT INTO sc_info2(
SELECT * FROM sc WHERE sno IN(SELECT sno FROM student WHERE sdept = '計算机系') AND grade > 80
);
--下面这种方法更容易理解一些
INSERT INTO sc_info2(
sno,
cno,
grade
)
SELECT * FROM sc WHERE sno IN(SELECT sno FROM student WHERE sdept = '計算机系') AND grade > 80
;
20.删除修课成绩小于50分的学生的修课记录。
DELETE FROM sc WHERE grade < 50;
21.将所有选修了'100'课程的学生的成绩加10分。
UPDATE sc SET grade = grade+10 WHERE cno = 100;
三 附加题 1.
SELECT s1.storeno,s1.store_name
FROM store s1,
(SELECT storeno,MAX(start_date) AS start_date
FROM store
WHERE start_date <= TO_DATE('2016011','YYYYMMDD')
AND over_date >= TO_DATE('2016011','YYYYMMDD')
GROUP BY storeno) s2
WHERE s1.storeno = s2.storeno
AND s1.start_date = s2.start_date;