目录
-- 1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
-- 6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
-- 7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
-- 8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
-- 11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
-- 12、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；-- 相关子查询 ? ?用父表的数据 逐条 遍历子表的数据?
-- 13、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；
-- 15、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；
-- 16、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
-- 17、按各科平均成绩从低到高和及格率的百分数从高到低顺序
-- 19、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
-- 20、查询各科成绩前三名的记录:(不考虑成绩并列情况)
-- 26、1981 年出生的学生名单(注：Student 表中Sage 列的类型是number)
-- 27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
-- 28、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
-- 29、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数
-- 31、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
-- 33、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
-- 35、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
-- 37、查询不同课程成绩相同的学生的学号、课程号、学生成绩
-- 39、统计每门课程的学生选修人数（超过10 人的课程才统计）。-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
-- 43、检索“c004”课程分数小于60，按分数降序排列的同学学号
将两个表拼接在一起，会有n*m个数据，运用笛卡尔积把相匹配的联系起来
--笛卡尔积
SELECT * FROM emp e,dept d；
--等值连接
SELECT * FROM emp e,dept d where e.deptno=d.deptno;
--非等值连接
SELECT * FROM emp e,salgrade s where e.sal between s.losal and s.hisal;
--自连接 找出每个员工和对应的领导 把自身认为是两张表
select e.ename,m.ename from emp e,emp m where e.mgr =m.empno; --会有一个没有领导就不会显示
--左外连接 没有领导的也会显示 保留左边
select e.ename,m.ename from emp e left join emp m on e.mgr =m.empno;
--右外连接 以右边的为主，尽管左边的没有
select e.ename,m.ename from emp e right join emp m on e.mgr =m.empno;
--满外连接 保留两边全部，尽管没匹配到
SELECT * FROM emp e full outer join dept d on e.deptno=d.deptno;通过group by 分组时只能select 分组的依据和计算聚合的结果
SELECT count(ename),
d.dname
FROM emp e
LEFT JOIN dept d
ON e.deptno=d.deptno
GROUP BY d.dname;
--但不能
SELECT count(ename),
d.deptno
FROM emp e
LEFT JOIN dept d
ON e.deptno=d.deptno
GROUP BY d.dname;
--union 并集，所有内容都查询，重复的只显示一次
语句A
union
语句B
--union all 并集 所有都显示
--nimus 差集
--intersect 交集select * from emp where sal > ( select sal from emp where empno =7566);
如果子查询结果是Null则结果也是null--找出部门10工资都高的人
select * from emp where sal > (select max(sal) from emp where deptno=10);--单行
select * from emp where sal > all (select sal from emp where deptno=10);
--找出任意一个比部门10工资高的人
select * from emp where sal > (select min(sal) from emp where deptno=10);--单行
select * from emp where sal > any(select sal from emp where deptno=10);
--找出一个与部门10工资相同
select * from emp where sal in (select sal from emp where deptno=30) and (deptno!=30 or deptno is null);? ?伪列 rowid :数据存储的物理地址? ? rownumber:序号，一定从1开始
注意，*与rownum同时选择可能会出现问题
select e.*,rowid,rownum from emp e;
--如果结果中没有1则不会出现结果
--选择工资前三的人
select t.empno,t.sal,t.rid,t.rno,rownum from
(select e.*, rowid rid,rownum rno from emp e order by sal desc) t
where rownum<=3;
--但要让选择工资从第三到第五，只能进行再次子查询
select p.*,rownum from
(select t.empno,t.sal,rownum ru from
(select e.* from emp e order by sal desc) t
where rownum<=5) p where p.ru>=3;
insert into emp
select 8002,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno
from emp e where e.empno =7844;
commit;1.对重复字段进行分组
2.把区分特征放到select 里
SELECT * FROM emp t where t.empno not in
(select min( e.empno) from emp e group by e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno);
如果都找不到其他区分特征，则rowid是一定可以作为区分特征。
select count(ename),deptno from emp group by deptno;
SELECT * FROM emp;
select count(e.ename),d.dname from emp e left join dept d on e.deptno=d.deptno group by d.dname ;
select e.ename from emp e where e.sal> (select sal from emp where ename ='JONES');
select t.job,t.asl from
(select job,avg(sal) asl,rownum ru from emp group by job order by avg(sal) desc) t
where t.ru<3;
select ename,deptno from emp where sal > all(select sal from emp where deptno=20) and deptno !=20;
select t.job,t.asl from
(select job,avg(sal) asl from emp group by job) t
where t.asl>2000;练习
--列出员工表中每个部门的员工数，和部门 no
select count(ename),deptno from emp group by deptno;
--列出员工表中每个部门的员工数（员工数必须大于 3），和部门名称
select count(e.ename),d.dname from emp e left join dept d on e.deptno=d.deptno group by d.dname ;
--找出工资比 jones 多的员工
select e.ename from emp e where e.sal> (select sal from emp where ename ='JONES');
--列出所有员工的姓名和其上级的姓名
SELECT e.ename,p.ename FROM emp e,emp p where e.empno=p.mgr;
--以职位分组，找出平均工资最高的两种职位
select job from
(select job,avg(sal) from emp group by job order by avg(sal) desc)
where rownum<3;
--查找出不在部门 20，且比部门 20 中任何一个人工资都高的员工姓名、部门名称
select ename,dname from (select *from emp where sal > all(select sal from emp where deptno=20) and (deptno !=20 and deptno is not null) )t1 join dept t2 on t1.deptno=t2.deptno;
--得到平均工资大于 2000 的工作职种
select job,t.asl from
(select job,avg(sal) asl from emp group by job) t
where t.asl>2000;
--分部门得到工资大于 2000 的所有员工的平均工资，并且平均工资还要大于2500
select job,asl from
(select job,avg(sal) asl from emp group by job)
where asl>2000 and asl>2500;
--得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置
select * from
(select t1.deptno,t2.dname,t2.loc from
(select deptno,avg(sal) asl from emp group by deptno)t1 left join dept t2 on t1.deptno=t2.deptno)
where rownum =1 ;
--分部门得到平均工资等级为 2 级（等级表）的部门编号
select deptno from
(select deptno,avg(sal) asl from emp group by deptno) t1,salgrade t2
where t2.grade=3 and t1.asl between t2.losal and t2.hisal;
--查找出部门 10 和部门 20 中，工资最高第 3 名到工资第 5 名的员工的员工名字，部门名字，部门位置
select t1.sal,t1.ename,t1.deptno,t2.loc,t1.ru from
(select sal,ename,deptno,rownum ru from
(select sal,ename,deptno from emp order by sal desc) where rownum<=5) t1
join dept t2 on t1.deptno=t2.deptno
where t1.ru>=3;
--查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入
select t1.ename,t1.sal+nvl(t1.comm,0),t2.ename,t2.empno,t2.sal+nvl(t2.comm,0) from emp t1,emp t2 where t1.empno=t2.mgr and t1.sal+nvl(t1.comm,0)<t2.sal+nvl(t2.comm,0);
--查找出工资等级不为 4 级的员工的员工名字，部门名字，部门位置
select t3.ename,d.dname,d.loc from
(select t1.ename,t1.deptno from emp t1,salgrade t2 where t2.grade!=4 and t1.sal between t2.losal and t2.hisal )t3
join dept d on t3.deptno=d.deptno;
--查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资
select avg(sal) from (select sal from emp where job= any(select job from emp where ename='MARTIN' or ename='SMITH') )
--查找出不属于任何部门的员工
select * from emp where deptno is null;
--按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）
select dname,loc from
(select pn,deptno,rownum ru from
(select count(ename) pn,deptno from emp group by deptno order by count(ename) desc)
where rownum <=5) t left join dept d on t.deptno=d.deptno where t.ru >=2;
--查询出 king 所在部门的部门号\部门名称\部门人数
select t.deptno,d.dname,t.pn from
(select count(ename) pn,deptno from emp group by deptno having deptno =
(select deptno from emp where ename='KING')) t left join dept d on t.deptno= d.deptno;
--查询出 king 所在部门的工作年限最大的员工名字
SELECT ename FROM emp where sysdate-hiredate=
(SELECT max(sysdate-hiredate) FROM emp
where deptno=( select deptno from emp where ename='KING'));
--查询出工资成本最高的部门的部门号和部门名称
select t.deptno,d.dname from
(select deptno ,sum(sal+nvl(comm,0)) from emp group by deptno order by sum(sal+nvl(comm,0)) desc ) t left join dept d on t.deptno=d.deptno
where rownum=1;
--创建表
create table student(
sno varchar2(10) primary key, --varchar2 表示可变字符串， primary key主键
sname varchar2(20),
sage number(3),
ssex varchar2(5)
);
commit;--提交
--查看
select * from user_tables;
--添加列
alter table student add phone varchar2(11)
--删除列，列中不能有数据
alter table student drop column phone;
--修改列
alter table student modify phone int;
--修改表名
alter table student rename to tb_student;--插入
insert into student values ('s001','张三',23,'男');
--用查询结果插
insert into student
select s0003,name,age from student where sno=2;
--修改
update student set sname='李思' where sno='s001';
--删除 用主键
delete from student where sno = 3;前提
/**********************创建表*************************/
create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(3),
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;
-- sc?
SELECT t1.sno
FROM sc t1,
? sc t2
WHERE t1.sno = t2.sno
AND t1.cno ? = 'c001'
AND t2.cno ? = 'c002'
AND t1.score > t2.score;
-- 能用连接查询,尽量使用连接查询
SELECT t1.sno
FROM sc t1
JOIN sc t2
ON t1.sno ? ?= t2.sno
AND t1.cno ? = 'c001'
AND t2.cno ? = 'c002'
AND t1.score > t2.score;-- sc?
SELECT sno,AVG(score) FROM sc GROUP BY sno HAVING AVG(score) > 60;
-- student,sc?
-- 以sc 查 sno count(cno) sum(score)
-- 以 sno ?和 student 关联
select s.sno,s.sname, nvl(t.count_sno,0),nvl(t.sum_score,0) from student s left join?
(select sno,count(cno) count_sno,sum(score) sum_score from sc group by sno) t on s.sno = t.sno;
-- 直接关联 student,sc?
select s.sno,s.sname,count(sc.cno),nvl(sum(sc.score),0) from student s left join sc?
on s.sno = sc.sno group by s.sno,s.sname order by count(sc.cno) desc;
select s.sno,max(s.sname),count(sc.cno),nvl(sum(sc.score),0) from student s left join sc?
on s.sno = sc.sno group by s.sno order by count(sc.cno) desc;-- teacher
select count(t.tname) from teacher t where t.tname like '刘%';-- teacher student course sc?
select * from student s where s.sno not in
(select distinct sc.sno from teacher t
join course c on t.tno = c.tno and t.tname = '谌燕'
join sc on c.cno = sc.cno);-- student sc?
select s.sno,s.sname from student s join?
(select distinct t1.sno from sc t1 join sc t2 on t1.sno= t2.sno and t1.cno = 'c001' and t2.cno = 'c002') t
on s.sno = t.sno;
select s.sno,s.sname from student s where s.sno in?
(select distinct t1.sno from sc t1 join sc t2 on t1.sno= t2.sno and t1.cno = 'c001' and t2.cno = 'c002');
select * from student s where s.sno in
(select distinct sc.sno from teacher t
join course c on t.tno = c.tno and t.tname = '谌燕'
join sc on c.cno = sc.cno);select t4.sno,t4.sname from
(select t1.sno from
(select * from sc where cno = 'c001') t1
join (SELECT * FROM sc where cno ='c002') t2 on t1.sno=t2.sno
where t1.score> t2.score)t3 join student t4 on t3.sno=t4.sno;-- sc?
select * from student s1 where s1.sno in
(select s.sno from sc s where s.sno not in
(select distinct sno from sc where score > 60));
update sc set score =50;
rollback;
select * from?
(select sno,count(cno) cou from sc group by sno) t1
join?
(select sno,count(cno) cou from sc where score < 60 group by sno) t2
on t1.sno = t2.sno and t1.cou = t2.cou;select s.sno,max(s.sname ),count(sc.cno) from student s left join sc?
on s.sno = sc.sno ?group by s.sno having count(sc.cno) ?<
(select count(cno) from course);select distinct s.sno,s.sname from student s join sc on s.sno = sc.sno and s.sno != 's001'
and sc.cno in (select sc.cno from sc where sc.sno = 's001');
insert into sc values ('s005','c004',60);
insert into sc values ('s005','c001',60);
rollback;update sc t1 set t1.score =?
(select avg(sc.score) from teacher t?
join course c on t.tno = c.tno and t.tname = '谌燕'
join sc on c.cno = sc.cno group by sc.cno having t1.cno = sc.cno)
where t1.cno in
(select c.cno from teacher t join course c on t.tno = c.tno and t.tname = '谌燕');
select * from sc where sc.cno in
(select c.cno from teacher t join course c on t.tno = c.tno and t.tname = '谌燕');
rollback;--先找到有哪些课程，然后通过in留下在这些课程里，在对每个学生数数，看是否是这个课程数，但有可能结果会比s001的课程多因为只留下的是符合s001的数，可以minus sc中的cno不在课程里的
(select sno from sc where cno in
(select sc.cno from sc where sc.sno = 's001') and sno != 's001' group by sno?
having count(cno) = (select count(sc.sno) from sc where sc.sno = 's001'))
minus
(select sno from sc where cno not in (select sc.cno from sc where sc.sno = 's001'));
select * from sc order by sno;
delete from sc where sno= 's002' and cno = 'c004';
insert into sc values ('s002','c003',60);
insert into sc values ('s002','c004',60);
rollback;
select t1.sno,t1.cno,t2.sno,t2.cno from?
(select * from sc where sno !='s001') t1
?join
(select * from sc where sno ='s001') t2?
on t1.cno = t2.cno;
select t1.sno from?
(select * from sc where sno !='s001') t1
join
(select * from sc where sno ='s001') t2?
on t1.cno = t2.cno group by t1.sno having count(t2.cno) ?=3
minus
(select sno from sc where cno not in (select sc.cno from sc where sc.sno = 's001'));
---可以先剔除所选课程不在s001中，再数数对比
select count(cno),sno from sc where sno not in
(select distinct sno from sc where cno not in
(select cno from sc where sno='s001'))
group by sno having sno!='s001' and
count(cno)=(select count(cno) from sc where sno='s001');delete from sc where sc.cno in?
(select c.cno from course c where c.tno =
(select t.tno from teacher t where t.tname = '谌燕'));
rollback;
select * from sc;insert into sc?
select sno,'c002',(select avg(score) from sc where cno = 'c002') from student where sno not in
(select sno from sc where cno = 'c002');
rollback;select cno,max(score),min(score) from sc group by cno;SELECT cno,
? AVG(score),
? SUM(
? CASE
? ? WHEN score >= 60
? ? THEN 1
? ? ELSE 0
? END )/COUNT(score)*100
? || '%'
FROM sc
GROUP BY cno
ORDER BY SUM(
? CASE
? ? WHEN score >= 60
? ? THEN 1
? ? ELSE 0
? END )/COUNT(score) DESC;select c.tno,sc.cno,avg(score) from course c join sc on c.cno = sc.cno group by c.tno,sc.cno
order by avg(score) desc;--也可以(select count(sno) from sc ?where ?sc.cno=t1.cno and score between 85 and 100 ),
select c.cno as "课程ID",c.cname as "课程名称",
sum(case when sc.score > 85 then 1 else 0 end ) as "[100-85]",
sum(case when sc.score > 70 and sc.score <=85 then 1 else 0 end ) as "[85-70]",
sum(case when sc.score >= 60 and sc.score <=70 then 1 else 0 end ) as "[70-60]",
sum(case when sc.score <60 then 1 else 0 end ) as "[ <60]"?
from course c join sc on c.cno = sc.cno group by c.cno,c.cname;-- row_number() over()
select * from sc order by cno;
-- 查询 各科 成绩比自己高的人数 <=2?
-- 相同课 ?p.score < c.score ? count <=2?
select * from sc p where?
(select count(sno) from sc c where p.cno = c.cno and p.score < c.score) <=2 order by cno,score desc;
select * from?
(select ?sc.*,row_number () over (partition by cno
? ? ? ? ? ? ? ? ? ? ? ? ? ?order by score desc) rn from sc ) t where t.rn <=3 ;
-- 考虑成绩并列情况select c.cno,max(c.cname),count(sc.sno) from course c left join sc on c.cno = sc.cno group by c.cno;select s.sno,max(s.sname) from student s join sc on s.sno = sc.sno group by s.sno having count(sc.sno) =1;select ssex,count(sno) from student group by ssex;select * from student where sname like '张%';select sname,ssex,count(sname) from student group by sname,ssex;select * from student where age = extract(year from sysdate)-1981;select cno,avg(score) from sc group by cno order by avg(score),cno desc;select sc.sno,max(s.sname),avg(sc.score) from sc join student s on sc.sno = s.sno group by sc.sno;select s.sname,sc.score from student s?
join sc on s.sno = sc.sno and sc.score < 60
join course c on sc.cno = c.cno and c.cname = '数据库';select * from student s
left join sc on s.sno = sc.sno;select s.sname,sc.score,c.cname from student s?
join ?(select sno from sc group by sno having min(score) > 70) t on s.sno = t.sno
join sc on t.sno = sc.sno
join course c on sc.cno = c.cno;select * from sc where score < 60 order by cno desc;select s.sno,s.sname,sc.score from student s?
join sc on s.sno = sc.sno and sc.score > 80;select count(distinct sno) from sc;select t.sname,t.score from?
(select * from student s
join sc on s.sno = sc.sno
join course c on c.cno = sc.cno
join teacher t on c.tno = t.tno and t.tname = '谌燕'?
order by sc.score desc) t?
where rownum = 1;select cno,count(sno) from sc group by cno;select t1.* from sc t1 , sc t2 where t1.cno != t2.cno and t1.score = t2.score and t1.sno = t2.sno;select * from sc p where?
(select count(sno) from sc c where p.cno = c.cno and p.score < c.score ) <=1;?select cno,count(sno) from sc group by cno having count(sno) > 10 order by count(sno) desc ,cno asc;select sno,count(cno) from sc group by sno having count(cno) >=2;select sc.cno,count(sc.sno),max(c.cname) from course c?
join sc on c.cno = sc.cno group by sc.cno?
having count(sc.sno) = (select count(sno) from student);select sno,count(cno) from sc where score < 60 group by sno having count(cno)>2;select sno from sc where score <60 and cno = 'c004' order by score asc;delete from sc where sno= 's002' and cno = 'c001';
rollback;