Oracle資料庫，簡單SQL練習與答案

1.數據

--創建職員表
create table tbEmp
(
eID number primary key, --職員編號
eName varchar2(20) not null, --職員姓名
eSex varchar2(2) not null --職員性別
check(esex in ('男','女')),
eAge number not null check(eage>=18), --職員年齡
eAddr varchar2(50) not null, --職員地址
eTel varchar2(30) not null, --職員電話
eEmail varchar2(30) null, --職員郵箱
eJoinTime date not null --入職時間
);
insert into tbemp
(eID,ename,esex,eage,eaddr,etel,eemail,ejointime)
values
(1,'趙龍','男',25,'湖南省長沙市伍家嶺江南苑9棟203號','0731-4230123','[email protected]','5-10月-2005');
insert into tbemp
(eID,ename,esex,eage,eaddr,etel,eemail,ejointime)
values
(2,'李雲','女',23,'湖南省長沙市東風路東風新村21棟502號','0731-4145268','[email protected]','3-7月-2003');
insert into tbemp
(eID,ename,esex,eage,eaddr,etel,eemail,ejointime)
values
(3,'孫一成','男',24,'湖南省株洲市601廠宿舍15棟308號','0732-8342567','[email protected]','2-11月-2002');
insert into tbemp
(eID,ename,esex,eage,eaddr,etel,eemail,ejointime)
values
(4,'林笑','男',27,'湖南省郴洲市人民醫院20棟301號','0735-2245214','[email protected]','5-1月-2006');
insert into tbemp
(eID,ename,esex,eage,eaddr,etel,eemail,ejointime)
values
(5,'衛晴','女',23,'湖南省長沙市望月湖12棟403號','0731-8325124','[email protected]','5-3月-2007');

commit;

2.習題

--1. 查詢所有職員的所有信息
select * from tbemp;

--2. 查詢所有職員的姓名,電話,地址
select ename,etel,eaddr from tbemp;

--3. 查詢所有女職員的詳細信息
select * from tbemp where esex = '女'

--4. 查詢年齡在24到26歲之間的職員的姓名,性別
select ename,esex from tbemp where eage between 24 and 26

--5. 查詢家住長沙的女職員的姓名,電話,地址
select ename,etel,eaddr from tbemp where esex = '女' and eaddr like '%長沙市%'

--6. 查詢李雲,孫一成,林笑的電話,地址
select ename,etel,eaddr from tbemp where ename in ('李雲','孫一成','林笑');

--7. 查詢郴洲和株洲的職員的姓名,性別,年齡
select ename,esex,eage from tbemp where eaddr like '%郴洲%' or eaddr like '%株洲%';

--8. 查詢家住長沙,年齡在25到28歲之間的男職員的姓名
select ename from tbemp where eaddr like '%長沙%' and (eage between 25 and 28) and esex = '男';

--9. 查詢郵件地址為空的職員
select * from tbemp where eemail is null;

--10.查詢入職時間超過兩年的員工 ----這裡涉及日期的運算，當前時間-入職時間---------------------------
select * from tbemp where months_between(sysdate,ejointime)/12 > 2

--11.查出1月份入職的員工
select * from tbemp where Extract(month from ejointime) = 1;

--12.將所有email為163.net的郵箱改為126.com ----修改查詢出來的結果,字元串替換函數-------------------
select * from tbemp where eemail like '%163.net%'

--13.找出年齡最小的兩位長沙員工------------分頁查詢--------------------
--1.分頁寫法 三層嵌套，上兩層是分頁處理，最裡面一層是解決rownum分頁和order by在一起導致排序錯誤
select a1.* from
    (select a2.*,rownum as rn from
        (select * from tbemp order by eage) a2
    where rownum <= 2) a1
where rn >= 1;

--14.查找員工信息，要求結果集表達方式為
--欄位名：個人信息
--內容描述：'員工１，趙龍，今年２５歲，家住湖南省長沙市伍家嶺江南苑9棟203號'
select '員工'||eid||'，'||ename||'，今年'||eage||'歲，家住'||eaddr as 員工介紹 from tbemp;

--15.求所有員工的年齡總和
select sum(eage) as 年齡總和 from tbemp;

--16.分別求男，女員工年齡總和
select esex as 性別,sum(eage) as 年齡總和 from tbemp group by esex;

3.總結

1.基本SQL格式

2.日期的運算

  a.日期運算：
      後面時間-前面時間
      日期相減預設結果是相差天數

      months_between(後面時間-前面時間)
      months_between預設返回結果是相差月數

  b.取日期的值
      Extract函數
      Extract(year from sysdate)

3.分頁查詢

　　rownum：oracle特有關鍵字，用於分頁

　　a.嵌套實現分頁查詢

　　　　分頁查詢採用兩條查詢嵌套，內部查詢用來指定上界，且結果集返回rownum,外部查詢指定下界

　　b.排序和分頁聯合使用的問題

　　　　rownum分頁與order by在一起使用分頁排序，會導致排序錯誤，解決方法：多嵌套一層select

             select a1.* from
　　　　　　(select a2.*,rownum as rn from
　　　　　　　　(select * from tbemp order by eage) a2
　　　　　　where rownum <= 2) a1　　
　　     where rn >= 1;