数据库（Oracle 中25个简单查询）

DDL(数据库定义语言) 创建数据库 创建表、视图 、索引
对数据库或表的结构操作（增删改）


DML(数据操作语言) 对表的记录进行更新（增删改）


DCL(数据控制语言) 用来定义访问权限和安全等级 对用户的创建和授权

DQL(数据查询语言) 用来查询记录 对表的记录的查询

============================================================================

create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
commit;
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS ','t001');
insert into course values ('c009','PHP ','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');

commit;

/*************************************************************************************************************/

Question:

1、查询姓“李”的学生的详细信息
2、查询20-23岁的男同学有多少个
3、查询姓“刘”的老师有多少个
4、查询男生、女生人数（一条SQL）
5、查询'刘阳'老师所教的课程
6、查找学员编号为's004','s007','s008'的学生信息
7、查找教'Oracle'课程的老师
8、查找成绩低于60分的课程名称
9、查找成绩良好（分数>80）的学员名称和课程名称
10、查询平均成绩大于60 分的同学的学号和平均成绩
11、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
12、查询同名同姓学生名单，并统计同名人数
13、1981 年出生的学生名单(注：Student 表中Sage 列的类型是number)
14、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
15、查询课程名称为“Oracle”，且分数低于60 的学生姓名和分数
16、查询所有学生的选课情况；
17、查询不及格的课程，并按课程号从大到小排列
18、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
19、求选了课程的学生人数
20、查询各个课程及相应的选修人数
21、查询不同课程成绩相同的学生的学号、课程号、学生成绩
22、检索“c004”课程分数小于60，按分数降序排列的同学学号
23、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
24、查询没学过“谌燕”老师课的同学的学号、姓名；
25、按各科平均成绩从低到高和及格率的百分数从高到低顺序

Answer:

--1、查询姓“李”的学生的详细信息
select * from student where sname like '李%'
--2、查询20-23岁的男同学有多少个
select count(*) from student where sage>=20 and sage<=23 and ssex='男'
--3、查询姓“刘”的老师有多少个
select count(*) from TEACHER WHERE tname LIKE '刘%'
--4、查询男生、女生人数（一条SQL）
select count(*) from student group by ssex

--5、查询'刘阳'老师所教的课程

select *from course where tno in(select tno from teacher where tname ='刘阳')

--6、查找学员编号为's004','s007','s008'的学生信息
select * from student where sno in('s004','s007','s008');


--7、查找教'Oracle'课程的老师
select tname from teacher where tno in(
select tno from course where cname ='Oracle')

--8、查找成绩低于60分的课程名称

select cname from course where cno in(select cno from sc where score <60)

--9、查找成绩良好（分数>80）的学员名称和课程名称

select (select sname from student where student.sno=sc.sno)as '姓名' ,
(select cname from course where course.cno=sc.cno)as '课程名称'
from sc where score>80
--方法二：多表连接
select S.sname,C.cname from student S,course C,sc
where sc.score>80 and sc.cno=C.cno and sc.sno=S.sno


--10、查询平均成绩大于60 分的同学的学号和平均成绩

select sno , avg(score) as '平均成绩' from sc group by sno having avg(score)>60

--11、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select cno ,max(score)as '最高分', min(score) as '最低分' from sc group by cno

--12、查询同名同姓学生名单，并统计同名人数

select s1.sname,count(*)as '人数' from student s1,student s2
where s1.sno!=s2.sno and s1.sname=s2.sname
group by s1.sname

--13、1991 年出生的学生名单(注：Student 表中Sage 列的类型是number)
select sname from student where YEAR(getdate())-sage=1991

--14、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select cno,AVG(score)as '平均成绩' from sc group by cno order by AVG(score), cno DESC

--15、查询课程名称为“Oracle”，且分数低于60 的学生姓名和分数

select (select sname from student where sno=sc.sno) ,score
from sc where cno in(
select cno from course where cname='Oracle')and score > 60


--16、查询所有学生的选课情况；
select (select sname from student where student.sno=sc.sno)as '学生',(select cname from course where course.cno=sc.cno) from sc


--17、查询不及格的课程，并按课程号从大到小排列

select cno,score from sc where score<60 order by cno DESC

--18、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；

select sno, sname from student where sno in(select sno from sc where cno='c001' and score>80)


-- 19、求选了课程的学生人数
select count(*) from student where exists
(select * from sc where sc.sno=student.sno)

-- 20、查询各个课程及相应的选修人数

select cno, count (*)as'人数' from sc group by cno


--21、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select S1.sno,S1.cno,S1.score from sc S1,sc S2 where S1.cno!=S2.cno and S1.score=S2.score

--22、检索“c004”课程分数小于60，按分数降序排列的同学学号
select sno from sc where score<60 and cno='c004' order by score DESC

--23、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
select S1.sno from sc S1,sc S2 where S1.sno=S2.sno and S1.cno='c001'and S2.cno='c002'

--24、查询没学过“谌燕”老师课的同学的学号、姓名；
select sno,sname from student where sno not in (
select sno from sc where student.sno=sc.sno and cno in (
select cno from course where tno in(select tno from teacher where tname='谌燕')
)
)

--25、按各科平均成绩从低到高 和 及格率的百分数从高到低顺序

--isnull(score,0)
--1.各科平均成绩
select cno ,AVG(score) as '平均成绩'
from sc group by cno order by AVG(score);
--2.及格率的百分数从高到低顺序
select cno,convert(varchar,100*SUM(CASE WHEN score>=60
THEN 1 ELSE 0 END)/count(*))+'%' as '及格百分比'
from sc group by cno
order by 100 * SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/count(*) DESC
---综合起来

select cno, AVG(score) as '平均成绩',
convert(varchar,100*SUM(CASE WHEN score>=60
THEN 1 ELSE 0
END)/count(*))+'%' as '及格百分比'
from sc group by cno order by AVG(score),
100 * SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/count(*) DESC