--显示薪金不在5000-1200这个范围之间的所有员工的姓氏和薪金 select t.last_name,t.salary from employees t
where t.salary<5000 or t.salary>1200;
5.排序
--查询员工资料，按薪资升序排列 select t.*
from employees t order by t.salary;
--查询员工资料，在部门号从大到小的情况下按按薪资升序排列 select t.*
from employees t
order by t.department_id desc ,t.salary;
--按姓名的字母顺序显示部门20和部门50中的所有员工的姓氏和部门编号 select t.last_name , t.department_id from employees t
where t.department_id in(20,50) order by t.last_name;
--显示可以赚取佣金的所有员工的姓氏、薪金和佣金，按薪金和佣金的降序对数据进行排序 select t.last_name , t.salary,t.commission_pct from employees t
where t.commission_pct is not null
order by t.salary desc,t.commission_pct desc;
6.函数（已看到此处）
--1、显示当前日期 select sysdate from dual;
--2、显示当前日期，格式为****年**月**日，别名为hday select to_char(sysdate,'yyyy年月日
--3、编写一个查询，显示姓名以J、A或M开始的所有员工的姓氏（第一个字母大写，其余字母小写）和姓氏的长度，给每列一个合适的标签； select initcap(t.last_name) lname,length(t.last_name) len from employees t
where substr(upper(last_name),0,1) in('J','A','M');
--4、计算每位员工截止到当前时间入职的星期数，别名为weeks_worked。按聘用的星期数对结果进行排序。该星期数舍入到最接近的整数。同时显示员工的名字； select t.first_name,round((sysdate-t.hire_date)/7) as weeks_worked from employees t;
--5、计算每位员工截止到当前时间入职的月数，别名为months_worked。该星期数舍入到最接近的整数。同时显示员工的名字。
select t.first_name,round(months_between(sysdate,t.hire_date)) as months_worked from employees t;
--6、查询在1998年2月20日和1998年5月1日之间入职的员工的姓氏、职务标识和起始日期
select t.last_name,t.job_id,t.hire_date from employees t where t.hire_date between to_date('19980220','yyyyMMdd') and to_date('19980501','yyyyMMdd');
--7、创建一个查询。显示所有员工的姓氏和薪金。将薪金格式规定为15个字符长，左边填充$
select lpad(t.salary,15,'$') sal from employees t;
/*8、显示每位员工的姓氏、聘用日期和薪金复核日期，薪金复核日期是服务六个月之后的第一个星期一。将该列标记为review。 这一日期的显示格式类似于：“Monday,the Thirty-First of July,2000”*/
select t.last_name,t.hire_date,/*next_day(add_months(t.hire_date,6),2) from employees t*/ to_char(next_day(add_months(t.hire_date,6),2),'fmday,\of rom employees t;
/*第八题to_char的第三个参数用于设置本次查询使用的国家和地区，ddspth中spth为后缀，表示Spelled, ordinal number*/
--9、显示员工的姓氏、聘用日期和该员工在星期几开始工作的。 select t.last_name,t.hire_date,to_char(t.hire_date,'DAY') as 开始 rom employees t;
--10、计算员工的月收入（工资+佣金）
select t.salary+t.salary*nvl(t.commission_pct,0) from employees t;
--11、创建一个查询，使其显示员工的姓氏，并用星号指明他们的年薪。每个星号代表一千美元。按薪资降序排列数据。
select t.last_name||lpad(' ',trunc(t.salary/1000)+1,'*'),t.salary from employees t order by t.salary desc;
--12、创建一个查询。使其显示员工的姓氏和佣金额。如果某位员工不赚取佣金则显示“No Commission”，将该列标记为COMM
select t.last_name,nvl(to_char(t.salary*t.commission_pct,'999,999.99'),'No Commission') COMM from employees t
/*13、使用decode函数编写一个查询，使其按照以下数据根据JOB_ID列的值显示所有员工
的级别，同时显示员工的姓氏
job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E 都不是 0 */
select t.last_name,t.job_id job, decode(t.job_id,'AD_PRES','A' ,'ST_MAN','B' ,'IT_PROG','C' ,'SA_REP','D' ,'ST_CLERK','E' ,'0'
) as grade from employees t;
--14、使用case语法重写一遍上题 select t.last_name,t.job_id job, case t.job_id
when 'AD_PRES'then 'A' when 'ST_MAN' then 'B' when 'IT_PROG' then 'C' when 'SA_REP' then 'D' when 'ST_CLERK' then 'E' else '0'
end
as grade from employees t;
************************************补充************************************ --1、显示当前日期，本月最后一天的日期，以及本月还剩多少天 SELECT SYSDATE,
LAST_DAY(SYSDATE)
LAST_DAY(SYSDATE) - SYSDATE FROM DUAL;
--2、显示今年的第一天
select trunc(sysdate,'year') from dual; --3、显示本月的第一天
select trunc(sysdate,'month') from dual; --4、最近一个星期四是哪天(不含今日) SELECT NEXT_DAY(sysdate,5) FROM DUAL;
7.分组查询
--求所有员工的平均工资、最高工资、最低工资和工资总和，给予适当的别名 select avg(t.salary),max(t.salary),min(t.salary),sum(t.salary) from employees t;
--求每种工作的平均工资 select avg(t.salary) from employees t group by t.job_id;
--求每个部门中同一种工作的平均工资,同时显示部门号,按部门号升序显示 select t.department_id,avg(t.salary) from employees t
group by t.department_id,t.job_id order by t.department_id;
--查询出各部门的部门编号以及各部门的总工资和平均工资,按部门编号升序排列。 select t.department_id,sum(t.salary),avg(t.salary) from employees t
group by t.department_id order by t.department_id;
--显示每种工作的人数 select t.job_id,count(*) from employees t group by t.job_id;
--显示员工最高工资超过10000的部门的id及其员工最高工资 select t.department_id,max(t.salary) m from employees t
group by t.department_id having max(t.salary)>10000;
--显示平均工资最高的部门id及其平均工资(要用到还未学习的知识) select * from(
select t.department_id,avg(t.salary) from employees t
group by t.department_id order by avg(t.salary) desc )
where rownum<2 ;
8.多表查询
/*
多表查询，基本知识（预习自检）： 什么是多表查询? 什么是笛卡尔积？ 怎样避免笛卡尔积？
要将n个表联结在一起，至少需要多少个联结条件？ 什么是等值联结？
如何在联结条件外附加限制条件？ 如何限定模糊的列名？ 如何进行非等值联结？ 什么是外联结？ 什么是自联结？
如何用sql1999标准表达：笛卡尔积、自然联结、等值联结、内联结、左外联结、右外联结和全外联结？ **/ /*
联结的分类：
Joins that are compliant with the SQL:1999 standard include the following: Natural joins:
NATURAL JOIN clause USING clause ON clause Outer joins:
LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN Cross joins
sql：1999联结语法
SELECT table1.column, table2.column FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] | [JOIN table2
ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)]| [CROSS JOIN table2];
知识总结：