本练习题包括“单表查询”，“多表查询”，“子查询”，“程序块”。
所有查询均基于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;
/
