Oracle的sql语句练习题含答案（一）
--1、选择部门30中的雇员
select * from emp where deptno=30;
--2、列出所有办事员的姓名、编号和部门
select ename,empno,dname from emp e inner join dept d on e.deptno = d.deptno where job=upper('clerk’);
--3、找出佣金高于薪金的雇员
select * from emp where comm>sal;
--4、找出佣金高于薪金60%的雇员
select * from emp where comm>sal*0.6
--5、找出部门10中所有经理和部门20中的所有办事员的详细资料
select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk '));
--6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')) or (job<>upper(‘manager’) and job<>upper(‘clerk’) and sal>=2000)
--7、找出收取佣金的雇员的不同工作
select distinct job from emp where comm>0;
--8、找出不收取佣金或收取的佣金低于100的雇员
select * from emp where nvl(comm,0)<100;
--9、找出各月最后一天受雇的所有雇员
select * from emp where hiredate= last_day(hiredate);
--10、找出早于25年之前受雇的雇员
select * from emp where months_between(sysdate,hiredate)/12>25;
select * from emp where hiredate<add_months(sysdate,-12*25);
--11、显示只有首字母大写的所有雇员的姓名
select ename from emp where ename=initcap(ename);
--12、显示正好为6个字符的雇员姓名
select ename from emp where length(ename)=6
--13、显示不带有'R'的雇员姓名
Select ename from emp where ename not like ‘%R%’;
Select ename from emp where instr(ename,’R’)=0;
--14、显示所有雇员的姓名的前三个字符
select substr(ename,1,3) from emp
--15、显示所有雇员的姓名，用a替换所有'A'
Select replace(ename,’A’,’a’) from emp
--16、显示所有雇员的姓名以及满10年服务年限的日期
Select ename,add_months(hiredate,12*10) ‘服务年限的日期’ from emp
--17、显示雇员的详细资料，按姓名排序
Select * from emp order by ename
--18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
Select ename from emp order by hiredate
--19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
Select ename,job,sal from emp order by job desc ,sal asc
--20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面
select ename,to_char(hiredate,'yyyy'),to_char(hiredate,'mm') from emp order by hiredate asc
--21、显示在一个月为30天的情况下所有雇员的日薪金
select ename,sal/30 from emp;
--22、找出在（任何年份的）2月受聘的所有雇员
select * from emp where to_char(hiredate,'mm')='02';
--23、对于每个雇员，显示其加入公司的天数
select ename,sysdate-hiredate from emp
--24、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名
select ename from emp where ename like '%A%';
select ename from emp where instr(ename,’A’,1)>0;
--25、以年、月和日显示所有雇员的服务年限
Select months_between(sysdate,hiredate)/12 as “年”, months_between(sysdate,hiredate) as “月”, sysdate-hiredate as “日” from emp
SQL查询语句复习题
2011-09-28 11:43:57| 分类： SQL语句 |字号 订阅
SQL查询语句复习题
新建学生-课程数据库的三个表:
学生表:Student(Sno,Sname,Ssex,Sage,Sdept) Sno为主码;
课程表:Course(Cno,Cname,Cpno,Credeit) Cno为主码;
学生选修表:SC(Sno,Cno,Grade) Sno,Cno,为主码;
Student
学号
Sno姓名
Sname性别
Ssex年龄
Sage所在系
Sdept
95001李勇男20CS
95002刘晨女19IS
95003王敏女18MA
95004张立男19IS
课程号
Sno课程名
Cname先行课
Cpno学分
Credit
1数据库54
2数学2
3信息系统14
4操作系统63
5数据结构74
6数据处理2
7Pascal语言64
Course:
SC:
学号
Sno课程号
Cno成绩
Grade
95001192
95001285
95001388
95002290
95002380
一:查询表中的列和行
1:查询全体学生的学与姓名
sele sno,sname from student
2:查询全体学生的姓名、学号、所在系。
sele sno,sname,sdept from student
3:查询全体学生的详细记录
sele * from student
4:查询全体学生的姓名及出生年份
sele sno,sage from student
5:查询全体学生的姓名,出生年份及所在系,要用小写字母表示系名
6:查询选修了课程的学生学号
sele sno,cno from sc
7:查询选修了课程的学生姓名
sele distinct sname from student,sc where student.sno=sc.sno
二:条件查询:
常用的查询条件
查询条件谓词
比较=,<,>,>=,<=,!=,<>,!>,!<;
not+上述比较运算符
确定范围Between and,Not between And,
确定集合IN,not IN
字符匹配Like,Not Like
空值IsNull,ISNOTNULL
多重条件AND,OR
1:查询计算机系全体学生的姓名
sele sname from student where sdept=”CS”
2:查询所有年龄在20岁以下的学生姓名及其年龄
sele sname,sage from student where sage<20
3:查询考试成绩有不及格的学生的学号
sele student.sno from student,sc where student.sno=sc.sno and grade<60
4:查询年龄在20到23间的学生的姓名,系别及年龄
sele sname,sdept,sage from student where sage between 20 and 23
5: 查询年龄不在20到23间的学生的姓名,系别及年龄
sele sname,sdept,sage from student where sage not between 20 and 23
6:查询信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别
sele sname,ssex from student where sdept in("IS","MA","CS")
7:查询不是信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别
sele sname,ssex from student where sdept not in("IS","MA","CS")
8:查询学号为”95001”的学生详细情况
sele * from student where sno=95001
9:查询所有姓刘的学生的姓名,学号和性别(where name like ‘刘%’)
sele sname,sno,ssex from student where sname like '刘%'
10:查询姓”欧阳”且命名为三个汉字的学生的姓名
sele sname from student where sname like '欧阳_'
11:查询名字中第2个字为”阳”字的学生姓名和学号(where sname like ‘_ _阳%’)
sele sname,sno from student where sname like '_ _阳%'
12:查询所有不姓刘的学生姓名
sele sname from student where sname not like '刘%'
13:查询DB_Design课程的课程号和学分(where cname like ‘Db_Design’Escape’’)
sele cno,gredit from course where cname like ‘Db_Design’Escape’’
14:查询以”DB_”开头,且倒数第3个字符为i的课程的详细情况(where cname like ‘DB_%i__’escape’’)
‘DB_%i__’escape’’) sele cno,gredit from course where cname like ‘Db_%i__’escape’’
15:查询缺少成绩的学生的学号和相应的课程号
sele student.sno,cno from student,sc where grade is null
16:查询所有成绩的学生学号和课程号(where grade is not null)
sele student.sno,cno from student,sc where grade is not null
17:查询计算机系年龄在20岁以下的学生姓名
sele sname from student where sdept=”CS” and sage<20
18:查询选修了3号课程的学生的学号及其成绩,分数降序排列
sele student.sno,grade from student,sc where student.sno=sc.sno and sc.cno=3 order by grade desc
19:查询全体学生情况,结果按所在系的号升序排列,同一系中的学生按年龄降序
sele * from student order by sdept,sage desc
三:使用集函数
count,sum,avg,max,min
1:查询学生的总人数
2:查询选修了课程的学生人数(select count(distinct sno))
3:计算1号课程的学生平均成绩
4:查询选修1号课程的学生最高分数
5:求各个课程号及相应的选课人数( selsect cno,count (sno); from sc; group by cno)
6:查询选修了3门以上的课程的学生学号
select sno
from sc
group by sno
having count(*)>3
四:连接查询:
<1>等值与非等值的连接查询
在连接查询中用来连接两个有的条件称为连接条件或连接谓词,,当连接运算符号为”=”时,称为等值连接,使用如,=,<,>,<=,>=,!=连接时称非等值连接
1:查询每个学生及其选修课程的情况
select student.*,sc.*
from student,sc
where student.sno=sc.sno
<2>自身连接
连接操作在同一个表中进行连接查询
2:查询每一门课的间接先修课(即先修课的先修课)
select first .cno,second.cno
from course first ,course second
where first.cno=second.cno
五：复合条件连接
1:查询选修2号课程且成绩在90分以上的所有学生。
Select student,sname
form student, sc
Where student.sno=sc.sno And
Sc.cno=’2’ and sc.grade>90
六：嵌套查询
1：带有谓词in的子查询
<1>查询与“刘晨”在同一个系学习的学生
select sno,sname,sdept
from student
where sdept in(
select sdept
from student
where sname=”刘晨”）
或：select s1.sname,s1.sdept
from student s1,student s2
where s1.dept=s2.dept and s2.name=”刘晨”
<2>查询选修了课程名为“信息系统”的学生学号和姓名
select sno,sname
from student
where sno in
( select sno
from sc
where cno in
(select cno
from course
where cname-“信息系统”)
或：select sno,sname
from student,sc,course
where student.sno=sc.sno and
sc.cno=course.cno and
course.cname=’信息系统’)
2:带有Any 或all谓词的子查询
<1>查询其他系中比信息系中某一学生年龄小的学生姓名和年龄
select sname, sage
from student
where sage <any(select sage
from student
where sdept=’is’
and sdept<>’is’
或用集函数：select sname, sage
from student
where sage<
(select max(sage)
from student
where sdept=’is’)
and sdept<>’is’
<2> 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄
select sname, sage
from student
where sage<all
(select sage
from student
where sdept=’is’)
and sdept<>’is’
3 带有Exitst谓词的子查询
<1>查询所有选修了1号课程的学生姓名
select sname
from student
where exists
(select *
from sc
where sno=student.sno and cno=’1’)
<2>查询没有选修1号课程的学生姓名
select sname
form student
where not exists
(select *
form sc
where sno=stuedent.sno and cno=’1’)
<2>查询选修所有全部课程的学生姓名
select sname
from student
where not exists
(select *
from course
where not exists
(select *
from sc
where sno=student.sno
and cno=course.cno)
<3>查询到少选修了学生95002选修的全部课程的学生号码
select distinct sno
from sc scx
where not exists
( select *
from sc scy
where scy.sno=’95002’ and
not exists
( select *
from sc scz
where scz.sno=scx.sno and
scz.cno=scy.cno)
oracle中sql语句练习
(2012-12-16 19:17:49)
转载▼
标签：
oracle
sql语句
基本练习
it
分类： 个人
--1、查询C01课程成绩不为Null的学生的姓名和成绩
select sname,grade
from sc,student
where sc.sno=student.sno
and cno='1'
and grade is not null;
--2、查询平均分高于80分的女同学的学号，姓名，平均成绩。
select student.sno,sname,avg(grade) as 平均成绩
from student,sc
where student.ssex='女'
and student.sno=sc.sno
group by student.sno,sname
having avg(grade)>=80;
--3、查询CS系学生“数据库”课程的最高分，列出姓名和最高分。
select sname,grade
from student,sc
where student.sno=sc.sno
and sdept='cs' and
grade in
(select max(grade)
from sc
where cno in (
select cno from course
where cname='数据库')
);
--4、查询总学分在8分以上的学生的平均成绩，列出学号，平均成绩
select student.sno,avg(grade)平均成绩
from student,sc,course
where student.sno=sc.sno
and course.cno=sc.cno
group by student.sno
having sum(credit )>8;
--5、查询所有18岁以上学生的选课门数，列出学号，姓名，年龄，选课门数。
select student.sno,sname,sage,count(cno)选课门数
from student,sc
where student.sno=sc.sno
and sage>18
group by student.sno,sname,sage;
--6、建立一个用户U1，给用户赋予Connect和DBA权限，并用该用户登录数据库。
create user u1 identified by user1;
grant connect,resource to u1;
grant dba to u1;
--7、用System用户登录，收回用户U1的DBA权限。
revoke dba from u1;
--8、删除所有CS系不及格的选课信息。
delete from sc
where grade<60
and sno in (select sno from student
where sdept='cs');
--9、将平均分不及格的学生成绩修改为空。
update sc set grade=null
where sc.sno in
( select sno from sc
group by sno
having avg(grade)<60);
commit;
Oracle的sql语句练习题含答案
--1、选择部门30中的雇员
select * from emp where deptno=30;
--2、列出所有办事员的姓名、编号和部门
select ename,empno,dname from emp e inner join dept d on e.deptno = d.deptno where job=upper(’clerk’);
--3、找出佣金高于薪金的雇员
select * from emp where comm>sal;
--4、找出佣金高于薪金60%的雇员
select * from emp where comm>sal*0.6
--5、找出部门10中所有经理和部门20中的所有办事员的详细资料
select * from emp where (deptno=10 and job=upper(’manager’)) or (deptno=20 and job=upper(’clerk ’));
--6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
select * from emp where (deptno=10 and job=upper(’manager’)) or (deptno=20 and job=upper(’clerk ’)) or (job<>upper(‘manager’) and job<>upper(‘clerk’) and sal>=2000)
--7、找出收取佣金的雇员的不同工作
select distinct job from emp where comm>0;
--8、找出不收取佣金或收取的佣金低于100的雇员
select * from emp where nvl(comm,0)<100;
--9、找出各月最后一天受雇的所有雇员
select * from emp where hiredate= last_day(hiredate);
--10、找出早于25年之前受雇的雇员
select * from emp where months_between(sysdate,hiredate)/12>25;
select * from emp where hiredate<add_months(sysdate,-12*25);
--1、列出至少有一个雇员的所有部门
select distinct dname from dept where deptno in (select distinct deptno from emp);
--2、列出薪金比"SMITH"多的所有雇员
select ename,sal from emp where sal>(select sal from emp where ename=upper('smith'));
--3、列出所有雇员的姓名及其直接上级的姓名
select e.ename,m.ename from emp e,emp m where e.mgr=m.empno(+);
--4、列出入职日期早于其直接上级的所有雇员
select ename from emp e where hiredate<(select hiredate from emp where empno=e.mgr);
--5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select dname,ename from dept d left join emp e on d.deptno=e.deptno;
--6、列出所有“CLERK”（办事员）的姓名及其部门名称
select ename,dname from emp e left join dept d on e.deptno=d.deptno where job=upper('clerk');
--7、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录
select job,min(sal) from emp group by job having min(sal)>1500;
--8、列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号
select ename from emp where deptno = (select deptno from dept where dname=uppder('SALES'))
--9、列出薪金高于公司平均水平的所有雇员
select ename from emp where sal>(select avg(sal) from emp);
--10、列出与“SCOTT”从事相同工作的所有雇员
select ename from emp where job=(select job from emp where ename=upper('scott'));
--11、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金
select ename,sal from emp where sal in (select sal from emp where deptno=30);
--12、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金
select ename ,sal from emp where sal>(select max(sal) from emp where deptno=30);
--13、列出每个部门的信息以及该部门中雇员的数量
select d.deptno,dname,count(ename) from dept d left join emp e on (d.deptno=e.deptno)
group by d.deptno,dname
--14、列出所有雇员的雇员名称、部门名称和薪金
Select e.ename,d.dname,e.sal from emp e left join dept d on (d.deptno=e.deptno)
--15、列出从事同一种工作但属于不同部门的雇员的不同组合
Select tba.ename,tbb.ename,tba.job,tbb.job,tba.deptno,tba.deptno
From emp tba,emp tbb
Where tba.job=tbb.job and tba.deptno<>tbb.deptno
--16、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员
Select dept.deptno,dname,loc,count(empno)
From dept,emp
Where dept.deptno=emp.deptno(+)
Group by dept.deptno,dname,loc
--17、列出各种类别工作的最低工资
Select min(sal) from emp group by job
--18、列出各个部门的MANAGER（经理）的最低薪金
Select deptno,min(sal) from emp where job=upper(‘manager’) group by deptno
--19、列出按年薪排序的所有雇员的年薪
select (sal+nvl(comm,0))*12 as avn from emp order by avn
--20、列出薪金水平处于第四位的雇员
Select * from (Select ename,sal, rank() over (order by sal desc) as grade from emp) where grade=4
Oracle sql 语句练习
博客分类：
oracle
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;
练习：
注意：以下练习中的数据是根据初始化到数据库中的数据来写的SQL 语句，请大家务必注意。
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
2、查询平均成绩大于60 分的同学的学号和平均成绩；
3、查询所有同学的学号、姓名、选课数、总成绩；
4、查询姓“刘”的老师的个数；
5、查询没学过“谌燕”老师课的同学的学号、姓名；
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
9、查询所有课程成绩小于60 分的同学的学号、姓名；
10、查询没有学全所有课的同学的学号、姓名；
11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名；
13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；
14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；
15、删除学习“谌燕”老师课的SC 表记录；
16、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；
17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
19、查询不同老师所教不同课程平均分从高到低显示
20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
22、查询每门课程被选修的学生数
23、查询出只选修了一门课程的全部学生的学号和姓名
24、查询男生、女生人数
25、查询姓“张”的学生名单
26、查询同名同性学生名单，并统计同名人数
27、1981 年出生的学生名单(注：Student 表中Sage 列的类型是number)
28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
30、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数
31、查询所有学生的选课情况；
32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
33、查询不及格的课程，并按课程号从大到小排列
34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
35、求选了课程的学生人数
36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
37、查询各个课程及相应的选修人数
38、查询不同课程成绩相同的学生的学号、课程号、学生成绩
39、查询每门功课成绩最好的前两名
40、统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
41、检索至少选修两门课程的学生学号
42、查询全部学生都选修的课程的课程号和课程名
43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
44、查询两门以上不及格课程的同学的学号及其平均成绩
45、检索“c004”课程分数小于60，按分数降序排列的同学学号
46、删除“s002”同学的“c001”课程的成绩
答案:
1.
*********************************
select a.* from
(select * from sc a where a.cno='c001') a,
(select * from sc b where b.cno='c002') b
where a.sno=b.sno and a.score > b.score;
*********************************
select * from sc a
where a.cno='c001'
and exists(select * from sc b where b.cno='c002' and a.score>b.score
and a.sno = b.sno)
*********************************
2.
*********************************
select sno,avg(score) from sc group by sno having avg(score)>60;
*********************************
3.
*********************************
select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno
*********************************
4.
*********************************
select count(*) from teacher where tname like '刘%';
*********************************
5.
*********************************
select a.sno,a.sname from student a
where a.sno
not in
(select distinct s.sno
from sc s,
(select c.*
from course c ,
(select tno
from teacher t
where tname='谌燕')t
where c.tno=t.tno) b
where s.cno = b.cno )
*********************************
select * from student st where st.sno not in
(select distinct sno from sc s join course c on s.cno=c.cno
join teacher t on c.tno=t.tno where tname='谌燕')
*********************************
6.
*********************************
select st.* from sc a
join sc b on a.sno=b.sno
join student st
on st.sno=a.sno
where a.cno='c001' and b.cno='c002' and st.sno=a.sno;
*********************************
7.
*********************************
SQL> select * from(
2 select sc.sno s_sno,count(sc.cno)
3 from sc right join (select cno from course where course.tno = (select tno from teacher where tname = '谌燕')) a
4 on sc.cno = a.cno group by sc.sno
5 having count(sc.cno)=(select count(cno) from course where course.tno = (select tno from teacher where tname = '谌燕'))
6 ) s join student on student.sno = s.s_sno;
S_SNO COUNT(SC.CNO) SNO SNAME SAGE SSEX
---------- ------------- ---------- -------------------- ---- -----
s001 4 s001 张三 23 男
*********************************
8.
*********************************
select * from student st
join sc a on st.sno=a.sno
join sc b on st.sno=b.sno
where a.cno='c002' and b.cno='c001' and a.score < b.score
*********************************
9.
*********************************
select st.*,s.score from student st
join sc s on st.sno=s.sno
join course c on s.cno=c.cno
where s.score <60
*********************************
10.
*********************************
select stu.sno,stu.sname,count(sc.cno) from student stu
left join sc on stu.sno=sc.sno
group by stu.sno,stu.sname
having count(sc.cno)<(select count(distinct cno)from course)
===================================
select * from student where sno in
(select sno from
(select stu.sno,c.cno from student stu
cross join course c
minus
select sno,cno from sc)
)
===================================
*********************************
11.
*********************************
select st.* from student st,
(select distinct a.sno from
(select * from sc) a,
(select * from sc where sc.sno='s001') b
where a.cno=b.cno) h
where st.sno=h.sno and st.sno<>'s001'
*********************************
12.
*********************************
select * from sc
left join student st
on st.sno=sc.sno
where sc.sno<>'s001'
and sc.cno in
(select cno from sc
where sno='s001')
*********************************
13.
*********************************
update sc c set score=(select avg(c.score) from course a,teacher b
where a.tno=b.tno
and b.tname='谌燕'
and a.cno=c.cno
group by c.cno)
where cno in(
select cno from course a,teacher b
where a.tno=b.tno
and b.tname='谌燕')
*********************************
14.
*********************************
select* from sc where sno<>'s001'
minus
(
select* from sc
minus
select * from sc where sno='s001'
)
*********************************
15.
*********************************
delete from sc
where sc.cno in
(
select cno from course c
left join teacher t on c.tno=t.tno
where t.tname='谌燕'
)
*********************************
16.
*********************************
insert into sc (sno,cno,score)
select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002')
from student st,sc
where not exists
(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';
*********************************
17.
*********************************
select cno ,max(score),min(score) from sc group by cno;
*********************************
18.
*********************************
select cno,avg(score),sum(case when score>=60 then 1 else 0 end)/count(*)
as 及格率
from sc group by cno
order by avg(score) , 及格率desc
*********************************
19.
*********************************
select max(t.tno),max(t.tname),max(c.cno),max(c.cname),c.cno,avg(score) from sc , course c,teacher t
where sc.cno=c.cno and c.tno=t.tno
group by c.cno
order by avg(score) desc
*********************************
20.
*********************************
select sc.cno,c.cname,
sum(case when score between 85 and 100 then 1 else 0 end) AS "[100-85]",
sum(case when score between 70 and 85 then 1 else 0 end) AS "[85-70]",
sum(case when score between 60 and 70 then 1 else 0 end) AS "[70-60]",
sum(case when score <60 then 1 else 0 end) AS "[<60]"
from sc, course c
where sc.cno=c.cno
group by sc.cno ,c.cname;
*********************************
21.
*********************************
select * from
(select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc)
where rn<4
*********************************
22.
*********************************
select cno,count(sno)from sc group by cno;
*********************************
23.
*********************************
select sc.sno,st.sname,count(cno) from student st
left join sc
on sc.sno=st.sno
group by st.sname,sc.sno having count(cno)=1;
*********************************
24.
*********************************
select ssex,count(*)from student group by ssex;
*********************************
25.
*********************************
select * from student where sname like '张%';
*********************************
26.
*********************************
select sname,count(*)from student group by sname having count(*)>1;
*********************************
27.
*********************************
select sno,sname,sage,ssex from student t where to_char(sysdate,'yyyy')-sage =1988
*********************************
28.
*********************************
select cno,avg(score) from sc group by cno order by avg(score)asc,cno desc;
*********************************
29.
*********************************
select st.sno,st.sname,avg(score) from student st
left join sc
on sc.sno=st.sno
group by st.sno,st.sname having avg(score)>85;
*********************************
30.
*********************************
select sname,score from student st,sc,course c
where st.sno=sc.sno and sc.cno=c.cno and c.cname='Oracle' and sc.score<60
*********************************
31.
*********************************
select st.sno,st.sname,c.cname from student st,sc,course c
where sc.sno=st.sno and sc.cno=c.cno;
*********************************
32.
*********************************
select st.sname,c.cname,sc.score from student st,sc,course c
where sc.sno=st.sno and sc.cno=c.cno and sc.score>70
*********************************
33.
*********************************
select sc.sno,c.cname,sc.score from sc,course c
where sc.cno=c.cno and sc.score<60 order by sc.cno desc;
*********************************
34.
*********************************
select st.sno,st.sname,sc.score from sc,student st
where sc.sno=st.sno and cno='c001' and score>80;
*********************************
35.
*********************************
select count(distinct sno) from sc;
*********************************
36.
*********************************
select st.sname,score from student st,sc ,course c,teacher t
where
st.sno=sc.sno and sc.cno=c.cno and c.tno=t.tno
and t.tname='谌燕' and sc.score=
(select max(score)from sc where sc.cno=c.cno)
*********************************
37.
*********************************
select cno,count(sno) from sc group by cno;
*********************************
38.
*********************************
select a.* from sc a ,sc b where a.score=b.score and a.cno<>b.cno
*********************************
39.
*********************************
select * from (
select sno,cno,score,row_number()over(partition by cno order by score desc) my_rn from sc t
)
where my_rn<=2
*********************************
40.
*********************************
select cno,count(sno) from sc group by cno
having count(sno)>10
order by count(sno) desc,cno asc;
*********************************
41.
*********************************
select sno from sc group by sno having count(cno)>1;
||
select sno from sc group by sno having count(sno)>1;
*********************************
42.
*********************************
select distinct(c.cno),c.cname from course c ,sc
where sc.cno=c.cno
||
select cno,cname from course c
where c.cno in
(select cno from sc group by cno)
*********************************
43.
*********************************
select st.sname from student st
where st.sno not in
(select distinct sc.sno from sc,course c,teacher t
where sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕')
*********************************
44.
*********************************
select sno,avg(score)from sc
where sno in
(select sno from sc where sc.score<60
group by sno having count(sno)>1
) group by sno
*********************************
45.
*********************************
select sno from sc where cno='c004' and score<90 order by score desc;
*********************************
46.
*********************************
delete from sc where sno='s002' and cno='c001';
*********************************
oracle数据库sql语句练习
2010-06-08 12:34:15| 分类： 每天进入一点点(j |字号 订阅
查询员工号，员工姓名，员工薪水（null 则 值为0），薪水*20%之后四舍五入取整，别名为“new salary”
select empno,ename,nvl(sal,0),round(sal*(1+0.2),0) "new salary" from emp;
nvl:为null 则用指定的代替
查询员工的姓名，工作了多少个月（四舍五入取整） 别名为 “wored_months”
select ename,round(months_between(sysdate,hiredate),0) "wored_months" from emp;
months_between:
查询员工姓名（大写）工资（必须是15位的，不足的用$补充）
select upper(ename) "name",lpad(sal,15,"$") "new salary" from emp;
lpad:第一个参数是原字符串，第二个参数是目标字符串长度，第三个是长度不够使用什么代替
查询 员工工作了满多少月了
使用trunc(months_between(sysdate,hiredate))
TRUNC函数的功能为将数字的小数部分截去，返回整数。 TRUNC函数语法TRUNC(number,num_digits)
做一个查询，按下面的形式显示结果
<ename> earns <sal> monthly but wants <sal*3>
Dream Salary
KING earns $24000 monthly but wants $72000
JONE earns $18000 monthly but wants $54000
select upper(ename)||' earns ' || to_char(sal,'$99999999') ||' monthly but wants ' || to_char(sal*3,'$999999999') "Dream Salary"
from emp
|| ： 连接功能
to_char(sal,'$9999999') : 将工资转换成字符，$99999代表以$开始，9999表示，有就输出，没有则不输出
查询公司的人数，以及在80,81,82,87年，每年雇用的人数，结果类似下面的格式
total 1980 1981 1982 1987
14 1 10 1 2
select count(*) total,
sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
from emp
decode()函数：如果是1980 返回 1 ，不是 返回0 如果 不写默认值是空
distinct 去重复