--题目并没有完成,好多都在更新中
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;