1.
启动
SQL*Plus
用
scott/tiger
登录。查询
scott
用户下的所有表,在本实验中,我们将使用这些表。
表:EMP
列名
|
含义
|
EMPNO
|
雇员号
|
ENAME
|
雇员姓名
|
JOB
|
职务
|
MGR
|
经理代码
|
HIREDATE
|
受雇日期
|
SAL
|
薪金
|
COMM
|
佣金
|
DEPTNO
|
部门号
|
表:DEPT
列名
|
含义
|
DEPTNO
|
部门号
|
DNAME
|
部门名
|
LOC
|
部门所在地
|
表:SALGRADE
列名
|
含义
|
GRADE
|
薪金等级
|
LOSAL
|
最低工资
|
HISAL
|
最高工资
|
2
用SQL语句完成下列查询,填写相应的查询语句。
(
注意,在
SQL*Plus
下,每写完一个
SQL
的查询,都可以通过直接拷贝,将你所写的
SQL
语句复制到该实验报告中
)
(1)
选择部门
30
中的雇员。
Select * from emp where deptno =30;
(2)
列出所有办事员的姓名、编号和部门。
Select ename ,empno ,deptno from emp where job=’CLERK’;
(3)
找出佣金高于薪金的雇员。
Select * from emp where comm>sal;
(4)
找出佣金高于薪金
60%
的雇员。
Select * from emp where comm> sal * 0.6;
(5)
找出部门
10
中所有经理和部门
20
中所有办事员的详细资料。
Select * from emp where deptno=10 and job=’MANAGER’
Union
Select * from emp where deptno=20 and job=’CLERK’;
(6)
找出部门
10
中所有经理、部门
20
中所有办事员以及既不是经理又不是办事员但其薪金大于或等于
2000
的所有雇员的详细资料。
Select * from emp where deptno=10 and job=’MANAGER’
Union
Select * from emp where deptno=20 and job=’CLERK’
Union
Select * from emp where job<>’MANAGER’ and job<>’CLERK’ and sal>=2000;
or
Select * from emp where deptno=10 and job='MANAGER'
or deptno=20 and job='CLERK'
or sal>=2000 and job not in ('MANAGER','CLERK');
(7)
找出收取佣金的雇员的不同工作。
Select job from emp where comm Is not null ;
(8)
找出不收取佣金或收取的佣金低于
100
的雇员。
Select * from emp where comm=null or comm <100;
(9)
找出各月最后一天受雇的所有雇员。
select * from emp where
Substr(to_char(last_day(hiredate)),1,2)=substr(to_char(hiredate),1,2);
or
select * from emp where
Hiredate=last_day(hiredate);
(10)
找出早于
12
年之前受雇的雇员。
Select * from emp where
to_number(substr(to_char(sysdate),8,2))-to_number(substr(to_char(hiredate),8,2)) +100>12;
or
Select * from emp where hiredate<=add_months(sysdate,-144)
(11) 显示只有首字母大写的所有雇员的姓名。
select * from emp where ename = initcap(ename);
(12)
显示正好为
15
个字符的雇员姓名。
Select ename from emp where length(ename)=15;
(13)
显示不带有“
R
”的雇员姓名。
Select ename from emp where ename not like ‘%R%’;
(14)
显示所有雇员的姓名的前三个字符。
Select substr(ename ,1,3)from emp ;
(15)
显示所有雇员的姓名,用
a
替换所有“
A
”。
//select replace(ENAME,'
替换后字符串
','
被替换字符串
') from emp;
select replace(ENAME,'A','a') from emp;
or
select translate(name,'A','a') from emp;
(16)
显示所有雇员的姓名以及满
10
年服务年限的日期。
SELECT ename,add_months(hiredate,120) from emp;
(17)
显示雇员的详细资料,按姓名排序。
Select * from emp order by ename;
(18)
显示雇员姓名,根据其服务年限,将最老的雇员排在最前面。
Select ename from emp order by (sysdate-hiredate) desc;
or
Select ename from emp order by hiredate;
(19)
显示所有雇员的姓名、工作和薪金,按工作内的工作的降序顺序排序,同工作按薪金排序。
Select ename ,job,sal from emp order by job desc , sal;
(20)
显示所有雇员的姓名和加入公司的年份和月份,按雇员受雇日所在月排序,并将最早年份的项目排在最前面。
Select ename,to_char(hiredate,'YYYY') as YYYY,to_char(hiredate,'MM') as MM from emp order by MM,YYYY
(21)
显示在一个月为
30
天的情况下所有雇员的日薪金,忽略卢比余数。
select ename ,round(sal/30,0) as day_sal from emp;
(22)
对于每个雇员,显示其加入公司的天数。
Select ename , trunc( sysdate-hiredate ) from emp;
or
select ename ,floor(sysdate-hiredate) from emp;
(23)
显示姓名字段的任何位置包含“
A
”的所有雇员的姓名。
Select ename from emp where ename like '%A%';
(24)
以年、月和日显示所有雇员的服务年限。
select ename ,hiredate,floor(margin/12) as years,
floor(mod(margin,12)) as months,
floor((mod(margin,12)-floor(mod(margin,12)))*31) as days,
sysdate
from (select ename ,hiredate, months_between(sysdate,hiredate) as margin from emp);