Oracle的综合查询练习

--逆水行舟，不进则退。
--题目并没有完成，好多都在更新中
create table s
  (
    sno   varchar2(3) primary key,
    sname varchar2(10),
    sex   char(2),
    birth date
  );
--创建课程信息表
create table c
  (
    cno     varchar2(3) primary key,
    cname   varchar(10),
    tercher varchar2(20)
  );
--创建学生选课表信息
create table sc
  (
    sno   varchar2(3),
    cno   varchar2(3),
    grade number(3)
  );
--插入一些数据
INSERT
INTO S VALUES
  (
    'S01',
    'BAO',
    '男',
    to_date('1995-03-07','yyyy-mm-dd')
  );
INSERT INTO S VALUES
  ('S02','GU','男',to_date('1997-08-15','yyyy-mm-dd')
  );
INSERT INTO S VALUES
  ('S03','AN','女',to_date('1996-11-21','yyyy-mm-dd')
  );
INSERT INTO S VALUES
  ('S04','LI','女',to_date('1992-05-01','yyyy-mm-dd')
  );
INSERT INTO S VALUES
  ('S05','ZHAO','男',to_date('1990-07-01','yyyy-mm-dd')
  );
INSERT INTO C VALUES
  ('C01', 'DB', 'SMITH'
  );
INSERT INTO C VALUES
  ('C02', 'MIS', 'STONE'
  );
INSERT INTO C VALUES
  ('C03', 'OS', 'JACK'
  );
INSERT INTO C VALUES
  ('C04', 'C', 'MIKE'
  );
INSERT INTO C VALUES
  ('C05', 'B', 'MIKE'
  );
INSERT INTO C VALUES
  ('C06', 'DB', 'SMITH'
  );
delete C where cno='C06';
INSERT INTO SC VALUES
  ( 'S01', 'C01', 80
  );
INSERT INTO SC VALUES
  ( 'S01', 'C02', 90
  );
INSERT INTO SC VALUES
  ( 'S01', 'C03', 100
  );
INSERT INTO SC VALUES
  ( 'S01', 'C04', 80
  );
INSERT INTO SC VALUES
  ( 'S02', 'C01', 79
  );
INSERT INTO SC VALUES
  ( 'S02', 'C02', 85
  );
INSERT INTO SC VALUES
  ( 'S02', 'C03', 65
  );
INSERT INTO SC VALUES
  ( 'S03', 'C02', 50
  );
INSERT INTO SC VALUES
  ( 'S03', 'C03', 72
  );
INSERT INTO SC VALUES
  ( 'S03', 'C04', NULL
  );
INSERT INTO SC VALUES
  ( 'S04', 'C04', 56
  );
INSERT INTO SC VALUES
  ( 'S05', 'C04', 65
  );
INSERT INTO SC VALUES
  ( 'S01', 'C05', 80
  );
INSERT INTO SC VALUES
  ( 'S03', 'C01', 80
  );
INSERT INTO SC VALUES
  ( 'S04', 'C01', 80
  );
INSERT INTO SC VALUES
  ( 'S05', 'C01', 80
  );
--查看表中的数据
select * from s;
select * from sc;
select * from c;
--练习
--1. 试用SELECT语句实现以下查询语句。
--① 检索学习课程号为C02的学生学号与成绩。
select sno 学号,
  grade 成绩
from sc
where cno='C02';
--思考题：检索学习课程号为C02的学生学号，姓名与成绩。
select s.sno 学号,
  s.sname 姓名,
  sc.grade 成绩
from s,
  sc
where s.sno=sc.sno
and sc.cno ='C02';
--② 检索所有女同学的姓名和年龄。
select sname 姓名,
  floor((sysdate-birth)/365) 年龄
from s
where gender='女';
--③ 检索至少选修JACK老师所授课程中一门课程的学生学号。
select sno 学号
from sc
where cno in
  (select cno from c where teacher='JACK'
  );
--思考题：选修JACK老师所授课程中一门课程的学生学号、姓名。
select sc.sno 学号,
  s.sname 姓名
from s,
  sc
where s.sno =sc.sno
and sc.cno in
  (select cno from c where teacher='JACK'
  );
--④ 检索至少选修课程号为C01和C03的学生学号。
select x.sno 学号
from sc x,
  sc y
where x.sno=y.sno
and x.cno  ='C01'
and y.cno  ='C03';
--思考题2：检索至少选修两门课程的学生的学号。(漏写distinct)
select distinct x.sno 学号
from sc x,
  sc y
where x.sno=y.sno
and x.cno <>y.cno;
--TT
SELECT DISTINCT x.sno
FROM sc x,
  sc y
WHERE x.sno = y.sno
AND x.cno  <>y.cno;
--思考题3：检索至少有学号为S02和S03学生选修的课程的课程号。
select x.cno 课程号
from sc x,
  sc y
where x.cno=y.cno
and x.sno  ='S02'
and y.sno  ='S03';
--TT
SELECT x.cno
FROM sc x,
  sc y
WHERE x.cno = y.cno
AND x.sno   = 'S02'
AND y.sno   ='S03';
--④ 检索不学C04课程的学生姓名，性别。
select sname 姓名,
  gender 性别
from s
where sno not in
  (select sno from sc where cno='C04'
  );
--TT
SELECT sname，gender
FROM s
WHERE sno NOT IN（SELECT sno
FROM sc
WHERE cno = 'C04'）;
--思考题：检索学号为S03学生不学的课程的课程号和课程名；
select cno 课程号,
  cname 课程名
from c
where cno not in
  (select cno from sc where sno='S03'
  );
--TT
SELECT cno，cname
FROM c
WHERE cno NOT IN（SELECT cno
FROM SC
WHERE sno ='S03'）;
--⑤ 检索没有选修课程的学生学号和姓名。
select sno 学号,
  sname 姓名
from s
where sno not in
  (select distinct sno from sc
  );
--思考题：检索没有被学生选修的课程编号和课程名。
select cno 课程编号,
  cname 课程名
from c
where cno not in
  (select distinct cno from sc
  );
--TT
SELECT cno,cname FROM c WHERE cno NOT IN（SELECT DISTINCT cno FROM sc ）;
--********⑥ 检索学习全部课程的学生学号和姓名。
select sno 学号,
  sname 姓名
from s
where not exists
  ( select cno from c where cno not in
    (select cno from sc where sc.sno=s.sno
    )
  );
--思考题：检索被所有学生选修的课程编号和姓名。
select cno 课程编号,
  cname 课程名
from c
where not exists
  ( select sno from s where sno not in
    (select sno from sc where c.cno=sc.cno
    )
  );
--TT
SELECT cno,
  cname
FROM c
WHERE NOT EXISTS （SELECT *
FROM s
WHERE NOT EXISTS （SELECT *
FROM sc
WHERE sc.sno =s.sno
AND sc.cno   = c.cno））;
--2. 对于上例教学数据库中基本表S、SC、C的数据进行查询和计算。
select * from s;
select * from sc;
select * from c;
--① 求男学生的总人数和平均年龄。
SELECT count(*) 人数,
  avg(2013   -to_number(substr(to_char(birth,'yyyy-mm-dd'),1,4))) 平均年龄
FROM s
WHERE gender ='男';
select count(       *) 总人数,
  floor(avg((sysdate-birth)/365)) 平均年龄
from s
where gender='男';
--② 统计选修了课程的学生人数。
SELECT COUNT（distinct sno）选修课程的总人数 FROM sc;
--③ 统计每门课程的选修人数，要求显示课程号和学生人数。
select cno 课程编号,
  count(sno) 学生人数
from sc
group by cno;
--思考题：统计每门课程的选修人数，要求显示课程编号，课程名和学生人数。
select cno 课程编号,
  (select cname from c where sc.cno=c.cno
  ) 课程名,
  count(sno) 学生人数
from sc
group by cno;
--TT
SELECT sc.cno 课程编号,
  (select cname from c where c.cno=sc.cno
  ) 课程名,
  COUNT（sno）选修人数
FROM sc
GROUP BY cno;