题目练习

标签：ename 题目 sal -- 练习 emp deptno select

--创建表
create table disney(
id number primary key,
data varchar2(10));
--创建序列
create sequence id_seq;
--创建触发器
create or replace trigger bifer_disney_id_pk
beFORe insert
on disney
FOR each row
begin
    select id_seq.nextval into :new.id FROM dual;
end;
--插入数据
insert into Disney(data) values('tom');
insert into Disney(id,data) values(6,'jerry');
--测试
select * from disney;

conn system/123456
alter user scott identified by tiger;
conn scott/tiger
create table student
(
id number,
name varchar2(50),
star varchar2(100)
);
commit;

conn system/123456
grant  create  view to scott;
conn scott/tiger;

--在SCOTT模式下，检索emp表的指定列（empno,ename,job）,并使用as关键字为这些列指定中文的别名
select ename 姓名 , empno 员工编号,job 职位 from emp;

--检索emp表的sal列，把其值调整为原来的1.1倍
update emp set sal=sal*1.1 ;

--显示emp表中job列，要求显示的“职务”文字不重复
select distinct job 职位 from emp;
--在scott模式下，查询emp表中工资（sal）大于1500的数据记录
select sal from emp where sal>1500 ;

--在emp表中，使用like关键字匹配以字母s开头的任意长度的员工名称
select * from emp where ename like 'S%';

--在emp表中，使用betwwen…and关键字查询工资（sal）在2000元到3000元之间的员工信息
select * from emp where sal  between 2000 and 3000;
--查询emp表中没有奖金的员工信息
select * from emp where comm is null;

--在emp表中，查询既不是最高工资，也不是最低工资的员工信息
select * from emp where sal<>(select max(sal) from emp) and sal !=
(select min(sal) from emp);

--在emp表中，使用关联子查询检索工资大于同职位的平均工资的员工信息
select * from emp e where sal>(select avg(sal) from emp where job=e.job);
group by 分组having 分组后筛选

--在scott 模式下，通过deptno 字段来内连接emp表和dept 表，并检索这两个表中相关字段的信息
select * from emp e ,dept d where e.deptno =d.deptno; 
--在scott 模式下，通过交叉连接dept 表和emp表，计算出查询结果的行数
select count(*) from emp e, dept d where e.deptno=d.deptno;
--在scott 模式下，使用count函数计算员工总数，使用AVG函数计算平均工资
select count(*), avg(sal) from emp;
--在emp表中，使用group by 子句对部门进行分组，并计算平均工资、所有工资的总和，以及最高工资 和各组的行数
select avg(sal),sum(sal),max(sal),count(*) FROM emp group by deptno;
--在emp表中，首先通过分组的方式计算出每个部门的平均工资，然后再通过having (类似where)子句过滤出平均工资大于2000元的记录信息
select deptno, avg(sal) from emp group by  deptno
having avg(sal)>2000;
--在scott模式下，检索emp表中的所有数据，并按照部门编号（deptno）、员工编号（empno）排序
select * from emp order by deptno asc,sal desc;
--在scott模式下，创建一个查询部门编号为20的视图
create view deptno_view
AS select * from emp where deptno=20;


--在scott 模式下，创建一视图，要求能够查询到每个部门的工资情况
create or replace  view  sal_view
AS
select deptno,avg(sal)avgsal,count(*)total from emp group by deptno;
--将emp_view_complex视图中员工编号是7566的员工的工资改为3000元
create view emp_view_complex
AS 
select empno,sal from emp;
update emp_view_complex set sal=3000 where empno=7566;

--使用loop循环依次输出1～5的立方数
--显示结果
set serverout on
declare
 i number:=1;
begin
    loop
         dbms_output.put_line(i*i*i);
         i:=i+1;
    exit when i>5;
    end loop;
end;
--for循环
declare
i number:=1;
begin
    for i in 1..10 loop
    dbms_output.put_line(i*i*i);
    end loop;
end;
--while循环
declare
 i number:=1;
begin
    while i<= 10 loop
    dbms_output.put_line(i*i*i);
    i:=i+1;
    end loop;
end;
--阶乘n!
set serverout on
declare
 res number:=1;
 i number:=1;
begin
    loop
    res:=res*i;
    i:=i+1;
    exit when i>6;
    end loop;
    dbms_output.put_line(res);
end;

declare
 res number:=1;
 i number:=1;
begin
    while i<=6 loop
    res:=res*i;
    i:=i+1;
    end loop;
dbms_output.put_line(res);
end;

declare
 res number:=1;
 i number:=1;
begin
    for i in 1..6 loop
    res:=res*i;
    
    end loop;
dbms_output.put_line(res);
end;

conn system/123456
show user
create user wangmin identified by w123456;
grant create table to wangmin;
会话权限：
grant create session to wangmin ;

grant resource to wangmin;
登录权限：
grant connect to wangmin;




create table student
(
xh number(4),
xm varchar(20),
sex char(2),
birthday date
);
create table class 
(
classid number(2),
cname varchar2(40)
);
--修改表修改数据类型
alter table student add(classid number(2));
alter table student modify(xh varchar(30));

rename student to stu;
rename stu to student;

insert into student values ('A001','张三','男','01-5月-05','2');
INSERT INTO student(xh,xm,sex,birthday) VALUES ('A004','MARTIN','男',null);

select * from student where birthday is null;
select * from student where birthday is not null;

drop table student; 
drop table class; 

--函数查询
conn scott/tiger
select max(sal),min(sal),avg(sal) from emp;
select ename,job from emp where sal=(select max(sal) from emp);

select deptno ,avg(sal),max(sal) from emp group by deptno;

select d.dname,e.ename,e.sal from emp e,dept d where e.deptno=d.deptno and e.deptno=10;


p111,5.1-5.8

conn system/123456
grant create view to scott;
conn scott/tiger
1
create or replace view emp_view
as select ename ,job,sal from emp;
2
create or replace view sal_mid
as select ename ,job,sal from emp
where sal between 1000 and 3000
with check option;
3
create or replace view dep_10as
as select ename ,job,sal from emp
where deptno =10
with read only;
4
create or replace view sal_avgAS
as select deptno,avg(sal)avgsal,count(*)total from emp
group by deptno;
5
create or replace view emp_deptAS
as select empno, ename ,dname,job,sal from emp,dept
where emp.deptno =dept.deptno;
6
create or replace view emp_view
as select empno, ename,job,sal from emp;
7
rename emp_view to emp_view1;
8
drop view emp_view1;

col view_name FORmat a10
select view_name,text from user_views;



--序列
create sequence myseq
start with 2899587
increment by 2
nocache
nocycle;
select 'JD'||myseq.nextval from dual;

p179 7.1-7.3
set serverout on 
--编写pl/sql块，根据输入的部门编号统计部门内的员工总人数
declare 
  n_deptno number;
  n_total number;
  no_dept exception;
begin
  n_deptno:=&n;
  select count(deptno) into n_total
from emp where deptno=n_deptno;
if n_total=0 then
  raise no_dept;
else
  dbms_output.put_line(n_deptno||'部门的总人数为'||n_total);
end if;
exception
  when no_dept then dbms_output.put_line('此部门不存在');
end;
--if条件分支控制语句。工作时间满15年增加1000元薪水，10-15增加500元，少于10增加200元
declare
  n_hire number(6,2);
  n_empno number;
begin
  n_empno:=&n;
  select months_between(sysdate,hiredate)into n_hire from emp where empno=n_empno;
  if n_hire>=360 then
  Update emp set sal=sal+1000 where empno=n_empno;
  elsif n_hire>=240 then
  Update emp set sal= sal+500 where empno=n_empno;
  else Update emp set sal=sal+300 where empno=n_empno;
end if ;
end;

set serveroutput on
--用for循环打印三角形
begin
  for i in 1..10
loop
    for j in 1..i
loop
      dbms_output.put('*');
   end loop;
   dbms_output.put_line('');
  end loop;
 end;

一、连接命令

1.conn[ect]

用法：conn 用户名/密码

2.disc[onnect]

说明: 该命令用来断开与当前数据库的连接

3.psssw[ord]

说明: 该命令用于修改用户的密码，如果要想修改其它用户的密码，需要用sys/system登录。

4.show user

说明: 显示当前用户名

5.exit

二、交互式命令 &n

三、oracle用户管理

概述：在oracle中要创建一个新的用户使用create user语句，一般是具有dba(数据库管理员)的权限才能使用。

create user 用户名 identified by 密码

给用户修改密码

概述：如果给自己修改密码可以直接使用

password 用户名

如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限

SQL> alter user 用户名 identified by 新密码

删除用户

概述：一般以dba的身份去删除某个用户，如果用其它用户去删除用户则需要具有drop user的权限。

比如 drop user 用户名【cascade】

四、建表
--学生表
create table student (    ---表名
          xh       number(4),   --学号
          xm    varchar2(20),      --姓名
          sex      char(2),     --性别
          birthday date         --出生日期

);

--班级表
CREATE TABLE class(
classId NUMBER(2),
cName VARCHAR2(40)

);

修改表
添加一个字段

SQL>ALTER TABLE student add (classId NUMBER(2));
修改一个字段的长度
SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30));
修改字段的类型/或是名字（不能有数据）不建议做
SQL>ALTER TABLE student modify (xm CHAR(30));
删除一个字段不建议做(删了之后，顺序就变了。加就没问题，应为是加在后面)
SQL>RENAME student TO stu;
删除表
SQL>DROP TABLE student;

添加数据
所有字段都插入数据
INSERT INTO student VALUES ('A001', '张三', '男', '01-5月-05');
oracle中默认的日期格式‘dd-mon-yy’ dd日子（天） mon 月份 yy 2位的年 ‘09-6月-99’ 1999年6月9日
修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）
ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd';
修改后，可以用我们熟悉的格式添加日期类型：
INSERT INTO student VALUES ('A002', 'MIKE', '男', '1905-05-06');
插入部分字段
INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女');
插入空值
INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null);
问题来了，如果你要查询student表里birthday为null的记录，怎么写sql呢？
错误写法：select * from student where birthday = null;
正确写法：select * from student where birthday is null;
如果要查询birthday不为null,则应该这样写：
select * from student where birthday is not null;

五、简单数据查询(50分 )

完成142-143页的6.34到6.45

六、数据分组 ——max，min， avg， sum， count

问题：如何显示所有员工中最高工资和最低工资？

SELECT MAX(sal),min(sal) FROM emp e;

最高工资那个人是谁？

错误写法：select ename, sal from emp where sal=max(sal);

正确写法：select ename, sal from emp where sal=(select max(sal) from emp);

注意：select ename, max(sal) from emp;这语句执行的时候会报错，说ORA-00937：非单组分组函数。因为max是分组函数，而ename不是分组函数.......

但是select min(sal), max(sal) from emp;这句是可以执行的。因为min和max都是分组函数，就是说：如果列里面有一个分组函数，其它的都必须是分组函数，否则就出错。这是语法规定的问题：如何显示所有员工的平均工资和工资总和？

问题：如何计算总共有多少员工问题：如何

扩展要求：

查询最高工资员工的名字，工作岗位

SELECT ename, job, sal FROM emp e where sal = (SELECT MAX(sal) FROM emp);

显示工资高于平均工资的员工信息

SELECT * FROM emp e where sal > (SELECT AVG(sal) FROM emp);

group by 和 having子句

group by用于对查询的结果分组统计，

having子句用于限制分组显示结果。

问题：如何显示每个部门的平均工资和最高工资？

SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno;

（注意：这里暗藏了一点，如果你要分组查询的话，分组的字段deptno一定要出现在查询的列表里面，否则会报错。因为分组的字段都不出现的话，就没办法分组了）

问题：显示每个部门的每种岗位的平均工资和最低工资？

SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job;

问题：显示平均工资低于2000的部门号和它的平均工资？

SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) < 2000;

对数据分组的总结

1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中)

2 如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by

3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错。

如SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000;

这里deptno就一定要出现在group by 中

多表查询：是指基于两个和两个以上的表或是视图的查询。在实际应用中，查询单个表可能不能满足你的需求，（如显示sales部门位置和其员工的姓名），这种情况下需要使用到（dept表和emp表）

问题：显示部门号为10的部门名、员工名和工资？

SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10;

问题：显示各个员工的姓名，工资及工资的级别？

先看salgrade的表结构和记录

SQL>select * from salgrade;

GRADE LOSAL HISAL

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

扩展要求：

问题：显示雇员名，雇员工资及所在部门的名字，并按部门排序？

SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno;

（注意：如果用group by，一定要把e.deptno放到查询列里面）

自连接

自连接是指在同一张表的连接查询

问题：显示某个员工的上级领导的姓名？

比如显示员工‘FORD’的上级

SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD';

七、子查询

什么是子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

单行子查询

单行子查询是指只返回一行数据的子查询语句

请思考：显示与SMITH同部门的所有员工？

思路：

1 查询出SMITH的部门号

select deptno from emp WHERE ename = 'SMITH';

2 显示

SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH');

数据库在执行sql 是从左到右扫描的，如果有括号的话，括号里面的先被优先执行。

多行子查询

多行子查询指返回多行数据的子查询

请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

SELECT DISTINCT job FROM emp WHERE deptno = 10;

SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10);

（注意：不能用job=..，因为等号=是一对一的）

在多行子查询中使用all操作符

问题：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号？

SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30);

扩展要求：

大家想想还有没有别的查询方法。

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

执行效率上，函数高得多

在多行子查询中使用any操作符

问题：如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号？

SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);

扩展要求：

大家想想还有没有别的查询方法。

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30);

多列子查询

单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询是指查询返回多个列数据的子查询语句。

请思考如何查询与SMITH的部门和岗位完全相同的所有雇员。

SELECT deptno, job FROM emp WHERE ename = 'SMITH';

SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH');

在from子句中使用子查询

请思考：如何显示高于自己部门平均工资的员工的信息

思路：

1. 查出各个部门的平均工资和部门号

SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno;

2. 把上面的查询结果看做是一张子表

SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;

如何衡量一个程序员的水平？

网络处理能力，数据库，程序代码的优化程序的效率要很高

八、用查询结果创建新表（视图）

这个命令是一种快捷的建表方式

CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp;

创建好之后，desc mytable；和select * from mytable;看看结果如何？

完成 111页开始的例题 5.1-5.8

合并查询

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus

多用于数据量比较大的数据局库，运行速度快。

1). union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

SELECT ename, sal, job FROM emp WHERE sal >2500

UNION

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

2).union all

该操作符与union相似，但是它不会取消重复行，而且不会排序。

SELECT ename, sal, job FROM emp WHERE sal >2500

UNION ALL

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

3). intersect

使用该操作符用于取得两个结果集的交集。

SELECT ename, sal, job FROM emp WHERE sal >2500

INTERSECT

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

4). minus

使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。

SELECT ename, sal, job FROM emp WHERE sal >2500

MINUS

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

（MINUS就是减法的意思）

九输出下列的编号序列和连接符

JD2898587 JD2898589 JD 2898589

十、oracle的函数

sql函数的使用

字符函数

介绍

字符函数是oracle中最常用的函数，我们来看看有哪些字符函数：

lower(char)：将字符串转化为小写的格式。

upper(char)：将字符串转化为大写的格式。

length(char)：返回字符串的长度。

substr(char,m,n)：取字符串的子串n代表取n个的意思，不是代表取到第n个 replace(char1,search_string,replace_string)

instr(char1,char2,[,n[,m]])取子串在字符串的位置

问题：将所有员工的名字按小写的方式显示

SQL> select lower(ename) from emp;

问题：将所有员工的名字按大写的方式显示。

SQL> select upper(ename) from emp;

问题：显示正好为5个字符的员工的姓名。

SQL> select * from emp where length(ename)=5;

问题：显示所有员工姓名的前三个字符。

SQL> select substr(ename,1,3) from emp;

问题：以首字母大写,后面小写的方式显示所有员工的姓名。

SQL> select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;

问题：以首字母小写,后面大写的方式显示所有员工的姓名。

SQL> select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp;

问题：显示所有员工的姓名，用“我是老虎”替换所有“A”

SQL> select replace(ename,'A', '我是老虎') from emp;

数学函数介绍

数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，我们讲最常用的：

round(n,[m])该函数用于执行四舍五入，如果省掉m，则四舍五入到整数，如果m是正数，则四舍五入到小数点的m位后。如果m是负数，则四舍五入到小数点的m位前。

trunc(n,[m]) 该函数用于截取数字。如果省掉m，就截去小数部分，如果m是正数就截取到小数点的m位后，如果m是负数，则截取到小数点的前m位。

mod(m,n)

floor(n) 返回小于或是等于n的最大整数

ceil(n) 返回大于或是等于n的最小整数

对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报表有不同的结果。

问题：显示在一个月为30天的情况下，所有员工的日薪金，忽略余数。

SQL> select trunc(sal/30), ename from emp;

SQL> select floor(sal/30), ename from emp;

在做oracle测试的时候，可以使用dual表

select mod(10,2) from dual;结果是0

select mod(10,3) from dual;结果是1

其它的数学函数

abs(n)：返回数字n的绝对值

select abs(-13) from dual;

acos(n)：返回数字的反余弦值

asin(n)：返回数字的反正弦值

atan(n)：返回数字的反正切值

cos(n)：

exp(n)：返回e的n次幂

log(m,n)：返回对数值

power(m,n)：返回m的n次幂

日期函数

介绍

日期函数用于处理date类型的数据。

默认情况下日期格式是dd-mon-yy 即12-7月-78

(1)sysdate: 该函数返回系统时间

(2)add_months(d,n)

(3)last_day(d)：返回指定日期所在月份的最后一天

问题：查找已经入职8个月多的员工

SQL> select * from emp where sysdate>=add_months(hiredate,8);

问题：显示满10年服务年限的员工的姓名和受雇日期。

SQL> select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10);

问题：对于每个员工，显示其加入公司的天数。

SQL> select floor(sysdate-hiredate) "入职天数",ename from emp;

SQL> select trunc(sysdate-hiredate) "入职天数",ename from emp;

问题：找出各月倒数第3天受雇的所有员工。

SQL> select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

转换函数

to_char

你可以使用select ename, hiredate, sal from emp where deptno = 10;显示信息，可是，在某些情况下，这个并不能满足你的需求。

问题：日期是否可以显示时/分/秒

SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp;

yy：两位数字的年份 2004-->04

yyyy：四位数字的年份 2004年

mm：两位数字的月份 8月-->08

dd：两位数字的天 30号-->30

hh24： 8点-->20

hh12：8点-->08

mi、ss-->显示分钟\秒

9：显示数字，并忽略前面0

0：显示数字，如位数不足，则用0补齐

.：在指定位置显示小数点

,：在指定位置显示逗号

$：在数字前加美元

L：在数字前面加本地货币符号

C：在数字前面加国际货币符号

G：在指定位置显示组分隔符、

D：在指定位置显示小数点符号(.)

问题：显示薪水的时候，把本地货币单位加在前面

SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'), to_char(sal,'L99999.99') from emp;

问题：显示1980年入职的所有员工

SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;

问题：显示所有12月份入职的员工

SQL> select * from emp where to_char(hiredate, 'mm')=12;

to_date

函数to_date用于将字符串转换成date类型的数据。

系统函数

sys_context

1）terminal：当前会话客户所对应的终端的标示符

2）lanuage: 语言

3）db_name：当前数据库名称

4）nls_date_format：当前会话客户所对应的日期格式

5）session_user：当前会话客户所对应的数据库用户名

6）current_schema：当前会话客户所对应的默认方案名

7）host：返回数据库所在主机的名称

通过该函数，可以查询一些重要信息，比如你正在使用哪个数据库？

select sys_context('USERENV','db_name') from dual;

注意：USERENV是固定的，不能改的，db_name可以换成其它,比如select sys_context('USERENV','lanuage') from dual;又比如select sys_context('USERENV','current_schema') from dual;

十一 PL/SQL 完成实训 7.1-7.4

十二触发器完成

122 例题5.25 需要输出6条以上的内容

标签：ename,题目,sal,--,练习,emp,deptno,select
From： https://www.cnblogs.com/bky-wang/p/18125067

相关文章

赞助商

阅读排行