db + oracle scott / emp / dept / salgrade / bonus

s

 

注意事项 示例或解释
列的选择 SELECT 语句中,除了聚合函数外的每个列都必须在 GROUP BY 子句中。
聚合函数 通常需要包含聚合函数,例如 COUNT(), SUM(), AVG(), MAX(), 或 MIN()
HAVING 子句 如果需要进一步过滤结果,可以使用 HAVING 子句。
列的选择 ORDER BY 子句中可以按照查询中的任意列进行排序。
排序顺序 默认情况下,排序是升序的(ASC)。可以使用 DESC 进行降序排序。
数字和字符串排序 SQL 对数字和字符串的排序方式可能会不同。确保理解你的数据类型,并根据需要选择正确的排序方式。
列的选择 GROUP BYORDER BY 可以引用不同的列,但是在 SELECT 子句中引用的列必须在 GROUP BY 子句中。
别名问题 ORDER BY 子句中不能使用 SELECT 中定义的列别名。
聚合函数和排序 如果在 SELECT 子句中使用了聚合函数,而且同时也使用了 ORDER BY,则应该按照聚合函数的别名或索引进行排序。
多列排序 可以同时按照多个列进行排序。在 ORDER BY 子句中,列的顺序决定了优先级。

 

jdbc 驱动包下载

https://repo1.maven.org/maven2/com/oracle/database/jdbc/

- Oracle 10g 数据库环境

1 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
2 PL/SQL Release 10.2.0.3.0 - Production
3 CORE 10.2.0.3.0 Production
4 TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
5 NLSRTL Version 10.2.0.3.0 - Production

- 服务端创建用户表空间,创建txj账户

C:\Documents and Settings\Administrator>sqlplus / as sysdba
--表空间默认50M,自动扩展10M,最大空间100M。 先建好文件夹testspace_datafile。
create tablespace testspace logging datafile 'c:\testspace_datafile\108.ora' size 50M autoextend on next 10M maxsize 100M;
--创建用户,指定表空间testspace,临时表空间temp,并解锁txj账户
create user txj profile default identified by txj default tablespace testspace temporary tablespace temp account unlock; 
--给普通用户txj赋权,CONNECT表示连库权限 , RESOURCE表示开发人员权限,DBA表示管理员权限
grant connect,resource,dba to txj;

- dba权限txj用户复制scott用户表,防止捣烂scoot表数据

-- dba权限txj用户复制scott用户表,防止捣烂scoot表数据
create table salgrade as select * from scott.emp;
create table salgrade as select * from scott.dept;
create table salgrade as select * from scott.bonus;
create table salgrade as select * from scott.salgrade;

- scott.emp员工表结构 , https://www.cnblogs.com/huyong/archive/2011/06/03/2071228.html

Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO    NUMBER(4)                       员工号       
ENAME    VARCHAR2(10) Y                  员工姓名       
JOB      VARCHAR2(9)  Y                  工作       
MGR      NUMBER(4)    Y                  上级编号       
HIREDATE DATE         Y                  雇佣日期       
SAL      NUMBER(7,2)  Y                  薪金       
COMM     NUMBER(7,2)  Y                  佣金       
DEPTNO   NUMBER(2)    Y                  部门编号

- scott.dept部门表 , 提示:工资=薪金+佣金

Name   Type         Nullable Default Comments 
------ ------------ -------- ------- -------- 
DEPTNO NUMBER(2)                         部门编号        
DNAME  VARCHAR2(14) Y                    部门名称     
LOC    VARCHAR2(13) Y                    地点   

scott.emp表的现有数据

SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     4000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
  102 EricHu     Developer  1455 2011-5-26 1   5500.00     14.00     10
  104 huyong     PM         1455 2011-5-26 1   5500.00     14.00     10
  105 WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10
 
17 rows selected

- Scott.dept表的现有数据

SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    50 50abc          50def
    60 Developer      HaiKou
 
6 rows selected

- 作业列表1

1.列出至少有一个员工的所有部门。
2.列出薪金比“SMITH”多的所有员工。
3.列出所有员工的姓名及其直接上级的姓名。
4.列出受雇日期早于其直接上级的所有员工。
5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
6.列出所有“CLERK”(办事员)的姓名及其部门名称。
7.列出最低薪金大于1500的各种工作。
8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。
9.列出薪金高于公司平均薪金的所有员工。
10.列出与“SCOTT”从事相同工作的所有员工。
11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
14.列出所有员工的姓名、部门名称和工资。
15.列出所有部门的详细信息和部门人数。
16.列出各种工作的最低工资。
17.列出各个部门的MANAGER(经理)的最低薪金。
18.列出所有员工的年工资,按年薪从低到高排序。

- 参考答案

--------1.列出至少有一个员工的所有部门。---------
SQL> select dname from dept where deptno in(select deptno from emp); 
DNAME
--------------
RESEARCH
SALES
ACCOUNTING
--------或--------
SQL> select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1); 
DNAME
--------------
ACCOUNTING
RESEARCH
SALES

--------2.列出薪金比“SMITH”多的所有员工。----------
SQL> select * from emp where sal > (select sal from emp where ename = 'SMITH');
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     4000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
  102 EricHu     Developer  1455 2011-5-26 1   5500.00     14.00     10
  104 huyong     PM         1455 2011-5-26 1   5500.00     14.00     10
  105 WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10
 16 rows selected

--------3.列出所有员工的姓名及其直接上级的姓名。----------
SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a; 
ENAME      BOSS_NAME
---------- ----------
SMITH      FORD
ALLEN      BLAKE
WARD       BLAKE
JONES      KING
MARTIN     BLAKE
BLAKE      KING
CLARK      KING
SCOTT      JONES
KING       
TURNER     BLAKE
ADAMS      SCOTT
JAMES      BLAKE
FORD       JONES
MILLER     CLARK
EricHu     
huyong     
WANGJING    
17 rows selected

--------4.列出受雇日期早于其直接上级的所有员工。----------
SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr); 
ENAME
----------
SMITH
ALLEN
WARD
JONES
BLAKE
CLARK 
6 rows selected

--------5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门----------
SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno
  2  from dept a left join emp b on a.deptno=b.deptno;
 
DNAME          EMPNO ENAME      JOB         MGR HIREDATE          SAL DEPTNO
-------------- ----- ---------- --------- ----- ----------- --------- ------
RESEARCH        7369 SMITH      CLERK      7902 1980-12-17     800.00     20
SALES           7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00     30
SALES           7521 WARD       SALESMAN   7698 1981-2-22     1250.00     30
RESEARCH        7566 JONES      MANAGER    7839 1981-4-2      2975.00     20
SALES           7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00     30
SALES           7698 BLAKE      MANAGER    7839 1981-5-1      2850.00     30
ACCOUNTING      7782 CLARK      MANAGER    7839 1981-6-9      2450.00     10
RESEARCH        7788 SCOTT      ANALYST    7566 1987-4-19     4000.00     20
ACCOUNTING      7839 KING       PRESIDENT       1981-11-17    5000.00     10
SALES           7844 TURNER     SALESMAN   7698 1981-9-8      1500.00     30
RESEARCH        7876 ADAMS      CLERK      7788 1987-5-23     1100.00     20
SALES           7900 JAMES      CLERK      7698 1981-12-3      950.00     30
RESEARCH        7902 FORD       ANALYST    7566 1981-12-3     3000.00     20
ACCOUNTING      7934 MILLER     CLERK      7782 1982-1-23     1300.00     10
ACCOUNTING       102 EricHu     Developer  1455 2011-5-26 1   5500.00     10
ACCOUNTING       104 huyong     PM         1455 2011-5-26 1   5500.00     10
ACCOUNTING       105 WANGJING   Developer  1455 2011-5-26 1   5500.00     10
50abc                                                                 
OPERATIONS                                                            
Developer                                                          
 
20 rows selected

--------6.列出所有“CLERK”(办事员)的姓名及其部门名称。----------
SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK'; 
ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ADAMS      RESEARCH
JAMES      SALES
MILLER     ACCOUNTING

--------7.列出最低薪金大于1500的各种工作。----------
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500; 
HIGHSALJOB
----------
ANALYST
Developer
MANAGER
PM
PRESIDENT

--------8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。----------
SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES'); 
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES 
6 rows selected

--------9.列出薪金高于公司平均薪金的所有员工。----------
SQL> select ename from emp where sal>(select avg(sal) from emp); 
ENAME
----------
JONES
BLAKE
SCOTT
KING
FORD
EricHu
huyong
WANGJING 
8 rows selected

--------10.列出与“SCOTT”从事相同工作的所有员工。--------
SQL> select ename from emp where job=(select job from emp where ename='SCOTT');
 ENAME
----------
SCOTT
FORD

--------11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。---------
SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal
  2  from emp b where b.deptno=30) and a.deptno<>30; 
ENAME            SAL
---------- ---------

--------12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。---------
SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30); 
ENAME            SAL
---------- ---------
JONES        2975.00
SCOTT        4000.00
KING         5000.00
FORD         3000.00
EricHu       5500.00
huyong       5500.00
WANGJING     5500.00 
7 rows selected

--------13.列出在每个部门工作的员工数量、平均工资和平均服务期限。---------
SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal
  2  from emp a group by deptno; 
DEPTNAME        DEPTCOUNT DEPTAVGSAL
-------------- ---------- ----------
ACCOUNTING              6 4208.33333
RESEARCH                5       2375
SALES                   6 1566.66666

--------14.列出所有员工的姓名、部门名称和工资。---------
SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a; 
 
ENAME      DEPTNAME             SAL
---------- -------------- ---------
SMITH      RESEARCH          800.00
ALLEN      SALES            1600.00
WARD       SALES            1250.00
JONES      RESEARCH         2975.00
MARTIN     SALES            1250.00
BLAKE      SALES            2850.00
CLARK      ACCOUNTING       2450.00
SCOTT      RESEARCH         4000.00
KING       ACCOUNTING       5000.00
TURNER     SALES            1500.00
ADAMS      RESEARCH         1100.00
JAMES      SALES             950.00
FORD       RESEARCH         3000.00
MILLER     ACCOUNTING       1300.00
EricHu     ACCOUNTING       5500.00
huyong     ACCOUNTING       5500.00
WANGJING   ACCOUNTING       5500.00
 
17 rows selected

--------15.列出所有部门的详细信息和部门人数。---------
SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a; 
DEPTNO DNAME          LOC            DEPTCOUNT
------ -------------- ------------- ----------
    10 ACCOUNTING     NEW YORK               6
    20 RESEARCH       DALLAS                 5
    30 SALES          CHICAGO                6
    40 OPERATIONS     BOSTON        
    50 50abc          50def         
    60 Developer      HaiKou     
 
6 rows selected

--------16.列出各种工作的最低工资。---------
SQL> select job,avg(sal) from emp group by job;
 
JOB         AVG(SAL)
--------- ----------
ANALYST         3500
CLERK         1037.5
Developer       5500
MANAGER   2758.33333
PM              5500
PRESIDENT       5000
SALESMAN        1400
 
7 rows selected

--------17.列出各个部门的MANAGER(经理)的最低薪金。--------
SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;
 
DEPTNO   MIN(SAL)
------ ----------
    10       2450
    20       2975
30       2850

--------18.列出所有员工的年工资,按年薪从低到高排序。---------
SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;
 
ENAME       SALPERSAL
---------- ----------
SMITH            9600
JAMES           11400
ADAMS           13200
MILLER          15600
TURNER          18000
WARD            21000
ALLEN           22800
CLARK           29400
MARTIN          31800
BLAKE           34200
JONES           35700
FORD            36000
SCOTT           48000
KING            60000
EricHu          66168
huyong          66168
WANGJING        66168
 
17 rows selected

- 作业列表2

/*
  作业:
  1.在EMP表中,增加一名员工,员工信息参照现有员工构造.
  2.员工SMITH部门调动到SALES部门,请编写SQL语句更新员工信息.
  3.员工JAMES已经离职,请编写SQL语句更新数据库.
  4.按照职位分组,求出每个职位的最大薪水
  5.求出每个部门中的每个职位的最大薪水
  6.列出SMITH的薪水和职位
  7.查询EMP表,输出每个部门的各个职位的平均工资,并按部门编号升序、平均工资降序排序。
  8.创建一张新表empbak,表数据和表结构均来自emp表
  9.给有奖金的涨薪15%,没有的奖金的薪资不变
  10.给MANAGER和SALESMAN职位的员工涨薪20%
*/
--   1.在EMP表中,增加一名员工,员工信息参照现有员工构造.
select * from emp;
insert into emp values('8002','张四','MANAGER',7902,to_date('2023/2/21','yyyy-mm-dd'),900.00,'',20);
--  2.员工SMITH部门调动到SALES部门,请编写SQL语句更新员工信息.
update emp set deptno=30 where empno=7369;
--  3.员工JAMES已经离职,请编写SQL语句更新数据库.
delete from emp where empno=7900;
--  4.按照职位分组,求出每个职位的最大薪水
select job,max(sal) from emp
group by job
order by max(sal) asc;
--  5.求出每个部门中的每个职位的最大薪水
select deptno,job,max(sal) from emp
group by deptno,job;
--  6.列出SMITH的薪水和职位
select ename,sal,job from emp where ename='SMITH';
--  7.查询EMP表,输出每个部门的各个职位的平均工资,并按部门编号升序、平均工资降序排序。
select deptno,avg(sal) from emp
group by deptno,job
order by deptno asc,avg(sal) desc;
--  8.创建一张新表empbak,表数据和表结构均来自emp表
create table empbak as select * from emp;
--  9.给有奖金的涨薪15%,没有的奖金的薪资不变
update emp set sal=sal*(1+0.15) where comm is not null;
select * from emp;
--  10.给MANAGER和SALESMAN职位的员工涨薪20%
update emp set sal=sal*(1+0.2) where job='MANAGER' or job='SALESMAN';
commit;
select * from emp;

update emp set sal=sal*(1+0.2) where job in ('MANAGER','SALESMAN');
commit;
select * from emp;

- 作业列表3

/*
    1、给emp表添加身份证号码card属性,类型为int型。
    2、修改card字段类型为字符型,长度为50
    3、删除emp表奖金字段
    4、查看学生表表 结构
    5、查询员工SMITH详细信息
    6、修改SMITH的奖金为500,升职为经理且部门改成30部门。
    7、删除ALLEN员工信息
    8、新增一个新员工信息至员工表,数据自己构造
    9、给没有奖金的员工涨薪20%
    10、给有奖金的员工降薪15%
create table txj.emp as select * from scott.emp;
select * from txj.emp;
drop table emp;
*/
-- 1
alter table emp add card int;
select * from txj.emp;
-- 2 
alter table emp drop column card;
alter table emp add card int;
-- 3
alter table emp drop column comm;
alter table emp add comm int;
-- 4
alter table emp drop column comm;
select * from emp where ename='SMITH';
-- 5
update emp set comm=500,job='MANAGER',deptno=30 where ename='SMITH';
commit;
-- 6
select * from emp where ename='SMITH';
-- 7
delete from emp where ename='ALLEN';
commit;
-- 8
insert into emp(ename) values('txj');
commit;
-- 9
update emp set sal=sal*(1+0.2) where comm is null;
commit;
-- 10
update emp set sal=sal*(1-0.15) where comm is not null;
commit;
select * from emp;

- 20230316 , primary key , foreign key

-

-- 建表Person,字段IDCard,姓名pname,性别sex,年龄age,日期
drop table person;
create table person(
       IDCard number primary key,
       pname varchar2(20) not null,
       sex varchar2(2),
       age int,
       birthday date
)
-- 主键写法二
create table person(IDCard number
       pname varchar2(20) not null,
       sex varchar2(2),
       age int,
       birthday date,
       primary key(IDCard)
)

alter table person modify IDCard varchar2(20);
insert into person values('123456789012345678','张三','',20,'');
insert into person values('223456789012345678','张三','',20,'');
commit;
select * from person;

delete from person where pname='张三';
commit;

alter table person drop constraint SYS_C005491; --删除系统自动主键名称约束

alter table person add constraint primary_key_name primary key(IDCard); --手工添加主键并指定主键名称为primary_key_name

alter table person add mobile varchar2(11);  --添加手机号字段
alter table person add constraint unique_mobile unique(mobile); --添加唯一性约束

create table person(
       IDCard number primary key,
       pname varchar2(20) not null,
       sex varchar2(2),
       age int,
       birthday date,
       mobile varchar2(11) unique
)

create table person(
       IDCard number 
       pname varchar2(20) not null,
       sex varchar2(2),
       age int,
       birthday date,
       primary key(IDCard),
       constraint person_ps_uq unique(pname) --也可以这样给名字增加唯一性约束
)

-- check 检查约束
alter table person add constraint check_sex check(sex in('',''));

select * from person;
insert into person values('123456789012345677','李四','',30,'','13851897759');
insert into person values('123456789012345676','李四','',30,'','13851897758');

-- 外键(FOREIGN KEY)约束:用来维护从表(Child Table)和主表(Parent Table)之间的引用完整性。
select * from orders;
drop table orders;
create table orders(
  order_id varchar2(20) primary key,
  price    number(10,2),
  pname    varchar2(2)   -- 一个汉字2个字节,不够插
);
alter table orders modify pname varchar2(20);

select * from order_detail;
drop table order_detail;
create table order_detail(
  order_detail_id  varchar2(20) primary key,
  order_id varchar2(20),
  item_name varchar2(20),
  constraint foreign_key_order_id foreign key(order_id) references orders(order_id)
);
alter table order_detail drop constraint foreign_key_order_id; --删除子表外键约束
-- 删除主表数据时,相关子表数据一并删除
alter table order_detail add constraint fk_order_id foreign key(order_id) references orders(order_id)on delete cascade;
-- 子表order_detail有了on delete cascade,主表删除order_id=100则自动删除子表order_id=100的行数据
delete from orders where order_id=(100); 

insert into orders values('100',20.05,'苹果');
insert into orders values('101',20,'香蕉');
insert into orders values('102',99.99,'榴莲');
select * from orders;

insert into order_detail values('10001','100','通信类');
commit;
select * from order_detail;

drop table master_bank;
create table master_bank(
       bankid number primary key,
       bankname varchar2(20)
)
drop table sub_bank;
create table sub_bank(
       fbankid number primary key,
       bankid number,
       fbank_name varchar2(20),
       address varchar2(20),
       manager varchar2(20),
       tel number,
       constraint fk_bankid foreign key(bankid) references master_bank(bankid)
)
select * from sub_bank;
alter table sub_bank modify fbankid varchar2(10);
alter table sub_bank drop constraint fk_bankid; --删除外键约束
-- 删除主表数据时,相关子表数据一并删除
alter table sub_bank add constraint fk_bankid foreign key(bankid) references master_bank(bankid) on delete cascade;

insert into master_bank values (1001,'中国银行');
insert into master_bank values (1002,'建设银行');
insert into master_bank values (1003,'交通银行');
insert into master_bank values (1004,'工商银行');
insert into master_bank values (1005,'浦发银行');
insert into master_bank values (1006,'南京银行');
insert into master_bank values (1007,'农业银行');
insert into master_bank values (1008,'邮政银行');
insert into master_bank values (1009,'光大银行');

insert into sub_bank values ('f_001',1001,'中国银行南京分行','中南京路1号','黄经理',833123333);
insert into sub_bank values ('f_002',1001,'中国银行合肥分行','中合肥路1号','李经理',833123334);
insert into sub_bank values ('f_003',1001,'中国银行苏州分行','中苏州路1号','李经理',833123335);
insert into sub_bank values ('f_004',1002,'建设银行南京分行','建南京路1号','黄经理',833123336);
insert into sub_bank values ('f_005',1002,'建设银行合肥分行','建合肥路1号','黄经理',833123337);
insert into sub_bank values ('f_006',1002,'建设银行苏州分行','建南京路1号','黄经理',833123338);
insert into sub_bank values ('f_007',1002,'建设银行镇江分行','建南京路1号','黄经理',833123339);
insert into sub_bank values ('f_008',1003,'工商银行南京分行','工南京路1号','黄经理',833123340);
insert into sub_bank values ('f_009',1003,'工商银行北京分行','工北京路1号','黄经理',833123341);
insert into sub_bank values ('f_010',1003,'工商银行云南分行','工云南路1号','黄经理',833123341);
-- insert into sub_bank values ('f_011',8888,'工商银行云南分行','工云南路1号','黄经理',833123341); -- 插入异常,data异常

select * from master_bank;
select * from sub_bank;
delete from master_bank;
delete from sub_bank;
/*
  1、给学生表的学号添加主键约束;
  2、给学生表的年龄添加默认值20;
  3、给学生表的出生日期添加不为空约束
  4、给学生表的姓名添加唯一约束
  5、给学生表的性别添加check约束
*/
-- 方式一,建表时就添加约束
drop table student3;
create table student4(
       sno number primary key,
       sname varchar2(20) unique,
       sex varchar2(2) check (sex in ('','')),
       age int default 20,
       birth date not null
)

-- 方式二,建表后,另外加约束
create table student5(
       sno number,
       sname varchar2(20),
       sage number default 20,
       ssex varchar2(2),
       birth date
)
alter table student5 add constraint pk_sno primary key(sno);
alter table student5 add constraint unique_sname unique(sname);
alter table student5 add constraint check_ssex check(ssex in('',''));
-- 删除外键等
alter table student5 drop constraint pk_sno;
alter table student5 drop constraint unique_sname;
alter table student5 drop constraint check_ssex;
alter table student5 modify birth not null;
alter table student5 modify sage default 30;

-- 连接查询,内连接(等值连接),左外连接,右外连接,全外连接, 自连接 
-- 注意:测试角度,三表以上联查是为bug,撑爆了。
drop table emp;
drop table dept;
create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
create table salgrade as select * from scott.salgrade;
select * from emp;
select * from dept;
select * from salgrade;
-- 内连接,写法一
select * from emp e,dept d where e.deptno = d.deptno order by empno;
select e.*,d.* from emp e,dept d where e.deptno = d.deptno order by empno;
-- 内连接,写法二,表1 inner join 表2 on 表1.字段 = 表2.字段
select * from emp e inner join dept d on e.deptno = d.deptno;

- 作业列表

/*
  1、查询员工编号,员工名称,薪水和员工所在的部门名称
  2、查询员工编号,员工名称,薪水,和员工所在部门名称,以及每个员工的薪资等级
  3、查询员工薪资等级
  4、查询有员工的部门(不是空部门)名称
  5、请查出SMITH的薪水等级和他所在部门所在地
*/
-- 查询员工编号,员工名称,薪水和员工所在的部门名称
select e.deptno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno order by sal;
-- 查询员工编号,员工名称,薪水,和员工所在部门名称,以及每个员工的薪资等级
select e.empno,e.ename,e.sal,d.dname,s.grade from emp e,dept d,salgrade s 
where e.deptno = d.deptno 
and e.sal between s.losal and s.hisal; --薪资比较等级
-- 查询员工薪资等级
select e.ename,e.sal,s.grade from emp e,salgrade s
where e.sal between s.losal and s.hisal;

-- 查询有员工的部门(不是空部门)名称
select * from emp,dept;
select * from dept; -- 肉眼能看出40空部门编号不在emp中,下一步剔除
select distinct(e.deptno) from emp e;
select distinct(e.deptno),d.dname from emp e,dept d
where e.deptno = d.deptno; --剔除不相等的条件,即剔除空部门的

-- 请查出SMITH的薪水等级和他所在部门所在地
select * from emp e where e.ename='SMITH';
select * from emp e,dept d where e.ename='SMITH';
select e.ename,s.grade,d.loc  from emp e,salgrade s,dept d
where e.sal between s.losal and s.hisal
and d.deptno = e.deptno
and e.ename = 'SMITH';

--不带where条件容易产生笛卡尔积,无意义
select count(1) from emp e,dept d;

- 作业列表

/*
  1、对emp表中sal、comm进行加计算,并使用别名命令为员工的月总收入,同时展示出员工部门编号、员工姓名信息。
  2、使用连接符查询emp表中员工的姓名和工资,并以如下格式列出且字段名展示为 TOTAL INCOME:
  SMITH total income is XXXXX
  3、使用distinct排重查询emp中的job类型
  4、从emp表中找出奖金高于 薪水60%的员工
  5、找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料。
  6、从emp和dept中联合查询,并将员工编号、姓名、职位、地址信息列出。
  7、统计各部门的薪水总和。  
  8、找出部门10中所有理(MANAGER),部门20中所有办事员(CLERK)以及既不是经理又不是办事员但其薪水大于或等2000的所有员工的详细资料。   
  9、列出各种工作的最低工资。
  10、列出各个部门的MANAGER(经理)的最低薪水。  
  11、列出有奖金的员工的不同工作。   
  12、找出无奖金或奖金低于300的员工。  
  13、显示所有员工的姓名,并使姓名首字母大写。  
  14、显示正好为5个字符的员工的姓名。  
  15、显示不带有“R”的员工姓名。   
  16、列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水。  
  17、列出在每个部门工作的员工数量、平均工资和平均服务期限。
  18、列出从事同一种工作但属于不同部门的员工的一种组合。 
  19、列出薪水比“SMITH”多的所有员工。  
  20、列出至少有一个员工的所有部门。 
  21、对于每个员工,显示其加入公司的天数、月数、年数。  
  22、对21中的天数、月数、年数取整显示。
  23、找出在每年5月受聘的所有员工。  
  24、显示在一个月为30天的情况下所有员工的日薪水,取整。  
  25、显示所有员工的姓名和加入公司的年份和月份,并将员工入职年月从低到高排序。   
*/
-- 1
select sal+nvl(comm,0) as 员工的月总收入,e.deptno 员工部门编号,e.ename 员工姓名 from emp e;
-- 2
select 'TOTAL INCOME:' || ename || ' total income is ' || sal from emp;
-- 3
select distinct(e.job) from emp e;
-- 4
select e.ename from emp e where comm > sal*0.6;
-- 5
select e.* from emp e
where (e.deptno = 10 and job='MANAGER')
or (e.deptno = 20 and job='CLERK');
-- 6
select e.empno,e.ename,e.job,d.loc from emp e,dept d
where e.deptno = d.deptno;
-- 7
select e.* from emp e;
select e.deptno,sum(e.sal) from emp e
group by e.deptno;
-- 8
select * from emp e
where (e.deptno = 10 and e.job = 'MANAGER')
or (e.deptno = 20 and e.job = 'CLERK')
or (job not in('MANAGER','CLERK') and sal >= 2000);
-- 9
select job,min(sal) from emp group by job;
-- 10
select e.deptno,min(e.sal) from emp e where e.job = 'MANAGER'
group by e.deptno;
-- 11
select e.job from emp e where comm is not null group by job;
select distinct(e.job) from emp e where comm is not null;
-- 12
select e.ename,e.comm from emp e where comm is null or comm < 300;
-- 13
select ename,initcap(e.ename) from emp e;
-- 14
select ename from emp e where length(e.ename)=5;
-- 15
select e.ename from emp e where e.ename not like '%R%';
-- 16
select ename,sal from where sal > (select max(sal) from emp where deptno=30);
-- 查无数据,造数据核实下
-- select * from emp for update;
-- 17
select deptno,count(1) 员工数量,round(avg(sal))平均工资 ,round(avg(round((sysdate - hiredate)/365))) 入职年限 from emp e
group by deptno;
-- 18 自连接
-- 19
select * from emp where sal > (select sal from emp where ename = 'SMITH');
-- 20
select distinct(d.dname) from emp e, dept d where e.deptno = d.deptno
-- 21
select (sysdate-hiredate) as 天数, (sysdate-hiredate)/365 年数,(sysdate-hiredate)/365*12 月数 from emp e;
-- 22
select round((sysdate-hiredate)) as 天数, round((sysdate-hiredate)/365) 年数,round((sysdate-hiredate)/365*12) 月数 from emp e;
-- 23
select * from emp where to_char(hiredate,'mm') = '05';
-- 24
select round((sal+nvl(comm,0))/30) from emp;
-- 25
select ename,to_char(hiredate,'yyyy') 年份, to_char(hiredate,'mm')月份 from emp
order by 年份,月份;

-

oracle 练习题

https://www.cnblogs.com/liyuelian/p/16782510.html

-

1.查询emp表,显示薪水大于2000,且工作类别是MANAGER的雇员信息
2.查询emp表,显示年薪大于30000,工作类别不是MANAGER的雇员信息
3.查询emp表, 显示薪水在1500到3000之间,工作类别以“M”开头的雇员信息
4.查询emp表,显示佣金为空并且部门号为20或30的雇员信息   
5.查询emp表,显示佣金不为空或者部门号为20的雇员信息,要求按照薪水降序排列
6.查询emp表,显示年薪大于30000工作类别不是MANAGER,且部门号不是10或40的雇员信息,要求按照雇员姓名进行排列
7.查询EMP表,输出每个部门的平均工资,并按部门编号降序排列.
8.查询EMP表,输出每个职位的平均工资,按平均工资升序排列.
9.查询EMP表,输出每个部门的各个职位的平均工资,并按部门编号升序、平均工资降序排序。
10.使用子查询,找出哪个部门下没有员工
11.使用子查询,找出那些工资低于所有部门的平均工资的员工
12.使用子查询,找出那些工资低于任意部门的平均工资的员工,比较一下与第2题输出的结果是否相同?
13.查询姓“刘”的老师的个数
14.查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
15.查询每门课程被选修的学生数;
16.查询同名同姓学生名单,并统计同名人数
17.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
18.查询课程名称为“SSH”,且分数低于60 的学生姓名和分数
19.查询所有学生的选课情况
20.查询任何一门课程成绩在70 分以上的姓名、课程名称和分数;
21.查询不及格的课程,并按课程号从大到小排列
22.查询课程编号为c001 ,且课程成绩在80 分以上,学生的学号和姓名
23.查询不同课程,成绩相同的学生的学号、课程号、学生成绩
24.统计每门课程的学生选修人数(超过10 人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列;
25.检索至少选修两门课程的学生学号
26.查询全部学生都选修的课程的课程号和课程名
27.检索“c003”课程分数小于60,按分数降序排列的同学学号
28.删除“s002”同学的“c001”课程的成绩
29.查询“c001”课程比“c002”课程成绩高的所有学生的学号
30.查询所有同学的学号、姓名、选课数、总成绩
31.查询没学过“谌燕”老师课的同学的学号、姓名
32.查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名
33.查询学过“谌燕”老师所教的所有课的同学的学号、姓名
34.查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名
35.查询没有学全所有课的同学的学号、姓名;
36.查询平均成绩大于85 的所有学生的学号、姓名和平均成绩;
37.查询两门以上不及格课程的同学的学号及其平均成绩。

-

-- 1
select e.ename,e.sal from emp e 
where sal+nvl(comm,0) > 2000 and job='MANAGER'

-

使用课程中初始化的表,完成练习:
1、查询平均成绩大于60 分的同学的学号和平均成绩;group-by-having-avg
2、查询姓“刘”的老师的个数;count-like
3、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;distinct-max-mingroup
4、查询每门课程被选修的学生数;
5、查询男生、女生人数;
6、查询姓“张”的学生名单;like
select sno, avg(score)
from sc
group by sno
HAVING avg(score) > 60;
select count(*)
from teacher
where tname
LIKE '刘%';
select distinct(cno) as 课程ID,max(score) as 最高分,min(score) as 最低分
from sc
GROUP BY cno;
select distinct(dname) as 课程ID,count(*) as 学生人数
from student
GROUP BY dname;
select count(*) as 男生人数 from student where ssex='';
select count(*) as 女生人数 from student where ssex='';
# 分组
select ssex,count(*)from student group by ssex;
7、查询同名同姓学生名单,并统计同名人数;count,group-by,having
8、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;
avg,order-by(asc,desc),多排序条件
9、查询课程名称为“SSH”,且分数低于60 的学生姓名和分数;三张表的连表查询
10、查询所有学生的选课情况;
11、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数;
select 姓名,课程名称,分数
from 学生表 join 学生成绩表 on 学生编号
大于70分;
select *
from student
where sname like '张%';
select sname,count(*) as snum
from student
group by sname
having count(*) > 1;
select cno,avg(score) as score
from sc
group by cno
order by score asc,cno desc;
select st.sname,sc.score
from student AS st
join sc on st.sno = sc.sno
join course on sc.cno = course.cno
where sc.score < 60 and course.cname='SSH';
select st.sname,st.dname,sc.cno
from student as st
join sc on st.sno = sc.sno;
12、查询不及格的课程,并按课程号从大到小排列;order-by desc,where 小于分数
13、查询课程编号为c001 ,且课程成绩在80 分以上,学生的学号和姓名;多表连接查询
14、求选了课程的学生人数;基于第10题,count
15、查询不同课程,成绩相同的学生的学号、课程号、学生成绩;where 课程不同 和 成绩相同
16、统计每门课程的学生选修人数(超过10 人的课程才统计)。要求输出课程号和选修人数,查询结果
按人数降序排列,若人数相同,按课程号升序排列;
select st.sname,course.cname,sc.score
from student as st
join sc on sc.sno = st.sno
join course on sc.cno = course.cno
where sc.score > 70;
select cno,score
from sc
where score < 60
ORDER BY cno desc;
select st.sno,st.sname
from student st
join sc on st.sno=sc.sno
where sc.cno='c001' and sc.score > 80;
select cno,count(sno)
from sc
group by cno;
select s1.sno,s1.cno,s1.score
from sc as s1
join sc as s2 on s1.sno=s2.sno
where s1.cno != s2.cno and s1.score=s2.score;
select cno,count(*)
from sc
group by cno
order by count(*) desc,cno asc;
17、检索至少选修两门课程的学生学号;count(*) > 1
18、查询全部学生都选修的课程的课程号和课程名;多表连接查询
19、检索“c003”课程分数小于60,按分数降序排列的同学学号;where 小于60,order-by-desc;
20、删除“s002”同学的“c001”课程的成绩;
21、查询“c001”课程比“c002”课程成绩高的所有学生的学号;group-by,where 比较
22、查询所有同学的学号、姓名、选课数、总成绩;join,count,sum(score)
select sno,count(*)
from sc
group by sno
having count(*) > 1;
# 查询课程表的编号--不存在--查询所有学生--不存在--查询学生成绩(成绩表学生编号=学生表编号,并
且,成绩课程编号=课程表编号)
select cno from course where not exists(select * from student where not
exists(select * from sc where sc.sno=student.sno and course.cno=sc.cno));
select st.sno
from student as st
join sc on sc.sno=st.sno
where sc.cno='c003' and sc.score < 60
order by sc.score desc;
delete from sc where sno='s002' and cno='c001';
select st.sno from student st
join sc a on st.sno=a.sno
join sc b on st.sno=b.sno
where a.cno='c002' and b.cno='c001' and a.score > b.score
23、查询没学过“谌燕”老师课的同学的学号、姓名;多表连接查询,not
24、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名;
学生表和学生成绩表连表,
where 多条件,and
25、查询学过“谌燕”老师所教的所有课的同学的学号、姓名;
同23,不用取反
26、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名;
连表
min(c002)
where cno=c001 and score < min(成绩)
select a.*,s.sname
from (
select sno,sum(score) as 总成绩,count(cno) as 选课数
from sc
group by sno
) as a ,student as s
where a.sno=s.sno;
select st.sno,st.sname from student st where st.sno not in
(
select distinct sno from sc s
join course c on s.cno=c.cno
join teacher t on c.tno=t.tno where tname='谌燕'
)
select st.sno,st.sname from sc as a
join sc as b on a.sno=b.sno
join student as st on st.sno=a.sno
where a.cno='c001' and b.cno='c002' and st.sno=a.sno;
select distinct st.sno,st.sname from student as st join sc as s on st.sno=s.sno
join course as c on s.cno=c.cno
join teacher as t on c.tno=t.tno
where t.tname='谌燕';
27、查询所有课程成绩小于60 分的同学的学号、姓名;连表,where
28、查询没有学全所有课的同学的学号、姓名;
29、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名;
30、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名;
select st.sno,st.sname from student as st
join sc as a on st.sno=a.sno
join sc as b on st.sno=b.sno
where a.cno='c002' and b.cno='c001' and a.score < b.score;
select st.sno,st.sname,s.score from student as st
join sc as s on st.sno=s.sno
join course as c on s.cno=c.cno
where s.score < 60;
select st.sno,st.sname,count(sc.cno) from student as st
left join sc on st.sno=sc.sno
group by st.sno,st.sname
having count(sc.cno)<(select count(distinct cno)from course);
select st.sno,st.sname from student as st,
(select distinct a.sno from
(select * from sc) as a,
(select * from sc where sc.sno='s001') as b
where a.cno=b.cno
) as c
where st.sno=c.sno and st.sno<>'s001'
select st.sno,st.sname from sc
left join student as st on st.sno=sc.sno
where sc.sno<>'s001' and sc.cno in (select cno from sc where sno='s001')
31、向SC 表中插入一些记录,这些记录要求符合以下条件:没有上过编号“c002”课程的同学学号、
“c002”号课的平均成绩;
insert into
32、按各科平均成绩从低到高和及格率的百分数从高到低顺序;
33、查询不同老师所教不同课程平均分从高到低显示;
34、统计列出各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60];
比较运算符,分组
35、查询出只选修了一门课程的全部学生的学号和姓名;
select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002')
from student as st,sc
where not exists
(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';
select cno,avg(score),sum(score>=60)/count(*)*100 as 及格率
from sc group by cno
order by avg(score) , 及格率 desc;
select max(t.tno),max(t.tname),max(c.cno),max(c.cname),c.cno,avg(score) from sc
, course as c,teacher as t
where sc.cno=c.cno and c.tno=t.tno
group by c.cno
order by avg(score) desc;
select sc.cno,c.cname,
sum(score between 85 and 100) AS "[100-85]",
sum(score between 70 and 85) AS "[85-70]",
sum(score between 60 and 70) AS "[70-60]",
sum(score <60) AS "[<60]"
from sc, course as c
where sc.cno=c.cno
group by sc.cno ,c.cname;
36、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩;
学生表和成绩表连接查询
avg(成绩)
37、查询选修“谌燕”老师所授课程的学生中,成绩最高的学生姓名及其成绩;
38、查询两门以上不及格课程的同学的学号及其平均成绩。
select sc.sno,st.sname,count(cno) from student as st
left join sc
on sc.sno=st.sno
group by st.sname,sc.sno
having count(cno)=1;
select st.sno,st.sname,avg(score) from student as st
left join sc on sc.sno=st.sno
group by st.sno,st.sname
having avg(score)>85;
select st.sname,score
from student as st,sc,course as c,teacher as t
where
st.sno=sc.sno and sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕'
and sc.score=(select max(score)from sc where sc.cno=c.cno);
select sno,avg(score)from sc
where sno in
(
select sno from sc where sc.score<60
group by sno having count(sno)>1
) group by sno;

-

oracle sql,查询员工表emp月工资是2000或3000的员工

SELECT *
FROM emp
WHERE sal = 2000 OR sal = 3000;

oracle sql,查询员工表emp每个员工的领导姓名

你可以使用自连接查询来实现。

SELECT e.ename, m.ename AS leader_name
FROM emp e
LEFT JOIN emp m
ON e.mgr = m.empno
ORDER BY e.ename;

上面的查询语句将获取emp表中每个员工的名字和他们的领导名字。e.mgr列与m.empno列进行连接,并将结果按照员工姓名进行排序。

oracle sql,查询员工每个员工的薪资等级

你可以使用Oracle提供的带有Analytic Function的查询语句,例如:

SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS salary_grade
FROM emp;

上面的查询语句将获取emp表中每个员工的名字和薪资,并使用DENSE_RANK() Over (order by sal desc)当做分析函数,将结果按照薪资排序并分为不同的薪资等级。

注意,这里使用了DENSE_RANK()函数,因为薪资可能存在相同的情况,所以要确保相同的薪资得到相同的薪资等级。

oracle sql,查询员工表emp cleark 职位的每个部门中的人数

你可以使用GROUP BY子句和聚合函数COUNT()来获取每个部门中Clerk职位的人数,例如:
SELECT deptno, COUNT() AS clerk_num
FROM emp
WHERE job = 'CLERK'
GROUP BY deptno;
上面的查询语句将获取emp表中职位为Clerk的员工所在的每个部门的编号以及Clerk人数,使用WHERE子句过滤职位为Clerk的员工,然后使用GROUP BY子句按照部门编号分组,最后使用COUNT()函数统计每个组内的行数(即Clerk人数)。

 

 

 

end

posted @ 2009-10-01 13:18  siemens800  阅读(31)  评论(0)    收藏  举报