Oracle SQL 练习题 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- 建表
CREATE TABLE copy_emp (
empno number(4),
ename varchar2(20),
hiredate date default sysdate,
deptno number(2),
sal number(8,2));

-- 插入数据 sal字段插入空值，部门号50，参加工作时间为2000年1月1日，其他字段随意
insert into copy_emp values(7555, 'Ahui', to_date('2000-01-01','yyyy-mm-dd'),50,null);

-- 插入数据，要求把emp表中部门号为10号部门的员工信息插入
insert all
into copy_emp
values(empno, ename, hiredate, deptno, sal)
select empno, ename, hiredate, deptno, sal
from emp
where deptno = 10;

-- 10号部门所有员工涨20%的工资
update copy_emp
set sal = sal * 1.2
where deptno=10;

-- 修改copy_emp表中sal为空的记录，工资修改为平均工资
update copy_emp 
set sal = (select avg(sal) from copy_emp)
where sal is null;

-- 把工资为平均工资的员工，工资修改为空
update copy_emp 
set sal = null
where sal = (select avg(sal) from copy_emp);

-- 删除工资为空的员工信息
delete from copy_emp where sal is null;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

-- 建表
create table student2 ( --学生表
xh char(4),--学号
xm varchar2(10),--姓名
sex char(2),--性别
birthday date,--出生日期
sal number(7,2), --奖学金
studentcid number(2) --学生班级号
);
Create table class2 ( --班级表
classid number(2), --班级编号
cname varchar2(20),--班级名称
ccount number(3) --班级人数
);

-- 插入
insert into class2(classid,cname) values(1,'JAVA1班');
insert into class2(classid,cname) values(2,'JAVA2班');
insert into class2(classid,cname) values(3,'JAVA3班');

insert into student2 values('A001','张三','男','01-5月-05',100,1);
insert into student2(xh,xm,sex,birthday,sal) values('A002','MIKE','男',to_date('1905-05-06','yyyy-mm-dd'),10);
insert into student2(xh,xm,sex) values('A003','JOHN','女');

-- 将A001学生性别修改为'女‘
-- 将A001学生信息修改如下：性别为男，生日设置为1980-04-01
update student2 set sex='女' where xh='A001';
update student2 set sex='男' where xh='A001';
update student2 set birthday=to_date('1980-04-01','yyyy-mm-dd') where xh='A001';

-- 将生日为空的学生班级修改为Java3班
update student2 
set studentcid=(select classid from class2 where class2.cname='JAVA3班') 
where birthday is null;

-- 请使用一条SQL语句，使用子查询，更新班级表中每个班级的人数字段
Update CLASS2 c2
    Set ccount=(select count(*)
                 from student2 s2
                 where c2.classid= s2.studentcid);

1
2
3
4
5
6
7
8
9

-- 插入日期类型数据
insert into DATE_TEST values(to_date('1998-08-18','YYYY-MM-DD'));
insert into DATE_TEST values(to_date(sysdate));

-- 增加列并设置默认值
alter table DATE_TEST add( G  char(2) DEFAULT '女');

-- 修改列数据类型
alter table DATE_TEST modify(G char(10));

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- 查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位
select ename, hiredate, job
from emp
where hiredate>to_date('1982-07-09','yyyy-mm-dd') and job != 'SALESMAN';

-- 查询员工姓名的第三个字母是A的员工姓名
select ename
from emp
where ename like '__A%';

-- 查询除了10、20号部门以外的员工姓名、部门编号
select ename, deptno
from emp
where deptno!=10 and deptno!=20;

-- 查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序
select *
from emp
where deptno=30
order by sal desc, ename asc;

-- 查询没有上级的员工(经理号为空)的员工姓名
select ename
from emp
where mgr is null;

-- 查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号
select ename, sal, deptno
from emp
where sal>=4500 and (deptno=10 or deptno=20);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

-- 添加数据
insert into STUDENT values('101','张三','男');
insert into STUDENT values('102','李云','女');
insert into STUDENT values('103','小明','男');
insert into STUDENT values('104','李四','男');

insert into SUBJECT values('A01','C++');
insert into SUBJECT values('A02','ASP');
insert into SUBJECT values('A03','JAVA');

insert into GRADETABLE values('101','A01',59);
insert into GRADETABLE values('101','A02',72);
insert into GRADETABLE values('101','A03',90);
insert into GRADETABLE values('102','A01',75);
insert into GRADETABLE values('102','A02',91);
insert into GRADETABLE values('103','A01',71);

-- 学号 学生姓名 课程名称 成绩 （要全部学生信息）
select st.studentid, st.studentname, su.subjectname, gr.grade
from STUDENT st left join GRADETABLE gr on gr.studentid = st.studentid 
    left join subject su on gr.subjectid = su.subjectid;

-- 学号 学生姓名 课程名称 成绩（只显示每科最高分）
select st.studentid, st.studentname, su.subjectname, tab.grade
from student st, subject su, (select studentid, subjectid, grade
                                from gradetable
                                where (subjectid,grade) in (select subjectid, max(grade)
                                from gradetable
                                group by subjectid)) tab
where st.studentid = tab.studentid and su.subjectid = tab.subjectid;


-- 学号 学生姓名 课程名称 成绩 （成绩大于60时的显示及格，小于60时的显示不及格）
select st.studentid, st.studentname, su.subjectname, 
        decode(sign(gr.grade-60), -1, '不及格','及格') 是否及格
from GRADETABLE gr,STUDENT st, subject su
where gr.studentid = st.studentid and gr.subjectid = su.subjectid;

-- 学号 学生姓名 （查询出选课超过1门以上学生的信息）
select st.studentid, st.studentname
from gradetable gr left join student st on st.STUDENTID = gr.STUDENTID
group by st.STUDENTID,st.studentname
having count(*) > 1;

-- 查询成绩大于90分为优秀的人数
select count(*)
from gradetable
group by studentid
having max(grade) > 90;

-- 求男生里面分数最高的那个同学的姓名，课程名，成绩
select st.studentname, su.subjectname, gr.grade
from student st, subject su, gradetable gr
where st.studentid = gr.studentid and su.subjectid = gr.subjectid
        and st.studentsex = '男' 
        and gr.grade = (select max(grade) from (select studentid from student where studentsex = '男') natural join gradetable);


-- 求没有成绩的学生姓名
select s.studentname
from student s
where s.studentid not in (select gr.studentid
                          from gradetable gr);