–1.查詢emp表，顯示薪水大於2000，且工作類別是MANAGER的僱員資訊
select * from emp
where sal > 2000
and job = `MANAGER`;
–2.查詢emp表，顯示年薪大於30000，工作類別不是MANAGER的僱員資訊
select * from emp
where (sal+nvl(comm , 0 ))*12 > 30000
and job != `MANAGER`;
–3.查詢emp表， 顯示薪水在1500到3000之間，工作類別以“M”開頭的僱員資訊
select * from emp
where sal between 1500 and 3000
and job like `M%`;
–4.查詢emp表，顯示佣金為空並且部門號為20或30的僱員資訊
select * from emp
where comm is null
and deptno in ( 20 , 30 );
–5.查詢emp表，顯示佣金不為空或者部門號為20的僱員資訊，要求按照薪水降序排列
select * from emp
where comm is not null
or deptno = 20
order by sal desc ;
–6.查詢emp表，顯示年薪大於30000工作類別不是MANAGER，且部門號不是10或40的僱員資訊，要求按照僱員姓名進行排列
select * from emp
where (sal+nvl(comm , 0 ))*12 > 30000
and job != `MANAGER`
and deptno
not in ( 10 , 40 )
order by ename ;
–7.查詢EMP、DEPT表，輸出的列包含員工姓名、工資、部門編號、部門名稱、部門地址.
select e.ename , e.sal , e.deptno , d.dname , d.loc
from emp e , dept d
where e.deptno = d.deptno ;
–8.使用自連線查詢EMP表，輸出的列包含員工姓名、主管姓名.
select e.ename employeename,m.ename managername
from emp e,emp m
where e.mgr = m.empno;
–9.在上一題的基礎上，思考下為什麼輸出結果沒有KING的資訊？ 如果要輸出KING的資訊，如何修改？
select e.ename employeename,m.ename managername
from emp e,emp m
where e.mgr = m.empno(+);
–10.使用左連線查詢員工部門，包括沒有員工的部門資訊，輸出列：部門編號、部門名稱、位置。 –（左表為dept表，emp為右表）
select e.deptno , d.dname , d.loc
from dept d left join emp e
on e.deptno = d.deptno ;
–11.查詢EMP表，輸出每個部門的平均工資，並按部門編號降序排列.
select deptno , avg(sal)
from emp
group by deptno
order by deptno desc ;
–12.查詢EMP表，輸出每個職位的平均工資，按平均工資升序排列.
select job , avg(sal)
from emp
group by job
order by avg(sal) ;
–13.查詢EMP表，輸出每個部門的各個職位的平均工資，並按部門編號升序、平均工資降序排序。
select deptno , job , avg(sal)
from emp
group by deptno , job
order by deptno ,avg(sal) desc ;
–14.使用子查詢，找出哪個部門下沒有員工
select deptno
from dept
where deptno != all
(select deptno
from emp );
–15.使用子查詢，找出那些工資低於所有部門的平均工資的員工
select ename , sal
from emp
where sal < all
(select avg(sal) de_sal
from emp
group by deptno) ;
–16.使用子查詢，找出那些工資低於任意部門的平均工資的員工，比較一下與上一題輸出的結果是否相同？
select ename , sal
from emp
where sal < any
(select avg(sal) de_sal
from emp
group by deptno) ;
–17.在EMP表中，增加一名員工，員工資訊參照現有員工構造.
insert into emp
values (1111 , `aa` , upper(`salesman`) , 7698 , sysdate , 8000 , 1000 , 40);
–18.員工SMITH部門調動到SALES部門，請編寫SQL語句更新員工資訊.
update emp
set deptno = `30`
where ename = `SMITH`;
–19.員工JAMES已經離職，請編寫SQL語句更新資料庫.
delete from emp
where ename = `JAMES`;
–20.使用者執行delete from emp;語句刪除了EMP表的記錄，但沒有提交，請問有辦法恢復EMP原來的資料嗎？
rollback
–21.得到平均工資大於2000的工作職種
select job , avg(sal)
from emp
group by job
having avg(sal)>2000;
–22.分部門得到工資大於2000的所有員工的平均工資，並且平均工資還要大於2500
方法一：
create or replace view emp_sal
as select ename , sal ,deptno
from emp
where sal>2000;
select deptno,avg(sal) dept_sal
from emp_sal
group by deptno
having avg(sal)>2500;
方法二：
select deptno , avg(sal) deptno
from emp
where sal >2000
group by deptno
having avg(sal)>2500;
–23.得到每個月工資總數最少的那個部門的部門編號，部門名稱，部門位置
select *
from
(
select d.deptno , d.dname , d.loc ,sum(sal)
from dept d , emp e
where e.deptno = d.deptno
group by d.deptno , d.dname , d.loc
order by sum(sal)
)
where rownum = 1 ;
–24.分部門得到平均工資等級為2級（等級表）的部門編號
select * from salgrade;
select deptno , avg(sal) dsal
from emp
group by deptno
having avg(sal) between 1201 and 1400;
–25.查詢出部門10和部門20中，工資最高第3名到工資第5名的員工的員工名字，部門名字，部門位置
select *
from
(select ename , dname , loc ,sal , rownum rn
from(
select e.ename , d.dname , d.loc ,e.sal
from emp e , dept d
where e.deptno!=(30) and e.deptno = d.deptno
order by sal desc))
where rn between 3 and 5
–26.查詢出收入（工資加上獎金），下級比自己上級還高的員工編號，員工名字，員工收入*/
select empno , ename , shouru
from
(
select a.ename , a.empno , a.sal+nvl(a.comm,0) shouru , b.sal+nvl(b.comm,0) shouru_2
from emp a , emp b
where b.empno = a.mgr
)
where shouru >shouru_2
–27.查詢出職位和`MARTIN` 或者`SMITH`一樣的員工的平均工資 */
select avg(sal)
from emp
where job = (select job from emp where ename = upper(`smith`))
or
job = (select job from emp where ename = upper(`martin`));
–28.查詢出不屬於任何部門的員工
select ename
from emp
where deptno is null;
–29.按部門統計員工數，查出員工數最多的部門的第二名到第五名（列出部門名字，部門位置）
select dname ,loc
from (select *
from (select rownum rn , deptno
from(select deptno , count(*)
from emp
group by deptno
order by count(*) desc))
where rn between 2 and 5) a ,dept b
where a.deptno = b.deptno
–30.查詢出king所在部門的部門號部門名稱部門人數
select denum , a.deptno ,dname
from
(select count(deptno) denum ,deptno
from emp
where deptno =
(select deptno
from emp
where ename = `KING`)
group by deptno) a ,dept b
where a.deptno = b.deptno ;
–31.查詢出king所在部門的工作年限最大的員工名字
select ename
from(
select *
from emp
where deptno = (
select deptno
from emp
where ename = `KING`)
order by hiredate )
where rownum = 1
–32.查詢出工資成本最高的部門的部門號和部門名稱
select a.deptno ,b.dname
from
(select sum(sal),deptno
from emp
group by deptno
order by sum(sal) desc) a , dept b
where a.deptno = b.deptno
and rownum = 1 ;
–33.顯示所有員工的姓名、工作和薪金,按工作的降序排序,若工作相同則按薪金排序.
select ename , job , sal
from emp
order by job desc , sal desc;
–34.顯示所有員工的姓名、加入公司的年份和月份,按受僱日期所在月排序,若月份相同則將最早年份的員工排在最前面.
select ename , to_char( hiredate , `YYYY MM` )
from emp
order by to_char( hiredate , `MM` ) ,to_char (hiredate , `dd`);
–35.顯示在一個月為30天的情況所有員工的日薪金,忽略餘數
select ename , trunc(sal/30)
from emp ;
–36.找出在(任何年份的)2月受聘的所有員工。
select ename
from emp
where to_char ( hiredate , `mm` ) = 2 ;
–37.對於每個員工,顯示其加入公司的天數.
select ename , ceil(to_number (to_char(sysdate – hiredate))) hireday
from emp ;
–38.顯示姓名欄位的任何位置包含”A”的所有員工的姓名.
select ename
from emp
where ename like `%A%`;
–39.以年月日的方式顯示所有員工的服務年限.
–年
select ename , round(to_number (to_char(sysdate – hiredate))/365 ) year
from emp ;
–月
select ename , round(to_number (to_char(sysdate – hiredate))/30 ) month
from emp ;
–日
select ename , ceil(to_number (to_char(sysdate – hiredate)) ) month
from emp ;
–40.顯示員工的姓名和受僱日期,根據其服務年限,將最老的員工排在最前面.
select ename , hiredate
from emp
order by hiredate
–41.
假設order_status2 表結構如下：
Name Type Nullable Default Comments
——————————- ————————- ———— ———— ——–
ID INTEGER
STATUS CHAR(10) Y
LAST_MODIFIED DATE Y SYSDATE
MODIFIED_BY INTEGER Y
INITIALLY_CREATED DATE sysdate
TEST VARCHAR2(15) Y
TESTB VARCHAR2(10) Y `testb`
–按表結構資訊在資料中建立order_status2表
create table order_status3
(
ID INTEGER not null,
STATUS CHAR(10) ,
LAST_MODIFIED DATE default SYSDATE ,
MODIFIED_BY INTEGER ,
INITIALLY_CREATED DATE default sysdate not null ,
TEST VARCHAR2(15),
TESTB VARCHAR2(10) default `testb`);
–修改欄位test, 使其不允許為空且給它賦予一個預設的值testing;
alter table order_status2 modify(test default `testing` not null);
–給order_status2表新增註釋, 併為其每一個欄位新增相應的註釋.
comment on table order_status2 is `1`;
comment on column order_status2.id is `1`;
comment on column order_status2.status is `1`;
comment on column order_status2.last_modified is `1`;
comment on column order_status2.initially_created is `1`;
comment on column order_status2.test is `1`;
comment on column order_status2.testb is `1`;
comment on column order_status2.MODIFIED_BY is `1`;
–42.在41題建立的order_status2表的基礎上完成以下練習：
–給order_status2表的status列新增一個check約束, 使其只允許輸入Male和Female;
alter table order_status
add constraint order_status_check
check (status in (`male` , `female`));
–為這個表的id列新增一個外來鍵約束, 外來鍵約束的表為employees, 對應的列為employee_id;
alter table order_status
add constraint order_status_f_id
foreign key (id) references employees(employee_id);
–43.對emp表中sal、comm進行加計算，並使用別名命令為員工的月總收入，同時展示出員工部門編號、員工姓名資訊。
select ename , empno , sal+nvl(comm , 0) “月總收入”
from emp;
–44.使用連線符查詢emp表中員工的姓名和工資，並以如下格式列出且欄位名展示為 TOTAL INCOME：
–SMITH total income is XXXXX
select ename||` total income is`||sal as “TOTAL INCOME”
from emp ;
–45.使用distinct排重查詢emp中的job型別
select distinct job from emp;
–46.從emp表中找出獎金高於 薪水60%的員工
select ename
from emp
where nvl(comm , 0 ) >nvl(comm , 0 )*0.6 ;
–47.找出部門10中所有經理（MANAGER）和部門20中所有辦事員（CLERK）的詳細資料。
select *
from emp
where deptno = 10 and job =`MANAGER` or deptno = 20 and job = `CLERK`;
–48.從emp和dept中聯合查詢，並將員工編號、姓名、職位、地址資訊列出。
select e.empno , ename ,job , loc
from emp e , dept d
where e.deptno = d.deptno;
–49.統計各部門的薪水總和。
select sum(sal) ,deptno
from emp
group by deptno;
–50.找出部門10中所有理（MANAGER），部門20中所有辦事員（CLERK）以及既不是經理又不是辦事員但其薪水大於或等2000的所有員工的詳細資料。
select *
from emp
where deptno = 10 and job =`MANAGER`
or deptno = 20 and job = `CLERK`
or job <> all(`MANAGER` , `CLERK`) and sal >= 2000;
–51.列出各種工作的最低工資。
select job , min(sal)
from emp
group by job;
–52.列出各個部門的MANAGER（經理）的最低薪水。
select min(sal)
from
(select *
from
(select sal, job , deptno
from emp)
where job = `MANAGER`)
group by deptno
–53.列出有獎金的員工的不同工作。
select ename , job , comm
from
(select *
from emp
where comm is not null);
–54.找出無獎金或獎金低於300的員工。
select * from emp where comm is null or nvl(comm , 0)<300;
–55.顯示所有員工的姓名,並使姓名首字母大寫。
select initcap(ename) from emp;
–56.顯示正好為5個字元的員工的姓名。
select ename from emp where length(ename) = 5;
–57.顯示不帶有“R”的員工姓名。
select ename from emp where ename not like `%R%`;
–58.列出薪水高於在部門30工作的所有員工的薪水的員工姓名和薪水。
select ename ,sal
from emp
where sal >all (select sal from emp where deptno = 30 ) ;
–59.列出在每個部門工作的員工數量、平均工資和平均服務期限。
select count(*) “員工人數” , avg(sal) “平均工資” ,avg (trunc(sysdate – hiredate)) “平均服務年限”
from emp
group by deptno ;
–60.列出從事同一種工作但屬於不同部門的員工的一種組合。
select a.ename , b.ename
from emp a , emp b
where a.job = b. job and a.deptno <> b.deptno ;
–61.列出薪水比“SMITH”多的所有員工。
select ename
from emp
where sal > (select sal from emp where ename = `SMITH`);
–62.列出至少有一個員工的所有部門。
select distinct deptno
from emp
where deptno in (select deptno from emp );
–63.對於每個員工，顯示其加入公司的天數、月數、年數。
select ename , trunc(sysdate – hiredate) “天數” ,
trunc(sysdate – hiredate)/30 “月數” , trunc(sysdate – hiredate)/365 “年”
from emp;
–64.對21中的天數、月數、年數取整顯示。
select ename , trunc(sysdate – hiredate) “天數” ,
round(trunc(sysdate – hiredate)/30) “月數” , round(trunc(sysdate – hiredate)/365) “年”
from emp;
–65.找出在每年5月受聘的所有員工。
select ename
from emp
where to_char(hiredate,`mm`) = 5 ;
–66.顯示在一個月為30天的情況下所有員工的日薪水，取整。
select ename , round(sal/30) daysal
from emp;
–67.顯示所有員工的姓名和加入公司的年份和月份，並將員工入職年月從低到高排序。
select ename , to_char(hiredate,`yyyy mm`)
from emp
order by hiredate ;
–68.請查SMITH領導的薪水
select sal
from emp
where empno = (select mgr
from emp
where ename = `SMITH`);
–69.請查SMITH領導的薪水和所在的部門地址
select sal ,loc
from emp e , dept d
where empno = (select mgr
from emp
where ename = `SMITH`)
and e.deptno = d.deptno;
–70.請查SMITH領導的薪水和所在的部門地址 以及領導的薪水等級
select a.* , grade
from
(
select sal , loc
from emp ,dept
where empno=(select mgr
from emp
where ename = `SMITH`) and dept.deptno=
(select deptno
from emp
where ename = `SMITH`)) a ,salgrade
where a.sal
between losal and hisal
;
–或
select a.* , grade ,loc
from
(select b.sal
from emp a,emp b
where a.mgr=b.empno and a.ename =`SMITH`) a ,
salgrade ,dept
where a.sal between losal and hisal
and deptno=(select b.deptno
from emp a,emp b
where a.mgr=b.empno and a.ename =`SMITH`);
–71.查出SMITH的薪水等級
select grade
from salgrade ,emp
where ename =`SMITH`
and sal between losal and hisal;
–72.請查出SIMIH的薪水等級和他所在部門所在地
select grade , loc
from dept d, salgrade ,emp e
where ename =`SMITH` and e.deptno = d.deptno
and sal between losal and hisal;
–73.按照職位分組，求出每個職位的最大薪水
select max(sal)
from emp
group by job;
–74.
–I)求出每個部門中的每個職位的最大薪水
select max(sal),deptno , job
from emp
group by deptno ,job ;
–II)在薪水大於1000，並且職位不是MANAGER的員工中，求職哪個職位的平均薪水大於2000
select job
from emp
where sal>1000 and job <> `MANAGER`
group by job
having avg(sal)>2000;
–75.列出SMITH的薪水和職位
select sal , job
from emp
where ename = `SMITH`;
–76.列出SMITH的部門地址和補貼和薪水等級（等級表salgrade)
select ename , loc , comm , grade
from emp e , dept d ,salgrade
where ename = `SMITH` and e.deptno = d.deptno
and e.sal between losal and hisal
–77.列出薪金比”SMITH”多的所有員工
select ename
from emp
where sal>(
select sal
from emp
where ename = `SMITH`);
–78.列出所有員工的姓名及其直接上級的姓名
方法一：
select a.ename “直接上級”, b.ename “員工姓名”
from emp a , emp b
where a.mgr = b.empno(+);
方法二：
select a.ename “直接上級”, b.ename “員工姓名”
from emp a left outer join emp b
on a.mgr = b.empno(+);
–79.列出部門不是10，職位不是C開頭的，薪資比公司平均薪資都高的員工名字
select ename
from emp
where deptno != 10 and job not like `C%`
and sal>(select avg(sal) from emp );
–80.哪個部門下面沒有員工
select deptno
from dept
where deptno <>all
(select deptno
from emp);
–81.誰的薪水比SMITH多，同時部門又是和SCOTT的部門相同
select ename
from emp
where sal > (select sal from emp where ename = `SMITH`)
and deptno = (select deptno from emp where ename =`SCOTT`);
–82.列出薪資比每個部門每個職位的平均薪資還要高的員工
select ename
from emp
where sal >all(select avg(sal) from emp
group by deptno, job );
–83.列出至少有一個員工的所有部門
select distinct deptno
from emp;
–84.新員工入職，請新增一個使用者
insert into emp(empno , ename , hiredate)
values(1111 , `aaaa` , sysdate);
–85.SMITH的職位變更為SCOTT的職位
update emp
set job = (select job from emp where ename = `SCOTT`)
where ename = `SMITH`;
–86.SMITH離職，請刪除該使用者
delete from emp
where ename = `SMITH`;