oracle例题9篇

翻了800篇帖子，就找的了九篇没有重复的例题，我也不能说我不是抄袭，但这些都是我自己手打每次寻找都是重复的，或者付费查看，太难过了，我没有钱，只能自己手打，我把这些做了一遍，我不确定答案是不是正确的，希望别人不用那么辛苦，毕竟人人都不容易啊！

第一篇

create table dept_test(
deptno number(2) ,
dname char(20) ,
location char(20)) ;

insert into dept_test values(10 , 'developer' , 'beijing') ;
insert into dept_test values(20 , 'account' , 'shanghai') ;
insert into dept_test values(30 , 'sales' , 'guangzhou') ;
insert into dept_test values(40 , 'operations' , 'tianjin') ;
commit ;

select * from dept_test

create table emp_test(
empno number(4) ,
ename varchar2(20) ,
job varchar2(15) ,
salary number(7 , 2) ,
bonus number(7 , 2) ,
hiredate date,
mgr number(4) ,
deptno number(10)
) ;

insert into emp_test values(1001 , '张无忌' , 'Manager' ,10000 , 2000 , to_date('2012-12-10','yyyy-mm-dd') , 1005 , 10) ;
insert into emp_test values(1002 , '刘苍松' , 'Analyst' ,8000 , 1000 , to_date('2012-05-20','yyyy-mm-dd'), 1001, 10) ;
insert into emp_test values(1003 , '李翊' , 'Analyst' ,9000 , 1000 , to_date('2012-02-15','yyyy-mm-dd') , 1001, 10) ;
insert into emp_test values(1004 , '郭芙蓉' , 'Programmer' ,5000 , null ,to_date('2013-04-30','yyyy-mm-dd'), 1001 , 10) ;
insert into emp_test values(1005 , '张三丰' , 'President' ,15000 , null , to_date('2011-07-20','yyyy-mm-dd'), null , 20) ;
insert into emp_test values(1006 , '燕小六' , 'Manager' ,5000 , 400 , to_date('2013-02-18','yyyy-mm-dd') , 1005 , 20) ;
insert into emp_test values(1007 , '陆无双' , 'clerk' ,3000 , 500 , to_date('2010-01-09','yyyy-mm-dd') , 1006 , 20) ;
insert into emp_test values(1008 , '黄蓉' , 'Manager' ,5000 , 500 , to_date('2011-12-07','yyyy-mm-dd') , 1005 , 30) ;
insert into emp_test values(1009 , '韦小宝' , 'salesman' ,4000 , null , to_date('2020-06-09','yyyy-mm-dd') , 1008 , 30) ;
insert into emp_test values(1010 , '郭靖' , 'salesman' ,4500 , 500 , to_date('2010-02-09','yyyy-mm-dd') , 1008 , 30) ;
commit ;

select * from emp_test
 计算员工的月收入？（包括工资和奖金）
 select ename, salary , bonus , salary + nvl(bonus, 0)  from emp_test
 机构中有多少种职位？
 select count (*) from ( select distinct job from emp_test) 
 查询每个部门不重复的职位？
 select deptno,job,count(job) from emp_test group by deptno,job having count(job)=1
 列出职位中第二个字符是 a 的员工数据？
 select * from emp_test where ename like'_小%'
 查询数据库中有多少个名字中以 ‘S_’ 开头的表？ 
 select ename from emp_test where ename like's_' 
 计算总月收入多少 
select ename, salary , bonus , salary + nvl(bonus, 0) month_sal from emp_test ;

 计算员工入职多少天? 
 select sysdate-hiredate from emp_test
 
 计算员工入职多少个月？
  select (sysdate-hiredate)/30 from emp_test

 根据员工的职位 , 计算加薪后的薪水数据 
要求： 
1) 如果职位是 Analyst：加薪 10% 
2) 如果职位是 Programmer：加薪 5% 
3) 如果职位是 clerk：加薪 2% 
4) 其他职位：薪水不变

select ename,salary,job,case when job= 'Analyst' then salary * 1.1 when job='Programmer' then salary * 1.05 when  job='clerk' then salary * 1.02 else salary

       end new_salary

  from emp_test;

按部门排序 , 同一部门按薪水由高到低排序 
select deptno,salary from emp_test group by deptno,salary order by deptno,salary desc
计算员工的人数总和、薪水总和、平均薪水是多少？
select count(empno),sum(salary),avg(salary) from emp_test
计算最早和最晚的员工入职时间 
select max(hiredate),min(hiredate) from emp_test
按部门计算每个部门的最高、最低薪水、薪水总和、平均薪水、总人数分别是多少？
select deptno,max(salary),min(salary),sum(salary),avg(salary) from  emp_test group by  deptno
按职位分组 , 每个职位的最高、最低薪水和人数？ 
select job,max(salary),min(salary),count(job) from emp_test group by job
平均薪水大于 5000 元的部门数据 , 没有部门的不算在内？
select * from dept_test where deptno in(select deptno from emp_test group by deptno having avg(salary)>5000
)
16）薪水总和大于 20000 元的部门数据？
select * from dept_test where deptno in(select deptno from emp_test group by deptno having sum(salary)>20000
)
查询最高薪水的是谁？ 
select * from emp_test where salary =(select max(salary) from emp_test
)
研发部有哪些职位？ 
select distinct job from emp_test where deptno=(select deptno from dept_test where dname='developer'
)

谁的薪水比张无忌高？ 
select ename from emp_test where salary>(select  salary from emp_test where ename='张无忌'
) 
谁和刘苍松同部门？列出除了刘苍松之外的员工名字
select ename from emp_test where deptno=(select deptno from emp_test where ename='刘苍松'
) and ename<>'刘苍松'
谁和刘苍松同部门？列出除了刘苍松之外的员工名字( 如果子查询得到的结果是多个 )
select ename from emp_test where deptno=(select deptno from emp_test where ename='刘苍松'
) and ename<>'刘苍松'
每个部门拿最高薪水的是谁？
select deptno,max(salary) from emp_test group by deptno 
select a.a,ename from emp_test,(select deptno a,max(salary) b from emp_test group by deptno 
) a where a.a=deptno and a.b=salary
哪个部门的人数比部门 号30 的人数多？
select deptno from emp_test group by deptno having count(deptno)>(select count(*) from emp_test where deptno=30
)
列出员工名字和职位 , 这些员工所在的部门平均薪水大于 5000 元
select ename,job from emp_test where deptno in(select deptno from emp_test group by deptno having avg(salary)>5000
)
哪些员工的薪水比本部门的平均薪水低？

select ename from emp_test,(select deptno a,avg(salary) b from emp_test group by deptno
) a where a.a=deptno and a.b>salary
哪些人是其他人的经理？
select distinct a.ename from emp_test a,emp_test b where a.empno=b.mgr
哪些部门没有员工？
select deptno from dept_test where deptno not in (select distinct deptno from emp_test
) 
查询第10到第15条数据
select * from (select rownum l, emp_test.* from emp_test where rownum<=15
)  where l>9

第二篇

create table bumen(
 b_id char(5) primary key,
  b_name char(10) not null);
  
  
  
select * from bumen 
  
  create table yuangong(
y_id char(5) primary key,
y_name varchar2(10),
y_sex char(1) check(y_sex in('f','m')),
y_age number(3),
v_address varchar2(20) default'buxiang',
b_id references bumen(b_id));

Insert into bumen values('001','后勤部');

Insert into bumen values('002','人事部');

Insert into bumen values('003','财务部');

insert into yuangong values('01','张三','f','40','buxiang','001');
insert into yuangong values('02','李四','m','80','buxiang','001');
insert into yuangong values('03','马五','f','20','buxiang','002');
insert into yuangong values('04','张毅','m','40','buxiang','002');
insert into yuangong values('05','张四','f','26','buxiang','003');

select * from yuangong

1、查询年龄在25至30岁之间的男员工的姓名和住址。
select y_name,v_address from yuangong where y_age between 25 and 30 and y_sex='f'
2、查询财务部所有40岁以下男员工的所有信息
select * from yuangong where y_age<40 and b_id=(select b_id from bumen where b_name='财务部'
) and y_sex='f'
3、查询人事部年龄最大的女员工姓名

select y_name from yuangong where y_sex='m' and b_id=(select b_id from bumen where b_name='人事部'
) and y_age=(select max(y_age) from yuangong where y_sex='m' and b_id=(select b_id from bumen where b_name='人事部'
))
4、在员工表中，将人事部年龄大于30岁的女同事，调到后勤部。

update yuangong set b_id=(select b_id from bumen where b_name='后勤部'
) where y_id=(select y_id from yuangong where y_sex='m' and b_id=(select b_id from bumen where b_name='人事部'
) and y_age>30)

select * from yuangong
5、查询每个部门年龄最大的员工，显示员工姓名、部门名字和年龄
select b_id,max(y_age) from yuangong group by b_id
select a.a,yuangong.y_name,a.b from (select b_id a,max(y_age) b from yuangong group by b_id
) a left join yuangong on a.a=yuangong.b_id and a.b=yuangong.y_age

select l.y_name,bumen.b_name,l.b from (select a.a,yuangong.y_name,a.b from (select b_id a,max(y_age) b from yuangong group by b_id
) a left join yuangong on a.a=yuangong.b_id and a.b=yuangong.y_age
) l left join bumen on l.a=bumen.b_id

6、查询每个部门年龄排前二的员工，显示员工姓名、部门名字和年龄。

select * from (select y_name,b_name,y_age,rank() over(partition by b_name order by y_age desc) as k from yuangong,bumen where yuangong.b_id=bumen.b_id 
) l where l.k<=2

7、将张三的的名字改为李四，并调到财务部。
update yuangong set y_name='李四'  where y_name='张三'

update yuangong set  b_id=(select b_id from bumen where b_name='财务部'
)  where y_name='李四' and b_id='001'

8、将后勤部年龄大于60岁的员工删除。
select y_id from yuangong where b_id=(select b_id from bumen where b_name='后勤部'
) and y_age>60

9、查询每个部门各有多少人，显示部门名字、人数和人数描述，按人数倒序，如果人数相同，按部门名正序，若人数超过6、人数在3-6之间和少于3人，则人数描述分别显示为: “员工数多”，“员工数偏多”， “员工数少”。

       
select b_name,a.b,a.描述 from bumen,(select b_id a,count(b_id) b,case when count(b_id)< 3 then '员工数少' when count(b_id) between 3 and 6 then '员工数偏多' else '员工数多'

       end 描述 from yuangong group by b_id) a where a.a=bumen.b_id order by a.b desc,b_name asc

10、查询员工表的第2至第4条记录。
select * from (select rownum l,yuangong.* from yuangong where rownum<=4
) where l>1
11、在y_name列创建唯一性降序索引idx1 。
create  index index2 on yuangong (y_name desc)

select * from dba_indexes where table_name='YUANGONG' 

第三篇

create table course
(
  id          number not null,
  cour_code   varchar2(20),
  cour_name   varchar2(30),
  p_cour_code varchar2(20)
);
comment on column course.cour_code
  is '课程代码';
comment on column course.cour_name
  is '课程名称';
comment on column course.p_cour_code
  is '父级课程代码';
alter table course
  add constraint pk_course primary key (id);
alter table course
  add constraint uk_course unique (cour_code);
 
create table student
(
  id       number not null,
  name     varchar2(20),
  code     varchar2(20),
  sex      char(1),
  birthday date,
  major    varchar2(20),
  note     varchar2(300)
);

select * from student
comment on column student.name
  is '学生姓名';
comment on column student.code
  is '学生学号';
comment on column student.sex
  is '性别';
comment on column student.birthday
  is '生日';
comment on column student.major
  is '专业';
comment on column student.note
  is '备注';
alter table student
  add constraint pk_student primary key (id);
 
create table stu_cour
(
  id        number not null,
  stu_code  varchar2(20),
  cour_code varchar2(20),
  degree     number(4,1)
);
comment on column stu_cour.stu_code
  is '学生学号';
comment on column stu_cour.cour_code
  is '课程代码';
comment on column stu_cour.degree
  is '课程分数';
 
alter table stu_cour
  add constraint pk_stu_court primary key (id);
 

select * from stu_cour

insert into course (id, cour_code, cour_name, p_cour_code)
values (1, 'LAU-100', '汉语言文学专业', null);
insert into course (id, cour_code, cour_name, p_cour_code)
values (2, 'C-LAU-101', '语言学概论', 'LAU-001');
insert into course (id, cour_code, cour_name, p_cour_code)
values (3, 'C-LAU-102', '现代汉语', 'LAU-001');
insert into course (id, cour_code, cour_name, p_cour_code)
values (4, 'C-LAU-103', '中国当代文学史', 'LAU-001');
insert into course (id, cour_code, cour_name, p_cour_code)
values (5, 'C-LAU-104', '大学英语', 'LAU-001');
insert into course (id, cour_code, cour_name, p_cour_code)
values (6, 'NEWS-100', '国际新闻专业', null);
insert into course (id, cour_code, cour_name, p_cour_code)
values (7, 'C-NEWS-101', '新闻采访', 'NEWS-100');
insert into course (id, cour_code, cour_name, p_cour_code)
values (8, 'C-NEWS-102', '报纸编辑', 'NEWS-100');
insert into course (id, cour_code, cour_name, p_cour_code)
values (9, 'C-NEWS-103', '电视新闻', 'NEWS-100');
insert into course (id, cour_code, cour_name, p_cour_code)
values (10, 'HIS-121', '历史学专业', null);
insert into course (id, cour_code, cour_name, p_cour_code)
values (11, 'C-HIS-335', '中国古代史', 'HIS-121');
insert into course (id, cour_code, cour_name, p_cour_code)
values (12, 'C-HIS-336', '世界古代史', 'HIS-121');
insert into course (id, cour_code, cour_name, p_cour_code)
values (13, 'C-HIS-337', '中国近代史', 'HIS-121');
insert into course (id, cour_code, cour_name, p_cour_code)
values (14, 'ADV-609', '广告学专业', null);
insert into course (id, cour_code, cour_name, p_cour_code)
values (15, 'C-ADV-239', '广告文案写作', 'ADV-609');
insert into course (id, cour_code, cour_name, p_cour_code)
values (16, 'C-ADV-240', '基础美术', 'ADV-609');
insert into course (id, cour_code, cour_name, p_cour_code)
values (17, 'C-ADV-241', '平面广告设计与制作', 'ADV-609');
insert into course (id, cour_code, cour_name, p_cour_code)
values (18, 'C-ADV-242', '市场营销学', 'ADV-609');
insert into course (id, cour_code, cour_name, p_cour_code)
values (19, 'C-ADV-243', '大众传播学', 'ADV-609');

select * from course
commit;
insert into student (id, name, code, sex, birthday, major, note)
values (1, '陈迪', 'stu-1011', '1', to_date('14-04-1993', 'dd-mm-yyyy'), 'LAU-100', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (2, '肖东菁', 'stu-1014', '1', to_date('15-02-1992', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (3, '汪佳丽', 'stu-1017', '2', to_date('16-08-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (19, '车晓', 'stu-1042', '1', to_date('18-03-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (5, '王聪', 'stu-1023', '1', to_date('18-03-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (6, '李璇', 'stu-1026', '2', to_date('19-05-1991', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (7, '马舒滟', 'stu-1029', '1', to_date('20-01-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (20, '张光北', 'stu-1018', '1', to_date('15-02-1992', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (9, '徐丹', 'stu-1035', '2', null, 'NEWS-100', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (11, '田野', 'stu-1041', '1', null, 'ADV-609', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (12, '彭亚光', 'stu-1044', '2', to_date('25-11-1990', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (14, '黄欢', 'stu-1050', '1', to_date('27-06-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (15, '庞琳', 'stu-1053', '1', to_date('28-05-1989', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (16, '张子腾', 'stu-1056', '2', to_date('18-03-1990', 'dd-mm-yyyy'), 'LAU-100', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (17, '姜春阳', 'stu-1059', '2', to_date('30-05-1988', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into student (id, name, code, sex, birthday, major, note)
values (18, '陈冰若', 'stu-1062', '1', to_date('31-10-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
commit;
insert into stu_cour (id, stu_code, cour_code, degree)
values (1, 'stu-1011', 'C-LAU-101', 35);
insert into stu_cour (id, stu_code, cour_code, degree)
values (2, 'stu-1011', 'C-LAU-102', 65);
insert into stu_cour (id, stu_code, cour_code, degree)
values (3, 'stu-1011', 'C-LAU-103', 25);
insert into stu_cour (id, stu_code, cour_code, degree)
values (4, 'stu-1011', 'C-LAU-104', 97);
insert into stu_cour (id, stu_code, cour_code, degree)
values (5, 'stu-1014', 'C-HIS-335', 53);
insert into stu_cour (id, stu_code, cour_code, degree)
values (6, 'stu-1014', 'C-HIS-336', 35);
insert into stu_cour (id, stu_code, cour_code, degree)
values (7, 'stu-1014', 'C-HIS-337', 65);
insert into stu_cour (id, stu_code, cour_code, degree)
values (8, 'stu-1017', 'C-NEWS-101', 25);
insert into stu_cour (id, stu_code, cour_code, degree)
values (9, 'stu-1017', 'C-NEWS-102', 65);
insert into stu_cour (id, stu_code, cour_code, degree)
values (10, 'stu-1017', 'C-NEWS-103', 25);
insert into stu_cour (id, stu_code, cour_code, degree)
values (11, 'stu-1023', 'C-ADV-239', 33);
insert into stu_cour (id, stu_code, cour_code, degree)
values (12, 'stu-1023', 'C-ADV-240', 42);
insert into stu_cour (id, stu_code, cour_code, degree)
values (13, 'stu-1023', 'C-ADV-241', 66);
insert into stu_cour (id, stu_code, cour_code, degree)
values (14, 'stu-1023', 'C-ADV-242', 69);
insert into stu_cour (id, stu_code, cour_code, degree)
values (15, 'stu-1023', 'C-ADV-243', 82);
insert into stu_cour (id, stu_code, cour_code, degree)
values (16, 'stu-1026', 'C-HIS-335', 37);
insert into stu_cour (id, stu_code, cour_code, degree)
values (17, 'stu-1026', 'C-HIS-336', 77);
insert into stu_cour (id, stu_code, cour_code, degree)
values (18, 'stu-1026', 'C-HIS-337', 34);
insert into stu_cour (id, stu_code, cour_code, degree)
values (19, 'stu-1029', 'C-NEWS-101', 35);
insert into stu_cour (id, stu_code, cour_code, degree)
values (20, 'stu-1029', 'C-NEWS-102', 75);
insert into stu_cour (id, stu_code, cour_code, degree)
values (21, 'stu-1029', 'C-NEWS-103', 32);
insert into stu_cour (id, stu_code, cour_code, degree)
values (22, 'stu-1035', 'C-NEWS-101', 19);
insert into stu_cour (id, stu_code, cour_code, degree)
values (23, 'stu-1035', 'C-NEWS-102', 11);
insert into stu_cour (id, stu_code, cour_code, degree)
values (24, 'stu-1035', 'C-NEWS-103', 93);
insert into stu_cour (id, stu_code, cour_code, degree)
values (25, 'stu-1041', 'C-ADV-239', 99);
insert into stu_cour (id, stu_code, cour_code, degree)
values (26, 'stu-1041', 'C-ADV-240', 88);
insert into stu_cour (id, stu_code, cour_code, degree)
values (27, 'stu-1041', 'C-ADV-241', 89);
insert into stu_cour (id, stu_code, cour_code, degree)
values (28, 'stu-1041', 'C-ADV-242', 63);
insert into stu_cour (id, stu_code, cour_code, degree)
values (29, 'stu-1041', 'C-ADV-243', 44);
insert into stu_cour (id, stu_code, cour_code, degree)
values (30, 'stu-1044', 'C-HIS-335', 73);
insert into stu_cour (id, stu_code, cour_code, degree)
values (31, 'stu-1044', 'C-HIS-336', 65);
insert into stu_cour (id, stu_code, cour_code, degree)
values (32, 'stu-1044', 'C-HIS-337', 25);
insert into stu_cour (id, stu_code, cour_code, degree)
values (33, 'stu-1050', 'C-ADV-239', 33);
insert into stu_cour (id, stu_code, cour_code, degree)
values (34, 'stu-1050', 'C-ADV-240', 42);
insert into stu_cour (id, stu_code, cour_code, degree)
values (35, 'stu-1050', 'C-ADV-241', 25);
insert into stu_cour (id, stu_code, cour_code, degree)
values (36, 'stu-1050', 'C-ADV-242', 33);
insert into stu_cour (id, stu_code, cour_code, degree)
values (37, 'stu-1050', 'C-ADV-243', 42);
insert into stu_cour (id, stu_code, cour_code, degree)
values (38, 'stu-1053', 'C-HIS-335', 66);
insert into stu_cour (id, stu_code, cour_code, degree)
values (39, 'stu-1053', 'C-HIS-336', 69);
insert into stu_cour (id, stu_code, cour_code, degree)
values (40, 'stu-1053', 'C-HIS-337', 35);
insert into stu_cour (id, stu_code, cour_code, degree)
values (41, 'stu-1056', 'C-LAU-101', 65);
insert into stu_cour (id, stu_code, cour_code, degree)
values (42, 'stu-1056', 'C-LAU-102', 25);
insert into stu_cour (id, stu_code, cour_code, degree)
values (43, 'stu-1056', 'C-LAU-103', 97);
insert into stu_cour (id, stu_code, cour_code, degree)
values (44, 'stu-1056', 'C-LAU-104', 53);
insert into stu_cour (id, stu_code, cour_code, degree)
values (45, 'stu-1059', 'C-HIS-335', 35);
insert into stu_cour (id, stu_code, cour_code, degree)
values (46, 'stu-1059', 'C-HIS-336', 25);
insert into stu_cour (id, stu_code, cour_code, degree)
values (47, 'stu-1059', 'C-HIS-337', 97);
insert into stu_cour (id, stu_code, cour_code, degree)
values (48, 'stu-1062', 'C-NEWS-101', 32);
insert into stu_cour (id, stu_code, cour_code, degree)
values (49, 'stu-1062', 'C-NEWS-102', 19);
insert into stu_cour (id, stu_code, cour_code, degree)
values (50, 'stu-1062', 'C-NEWS-103', 11);

commit;
 
 
--题目
--查询学生表中的姓名，专业
select name,major from student
--查询学生表, 按照学号由小到大排序, 前五个同学的姓名，专业
select * from (select name,major from student order by id asc
) where rownum <=5
--查询专业为国际新闻的学生的所有信息
select * from student where major=(select cour_code from course where cour_name='国际新闻专业'
)
--查询1991年出生的学生姓名和专业
select name,major from student where to_char (birthday,'yyyy')=1991
--查询历史, 广告, 国际新闻专业的所有学生信息

select * from student where major in(select cour_code from course where cour_name='历史学专业'
or cour_name='广告学专业' or cour_name='国际新闻专业'
)

--查询姓名是两个字的姓王, 姓陈, 姓李的所有学生信息
select * from student where name like'王_' or name like'陈_' or name like'李_'
--查询没有学分的学生信息
select * from student where note is null
--查询课程表中不是专业的记录
select * from course where cour_name not like'%专业%'
--查询计算机专业的没有记录生日的学生信息
select * from student where major=(select cour_code from course where cour_name='计算机专业'
) and birthday is null
--查询按照专业降序，学号升序排列所有学生信息
select * from student order by major desc,id asc
--查询表中前三个的学生的姓名，专业，到现在的年龄并按照年龄降序排列
select a.name,a.major from (select * from student where rownum<=3
) a order by to_char(sysdate,'yyyy')-to_char(a.birthday,'yyyy') desc
--查询学生表中专业, 并显示为文字
select name,cour_name from student,course where major=cour_code

--查询所有的课程代码和每个课程的平均成绩并按照课程号排序
select cour_code,avg(degree) from stu_cour group by cour_code order by cour_code
--查询出每个课程代码的最高分和最低分
select cour_code,max(degree),min(degree) from stu_cour group by cour_code
--查询学号为stu-1023的学生的各课成绩
select * from stu_cour where stu_code='stu-1023'
--查询各专业各有多少人
select major,count(major) from student group by major
--查询出历史学专业有多少人
select count(major) from student where major=(select cour_code from course where cour_name='历史学专业' 
)
--查询出各专业里男女生各有多少人
select major,sex,count(sex) from student group by major,sex 
--查询出学生所有课程的平均分在50分以上的学生学号
select stu_code from stu_cour group by stu_code having avg(degree)>50
--查询每个学生有几门课成绩高于80分
select stu_code,count(cour_code) from stu_cour where degree>80 group by stu_code
--查询所有学生的学号，姓名，专业课程号，成绩
select student.code,name,cour_code,degree from student,stu_cour where student.code=stu_cour.stu_code
--查询课程号为c-his-336的学生的姓名和成绩
select name,degree from student,stu_cour where student.code=stu_cour.stu_code and cour_code='C-HIS-336'
--查询选修广告学专业的基础美术这门课程的学生学号，姓名，成绩
select student.code,name,degree from student,stu_cour where student.code=stu_cour.stu_code and
 cour_code=(select cour_code from course where cour_name='基础美术' 
)
--查询选修课程号为c-news-101这门课的所有学生信息和成绩
select student.*,degree from student,stu_cour where student.code=stu_cour.stu_code and cour_code='C-NEWS-101'

--查询生日是同一天的学生信息
select distinct a.* from student a,student b where a.id<>b.id and a.birthday=b.birthday
--查询各课的课程名，课程号，每门课所有学生的平均成绩
select cour_name,course.cour_code,degree from course,stu_cour where course.cour_code=stu_cour.cour_code 
--查询所有学生的平均成绩
select stu_code,avg(degree) from stu_cour group by stu_code
--查询每个专业的每个课程的平均分是多少
select cour_code,avg(degree) from stu_cour group by cour_code
--查询平均分大于40分的国际新闻专业的每个学生姓名，学号和各课的平均分
  
select l.* from (select name,stu_code,avg(degree) from student,stu_cour where student.code=stu_cour.stu_code 
  group by name,stu_code) l where l.stu_code in(select stu_code from stu_cour where stu_code in(select code from student,course where student.major=course.cour_code and cour_name='国际新闻专业'
) group by stu_code having avg(degree)>40)
 

--查询平均分大于等于课程号为c-adv-239的课程号和平均分

select a.* from (select cour_code,avg(degree) b from stu_cour group by cour_code
) a where a.b>(select avg(degree) from stu_cour where cour_code='C-ADV-239'
)
--查询历史学专业下的课程的及格率(课程得分在50以上的人数除以总人数)

select concat(b.k/a.l*100,'%') 及格率 from (select count(*) l from student where major=(select cour_code from course where cour_name='历史学专业'
)) a,(select distinct count(*) k from stu_cour where cour_code in(select cour_code from course where p_cour_code=(select cour_code from course where cour_name='历史学专业'
)) group by stu_code having avg(degree)>50) b 

--查询没有选修c-news-101这门课程的学生信息和课程信息

select * from student where code not in(select stu_code from stu_cour where cour_code='C-NEWS-101'
)
--查询没有课程成绩的学生信息
select * from student where code not in(select distinct stu_code from stu_cour
)

--假设在一个页面上要显示成绩表, 按照成绩降序排列, 分页显示信息, 每页显示5条, 查询出第2页的信息

select * from (select rownum l,a.* from (select * from stu_cour order by degree desc
) a where rownum <=10) where l>=5

commit
 

第四篇

create table stu(
       sid number(6) constraint pk_stu_sid primary key,
       sname varchar2(20),
       sex varchar2(4),
       birthday date,
       age number(2),
       smoney number(7,2),
       cid number(4)
);

create table classinfo(
       cid number(4) constraint pk_cls_cid primary key,
       cname varchar2(20)
);

insert into classinfo values(1101,'工商管理');
insert into classinfo values(1102,'计算机');
insert into classinfo values(1103,'会计');
insert into classinfo values(1104,'药品');

insert into stu values(110001,'小明','男',to_date('1999-9-9','yyyy-mm-dd'),10,11220.50,1103);
insert into stu values(110002,'小张','女',to_date('1991-6-9','yyyy-mm-dd'),19,10000.00,1102);
insert into stu values(110003,'小红','女',to_date('1996-7-3','yyyy-mm-dd'),14,8800.00,1102);
insert into stu values(110004,'张三','男',to_date('1996-2-1','yyyy-mm-dd'),14,6600.00,1102);
insert into stu values(110005,'小花','女',to_date('1994-6-7','yyyy-mm-dd'),16,11440.50,1101);
insert into stu values(110006,'王五',null,to_date('1991-12-5','yyyy-mm-dd'),20,11440.50,1101);
commit;

select * from stu; 
select * from classinfo;

--1、  查询入学年龄在18-20的女生或者未输入性别的学生信息，且年龄小的排在后面
select * from stu where to_char(sysdate,'yyyy')-to_char(birthday,'yyyy') between 18 and 25 or sex is null order by to_char(sysdate,'yyyy')-to_char(birthday,'yyyy') desc ;

--2、  查询班级名称、学生姓名、性别、缴费（要求显示单位：元），相同班级的要放在一起，再按姓名升序排列。
select cname,sname,sex,concat(smoney,'元') from classinfo,stu where classinfo.cid=stu.cid order by cname,sname 
--3、  查询各班名称和人数。
select cname,count(sname) from classinfo,stu where classinfo.cid=stu.cid group by cname
--4、  查询各班名称和人数，但人数必须不少于2，且人数多的放在前面。
select cname,count(sname) from classinfo,stu where classinfo.cid=stu.cid group by cname having count(cname)>=2 order by count(cname)desc 

--5、  查询1996年出生的有哪些学生。
select * from stu where to_char(birthday,'yyyy')=1996
--6、  查询男生和女生人数，没有输入性别的当作男生计算。
select nvl(sex,'男') k from stu 
select k,count(*) from (select nvl(sex,'男') k from stu ) a group by k
--7、  查询没有人员的班级。

select cname from classinfo where cname not in(select distinct a.cname from (select cname,sname,sex,concat(smoney,'元') from classinfo,stu where classinfo.cid=stu.cid order by cname,sname 
) a)
--8、  查询入学年龄在20以上的学生信息。
select * from stu where age>20
--9、  查询班级平均入学年龄在20及以上的班级名称和平均年龄。
select cname,avg(age) from stu,classinfo where stu.cid=classinfo.cid and stu.cid=(select cid from stu group by cid having avg(age)>=20
) group by cname

commit;

第五篇

create table bm(
       bid number(4) constraint pk_bm_bid primary key,
       bname varchar2(10)
);
create table ry(
       rid number(4) constraint pk_ry_rid primary key,
       rname varchar2(20),
       bid number(4)
);
create table gz(
       rid number(4),
       sal number(7,2),
       rq date
);

alter table ry add constraint fk_ry_bm_bid foreign key(bid) references bm(bid);
alter table gz add constraint fk_gz_ry_rid foreign key(rid) references ry(rid);

insert into bm values(1001,'销售');
insert into bm values(1002,'后勤');

insert into ry values(1101,'tom',1001);
insert into ry values(1103,'barbie',1001);
insert into ry values(1102,'jim',1002);

insert into gz values(1101,1200.5,sysdate);
insert into gz values(1101,1200.5,sysdate);
insert into gz values(1102,2000,sysdate);
insert into gz values(1103,5000,sysdate);

select * from bm;
select * from ry;
select * from gz;

--1、  查询员工姓名、部门名称和个人总工资。
select rname,bname,sum(sal) from bm,ry,gz where bm.bid=ry.bid and ry.rid=gz.rid group by bname,rname

--2、  查询本月发了2笔以上工资的员工信息。
select * from ry where rid=(select rid from gz group by rid having count(*)>=2
)
--3、  查询各部门的总工资
select bname,sum(sal) from bm,gz,ry where bm.bid=ry.bid and ry.rid=gz.rid group by bname

第六篇

create table A (aid number,aname varchar2(20),asex varchar2(10),aclass varchar2(30))
insert into A values(1001,'李明','男','计算机系');
insert into A values(1002,'张三','男','数学系');
insert into A values(1003,'李四','男','计算机系');
insert into A values(1004,'王二','男','物理系');
insert into A values(1005,'李娜','女','心理系');
insert into A values(1006,'孙俪','女','数学系');

create table B(bid number,bname varchar2(50),cname varchar2(50))
insert into B values (001,'男子五千米','一操场');
insert into B values (002,'男子标枪','一操场');
insert into B values (003,'男子跳远','二操场');
insert into B values (004,'女子跳高','二操场');
insert into B values (005,'女子三千米','三操场');

create table Z (aid number,bid number,score number)
insert into Z values (1001,001,6);
insert into Z values (1002,001,4);
insert into Z values (1003,001,2);
insert into Z values (1004,001,0);
insert into Z values (1001,003,4);
insert into Z values (1002,003,6);
insert into Z values (1004,003,2);
insert into Z values (1005,004,6);
insert into Z values (1006,004,4);
insert into Z values (1003,002,6);
insert into Z values (1005,002,4);
insert into Z values (1006,002,2);
insert into Z values (1001,002,0);

A、求出目前总积分最高的系名，及其积分。

select aclass,sum(score) a from a,z where a.aid=z.aid group by aclass
having sum(score)=(select max(a.a) from (select aclass,sum(score) a from a,z where a.aid=z.aid group by aclass
) a)
B、找出在一操场进行比赛的各项目名称及其冠军的姓名。

select a.aname,z.b from (select a.a,b.b from (select aid a,bid b from z where bid in(select bid from B where cname='一操场' 
) and score=(select max(score) from z)) a,(select bid a,bname b from B where cname='一操场' 
) b where a.b=b.a) z,a where z.a=a.aid

C、找出参加了张三所参加的所有项目的其他同学的姓名。
  
 

select aname from a where aid in(select distinct aid from z where bid in( select bid from z where aid=(  select aid from a where aname='张三'
)) and aid <>(select aid from a where aname='张三')
)

D、经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中作出相应修改。

update z set score=0 where aid=(select aid from a where aname='张三'
)

E、经组委会协商，需要删除女子调高比赛项目。

delete from b where bname='女子跳高'

commit

第七篇

create table studu(
sno varchar(10) primary key,
sname varchar(20),
sage numeric(2),
ssex varchar(5)
);

create table teac(
tno varchar(10) primary key,
tname varchar(20)
);

create table cour(
cno varchar(10),
cname varchar(20),
tno varchar(20)
);

create table grad(
sno varchar(10),
cno varchar(10),
score numeric(4,2)
);

/初始化史莱克学院的学生数据/
insert into studu values ('s001','唐三',23,'男');
insert into studu values ('s002','戴沐白',23,'男');
insert into studu values ('s003','马红俊',20,'男');
insert into studu values ('s004','奥斯卡',25,'男');
insert into studu values ('s005','小舞',22,'女');
insert into studu values ('s006','朱竹清',20,'女');
insert into studu values ('s007','宁荣荣',21,'女');
commit;

/初始化史莱克学院的教师数据/
insert into teac values ('t001', '弗兰德');
insert into teac values ('t002', '柳二龙');
insert into teac values ('t003', '赵无极');
commit; );

/初始化史莱克学院的课程表/
insert into cour values ('c001','linux','t001');
insert into cour values ('c002','oracle','t002');
insert into cour values ('c003','python','t003');
commit;

/初始化史莱克学院的成绩表/
insert into grad values ('s001','c001',78.9);
insert into grad values ('s002','c001',80.9);
insert into grad values ('s003','c001',81.9);
insert into grad values ('s004','c001',40.9);
insert into grad values ('s005','c001',60.9);
insert into grad values ('s006','c001',50.9);
insert into grad values ('s007','c001',90.9);
insert into grad values ('s001','c002',98.0);
insert into grad values ('s002','c002',70.9);
insert into grad values ('s003','c002',41.9);
insert into grad values ('s004','c002',80.9);
insert into grad values ('s005','c002',20.9);
insert into grad values ('s006','c002',70.9);
insert into grad values ('s007','c002',90.9);
insert into grad values ('s001','c003',99.9);
insert into grad values ('s002','c003',40.9);
insert into grad values ('s003','c003',21.9);
insert into grad values ('s004','c003',60.9);
insert into grad values ('s005','c003',90.9);
insert into grad values ('s006','c003',60.0);
insert into grad values ('s007','c003',60.9);
commit;
select * from grad

1、统计史莱克学院所有学生的男生、女生人数
select ssex,count(ssex) from studu group by ssex 
2、查询学生中有姓“唐”的学生信息
select * from studu where sname like'唐%'
3、查询年纪在20岁到23岁区间的学生
select * from studu where sage between 20 and 23
4、查询年纪大于23岁的学生的学生
select * from studu where sage>23
5、查询史莱克学院中所有学生的平均年龄
select avg(sage) from studu
6、给史莱克学院的学生按年龄从大到小排序
select * from studu order by sage desc
7、查询史莱克学院所有同学的学号、姓名、性别、课名、对应老师
select studu.sno,sname,ssex,cname,tname from studu,cour,teac,grad where studu.sno=grad.sno and grad.cno=cour.cno and cour.tno=teac.tno group by studu.sno,sname,ssex,cname,tname
8、查询各科成绩最高和最低的分：显示：课程ID，最高分数 ，最低分数
select cour.cno,max(score),min(score) from grad,cour where grad.cno=cour.cno group by cour.cno
9、查询所有单科成绩小于60 分的同学的学号、姓名、分数，从大到小排序
select studu.sno,sname,score from studu,grad where studu.sno=grad.sno and score<60
10、查询史莱克学院所有学生的平均成绩
select sname,avg(score) from studu,grad where studu.sno=grad.sno group by sname
11、查询史莱克学院所有学生平均成绩大于60 分的同学的学号和平均成绩；

select k.* from (select studu.sno,avg(score) b from studu,grad where studu.sno=grad.sno group by studu.sno
) k where k.b>60
12、查询 c001课程比c002 课程成绩高的所有学生姓名、学号、c001成绩、c002成绩
select studu.sno,sname,cno,score from studu,grad where studu.sno=grad.sno and studu.sno in(select a.sno from grad a,grad b where a.cno='c001' and b.cno='c002' and a.score>b.score and a.sno=b.sno
) and (cno='c001' or cno='c002')
13、查询史莱克学院学生各科成绩的前三名
select * from (select sno,cno,score,row_number()over(partition by cno order by score desc ) as k from grad group by sno,cno,score
) k where k.k<=3

14、查询史莱克学院学生总成绩排名的前三名

 
select * from(select sno n,sum(score) m from grad group by sno order by 
sum(score) desc) a where rownum<=3

第八篇

create table goodstype (
typeid varchar2(8),
typename varchar2(20),
superid varchar2(4));
-- 向商品类型表中插入记录
insert into goodstype values('010101','手机','0101');
insert into goodstype values('0102','摄影机','01');
insert into goodstype values('0201','电视机','02');
insert into goodstype values('0301','笔记本','03');
insert into goodstype values('01','数码产品','0');
insert into goodstype values('0101','通讯产品','01');
insert into goodstype values('010102','对讲机', '0101');
insert into goodstype values('010103','固定电话','0101');
insert into goodstype values('02','家电产品','0');
insert into goodstype values ('0202', 't #L', '02');

create table goods (
gid number (7),
gname varchar2 (30),
typeid varchar2(8),
price number (6, 2),
stock number (3),
soldnum number (2),
scrq date) ;
-- 向商品信息表中插入记录
insert into goods values(2024551,'联想(Lenovo)天逸100','0301',3800,10,2, to_date('2016-03-24','yyyy-mm-dd'));
insert into goods values (1856588, 'Apple iPhoe 6s (A1700) ', '010101', 6400, 10, 2, to_date('2016-04-02', 'yyyy-mm-dd')) ;
insert into goods values(1912210,'创维(Skyworth)55M5','0201',3998,20,3,to_date('2016-05-02','yyyy-mm-dd'));
insert into goods values(1509661,'华为P8','010101',2058,20,5, to_date('2016-01-14','yyyy-mm-dd'));
insert into goods values(1514801,'小米Note白色','010101',1898,10,2,to_date('2016-05-18','yyyy-mm-dd'));
insert into goods values(2327134,'佳能(Canon)HFR76','0102',3570,10,2, to_date('2016-02-26','yyyy-mm-dd'));
insert into goods values(2365929,'索尼(SONY)数码摄像机','0102',9860,10,2, to_date('2016-03-08','yyyy-mm-dd'));
insert into goods values(2381431,'联想(Lenovo)扬天A8000f','0301',8988,10,2,to_date('2016-03-08','yyyy-mm-dd'));
insert into goods values(2571148,'小米红米Note增强版','010101',950,15,4, to_date('2015-12-28','yyyy-mm-dd'));
insert into goods values(2365929,'索尼(SONY)\数码摄像机’,'0102',9860,10,2, to_date('2016-03-08','yyyy-mm-dd'));
insert into goods values (1440305, '松下(Panascinic) HC-V270gK-K', '0201', 3170, 10, 2, to_date('2015-09-15', 'yyyy-mm-dd')) ;
insert into goods values (2527431, 'OPPOA33', '010101', 1280, 25, 9, to_date ('201512-18', 'yyyy-mm-dd'));
insert into goods values(3451067,'魅族MX5','010101',1840,27,6, to_date('2015-11-14','yyyy-mm-dd'));
insert into goods values(1353858,'小天鹅(Little Swan)TG70-1229ED','0202',2800,14,6, to_date('2015-11-14','yyyy-mm-dd'));

select * from goods
select * from goodstype

1.查询商品信息表中生产日期在2016年1月16日之前的商品信息,查询结果仅包含商品名称
和生产日期
select gname,scrq from goods where to_char(scrq,'yyyy-mm-dd')<'2016-01-16'
2.查询商品价格分别为950,2800和3800的商品信息,查询结果仅包括商品名称、价格和
库存数量
select gname,price,(stock-soldnum) 库存 from goods where price in (950,2800,3800)
3.查询商品名称中以“联想”开头的商品信息,查询结果仅包含商品名称
select gname from goods where gname like'联想%'
4.查询商品价格在1000元至3900元之间的商品种数。
select count(*) from goods where price between 1000 and 3900
5.查询商品的总库存数量
select gname,stock from goods
6. 查询不重复的商品类型数量
select distinct typeid from goods
7.查询商品的最高价、最低价和平均价格
select max(price),min(price),avg(price) from goods
8.查询商品价格在5000元以上的商品数据,要求按价格的升序输出。查询结果仅包含商品名
称和商品价格两列
select gname,price from goods where price >5000 order by price desc
9. 查询商品价格在3000元以下的商品数据,要求按生产日期的升序输出,生产日期相同的按
商品价格的降序输出。查询结果只包含商品名称、生产日期和商品价格三列。
select gname,scrq,price from goods where price<3000 order by scrq asc,price desc
10.统计各类商品的平均价格和商品种数
select typeid,avg(price),count(typeid) from goods group by typeid
11.查询2016年生产的价格高于3000元的商品信息,并按金额的降序排列,查询结果包括类型编号,平均价格,和金额三列
数据
select gid,avg(price),price from goods where price>3000 and to_char(scrq,'yyyy')=2016 group by gid,price order by price desc
12.查询商品编号、商品名称、类型名称、商品价格和库存数量,并按商品编号的升序排列
select gid,gname,typename,price,(stock-soldnum) 库存 from goods,goodstype where goods.typeid=goodstype.typeid
13.查询商品信息表中库存数量相等的不同商品,要求查询结果不能出现重复商品,只包括
商品名称为”Apple iPhoe 6s(A1700)“的相同记录,查询结果包括商品编号,商品名称,商品
价格和库存数量四列
select gid,gname,price,(stock-soldnum) 库存 from goods where gname in(select b.gname from goods a,goods b where a.gname='Apple iPhoe 6s (A1700) ' and a.stock=b.stock and a.gname<>b.gname
)
14.查询商品名称为“华为p8”的类型名称
select typename from goods,goodstype where goods.typeid=goodstype.typeid and gname='华为P8'
15.查询价格最高的商品,查询结果包括商品名称、商品价格和库存数量
select gname,price,(stock-soldnum) 库存 from goods where price=(select max(price) from goods 
)
SELECT  * FROM GOODS
commit

第九篇

create table employee (eid number,name varchar2(20),department varchar2(20),job varchar2(20),eamil varchar2(100),password varchar2(100))
insert into employee (eid,name,department,job)values(10001,'李明','sbb','eg');
insert into employee (eid,name,department,job)values(10003,'李筠平','like','itm');
insert into employee (eid,name,department,job)values(11045,'李洁','sbb','eg');
insert into employee (eid,name,department,job)values(10044,'胡玟','mtd','etn');
insert into employee (eid,name,department,job)values(10009,'徐仲刚','sbb','eg');
insert into employee (eid,name,department,job)values(10023,'李燕','sbb','etn');
insert into employee (eid,name,department,job)values(20460,'路名生','mtd','etn');
insert into employee (eid,name,department,job)values(20078,'张青','mmm','eg');
insert into employee (eid,name,department,job)values(20001,'李立','like','etn');

create table training (tid number,eid number,course varchar2(30),grade number,orders varchar(100))

insert into training(tid,eid,course,grade) values (1,10001,'t-sql',60);
insert into training(tid,eid,course,grade) values (3,11045,'oracle',71);
insert into training(tid,eid,course,grade) values (2,20460,'java',34);
insert into training(tid,eid,course,grade) values (1,10003,'t-sql',59);
insert into training(tid,eid,course,grade) values (3,10001,'oracle',90);
insert into training(tid,eid,course,grade) values (2,20001,'java',12);
insert into training(tid,eid,course,grade) values (2,20078,'java',76);
insert into training(tid,eid,course,grade) values (2,10003,'java',78);
insert into training(tid,eid,course,grade) values (3,30001,'oracle',71);
insert into training(tid,eid,course,grade) values (3,20048,'oracle',36);

1.统计各部门人数和各部门姓李的人数

select * from (select department a,count(department) b from employee group by department
) a left join (select department a,count(department) b from employee where name like'李%' group by department
) b on a.a=b.a
2.显示eid,name,department,course
select training.eid,name,department,course from employee,training where employee.eid=training.eid
3.筛出未培训的人员名单
select name from employee where eid not in(select distinct eid from training 
)
4.更新Email
update employee set eamil=(department||name||'@ahcc.com.cn')
select * from employee
5.列出所有各科成绩最高的员工信息

select * from employee where eid in(select eid from (select course a,max(grade) b from training group by course
) a,training where a.a=course and a.b=grade)