Oracle第二章習(xí)題及答案.doc

一、基于自己創(chuàng)建表的操作1：創(chuàng)建一張學(xué)生表student，擁有stuid,stuname,sex,三個字段，其中stuid為主鍵。create table student( stuid int primary key, stuname VARCHAR(20), sex VARCHAR(20)2：為該表增加一個新列score。alter table student add(score varchar(10);3：修改該表score列名為stuscore。alter table student rename column score to stuscoree;4：為student表插入5條記錄。insert into student values(1,張三豐,男,80);insert into student values(2,阿悄,女,70);insert into student values(3,陳龍,男,90);insert into student values(4,章子怡,女,50);insert into student values(5,張衛(wèi)健,男,60);5：查詢student表中的全部數(shù)據(jù)，其中列名為中文。select STUID as 學(xué)號,STUNAME as 姓名 ,SEX as 性別,STUSCOREE as 分?jǐn)?shù) from student;6：查詢學(xué)生姓名和分?jǐn)?shù)，并是查詢結(jié)果按照學(xué)生成績降序排列。select STUNAME,STUSCOREE from student order by STUSCOREE desc;7：修改所有性別為“男”的學(xué)生信息為性別為“male”。update student set SEX=male where SEX=男;8：刪除所有記錄。delete from student;9：刪除student表。drop table student;二、基于emp表的操作1：創(chuàng)建一張新表emp1，和emp表結(jié)構(gòu)和記錄完全一樣。 create table emp1 as select*from Scott.Emp;基于emp1表的操作：1：選擇部門30中的雇員。select*from emp1 where DEPTNO=30 and JOB=CLERK;2：列出所有辦事員（CLERK）的姓名、編號和部門。select ENAME,EMPNO,DEPTNO from emp1 where JOB=CLERK;3：找出傭金高（comm）于薪金的雇員。 select*from emp1 where COMM SAL and JOB=CLERK;4：找出傭金高于薪金60%的雇員。select*from emp1 where COMM*0.6 SAL and JOB=CLERK;5：找出部門10中所有經(jīng)理和部門20中所有辦事員的詳細(xì)資料。select*from emp1 where DEPTNO=10 and JOB=MANAGER union select*from emp1 where JOB=SALESMAN;6：找出部門10中所有經(jīng)理和部門20中所有辦事員以及既不是經(jīng)理又不是辦事員但其薪金大于或等于2000的所有雇員的詳細(xì)資料。select*from emp1 where DEPTNO=10 and JOB=MANAGER union select*from emp1 where JOB=SALESMAN union select*from emp1 where JOB in(CLERK) and SAL=2000;7：找出收取傭金的雇員的不同工作。 select distinct JOB from emp1 where job=CLERK;8：找出不收取傭金或收取傭金低于100的雇員。select *from emp1 where COMM is null or nvl(COMM,0)12;3：顯示只有首字母大寫的所有雇員的姓名 select UPPER( SUBSTR(ename,1,1) |LOWER(SUBSTR(ename,2,LENGTH(ename)-1) FROM emp where JOB=CLERK;4：顯示正好為15個字符的雇員姓名select deptno,ename from emp where length(ename)=15 and JOB=CLERK;5：顯示不帶有R的雇員姓名 select ename from emp where ename not like %R%and JOB=CLERK;6：顯示所有雇員的姓名的前3個字符select substr(ename,1,3) from emp where JOB=CLERK;7：顯示所有雇員的姓名，用a替換所有的Aselect replace(ename,A,a) from emp;8：顯示所有雇員的姓名以及滿10年服務(wù)年限的日期select ename,hiredate,add_months(hiredate,10*12) from emp;9：顯示雇員的詳細(xì)資料，按姓名排序select * from emp order by ename;10：顯示雇員姓名，根據(jù)其服務(wù)年限，將最老的雇員排在最前面select empno,ename,job,mgr,hiredate,sal ,comm,deptno ,(sysdate-hiredate)/365 from emp order by (sysdate-hiredate)/365 desc;11：顯示所有雇員的姓名、工作和薪金，按工作內(nèi)的工作的降序順序排序，而工作按薪金排序select ename,job,sal from emp order by job desc,sal asc;12：顯示所有雇員的姓名和加入公司的年份和月份，按雇員受雇日所在月排序，并將最早年份的項(xiàng)目排在最前面select ename,to_char(hiredate,YYYY) year1,to_char(hiredate,MON) month1 from emp order by year1 asc,month1;13：顯示在一個月為30天的情況下所有雇員的日薪金，忽略小數(shù)select round(sal/30,0) as daypay from emp;14：找出在（任何年份的）2月份受雇的所有雇員select * from emp where hiredate like %-2月%;15：對于每個雇員，顯示其加入公司的天數(shù) select round(sysdate-hiredate) from emp;16：顯示姓名字段的任何位置，包含A的所有雇員的姓名select ename from emp where ename like %A%;17：以年、月和日顯示所有雇員的服務(wù)年限select ename,to_number(to_char(sysdate,YYYY)-to_number(to_char(hiredate,YYYY)-1,to_number(to_char(sysdate,mm)-to_number(to_char(hiredate,mm) +10,to_number(to_char(sysdate,dd)-to_number(to_char(hiredate,dd)+30 from emp;18: 列出至少有一個雇員的所有部門 select deptno from emp group by deptno having count(deptno)0;19：列出薪金比“SMITH”多的所有雇員SELECT ename,sal, deptno FROM emp WHERE sal(SELECT sal FROM emp WHERE ename=SMITH);20：列出所有雇員的姓名及其部門名select ENAME,DEPTNO from emp where JOB=CLERK; 21：列出所有入職日期早于其直接上級的所有雇員SELECT e.ename as 職工, m.ename as 經(jīng)理 FROM emp e left join emp m on (e.mgr=m.empno) where e.hiredate1500;23：列出薪金高于公司平均水平的所有雇員select ename, deptno from emp where sal(select avg(sal) from emp);24：列出與“SCOTT”從事相同工作的所有雇員 select deptno,ename from emp where job=(select job from emp where ename=SCOTT);25:列出薪金高于在部門30工作的所有雇員的薪金SELECT ename,sal from emp where sal all(select sal from emp where deptno=30);26：列出薪金高于在部門30工作的最高的薪金SELECT ename,sal from emp where salall(select max(sal) from emp where deptno=30);27：列出每個部門雇員的數(shù)量select deptno,count(*) from emp group by deptno;28:列出所有雇員的名稱，部門名稱和薪金select ENAME,DEPTNO,SAL from emp where JOB=CLERK;29：列出從事同一種工作但不屬于同一部門的這些員工select job,deptno from emp group by job,deptno;30：列出個類別工作的最低工資select job,min(sal) from emp group by job;31：列出各個部門的經(jīng)理的最低薪金 select DEPTNO,min(sal) from emp where job=MANAGER group by DEPTNO;32：列出按計算的字段排序的所有雇員的年薪select ename,(sal+nvl(comm,0)*12 as yearpay from emp order by yearpay desc;33:列出所有CLERK的姓名及其部門名稱select ENAME,DEPTNO from ep where JOB=CLERK;34：列出薪金水平處于前四位的雇員select sal,ename from emp order by sal desc;35:求出所有員工入職了多少年零多少月零多少天select empno,ename,to_char(floor(to_number(sysdate-hiredate)/365)| years |to_char(ceil(months_between(sysdate,hiredate)-(floor(to_number(sysdate-hiredate)/365)*12)| months from emp;作業(yè)要求：將SQL語句復(fù)制粘貼在相對應(yīng)的題目下面。1：找出各月最后一天受雇的所有雇員select * from emp where hiredate=last_day(hiredate);2：找出早于12年之前受雇的雇員select * from emp where to_number(to_char(sysdate,yyyy)-to_number(to_char(hiredate,yyyy)12;3：顯示只有首字母大寫的所有雇員的姓名SELECT initcap(ename) FROM emp;SELECT ename from emp where ename=initcap(ename);4：顯示正好為15個字符的雇員姓名 select ENAME from emp where length(ENAME)=15;5：顯示不帶有R的雇員姓名select ENAME from emp where ENAME not like %R%;6：顯示所有雇員的姓名的前3個字符select substr(ENAME,0,3) from emp;7：顯示所有雇員的姓名，用a替換所有的Aselect replace(ENAME,A,a) from emp;8：顯示所有雇員的姓名以及滿10年服務(wù)年限的日期select ename, add_months(hiredate,120) from emp;9：顯示雇員的詳細(xì)資料，按姓名排序select * from emp order by ename;10：顯示雇員姓名，根據(jù)其服務(wù)年限，將最老的雇員排在最前面 select ename from emp order by hiredate;11：顯示所有雇員的姓名、工作和薪金，按工作內(nèi)的工作的降序順序排序，而工作按薪金排序select ename,job,sal from emp order by job desc, sal desc;12：顯示所有雇員的姓名和加入公司的年份和月份，按雇員受雇日所在月排序，并將最早年份的項(xiàng)目排在最前面select ename, to_char(hiredate, yyyy-mm) as 加入年月 from emp order by to_char(hiredate,mm) asc, to_char(hiredate,yyyy) asc;13：顯示在一個月為30天的情況下所有雇員的日薪金，忽略小數(shù)select round(sal/30,0) from emp;14：找出在（任何年份的）2月份受雇的所有雇員select * from emp where to_char(hiredate, mm)=02;15：對于每個雇員，顯示其加入公司的天數(shù)select ename,round(sysdate-hiredate,0) 加入公司天數(shù) from emp;16：顯示姓名字段的任何位置，包含A的所有雇員的姓名select ename from emp where ename like %A%;17：以年、月和日顯示所有雇員的服務(wù)年限select ename,hiredate,trunc(months_between(sysdate,hiredate)/12) 年,trunc(mod(months_between(sysdate,hiredate),12) 月, trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate) 日 from emp;18: 列出至少有一個雇員的所有部門select deptno,count(0) from emp group by deptno having count(0)=119：列出薪金比“SMITH”多的所有雇員 select ename from emp where sal (select sal from emp where ename = SMITH);select e2.* from emp e1, emp e2 where e1.ename=SMITHand e2.sale1.sal;20：列出所有雇員的姓名及其部門名select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;21：列出所有入職日期早于其直接上級的所有雇員select * from emp e1 where hiredate1500;23：列出薪金高于公司平均水平的所有雇員select * from emp where sal(select avg(sal) from emp);24：列出與“SCOTT”從事相同工作的所有雇員select * from emp where job=(select job from emp where ename=SCOTT);25:列出薪金高于在部門30工作的所有雇員的薪金select ename, sal from emp where sal(select max(sal) from emp where deptno=30);select ename, sal from emp where salall(select sal from emp where deptno=30);26：列出薪金高于在部門30工作的最高的薪金select ename, sal from emp where sal(select max(sal) from emp where deptno=30);27：列出每個部門雇員的數(shù)量select d.*, (select count(deptno) from emp e where e.deptno=d.deptno) 雇員人數(shù) from dept d;28:列出所有雇員的名稱，部門名稱和薪金select ename,(select dname from dept d where d.deptno=e.deptno) 部門名稱,sal from emp e;select e.ename,d.dname,sal from emp e,dept d where e.deptno=d.deptno;29：列出從事同一種工作但不屬于同一部