SQL/Oracle——第五章 SQL语言基础及调优前三节(作业1）

[实验目的]
1.掌握SELECT语句的基本语法。
2.掌握常用函数的使用。
3.了解格式化查询结果的常用命令。(实验内容]
根据Orecle数据库so方案下的emp表和dept表,完成下列操作:

(1)查询部门编号是20的员工信息。

select * from emp where deptno in 20;

(2)查询工作为CLERK的员工的员工号、员工名和部门号。

select empno,ename,deptno from emp where job in 'CLERK';

(3)查询奖金COMM高于工资SAL的员工信息。

select * from emp where comm > sal;

(4)查询奖金高于工资20%的员工信息。

select * from emp where comm > sal*0.2;

(5)查询部门编号是10并且工作为MANAGER的员工和部门编号是20并且工作为CLERK的员工的信息。

select * from emp where deptno in 10 and job in 'MANAGER' or deptno in 20 and job in 'CLERK';

(6)查询工作不是MANAGER和CLERK,并且工资大于或等于2000的员工信息。

select * from emp where job not in ('MANAGER','CLERK') and sal >=2000;

(7)查询有奖金的员工信息。

select * from emp where comm > 0;

(8)查询所有员工的人数和他们的平均工资。

select avg(sal),count(*) from emp;

(9)查询没有奖金或奖金低于100的员工信息。

select * from emp where comm <=100;

(10)查询最近两年入职的员工信息。

select * from emp where (sysdate-hiredate)/365<=2;

(11)查询工龄大于或等于10年的员工信息。

select * from emp where (sysdate-hiredate)/365>=10;

(12)查询员工信息，要求以首字母大写的方式显示所有员工的姓名。

select initcap(lower(ename)) from emp;

(13)查询员工名正好为6个字母的员工信息。

select * from emp where length(ename)=6;

(14)查询员工名字中不包含字母S的员工。

select ename from emp where ename not in '%S%' ;

(15)查询员工姓名的第二个字母为M的员工信息。

select * from emp where ename like '_M%';

(16)查询所有员工姓名的前三个字符。

select substr(ename,1,3) from emp;

(17)查询所有员工的姓名，如果包含字母s,则用S替换。

select replace(ename,'B','S') from  emp;

(18)查询员工的姓名和人职日期，并按人职日期从先到后进行排序。

select ename,hiredate from emp order by hiredate;

(19)显示所有员工的姓名、工作、工资，按工作降序排序，著工作相同则按工资升序排序。

select ename,job,sal from emp order by job,sal desc;

(20)显示所有员工的姓名、人职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序。

select ename,extract(year from hiredate) as 年份,extract(month from hiredate) as 月份 from emp order by 月份,年份;

(21)查询每个部门中的员工数量、平均工资和平均工作年限。

select deptno,count(*),avg(sal),avg((sysdate-hiredate)/365) from emp group by deptno;

(22)查询各个部门的人数及平均工资。

select count(*),avg(sal) from emp group by deptno;

(23)查询各种工作的最低工资，并输出最低工资低于3000的工作名称。

select job from emp group by job having min(sal)<3000;

(24)查询各个部门中不同工种的最高工资。

select deptno,job,max(sal) from emp group by deptno,job;

(25)统计各个工种的员工人数与平均工资

select deptno,job,max(sal) from emp group by deptno,job;

1）服务都打开
2）密码rjxy123456
3)alter user scott identified by tiger account unlock;//解锁用户并修改密码
4）connect scott/tiger;//链接用户

练习：
1.查询scott方案下的emp表中每个雇员的姓名、工作和工资增加300元后的新工资。（要求：不能先用update更新）

	select ename,job,sal+(300) from emp;（带有表达式的select语句）
select ename||'的工作是'||job as 雇员,sal+300 as 新工资 from scott.emp;

2.查询scott方案下的emp表中job和deptno字段，要求去除重复的行。

	select distinct job,deptno from emp;（显示不重复记录）
select distinct job,deptno from emp;

3.查询scott方案下的emp表中不在10和20部门工作的雇员信息。

	select empno,ename,sal,deptno from emp where deptno not between 10 and 20;
select * from emp where not (deptno=10 or deptno=20);
或where deptno not in(10,20);

4.查询scott方案下的emp表中姓名以A开头的雇员信息。

select empno,ename,job  from emp where ename like 'A%';
老师：select * from emp where  ename like 'A%';

5.查询scott方案下的emp表中姓名的倒数第二个字母是E的雇员信息。

select empno,ename,job  from emp where ename like '%E_';
老师：select * from emp where ename like '%E_';

6.查询scott方案下的emp表中雇佣日期是1987年的雇员的信息。

select * from  emp  where hiredate between '1-1月-1987' and '31-12月-1987';

7.查询scott方案下的emp表中工作分别为CLERK、ANALYST、MANAGER的雇员信息。

select * from emp where job in('CLERK','ANALYST','MANAGER');
或where job='CLERK' or job='ANALYST' or job='MANAGER;'
或select * from emp where job=any('CLERK','ANALYST','MANAGER');