Oracle数据库练习题

本练习题包括“单表查询”,“多表查询”,“子查询”,“程序块”。
所有查询均基于HR用户下完成,代码如果有误,可以在文章下面评论,我会及时修改。

单表查询

1、 查询salary大于3000的雇员人数。

select count(*) from employees where salary>3000;

2、查询雇员的最高salary和最低salary

select max(salary) as 最高 ,min(salary) as 最低 from employees;

3、查询每个部门的最高salary和最低salary

select department_id ,max(salary) as 最高,min(salary) as 最低 from employees group by department_id;

4、查询雇员工龄大于十年的雇员编号

select employee_id from employees where (sysdate-hire_date)/365>10;

5、查询第三季度雇佣的雇员的姓名

select first_name||last_name from employees where to_char(hire_date,'mm') in ('07','08','09');

6、查询雇员部门平均工资5000以上的部门编号

select department_id from employees group by department_id having avg(salary)>5000;

7、查询雇员表中不同工种的最高工资,并且按照最高工资的降序排列

select department_id,job_id,max(salary) from employees group by department_id,job_id order by max(salary) desc;

多表查询

1、查询工种名称为Accountant的雇员编号

select employee_id

from employees e,jobs j

where e.job_id=j.job_id and job_title='Accountant';

2、查询编号为100的雇员的部门名称

select department_name

from employees e,departments d

where e.department_id=d.department_id and employee_id=100;

3、查询工资在4000到6000之间的雇员的工种名称

select job_title

from employees e,jobs j

where e.job_id=j.job_id and salary between 4000 and 6000;

4、查询部门名称为Marketing的雇员人数

select count(employee_id)

from employees e,departments d

where e.department_id=d.department_id and deapartment_name='Marketing';

5、查询部门名称为Marketing的雇员编号

select employee_id

from employees e,departments d

where e.department_id=d.department_id and department_name='Marketing';

子查询

6、查询工资最高的员工的编号及其姓名

select employee_id,first_name||last_name

from employees

where salary in (select max(salary) from employees);

7、查询工资大于平均工资的雇员的部门名称

select distinct department_name

from employees,departments

where salary>(select avg(salary) from employees);

8、查询2007年雇佣的雇员的平均工资

select avg(salary)

from employees

where employee_id in (select employee_id

from emploees

where to_char(hire_date,'yyyy')=2007);

9、查询不是2006年雇佣的雇员的编号

select employee_id

from employees

where employee_id in (select employee_id

from employees

where to_char(hire_date,'yyyy')!=2006);

10、查询工资小于5000或者2007年雇佣的雇员编号及其工资(union-并)

select employee_id,salary from emmployees where salary<5000

union

select employee_id,salary from employees where to_char(hire_date,'yyyy')=2007;

11、查询工资大于4000并且编号大于50的雇员编号及last_name (intersect-交)

select employee_id,last_name from employees where salary>4000

intersect

select employee_id,last_name from employees where employee_id>50;

12、查询工资大于4000但不在部门编号为50的雇员编号及其工资 (minus-差)

select employee_id,salary from employees where salary>4000

minus

select employee_id,salary from employees where employee_id=50;

用户与方案

1、System用户登录,创建用户u1,密码u1,默认的表空间为users,分配的配额为10m,密码不过期

create user u1 identified by u1

default tablespace users

quota 10m on users;

2、System用户登录,创建用户u2,密码u2,默认的表空间为users,分配的配额为10m,密码过期

create user u2 identified by u2

default tablespace users

quota 10m on users

password expire;

3、System用户登录,创建用户test,密码为test,默认的表空间为users,配额不限制

create user test identified by test

default tablesapce users

quota unlimited on users;

4、修改用户test,密码为newtest

alter user test identified by newtest

5、锁定用户test

alter user test account lock;

6、System用户登录, 授予用户u1和u2用户连接数据库的权限

grant connect to u1,u2;

7、System用户登录,授予用户u1和u2创建表的权限

grant create table to u1,u2;

8、u1登录并创建表 t1,并输入数据

create table t1

(aa int,bb int);

9、u1授权u2查询表t1和修改表t1

grant select ,update on ti to u2;

10、u2 授权u1删除表t2

grant delete on t2 to u1;

11、u1授权u2修改t1表中的某一列

grant update(aa) on t1 to u2;

12、u2撤销u1删除t2的权限

revoke delete on t2 from u1;

程序块

1.嵌套程序块
set serverout on;
declare
a1 int;
begin
a1:=100;
declare
a2 int;
begin
a2:=200;
dbms_output.put_line(a2);
end;
dbms_output.put_line(a1);
end;

/

2.变量和常量

set serverout on;
declare
aa int;
bb constant int:=100; //常量-constant
cc boolean not null default false;
begin
null;
end;
/

3.若定义变量时指定了null属性,那么必须在定义变量的同时为变量赋值
declare
aa int not null:=100;
begin
null;
end;
/

4.使用select语句为变量赋值,使用是必须和into一起使用,且查询只能返回一行数据
set serveroutput on;
declare
var1 number;
begin
select 10 into var1 from dual;
dbms_output.put_line(var1);
end;
/

5.查询雇员编号为100的员工的工资和last_name
set serverout on;
declare
empname varchar(20);
empsal int;
begin
select last_name,salary into empname,empsal
from employees
where employee_id=100;
dbms_output.put_line('empname:'||empname);
dbms_output.put_line('empsal'||empsal);
end;
/

6.查询雇员编号为100的员工的工资和last_name和工种名称
set serverout on;
declare
empname varchar(20);
empsal int;
job_name varchar(20);
begin
select last_name,salary,job_title
into empname,empsal,job_name
from employees e,jobs j
where e.job_id=j.job_id and employee_id=100;
dbms_output.put_line('empname:'||empname);
dbms_output.put_line('empsal'||empsal);
dbms_output.put_line('job_name:'||job_name);
end;
/

7.查询雇员编号为任意编号的员工的工资和last_name和工种名称
set serverout on;
declare
empname varchar(20);
empsal int;
job_name varchar(40);
begin
select last_name,salary,job_title
into empname,empsal,job_name
from employees e,jobs j
where e.job_id=j.job_id and employee_id=&kk;
dbms_output.put_line('empname:'||empname);
dbms_output.put_line('empsal:'||empsal);
dbms_output.put_line('job_name:'||job_name);
end;
/

8.为创建的新表newemp插入雇员编号为100的员工信息
declare
kk int;
begin
insert into newemp
select * from employees where employee_id=&kk;
end;
/