Sql50语句小练

N多年前的练习库,偶尔看看、动动手、清爽一下:

create database  company

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)--部门编号 
)
go
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 e.*,d.*,ename from EMP e left join DEPT d on e.DEPTNO=d.DEPTNO
--右外链接
select e.*,d.*,ename from EMP e right join DEPT d on e.DEPTNO=d.DEPTNO
--全链接
select e.*,d.*,ename from EMP e full join DEPT d on e.DEPTNO=d.DEPTNO
--内链接
select e.*,d.* from EMP e ,DEPT d where e.DEPTNO=d.DEPTNO 

select e.*,d.* from EMP e inner join DEPT d  on e.DEPTNO=d.DEPTNO where SAL>1500
--全链接/交叉链接
select e.*,d.* from EMP e cross join DEPT d 
--多表链接

 --查询EMP表中第6-10行数据（有主键且主键不一定连续)
 select * from emp
 select top 5* from EMP
 where EMPNO not in (select top 5 EMPNO from EMP)--not in刨除前5条
 

--1、查询所有的雇员名字
use company
select ENAME from EMP

--2、查询所有的部门
SELECT DNAME FROM DEPT

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

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

--5、选择部门编号=30中的雇员
SELECT * FROM EMP WHERE DEPTNO=30


--6、列出所有Job办事员（"CLERK"）的姓名、编号和部门名称
SELECT ENAME,EMPNO,DEPTNO FROM EMP WHERE JOB='CLERK'


--7、找出佣金高于薪金的雇员
select * from EMP where COMM>sal

--8、找出佣金高于薪金的60%的雇员
select * from EMP where COMM>(SAL*0.6)

--9、找出部门10中所有经理和部门20中的所有办事员的详细资料
select * from emp
where DEPTNO=10 and job='manager' or  job='clerk' and DEPTNO=20

--10、找出部门10中所有经理、部门20中所有办事员.
select ENAME from emp
where DEPTNO=10 and job='manager' or  job='clerk' and DEPTNO=20 
go

--既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
select * from EMP where job!='clark' and job!='manager' and SAL>=2000
go

--11、找出收取佣金的雇员的不同工作
select e.JOB from EMP e where COMM is not null group by job

--12、找出不收取佣金或收取的佣金低于100的雇员
select * from EMP where COMM is null or COMM<100

--13、找出早于8年之前受雇的雇员DATEADD(MONTH ,-8,HIREDATE )
select  e.ENAME,e.HIREDATE from EMP e where HIREDATE<=DATEADD(YEAR ,-8,GETDATE())
select  * from EMP
--14、显示首字母大写的所有雇员的姓名
alter table emp
alter column ename varchar(20)
collate chinese_prc_cs_as
select e.ENAME from EMP e where e.ENAME like '[ABCDEFGH]%'

--15、显示正好为5个字符的雇员姓名
select e.ENAME from EMP e where e.ENAME like '_____'


--16、显示带有'R'的雇员姓名 不区分大小写
select e.ENAME from EMP e where e.ENAME like '%R%'

--区分大小写

--collate:指定排序规则的

--修改表,设置大小写是否敏感, chinese_prc_ci_as 不区分大小写

--区分大小写 chinese_prc_cs_as
alter table emp
alter column ename varchar(20)
collate chinese_prc_cs_as--区分中国大陆地区大小写，默认不分大小写

select ename from emp
where ENAME like '[S]%'--[az]

--17、显示不带有'R'的雇员姓名
alter table emp
alter column ename varchar(20)
collate chinese_prc_cs_as
select e.ENAME from EMP e where e.ENAME not like '%R%'



--18、显示包含"A"的所有雇员的姓名及"A"在姓名字段中的位置
     -- 提示: 使用函数 charindex()  
select e.ENAME ,CHARINDEX('A',ENAME) from EMP e where e.ENAME  like '%A%'




--19、显示所有雇员的姓名，用a替换所有'A' Eg: select  replace('abc','a','x') 
select REPLACE(ENAME,'A','a') from EMP e   


--20、显示所有雇员的姓名的前三个字符,SUBSTRING(string,start_position,length)
select e.ENAME,SUBSTRING(ename,1,3) from EMP e



--21、显示雇员的详细资料，按姓名排序
select * from emp e order by e.ENAME desc 




--22、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select e.ENAME,e.HIREDATE from EMP e order by (hiredate) asc




--23、显示所有雇员的姓名、工作和薪金，按工作降序顺序排序，
--而工作相同的按薪金升序排序.
select e.ENAME,e.JOB,e.SAL   from EMP e order by JOB desc ,SAL



--24、显示在一个月为30天的情况下所有雇员的日薪金,忽略小数
    select e.ENAME,cast(sal/30  as int)from EMP e





--25、找出在（任何年份的）2月受聘的所有雇员
select * from EMP where month(hiredate) like '2'


--26、对于每个雇员，显示其加入公司的天数
 select ENAME,DATEDIFF(day,hiredate,getdate()) 总天数 from EMP


--27、列出至少有一个雇员的所有部门（编号）
select * from EMP
select DEPTNO from EMP group by DEPTNO having COUNT(*)>0

--28、列出各种类别工作的最低工资
select min(sal),job from EMP group by job 

--29、列出各个部门的MANAGER（经理）的最低薪金
select MIN(sal),deptno from EMP where JOB='manager' group by DEPTNO 


--30、列出薪金高于公司平均水平的所有雇员(提示：子查询)
--select EMP.* from EMP where SAL>1000
select * from EMP where sal>(select AVG(sal) from EMP)

--31、列出各种工作类别的最低薪金，并使最低薪金大于1500
select min(SAL),JOB from EMP e where e.SAL>1500 group by JOB 

--32、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇 日所在月排序，将最早年份的项目排在最前面$$$$$$$$$$$$$$$

select YEAR(hiredate),MONTH(hiredate),DAY(hiredate) from EMP order by YEAR(hiredate) asc

--33、显示所有雇员的姓名以及满8年服务年限的日期
select  e.ENAME,e.HIREDATE ,year(hiredate)+8 as 满八年 from EMP e 

--34、显示所有雇员的服务年限：总的年数或总的月数或总的天数
select EMP.ENAME,DateDiff(year,HIREDATE,getdate()) 总年数 ,
       DateDiff(MONTH,HIREDATE,getdate()) 总月数,
       DateDiff(DAY,HIREDATE,getdate()) 总天数
  from EMP 



--35、列出按计算的字段排序的所有雇员的年薪.即：按照年薪对雇 员进行排序,年薪指雇员每月的总收入总共12个月的累加

select   e.ENAME,e.SAL*12 as 年薪   from  EMP e order by SAL asc

--36、列出年薪前5名的雇员
select   top 5 e.ENAME ,e.SAL,e.JOB from EMP e order by SAL desc



--37、列出年薪低于10000的雇员
--isnull(列名，0） ：如果该列中有空值，就把空值当做0做计算

select e.ENAME ,e.SAL from EMP e where SAL*12<10000



--38、列出雇员的平均月薪和平均年薪
select AVG(sal) 平均月薪 ,avg(SAL*12) 平均年薪 from EMP e


--39、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select   e.ENAME 雇员,d.DNAME 部门 from EMP e left join DEPT d on e.DEPTNO=d.DEPTNO




--40、列出每个部门的信息以及该部门中雇员的数量
SELECT D.DEPTNO,D.DNAME,D.LOC部门所在地,COUNT(EMPNO)
FROM DEPT D left JOIN EMP E--inner join不能完全显示
ON E.DEPTNO=D.DEPTNO 
GROUP BY  D.DEPTNO,D.DNAME,D.LOC部门所在地 


--41、列出薪金比"SMITH"多的所有雇员(子查询)
SELECT EMP.ENAME FROM EMP
SELECT e.ENAME   FROM EMP e where SAL>(select e.SAL from EMP e where e.ENAME='SMITH') 


--42、列出所有雇员的姓名及其直接上级的姓名(自连接)$$$$$$$$$$$$$$$$$$$$$$$$$
      --自连接:就是一个表自己和自己连接,
      --注意一定要用表别名,区分它们
    select  e1.EMPNO,e1.ENAME as 员工姓名,e1.MGR,e2.EMPNO
    ,e2.ENAME as 领导姓名
     from EMP e1,EMP e2
      where e1.MGR=e2.EMPNO  



--43、列出入职日期早于其直接上级的所有雇员
select  a.ENAME,B.ename from emp a,EMP b where a.MGR=b.EMPNO  and a.HIREDATE>b.HIREDATE


--44、列出所有办事员（"CLERK"）的姓名及其部门名称
select e.ENAME ,e.JOB,d.DNAME from EMP e inner JOIN DEPT d ON e.DEPTNO=d.DEPTNO and e.JOB='clerk'
--select * from EMP 


--45、列出从事"SALES"（销售）工作的雇员的姓名，假定不知道 销售部的部门编号
 
  select e.ENAME ,e.JOB from EMP e inner join DEPT d on  e.DEPTNO =d.DEPTNO   --JOB='sales'  

--46、列出与"SCOTT"从事相同工作的所有雇员

select  e.ENAME,e.JOB  from EMP e where  job=(select JOB from EMP where ENAME='scott')  
and e.ENAME<>'scott'


--47、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30 中任何一个雇员的薪金
select a.ename, a.sal from emp a,emp b where a.deptno<>30 and b.DEPTNO=30 and a.SAL=b.SAL

--48、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30 中所有雇员的薪金
--select  e.ENAME,e.SAL ,e.DEPTNO  from EMP e,EMP e1 where e.SAL >=(select e1.SAL from EMP where DEPTNO=30  ) 

--49、列出从事同一种工作但属于不同部门的雇员的不同组合

  select ename,dname from EMP,dept where EMP.DEPTNO=DEPT.DEPTNO  group by dname,ename
  
   --on e.JOB=(select JOB from EMP e)-- inner join EMP d where e.JOB=d.JOB)    

--50、列出所有雇员的雇员名称、部门名称和薪金
select  e.ENAME 雇员,d.DNAME 部门,e.SAL 薪金  from EMP e left join DEPT d on e.DEPTNO=d.DEPTNO

--51 回家作业:(分页）top+子查询
 --要求:用子查询实现,查询emp表中的第6到第10个员工(分页的基础)
 --(特别提醒:员工编号唯一,但不一定连续)