CREATE TABLE EMP
(
EMPNO numeric(5,0) NOT NULL primary key,--雇员的编号
ENAME nvarchar(10) not null,--雇员的名字
JOB nvarchar(9)not null,--雇员的的职位
MGR numeric(5,0),--上级主管编号
HIREDATE datetime,--入职(受雇)日期
SAL numeric(7, 2),--薪金；
COMM numeric(7, 2),--佣金；
DEPTNO numeric(2,0)--部门编号
)

CREATE TABLE DEPT
(
DEPTNO numeric(2) primary key,--部门编号
DNAME nvarchar(14) not null,--部门名称
LOC部门所在地 nvarchar(13) --部门所在地
);

INSERT INTO EMP VALUES (7369, ‘SMITH‘, ‘CLERK‘, 7902,‘2000-12-17‘, 800, NULL, 20);
INSERT INTO EMP VALUES (7499, ‘allen‘, ‘SALESMAN‘, 7698,‘2001-2-20‘, 1600, 300, 30);
INSERT INTO EMP VALUES (7521, ‘WARD‘, ‘SALESMAN‘, 7698,‘2001-2-22‘, 1250, 500, 30);
INSERT INTO EMP VALUES (7566, ‘JONES‘, ‘MANAGER‘, 7839,‘2001-4-2‘, 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, ‘MARTIN‘, ‘SALESMAN‘, 7698,‘2001-9-28‘,1250, 1400, 30);
INSERT INTO EMP VALUES (7698, ‘BLAKE‘, ‘MANAGER‘, 7839,‘2001-5-1‘, 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, ‘CLARK‘, ‘MANAGER‘, 7839,‘2001-6-9‘, 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, ‘scott‘, ‘ANALYST‘, 7566,‘2002-12-9‘,3000, NULL, 20);
INSERT INTO EMP VALUES (7839, ‘king‘, ‘PRESIDENT‘, NULL,‘2001-11-17‘,5000, NULL, 10);
INSERT INTO EMP VALUES (7844, ‘TURNER‘, ‘SALESMAN‘, 7698,‘2001-9-8‘, 1500, 0, 30);
INSERT INTO EMP VALUES (7876, ‘ADAMS‘, ‘CLERK‘, 7788,‘2003-1-12‘,1100, NULL, 20);
INSERT INTO EMP VALUES (7900, ‘JAMES‘, ‘CLERK‘, 7698,‘2001-3-12‘,950, NULL, 30);
INSERT INTO EMP VALUES (7902, ‘FORD‘, ‘ANALYST‘, 7566,‘2001-3-12‘,3000, NULL, 20);
INSERT INTO EMP VALUES (7934, ‘MILLER‘, ‘CLERK‘, 7782,‘2002-01-23‘,1300, NULL, 10);
INSERT INTO DEPT VALUES (10, ‘ACCOUNTING‘, ‘NEW YORK‘);
INSERT INTO DEPT VALUES (20, ‘RESEARCH‘, ‘DALLAS‘);
INSERT INTO DEPT VALUES (30, ‘SALES‘, ‘CHICAGO‘);
INSERT INTO DEPT VALUES (40, ‘OPERATIONS‘, ‘BOSTON‘);

SELECT *FROM EMP
--1、查询所有的雇员名字
SELECT ENAME FROM EMP

--2、查询所有的职位
SELECT DISTINCT JOB FROM EMP --DISTINCT 隐藏重复的行

--3、查询没有佣金（COMM）的所有雇员信息
select * from EMP where COMM is null

--4、查询薪金(SAL)和佣金(COMM)总数大于2000的所有雇员信息
select * from EMP where (SAL+COMM)>2000  --cuo

SELECT * FROM EMP
WHERE SAL+ISNULL(COMM,0)>2000
--提示：isnull(列名，0） ：如果该列中有空值，就把空值当做0做计算

--5、选择部门编号=30中的雇员
select * from EMP  where EMPNO=30

--6、列出所有Job办事员（"CLERK"）的姓名、编号和部门名称
select EMP.ENAME,EMP.EMPNO,DEPT.DNAME from EMP left join Dept  on EMP.DEPTNO=DEPT.DEPTNO where EMP.Job=‘CLERK‘

--6、列出所有Job办事员（"CLERK"）的姓名、编号和部门名称
SELECT ENAME,EMPNO,DNAME FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB=‘CLERK‘
--7、找出佣金高于薪金的雇员
select * from EMP where ISNULL(COMM,0)>SAL
select * from EMP where COMM>SAL
--8、找出佣金高于薪金的60%的雇员
select * from EMP where ISNULL(COMM,0)/(ISNULL(COMM,0)-SAL)>0.6
SELECT ENAME FROM EMP WHERE COMM>0.6 * SAL
--9、找出部门10中所有经理和部门20中的所有办事员的详细资料
select * from EMP where (EMP.DEPTNO=10 and EMP.JOB=‘MANAGER‘) or (EMP.DEPTNO=20 and JOB=‘CLERK‘)

SELECT *FROM EMP WHERE JOB=‘MANAGER‘ AND DEPTNO=10 OR JOB=‘CLERK‘ AND DEPTNO =20

--10、既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
select * from EMP where job !=‘MANAGER‘ and job!=‘CLERK‘ and SAL>2000
--11、找出收取佣金的雇员的不同工作
--select a.JOB from EMP as a left join EMP as b on a.ENAME=b.ename where 
select DISTINCT  job from  emp  where COMM is not null

select * from  emp  where COMM is not null

--如何查询某一张表某一字段重复次数，以及重复的字段
select count(*) as count,job from EMP Group by JOB HAVING count(*)>1

--12、找出不收取佣金或收取的佣金低于100的雇员
select * from emp where COMM is null or comm<100
--13、找出早于8年之前受雇的雇员
select * from emp where HIREDATE < DateAdd(yyyy,-8,getdate())
SELECT * FROM EMP WHERE GETDATE()-HIREDATE>8----例子错误
--14、显示首字母大写的所有雇员的姓名
--区分大小写:collate:指定排序规则的
--修改表,设置大小写是否敏感, chinese_prc_ci_as 不区分大小写
--区分大小写 chinese_prc_cs_as
SELECT * FROM emp WHERE (ASCII(SUBSTRING(ENAME, 1, 1)) > 64) AND (ASCII(SUBSTRING(ENAME, 1, 1)) < 91)
select * from emp where ENAME collate chinese_prc_cs_as_ws like ‘[A]%‘

--SELECT  UPPER(SUBSTRING(‘aYAME‘,1,1))+LOWER(SUBSTRING(‘aYAME‘,2,( SELECT LEN(‘aYAME‘))))

--15、显示正好为5个字符的雇员姓名
select * from emp where LEN(ENAME)=5
--16、显示带有‘R‘的雇员姓名 不区分大小写
select * from emp where ENAME  like ‘%R%‘
--17、显示不带有‘R‘的雇员姓名
select * from emp where ENAME not like ‘%R%‘
--18、显示包含"A"的所有雇员的姓名及"A"在姓名字段中的位置
--select emp.ENAME from emp where ENAME
select ename,CHARINDEX(‘A‘,ENAME) from emp where ename like ‘%A%‘;

--19、显示所有雇员的姓名，用a替换所有‘A‘
select REPLACE(ename,‘A‘,‘a‘) as ename from emp 
--20、显示所有雇员的姓名的前三个字符
--1.left(name,4)截取左边的4个字符
--2.right(name,2)截取右边的2个字符
--3.SUBSTRING(name,5,3) 截取name这个字段 从第五个字符开始 只截取...
--4.SUBSTRING(name,3) 截取name这个字段 从第三个字符开始,之后的所有个...
--5.SUBSTRING(name, -4) 截取name这个字段的第 4 个字符位置(倒数)..
select left(ename,3) as Ename  from emp
--21、显示雇员的详细资料，按姓名排序
select * from emp order by ENAME 
--22、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename from emp  order by HIREDATE Asc
--23、显示所有雇员的姓名、工作和薪金，按工作降序顺序排序，
--而工作相同的按薪金升序排序.
select emp.ENAME,EMP.JOB,emp.SAL from emp order by job desc ,SAL ASC   --如果第一个排序条件重复,则继续用第二个排序
--24、显示在一个月为30天的情况下所有雇员的日薪金,忽略小数
SELECT ENAME,SAL/30, CAST(SAL/30 AS INT) FROM EMP
--25、找出在（任何年份的）2月受聘的所有雇员
SELECT ENAME,HIREDATE FROM EMP WHERE MONTH(HIREDATE)=2
--26、对于每个雇员，显示其加入公司的天数
--提示:datediff(day,hiredate,getdate())
--获取两个时间的差值.(单位可选)
select ename, DATEDIFF(DAY,emp.HIREDATE,GETDATE()) as tianshu from EMP
--27、列出至少有一个雇员的所有部门
select count(EMP.EMPNO),DEPT.DNAME from emp left join DEPT on EMP.DEPTNO=DEPT.DEPTNO GROUP BY DEPT.DNAME 
 Having  COUNT(EMP.EMPNO)IS NOT NULL

 SELECT DEPTNO,COUNT(EMPNO) 人数 FROM EMP a
GROUP BY DEPTNO
HAVING COUNT(EMPNO) IS NOT NULL
--28、列出各种类别工作的最低工资
select JOB, MIN(sal) AS 最低工资 from emp GROUP BY JOB ORDER BY 最低工资 DESC

SELECT JOB,MIN(SAL+ISNULL(COMM,0)) 最低工资 FROM EMP GROUP BY JOB
--29、列出各个部门的MANAGER（经理）的最低薪金
select min(Sal) from emp where job=‘MANAGER‘ --所有的取最低值
SELECT DEPTNO 部门名称,MIN(SAL) 最低薪金 FROM EMP WHERE JOB=‘MANAGER‘ GROUP BY DEPTNO --分组之后 ,取组内最低值
--30、列出薪金高于公司平均水平的所有雇员
select *  from emp Having SAL> AVG(SAL) --cuo
SELECT * FROM EMP WHERE SAL>(SELECT AVG(SAL)FROM EMP)
--31、列出各种工作类别的最低薪金，并显示最低薪金大于1500
--select job, min(SAL)>1500 from emp  group by job 错误
SELECT JOB,MIN(SAL) FROM EMP GROUP BY JOB HAVING MIN(SAL)>1500

--32、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇 日所在月排序，将最早年份的项目排在最前面
select ename,emp.HIREDATE from emp 
SELECT ENAME,MONTH(HIREDATE) as 月,YEAR(HIREDATE) as 年 FROM EMP ORDER BY MONTH(HIREDATE) ,YEAR(HIREDATE)ASC
--33、显示所有雇员的姓名以及满8年服务年限的日期
select emp.ENAME ,emp.HIREDATE  from emp  where DATEDIFF(YEAR,emp.HIREDATE,GETDATE())>8
--34、显示所有雇员的服务年限：总的年数或总的月数或总的天数
select *, DATEDIFF(YEAR,emp.HIREDATE,GETDATE()) As 年,DATEDIFF(MONTH,emp.HIREDATE,GETDATE()) As 月,
DATEDIFF(DAY,emp.HIREDATE,GETDATE()) As 天 from emp
--35、列出按计算的字段排序的所有雇员的年薪.即：按照年薪对雇 员进行排序,年薪指雇员每月的总收入总共12个月的累加
--select  emp.ename, sum(sal) as 年薪 from emp where YEAR(EMP.HIREDATE)=2020 group by EMP.ENAME  ling
SELECT ENAME ,SAL*12 FROM EMP ORDER BY SAL*12 ASC
--36、列出年薪前5名的雇员
SELECT TOP 5 ENAME, SAL*12 AS 年薪 FROM EMP ORDER BY SAL*12 DESC
--37、列出年薪低于10000的雇员
select * ,(sal*12) as nianxin from emp where sal*12<10000
--38、列出雇员的平均月薪和平均年薪
select avg(sal)as 平均月薪, AVG(sal*12)as 平均年薪 from emp
SELECT CAST( AVG(SAL)AS INT)AS 平均月薪,CAST( AVG(SAL*12)AS INT)AS 平均年薪 FROM EMP
--39、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select d.DNAME,e.ENAME  from dept d left join emp e on d.DEPTNO=e.DEPTNO
SELECT DNAME,ENAME FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
--40、列出每个部门的信息以及该部门中雇员的数量
--select count(DEPTNO) from (select d.*  from DEPT d left join emp e on e.DEPTNO=d.DEPTNO) group by d.DEPTNO cuowu

select d.DEPTNO,DNAME,LOC部门所在地,count(d.DEPTNO)As 人数  from DEPT d  left join emp e on e.DEPTNO=d.DEPTNO  group by DNAME,LOC部门所在地,d.DEPTNO
SELECT DEPT.DEPTNO,DNAME,LOC部门所在地,COUNT(EMPNO) 雇员数量 FROM EMP RIGHT JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO
GROUP BY DNAME,LOC部门所在地,DEPT.DEPTNO

--41、列出薪金比"SMITH"多的所有雇员
select * from emp where SAL>(select sal from emp where ename=‘JONES‘)
SELECT A.ENAME,A.SAL FROM EMP AS A,EMP AS B WHERE A.SAL>B.SAL AND B.ENAME=‘JONES‘--自连接 当同一列的值相互之间进行比较时
ORDER BY A.SAL ASC

--42、列出所有雇员的姓名及其直接上级的姓名(自连接)
--select a.ename from emp a ,emp b where  a.DEPTNO=b.DEPTNO and 
--select ename,DEPTNO from EMP where job=‘MANAGER‘
select a.ename ,b.ENAME from emp a ,emp b where a.MGR=b.EMPNO
select a.ename ,b.ename from emp a left join emp b on a.MGR=b.EMPNO
--43、列出入职日期早于其直接上级的所有雇员
select a.ename ,b.ename from emp a inner join emp b on a.MGR=b.EMPNO and a.HIREDATE<b.HIREDATE

SELECT A.ENAME FROM EMP AS A,EMP AS B WHERE A.MGR=B.EMPNO AND A.HIREDATE<B.HIREDATE
--44、列出所有办事员（"CLERK"）的姓名及其部门名称
select ename,dept.DNAME from emp inner join DEPT on  EMP.JOB=‘CLERK‘ AND emp.DEPTNO=DEPT.DEPTNO
SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.JOB=‘CLERK‘
--45、列出从事"SALES"（销售）工作的雇员的姓名，假定不知道 销售部的部门编号
select ename from DEPT  join emp on DEPT.DNAME=‘SALES‘  and DEPT.DEPTNO=EMP.DEPTNO
SELECT ENAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DEPT.DNAME=‘SALES‘
--46、列出与"SCOTT"从事相同工作的所有雇员
select  b.ENAME from emp a inner join emp b on a.ename=‘SCOTT‘ and  a.JOB=b.job
--47、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30 中任何一个雇员的薪金

select ename,sal,deptno from emp where sal=any(select sal from emp where DEPTNO=30)
--48、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30 中所有雇员的薪金
select ename,sal,deptno from emp where sal>all(select sal from emp where DEPTNO=30)
--49、列出从事同一种工作但属于不同部门的雇员的不同组合
select DISTINCT a.ENAME, b.ENAME from  emp a join emp b on  a.JOB=b.JOB and a.DEPTNO != b.DEPTNO
SELECT DISTINCT A.ENAME, B.ENAME FROM EMP AS A,EMP AS B WHERE A.JOB=B.JOB AND A.DEPTNO!=B.DEPTNO