JAVA入门到精通-第67讲-sqlserver作业讲评

sql server作业点评：

使用scott/tiger用户下的emp表完成下列练习，表的结构说明如下

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

//把课堂上创建的emp,dept表自己创建一下(参考图)

 

//根据emp表和dept表，完成下面的练习

--1、选择部门30中的所有员工

select * from emp where deptno=30

 

--2、列出所有办事员(CLERK)的姓名，编号和部门编号

select ename,empno,deptno from emp where job='clerk'

 

--3、找出佣金高于薪金的员工(佣金是奖金)

select * from emp where isnull(comm,0)>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>0

 

--8、找出不收取佣金或收取的佣金低于100的员工

select * from emp where comm<100 or comm is null

 

--9、找出各月倒数第3天受雇的所有员工

//oracle中有一个函数可以得到某个日期的最后一天

select * from emp where hiredate=dateadd(day,-3,datename(year,dateadd(month,1,hiredate))+datename(month,dateadd(month,1,hiredate))+'01')

 

--10、找出早于12年前受雇的员工

select * from emp where hiredate<dateadd(dd,-12*365,getdate())

select * from emp where datediff(year,hiredate,getdate())>12

 

--11、以首字母大写的方式显示所有员工的姓名

select upper(substring(ename,1,1))+lower(substring(ename,2,len(ename))) from emp

 

--12、显示正好为5个字符的员工的姓名

select * from emp where ename like'_____'

select * from emp where len(ename)=5

 

--13、显示不带有R的员工的姓名

select ename from emp where ename not like'%R%'

 

--14、显示所有员工姓名的前三个字符

select substring(ename,1,3) from emp

 

--15、显示所有员工的姓名，用a替换所有A

select replace(ename,'A','a') from emp

 

--16、显示满10年服务年限的员工的姓名和受雇日期

select ename,hiredate from emp where datediff(year,hiredate,getdate())>10

 

--17、显示员工的详细资料，按姓名排序

select * from emp order by ename

 

--18、显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面

select ename,hiredate from emp order by hiredate

 

--19、显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同则按薪金排序

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

 

--20、显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面

select ename,datepart(year,hiredate) y,datepart(month,hiredate) m from emp order by m,y

 

---

 

--21、列出至少有一个员工的所有部门
select count(*),deptno from emp group by deptno having count(*)>1
 
--22、列出薪金比“SMITH”多的所有员工
select * from emp where sal>(select sal from emp where ename='smith')and ename<>'smith'
 
--23、列出所有员工的姓名及其直接上级的姓名
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno
 
--24、列出受雇日期晚于其直接上级的所有员工
select e1.ename,e1.hiredate,e2.ename,e2.hiredate from emp e1,emp e2 where e1.mgr=e2.empno and e1.hiredate>e2.hiredate
 
--25、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select d.dname,e.ename,e.job from emp e right join dept d on e.deptno=d.deptno
 
--26、列出所有“clerk”(办事员)的姓名及其部门名称
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and job='clerk'
 
--27、列出最低薪金大于1500的各种工作。
select min(sal),job from emp group by job having min(sal)>1500
 
--28、列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
select ename,'sales' from emp where deptno=(select deptno from dept where dname='sales')
 
--29、列出薪金高于公司平均薪金的所有员工。
select * from emp where sal>(select avg(sal) from emp)
 
--30、列出与“SCOTT”从事相同工作的所有员工。
select * from emp where job=(select job from emp where ename='scott')
 
--31、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
select ename,sal from emp where sal in(select sal from emp where deptno=30)
 
--32、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30)

--33、列出在每个部门工作的员工数量、平均工资和平均服务期限
select count(*)"员工人数",avg(sal)"部门平均工资",avg(datediff(year,hiredate,getdate()))"部门平均服务期限",deptno from emp group by deptno
 
--34、列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal from emp e,dept d where e.deptno=d.deptno
 
--35、列出从事同一种工作但属于不同部门的员工的一种组合。
select w1.ename,w1.job,w1.deptno,w2.ename,w2.job,w2.deptno from emp w1,emp w2 where w1.job=w2.job and w1.deptno<>w2.deptno
 
--36、列出所有部门的详细信息和部门人数。
select d2.dname,d2.loc,isnull(d.c,0) from dept d2 left join (select count(*) c,deptno de from emp group by deptno) d on d2.deptno=d.de
 
--37、列出各种工作的最低工资
select min(sal),job from emp group by job
 
--38、列出MANAGER(经理)的最低薪金
select min(sal) from emp where job='manager'
 
--39、列出所有员工的年工资，按年薪从低到高排序
select ename,(sal+isnull(comm,0))*12"年工资" from emp order by "年工资"

 

 

<wiz_tmp_tag id="wiz-table-range-border" contenteditable="false" style="display: none;">

 

 

 

 

来自为知笔记(Wiz)