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);