- SELECT * FROM S;
- SELECT * FROM c;
- SELECT * FROM sc;
- -----------1. 使用标准SQL嵌套语句查询选修课程名称为 理综 的学员学号和姓名
- SELECT s.sid,s.sn FROM s WHERE s.sid in (SELECT sc.sid FROM sc WHERE sc.cid = (SELECT c.cid FROM c WHERE c.cn = '理综'));
- --2. 使用标准SQL嵌套语句查询选修课程编号为’2’的学员姓名和所属单位
- SELECT s.sn,s.sd FROM s WHERE s.sid IN (SELECT sc.sid FROM sc WHERE sc.cid = 2);
- --3. 使用标准SQL嵌套语句查询不选修课程编号为’2’的学员姓名和所属单位
- SELECT s.sn,s.sd FROM s WHERE s.sid NOT IN (SELECT sc.sid FROM sc WHERE sc.cid = 2);
- --4. 使用标准SQL嵌套语句查询 选修全部课程 的学员姓名和所属单位(不会做,先看看第6题)
- SELECT s.sn,s.sd FROM s WHERE s.sid IN (SELECT sc.sid FROM sc GROUP BY sc.sid HAVING COUNT(sc.cid) = (SELECT COUNT(c.cid) FROM c));
- --5. 查询选修了课程的学员人数
- SELECT COUNT(DISTINCT sc.sid) FROM sc;
- --6. 查询选修课程超过3门的学员学号和所属单位
- SELECT s.sn,s.sd FROM s WHERE s.sid IN (SELECT sc.sid FROM sc GROUP BY sc.sid HAVING COUNT(sc.cid) > 3);
- --7. 查询某课程成绩高于平均成绩的学员的姓名和成绩、课程、课程平均成绩
- SELECT s.sn,sc.g,c.cn,a.a FROM s,sc,c,( SELECT sc.cid,AVG(sc.g) a FROM sc GROUP BY sc.cid) a WHERE sc.cid = a.cid AND s.sid = sc.sid AND sc.cid = c.cid AND sc.g > a.a;
- --现有一数据库,包含三张表,每表内容如下:
- --S表
- --SID,SN,SD,SA分别代表学号,学员姓名,所属单位,学员年龄
- --C表
- --CID,CN分别代表课程编号,课程名称
- --SC表
- --SID,CID,G分别代表学号,课程编号,学习成绩
- DROP TABLE S;
- DROP TABLE c;
- DROP TABLE sc;
- CREATE TABLE s (
- SID NUMBER(10) PRIMARY KEY,
- SN VARCHAR2(20),
- SD VARCHAR2(30),
- SA NUMBER(3)
- );
- CREATE TABLE c (
- CID NUMBER(4) PRIMARY KEY,
- CN VARCHAR2(30)
- );
- CREATE TABLE sc (
- SID NUMBER(10),
- CID NUMBER(4),
- G NUMBER(3),
- PRIMARY KEY(SID,cid)
- );
- -----------------------两种插入方式
- INSERT INTO sc(SID,cid,g) VALUES (2011001,001,90);
- INSERT INTO sc(SID,cid,g) VALUES (2011001,002,90);
- INSERT INTO sc(SID,cid,g) VALUES (2011001,003,90);
- INSERT INTO sc(SID,cid,g) VALUES (2011001,004,90);
- INSERT INTO sc(SID,cid,g) VALUES (2011002,001,91);
- INSERT INTO sc(SID,cid,g) VALUES (2011003,001,92);
- INSERT INTO sc(SID,cid,g) VALUES (2011004,002,93);
- INSERT INTO sc(SID,cid,g) VALUES (2011005,002,94);
- INSERT INTO sc(SID,cid,g) VALUES (2011006,002,95);
- INSERT INTO sc(SID,cid,g) VALUES (2011007,003,96);
- INSERT INTO sc(SID,cid,g) VALUES (2011008,003,97);
- INSERT INTO sc(SID,cid,g) VALUES (2011009,004,98);
- INSERT INTO sc(SID,cid,g) VALUES (2011010,004,99);
- INSERT INTO sc(SID,cid,g)
- SELECT 2011001,001,90 FROM dual UNION ALL
- SELECT 2011002,001,91 FROM dual UNION ALL
- SELECT 2011003,001,92 FROM dual UNION ALL
- SELECT 2011004,002,93 FROM dual UNION ALL
- SELECT 2011005,002,94 FROM dual UNION ALL
- SELECT 2011006,002,95 FROM dual UNION ALL
- SELECT 2011007,003,96 FROM dual UNION ALL
- SELECT 2011008,003,97 FROM dual UNION ALL
- SELECT 2011009,004,98 FROM dual UNION ALL
- SELECT 2011010,004,99 FROM dual ;
- INSERT INTO S(SID,sn,sd,sa)
- SELECT 2011001,'jim','DL',90 FROM dual UNION ALL
- SELECT 2011002,'tom','DL',91 FROM dual UNION ALL
- SELECT 2011003,'jerry','DL',92 FROM dual UNION ALL
- SELECT 2011004,'lucy','HTC',93 FROM dual UNION ALL
- SELECT 2011005,'bill','HTC',94 FROM dual UNION ALL
- SELECT 2011006,'warren','HTC',95 FROM dual UNION ALL
- SELECT 2011007,'james','LG',96 FROM dual UNION ALL
- SELECT 2011008,'cart','LG',97 FROM dual UNION ALL
- SELECT 2011009,'lee','LG',98 FROM dual UNION ALL
- SELECT 2011010,'kobe','APPLE',99 FROM dual ;
- INSERT INTO C(CID,CN)
- SELECT 001,'语' FROM dual UNION ALL
- SELECT 002,'数' FROM dual UNION ALL
- SELECT 003,'英' FROM dual UNION ALL
- SELECT 004,'理综' FROM dual;