SqlServer练习题

use hpCompany
–第一部分

–1. 选出部门30中的所有员工。
select * from emp where deptno = 30;

–2. 列出所有办事员(CLERK)的姓名、编号和部门编号。
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中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料。
Select * from emp where deptno =10 and job = ‘manager’ or deptno = 20 and job = ‘clerk’;

–6. 找出部门10中所有经理(MANAGER)，部门20中所有办事员(CLERK)，既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料。
Select * from emp where deptno =10 and job = ‘manager’ or deptno = 20 and job = ‘clerk’ or job != ‘manager’ and job != ‘clerk’ and sal >= 2000;

–7.找出收取佣金的员工的不同工作。
Select distinct job from emp where comm is not null;

–8. 找出不收取佣金或收取的佣金低于100的员工。
select * from emp where comm is null or comm < 100;

–9. 查询所有部门名称和员工姓名，包括没有员工的部门名称也显示。
select dname,ename from dept d left join emp e on d.deptno = e.deptno;

–10.查询工资高于公司平均工资的所有员工信息。
select * from emp where sal > (select AVG(sal) from emp);

–11. 查询工资高于部门平均工资的所有员工。
select * from emp e where sal>(select avg(sal)from emp where e.deptno=deptno group by deptno);

–12.查询emp表的第1~3行。
select top 3 * from emp order by sal;

–13.查询emp表的第4-6行。1
select top 3 * from (select top 6 * from emp order by empno asc) a order by a.empno desc;

–13.查询emp表的第4-6行.2
select top 3 * from emp where empno not in (select top 3 empno from emp ) ;

–14. 把员工姓名和工作类型连接在一起，中间用“-”分割显示(concat函数)。(用concat函数报错）
select ename + ‘-‘+job from emp;

–15. 截取员工姓名的前3个字符和第4个字符以后的内容显示。
select ename,left(ename,3) 前三个字符,substring(ename,4,LEN(ename)) 第四个字符以后的内容 from emp;

–16. 查询员工编号，姓名和所在部门的名称。
select empno,ename,dname from emp, dept where emp.deptno = dept.deptno;

–17. 查询部门的名称以及该部门的人数，要求没有员工的部门也要显示。
select dname,count(empno) 人数 from emp right join dept on emp.deptno = dept.deptno group by dept.dname;

–18. 查询员工姓名及其直接上级的姓名。
select a.ename 姓名,b.ename 上级 from emp a,emp b where a.mgr = b.empno order by b.ename;

–19. 查询工资高于平均工资的员工姓名。
select ename from emp where sal > (select AVG(sal) from emp);

–20. 查询工资高于本部门平均工资的员工。
select * from emp e where sal >(select AVG(sal) from emp where e.deptno=deptno );

–21. 查询每个部门中拥有最高工资的员工的信息。
select * from emp e where sal = (select max(sal) from emp where e.deptno = deptno);
select * from emp e where not exists (select * from emp where deptno = e. deptno and sal > e.sal);

–22. 统计“JONES”所带领的团队的工资总额。
select SUM(sal)from emp where ename = ‘jones’ or mgr = (select empno from emp where ename = ‘jones’);

–23.列出10号部门中有而20号部门中没有的工作。
select job from emp where deptno = 10 and job not in(select job from emp where deptno = 20);

–第二部分

–1. 查询津贴为空的所有员工。
select * from emp where comm is null;

–2. 查询姓名以字母“S”开头的员工。
select * from emp where ename like ‘s%’;

–3.查询所有的经理(MANAGER)和办事员(CLERK)的员工信息。
select * from emp where job = ‘manager’ or job = ‘clerk’;

–4.按参加工作时间从早到晚显示员工信息。
select * from emp order by hiredate asc;

–5.增加一个部门。
insert into dept (deptno,dname) values (50,’dname’);

–6.给员工涨工资5%。
update emp set sal = sal * 1.05;

–7.删除新增的部门“培训部”。
insert into dept (deptno,dname) values (60,’培训部’);
delete from dept where dname = ‘培训部’;

–8.查询系统时间。
select GETDATE();

–9.查询在20(30)年前参加工作的员工。
select * from emp where datediff(year,cast(hiredate as date),GETDATE()) >20;
select * from emp where datediff(year,cast(hiredate as date),GETDATE()) >30;

–10.查询在当月倒数第三天参加工作的员工。
select * from emp where DATEDIFF(month,cast(hiredate as date),DATEADD(day,3,cast(hiredate as date))) = 1;

–11.查询每个员工的工作天数。
select *,DATEDIFF(day,CAST(hiredate as DATE),GETDATE()) 工作天数 from emp;

–12.显示系统时间是xxxx年xx月xx日，是一年中的第几天，是星期几。
select cast(GETDATE()as DATE) 日期,DATEPART(dayofyear,getdate()) 一年中的第几天,DATEPART(weekday,getdate())-1 星期;

–13.按每月30天计算员工的日薪金，要求计算结果四舍五入到小数点后2位。
select *,ROUND(sal*1.00 / 30,2) 日薪 from emp;

–14.计算每个员工已经工作了多少个月，要求忽略小数部分。
select *,DATEDIFF(month,cast(hiredate as DATE),GETDATE()) 工作月数 from emp;

–15.按年和月的格式显示员工参加工作的时间。
select empno,ename,datepart(year,CAST(hiredate as DATE)) 年, datepart(month,CAST(hiredate as DATE)) 月 from emp;
select empno,ename,convert(varchar(7),hiredate,120) 年月 from emp;
select empno,ename,year(CAST(hiredate as DATE)) 年,month(CAST(hiredate as DATE)) 月 from emp;

–16.查询在1987年2月到1987年5月之间(包括2月和5月)参加工作的员工。
select * from emp where hiredate >= cast(‘1987-02-01’ as DATE) and hiredate <= cast(‘1987-05-31’ as DATE);
select * from emp where hiredate between cast(‘1987-02-01’ as DATE) and cast(‘1987-05-31’ as DATE);

–17.按指定格式显示员工的工资(工资格式为9,990.99)。
select empno,ename,sal*1.00 from emp;
select empno,ename,convert(sal as double) from emp;

–18.显示员工的津贴信息，如果没有津贴就显示0。( 使用存储过程)
creat procedure printComm
as
begin
declare @sum int;
select @sum = count(empno) from emp;
declare @comm int,@empno int, @ename nvarchar(10);
select @empno = empno,@ename = ename,@comm=comm from emp;
print ‘编号-姓名-津贴’
while @sum > 0
begin
if @comm is null
set @comm = 0
print cast(@empno as nvarchar(10)) + ‘-‘+ @ename+ ‘-‘+cast(@comm as nvarchar(10));
set @sum = @sum - 1;
select @empno = empno ,@ename = ename,@comm=comm from emp where empno < @empno order by empno asc;
end
end

execute printComm;

–19.统计员工的人数，津贴不为空的人数。
create procedure countC
as
begin
declare @countEmpno int,@countComm int;
select @countEmpno = COUNT(empno)from emp;
select @countComm = COUNT(empno) from emp where comm is not null;
select @countEmpno 员工人数, @countComm 津贴不为空的人数;
end

execute countC;

–20.统计部门最低工资大于900的部门和最低工资。
select deptno,(select MIN(b.sal) from emp b where a.deptno = B.deptno) from dept a group by deptno having (select MIN(b.sal) from emp b where a.deptno = B.deptno) > 900;

–21.统计每个部门工资在1400元以上的所有员工的工资总额。
select deptno,SUM(sal) from emp where sal > 1400 group by deptno;

–22.统计不同工作的个数。
select count(distinct job) from emp;

–23.找出各月倒数第3天受雇的所有员工。
select * from emp where datediff(month,hiredate,dateadd(day,3,cast(hiredate as DATE))) =1;
select * from emp where DAY(DATEADD(day,3,hiredate)) = 1;

–24.找出早于12（30）年前受雇的员工。
select * from emp where DATEDIFF(year,hiredate,getdate())>12;
select * from emp where DATEDIFF(year,hiredate,getdate())>30;

–25.以首字母大写的方式显示所有员工的姓名。
select LEFT(ename,1) + SUBSTRING(lower(ename),2,len(ename))from emp;

–26.显示正好为5个字符的员工的姓名。
select ename from emp where LEN(ename) = 5;

–27.显示不带有“R”的员工的姓名。
select ename from emp where ename not like ‘%R%’;

–28.显示所有员工姓名的前三个字符。
select LEFT(ename,3) from emp;

–29.显示所有员工的姓名，用“a”替换所有“A”。
select REPLACE(ename,’A’,’a’)from emp;

–30.显示满10(30)年服务年限的员工的姓名和受雇日期。
select ename,hiredate from emp where DATEDIFF(year,hiredate,getdate())>10;
select ename,hiredate from emp where DATEDIFF(year,hiredate,getdate())>30;

–31.显示员工的详细资料，按名称排序。
select * from emp order by ename asc;

–32.显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面。
select ename,hiredate from emp order by hiredate asc;

–33.显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同则按薪金排序。
select ename,job,sal from emp order by job desc ,sal asc;

–35.显示在一个月为30天的情况所有员工的日薪金，忽略余数。
select empno,ename,sal/30 日薪 from emp;

–36.找出在(任何年份的)2月受聘的所有员工。
select * from emp where MONTH(hiredate) = 2;

–37.对于每个员工显示其加入公司的天数。
select * ,datediff(day,hiredate,getdate()) 加入天数 from emp ;

–38.显示姓名字段的任何位置包含“A”的所有员工的姓名。
select ename from emp where ename like ‘%A%’;

–39.以年月日的方式显示所有员工的服务年限(大概)。
select CAST(datediff(day,hiredate,getdate())/365 as varchar(10)) +’年’+ CAST(datediff(day,hiredate,getdate())%365 /30as varchar(10)) +’月’+ CAST(datediff(day,hiredate,getdate())%365 %30 as varchar(10)) +’日’ from emp;

–40.列出至少有一个员工的所有部门信息。
select * from dept where deptno in (select deptno from emp );

–41.列出薪金比“SMITH”多的所有员工。
select * from emp where sal > (select sal from emp where ename = ‘smith’);

–42.列出所有员工的姓名及其直接上级的姓名。
select a.ename 员工,(select ename from emp where a.mgr = empno) 上级 from emp a order by ename;

–43.列出受雇日期早于其直接上级的所有员工。
select * from emp a where hiredate < (select hiredate from emp where a.mgr = empno);

–44.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select d.dname,e.* from dept d left join emp e on d.deptno = e.deptno;

–45.列出所有“CLERK”(办事员)的姓名及其部门名称。
select ename,dname from emp e,dept d where e.job = ‘clerk’ and e.deptno = d.deptno;

–46.列出最低薪金大于1500的各种工作。
select job from emp group by job having MIN(sal) > 1500;

–47.列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
select ename from emp where deptno=(select deptno from dept where dname = ‘sales’);

–48.列出与“SCOTT”从事相同工作的所有员工。
select * from emp where job = (select job from emp where ename = ‘scott’);

–49.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。(不确定题意。）
select ename,sal from emp where sal in (select sal from emp where deptno = 30);

–50.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal from emp where sal > (select MAX(sal) from emp where deptno = 30);

–51.列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno,count(empno),AVG(sal) ,AVG(DATEDIFF(year,hiredate,getdate())) from emp group by deptno;

–52.列出所有员工的姓名、部门名称和工资。
select ename,dname,sal from emp,dept where emp.deptno = dept.deptno order by ename asc;

–53.列出所有部门的详细信息和部门人数。
select *,(select COUNT(empno) from emp where dept.deptno = emp.deptno) from dept ;

–54.列出各种工作的最低工资。
select job,MIN(sal) from emp group by job;

–55.列出各个部门的MANAGER(经理)的最低薪金。
select MIN(sal) from emp where job = ‘manager’;

–56.列出所有员工的年工资，按年薪从低到高排序。
select *,sal*12 as 年薪 from emp order by 年薪 asc;

–57.列出工资最高的员工信息。
select * from emp where sal = (select MAX(sal) from emp);