相关表：
create table DEPT( deptno int not null primary key, dname varchar(14) null, loc varchar(13) null)create table EMP( empno int not null primary key, ename varchar(10) null, job varchar(9) null, mgr int null, hiredate date null, sal float null, comm float null, deptno int null foreign key references DEPT(deptno),)create table SALGRADE( grade int null, losal int null, hisal int null,)
查询部分：
--查询雇员姓名的最后三个字母SELECT SUBSTRING(ename,LEN(ename)-3,3) FROM dbo.EMP--查询10部门雇员进入公司的星期数SELECT ename,DATEDIFF(wk,hiredate,GETDATE())AS 进入星期数 FROM dbo.EMP WHERE deptno=10--1 查询部门30中的全部员工SELECT * FROM dbo.EMP WHERE deptno=30--2 列出全部办事员(CLERK)的姓名,编号和部门编号SELECT ename , empno , deptno FROM emp WHERE job='clerk'--3 找出佣金高于薪金的员工SELECT * FROM dbo.EMP WHERE comm>sal--求出每一个雇员的年薪SELECT ename,sal*12+ISNULL(comm,0)*12 AS 年薪 FROM dbo.EMP--4 找出佣金高于薪金的60%的员工SELECT * FROM dbo.EMP WHERE comm>sal*0.6--5 找出部门10中全部经理(MANAGER)和部门20中全部办事员(CLERK)的具体资料SELECT * FROM dbo.EMP WHERE deptno=10 AND job='manager' OR deptno=20 and job='CLERK'--6 -- 既不是经理又不是办事员但薪金大于或等于2000的全部员工的具体资料SELECT * FROM dbo.EMP WHERE job!='manager' AND job!='clerk' AND sal>=2000--7 找出收取佣金的员工的不同工作SELECT DISTINCT job FROM dbo.EMP WHERE comm IS NOT NULL--8 找出不收取佣金或收取佣金低于100的员工SELECT * FROM dbo.EMP WHERE comm IS NULL OR comm<100--9 找出各月倒数第3天受雇的全部员工SELECT * FROM dbo.EMP WHERE MONTH(DATEADD(DAY,3,hiredate))=MONTH(DATEADD(MONTH,1,hiredate))--10 找出早于12年前受雇的员工SELECT * FROM dbo.EMP WHERE DATEDIFF(YEAR,hiredate,GETDATE())>12--11 以首字母大写的方式显示全部员工的姓名select upper(substring(ename,1,1))+lower(substring(ename,2,len(ename)-1)) from emp;--12 显示正好为5个字符的员工的姓名SELECT ename FROM emp WHERE LEN(ename)=5--13 显示不带有"R"的员工的姓名SELECT ename FROM dbo.EMP WHERE ename NOT LIKE '%R%'--14 显示全部员工的姓名的前三个字符SELECT SUBSTRING(ename,1,3) FROM dbo.EMP--15 显示全部员工的姓名,用"a"取代全部的"A"SELECT REPLACE(ename,'a','A') FROM dbo.EMP--16 显示满10年服务年限的员工的姓名和受雇日期SELECT ename,hiredate FROM dbo.EMP WHERE DATEDIFF(year,hiredate,GETDATE())>10--17 显示员工具体信息,按姓名排序SELECT * FROM dbo.EMP ORDER BY ename--18 显示员工的姓名和受雇日期,依据其服务年限,将最老的员工排在前面SELECT ename,hiredate FROM dbo.EMP ORDER BY DATEDIFF(year,hiredate,GETDATE()) DESC--19 显示全部员工的姓名,工作和薪金,按工作的降序排序,若工作同样则薪金排序SELECT ename,job ,sal+ISNULL(comm,0)AS 薪金 FROM dbo.EMP order BY job ,sal+ISNULL(comm,0)--20 显示全部员工的姓名,增加公司的年份和月份,按受雇日期所在月排序,若月份同样则将--最早年份的员工排在前面SELECT ename,YEAR(hiredate),MONTH(hiredate) FROM dbo.EMP ORDER BY MONTH(hiredate),YEAR(hiredate) --21 显示在一个月为30天的情况,全部员工的日薪金,忽略余数SELECT ROUND((sal+ISNULL(comm,0))/30,0) FROM dbo.EMP--22 找出在(不论什么年份的)2月受雇的全部员工SELECT * FROM dbo.EMP WHERE MONTH(hiredate)=2--23 对于每一个员工,显示其增加公司的天数SELECT ename ,DATEDIFF(DAY,hiredate,GETDATE())AS 入职天数 FROM dbo.EMP--24 显示姓名字段的不论什么位置包括"A"的全部员工的姓名SELECT ename FROM dbo.EMP WHERE ename LIKE '%A%'--25 以年月日的方式显示全部员工的服务年限(大概)SELECT STR(ROUND(DATEDIFF(day,hiredate,GETDATE())/365,0))+'年'+str(ROUND(DATEDIFF(day,hiredate,GETDATE())%365/30+1,0))+'月'+STR(DATEDIFF(day,hiredate,GETDATE())%365%30)+'日' AS 服务年限 FROM dbo.EMP