Oracle学习笔记_交作业_教程宋红康_oracle_sql_plsql

学习Oracle有个把月了,教程花了一点时间看完,跟着章节练习加强记忆.

文中所用的数据表

对应的视频教程

 

lesson01基本sql-select语句

1.sql*plus命令可以 控制数据库吗
不能

2.下列语句是否可以执行成功
select last_name,job_id,salary as say
from employees;
可以

3.找出下面语句中的错误  英文标点符号
select employee_id , last_name，
salary * 12  “ANNUAL SALARY”
from employees;

4.显示表departments的结构,并查询其中的全部数据
desc departments;
select * from departments;

5.显示表employees中的全部job_id(不能重复)
select distinct job_id from employees;

6.显示出表employees的全部列,各个列之间用逗号链接,列头显示成OUT_PUT
select last_name||','||email||','||job_id||','||salary "OUT_PUT"
from employees

 

lesson02过滤和排序数据

 

1.查询工资大于12000的员工姓名和工资
select last_name,salary
from employees
where salary > 12000;

2.查询员工号为176的员工的姓名和部门号
select last_name,department_id
from employees
where employee_id = 176;

3.选择工资不在5000到12000的员工的姓名和工资
select last_name,salary
from employees
where salary not between 5000 and 12000;

4.选择雇佣时间在1998-02-01到1998-05-01之间的员工姓名,job_id和雇佣时间
select last_name,job_id,hire_date
from employees
where to_char(hire_date,'yyyy-mm-dd') between '1998-02-01' and '1998-05-01'

5.选择在20或者50号部门工作的员工姓名和部门号
select last_name,department_id
from employees
where department_id = 20 or department_id = 50;  --第一种写法
where department_id in (20,50); --第二种写法

6.选择在1994年雇佣的员工的姓名和雇佣时间
select last_name,hire_date
from employees
where hire_date like '%94'; --第一种写法
where to_char(hire_date,'yyyy') = '1994';  --第二种写法

7.选择公司中没有管理者的员工姓名和job_id
select last_name,job_id
from employees
where manager_id is null;

8.选择公司中有奖金的员工姓名,工资和奖金级别
select last_name,salary,commission_pct
from employees
where commission_pct is not null

9.选择员工姓名的第三个字母是a的员工姓名
select last_name
from employees
where last_name like '__a%';

10.选择姓名中有字母a和e的员工姓名
select last_name
from employees
where last_name like '%a%e%' or last_name like '%e%a%';

 

lesson03单行函数

 

1.显示系统时间
select sysdate from dual;
1.显示系统时间(日期+时间)
select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss') from dual

2.查询员工号,姓名,工资,以及工资提高百分之20后的结果(new salary)
select employee_id,last_name,salary*1.2 "new salary"
from employees;

3.将员工的姓名按首字母排序,并写出姓名的长度(length)
select last_name,length(last_name)
from employees
order by last_name asc

4.查询个员工的姓名,并显示出各员工在公司工作的月份数(worked_month)
--months_between(date1,date2)返回两个日期的月份数
select last_name,hire_date,round(months_between(sysdate,hire_date)) workede_month
from employees

5.查询员工的姓名,以及在公司工作的月份数(worked_month),并按月份数降序排序
select last_name,round(months_between(sysdate,hire_date)) as worked_month
from employees
order by worked_month desc

 

lesson04多表查询

 

1.显示所有员工的姓名,部门号和部门名称
select last_name,e.department_id,department_name
from employees e,departments d
where e.department_id = d.department_id

2.查询90号部门员工的job_id和90号部门的location_id
select job_id,location_id
from employees e left outer join departments d
on e.department_id = d.department_id
where d.department_id = 90

3.选择所有有奖金的员工的last_name,department_name,location_id,city

select last_name,department_name,d.location_id,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where commission_pct is not null

4.选择city在Toronto工作的员工的last_name,job_id,department_id,department_name

select last_name,job_id,e.department_id,d.department_name,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where l.city = 'Toronto'

5.选择指定员工的姓名,员工号,以及它的管理者的姓名和员工号
select e1.last_name "employees",e1.employee_id "Emp#",e2.last_name "manager",e2.employee_id "Mgr#"
from employees e1,employees e2
where e1.manager_id = e2.employee_id

 

lesson04多表查询复习

 

--多表连接查询时, 若两个表有同名的列, 必须使用表的别名对列名进行引用, 否则出错!

--查询出公司员工的 last_name, department_name, city
select last_name,department_name,city
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id

--查询出 last_name 为 'Chen' 的 manager 的信息. (员工的 manager_id 是某员工的 employee_id) 
--自连接
select manager.last_name,manager.salary,manager.email
from employees emp,employees manager
where emp.manager_id = manager.employee_id and lower(emp.last_name) = 'chen'

--子查询
select last_name,salary,email from employees
where employee_id = (
  select manager_id from employees
  where lower(last_name) = 'chen'
)

--两条sql查询
select manager_id from employees
where last_name = 'Chen'

select last_name,salary,email from employees
where employee_id  = 108

--查询每个员工的 last_name 和 GRADE_LEVEL(在 JOB_GRADES 表中). ---- 非等值连接
select last_name,salary,grade_level
from employees,job_grades
where salary between lowest_sal and highest_sal

 

lesson05分组函数

 

1.组函数处理多行返回一行吗
多行返回多行

2.组函数不计算空值吗
可以计算空值

3.where子句可否使用组函数进行过滤
不可以,可以使用having代替where

4.查询公司员工工资的最大值,最小值,平均值,总和
select max(salary) maxsay,min(salary) minsay,avg(salary) avgsay,sum(salary) sumsay
from employees

5.查询各job_id的员工工资的最大值,最小值,平均值,总和
总和
select max(salary) maxsay,min(salary) minsay,avg(salary) avgsay,sum(salary) sumsay
from employees
group by job_id

6.选择具有各个job_id的员工人数
select job_id,count(employee_id)
from employees
group by job_id

7.查询员工最高工资和最低工资的差距(difference)
select jmax(salary),min(salary),max(salary)-min(salary) "difference"
from employees

8.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id

9.查询所有部门的名字,location_id,员工数量和平均工资值
select d.department_name,d.location_id,count(e.employee_id),avg(e.salary) avgsay
from employees e right outer join departments d
on e.department_id = d.department_id
group by d.department_name,location_id

10.查询工资在1995-1998年之间,每年雇佣的人数,结果类似下面的格式
total 1995 1996 1997 1998
 20    3    4    6    7
 
select count(*) "total",
  count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995", 
  count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
  count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
  count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in ('1995','1996','1997','1998')

 

lesson05分组函数复习

 

分组函数作用与一组数据,并对一组数据返回一个值,比如sum,avg,max,min,count(计数),stddev(求标准差)等就是典型的分组函数

group by 对数据进行分组
having 过滤数据集

max,min不限制数据类型,number,varchar,date类型都可以
avg和sum只能number类型
count(*)返回表中的记录总数,适用于任何数据类型;()括号里面只要有值就行 
count((nvl)commission_pct,1)  1星号是当数据为空的时候赋值
单行函数可以嵌套在分组函数里面
count(distinct expr)返回expr非空且不重复的记录总数

使用多个列分组:
group by deparmtent_id,job_id

凡是查询当中的列,不是组函数的列,都应该出现在group by中

非法使用组函数:
不能再where子句中使用组函数
可以在having中使用组函数

 

lesson06子查询

 

1.查询和Zlotkey相同部门的员工姓名和雇佣日期
select last_name,hire_date
from employees
where department_id = (
  select department_id
  from employees
  where last_name = 'Zlotkey'
)

2.查询工资比公司平均工资高的员工的员工号,姓名和工资
select employee_id,last_name,salary
from employees
where salary > (
  select avg(salary)
  from employees
)

3.查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
select employee_id,last_name,salary
from employees e1
where salary > (
  select avg(salary)
  from employees e2
  where e1.department_id = e2.department_id
  group by department_id
)

4.查询姓名中包含字母U的员工在相同部门的员工的员工号和姓名
select employee_id,last_name
from employees
where department_id in (
  select department_id
  from employees
  where last_name like '%u%'
)
and last_name not like '%u%'

5.查询在部门的location_id为1700的部门工作的员工的员工号
select employee_id
from employees
where department_id in (
  select department_id
  from departments
  where location_id = 1700
)

6.查询管理者是King的员工姓名和工资
select last_name,salary
from employees
where manager_id in (
  select employee_id
  from employees
  where last_name = 'King'
)

 

lesson07创建和管理表

 

1.创建表dept1

 

create table dept1(
id number(7)
name varchar2(25)
)

 

2.将表departments中的数据插入新表dept2中

 

create table dept2
as
select * from departments

 

3.创建表emp5

 

create table emp5(
id number(7),
first_name varchar2(25),
last_name varchar2(25),
dept_id number
)

 

4.将列last_name的长度增加到50

 

alter table emp5
modify (last_name varchar2(50))

 

5.根据表employees创建employees2

 

creat table employees2
as
from * employees

 

6.删除表emp5

 

drop table emp5

 

7.将表employees2重命名为emp5

 

rename employees2 to emp5

 

8.在表dept和emp5中添加新列test_column,并检查所做的操作

 

alter table dept
add(test_column number(10));
desc dept;

 

9.在表dept和emp5中将列test_column设置成不可用,之后删除

 

alter table emp5
set unused column test_column;

alter table emp5
drop unused columns;

 

10.直接删除表emp5中的列dept_id

 

alter table emp5
drop column dept_id

 

lesson08数据处理

 

1.运行以下脚本创建表my_employees
create table my_employees(
  id number(3),
  first_name varchar2(10),
  last_name varchar2(10),
  user_id varchar2(10),
  salary number(5)
)

2.显示表my_employees的结构

oracle:
select * from user_tab_columns where table_name='MY_EMPLOYEES'

命令窗口:
desc my_employees

3.将以下数据插入表中
insert into my_employees values(1,'patel','ralph','rpatel',895);
insert into my_employees values(2,'dancs','betty','bdancs',860);
insert into my_employees values(3,'biri','ben','bbiri',1100);
insert into my_employees values(4,'newman','chad','cnewman',750);
insert into my_employees values(5,'ropeburn','audrey','aropebur',1550);

4.将3号员工的last_name修改为"drelxer"
update my_employees
set last_name='drelxer'
where id = 3

5.将所有工资少于900的员工的工资修改为1000
update my_employees
set salary = 1000
where salary <900

6.检查所做的修改
select * 
from my_employees
where salary < 900
7.提交
commit
8.删除所有数据
delete from my_employees
9.检查所做的修改
select * from employees
10.回滚
rollback
11.清空表my_employees
truncate table my_employees

 

lesson09约束

 

准备工作
create table emp2 as select employee_id id,last_name name,salary
from employees

create table dept2 as select department_id id,department_name dept_name
from departments

1.向表emp2的id列中添加primary key约束(my_emp_id_pk)
alter table emp2
add constraint my_emp_id_pk primary key(id)

2.向表dept2的id列中添加primary key约束(my_dept_id_pk)
alter table dept2
add constraint my_dept_id_pk primary key(id)

3.向表emp2中添加列dept_id,并在其中定义foreign key约束,与之相关联的列是dept2表中的id列
alter table emp2
add(detp_id number(10) constraint emp2_dept_id_fk references
dept3(id))

 

lesson10视图

 

1.使用表employees创建视图employee_vu,其中包括姓名(last_name),员工号(employee_id),部门号(department_id)
-- create or replace view 如果已有这个视图名则替代
-- create view 如果已有这个视图名则报错
create or replace view employee_vu
as
select last_name,employee_id,department_id
from employees

2.显示视图的结构
desc employee_vu

3.查询视图中的全部内容
select * from employee_vu

4.将视图中的数据限定在部门号是80的范围
create or replace view employee_vu
as
select last_name,employee_id,department_id
from employees
where department_id = 80

5.将视图改变成只读视图
create or replace view employee_vu
as
select last_name,employee_id,department_id
from employees
where department_id = 80
with read only  -- 视图设置为只读

 

lesson11其他数据库对象

 

准备工作:基于employees表创建表dept,dept表数据为空
create table dept as
select department_id id,depattment_name name
from employees
where 1=2

1.创建序列dept_id_seq,开始值为200,每次增长10,最大值为10000
create sequence dept_id_seq
start with 200
increment by 10
maxvalue 10000

2.使用序列向表dept中插入数据
insert into dept01 values(dept_id_seq.nextval,'Account')

 

lesson12控制用户权限

 

1.如果用户能够登录到数据库,至少需要哪种权限?是系统权限还是对象权限?
create session --系统权限

2.创建表需要哪种权限?
create table  --创建表权限

3.将表departments的查询权限分配给用户systeem
grant select      --赋予/授予 查询权限
on departments    --将departments表的查询权限赋予给
to system         --赋予给 system这个用户

4.从system出回收刚才赋予的权限
revoke select     --取消/废除 查询权限
on departments    --将departments表的查询权限删除,删除哪个用户的权限?
from system       --删除 system用户的权限

5.创建角色dvp,并将如下权限赋予给该角色
--CREATE PROCEDURE
--CREATE SESSION
--CREATE TABLE
--CREATE SEQUENCE
--CREATE VIEW

create role dvp;  --创建角色,名为dvp
grante --赋予/授予
create procedure, --创建 储存过程权限
create session,  --创建 会话控制权限
create table,  --创建 表权限
create sequence,  --创建 序列权限
create view  --创建 视图权限
to dvp;  --赋予给 dvp这个用户