【正文】 GROUP BY dname , ename列出薪金高于公司平均水平的所有雇員SELECT ename , salFROM WHERE sal ( SELECT AVG(sal) FROM )3列出各種工作類別的最低薪金，并使最低薪金大于1500SELECT job , MIN(sal) minsalFROM GROUP BY job HAVING MIN(sal) 1500 3顯示所有雇員的姓名和加入公司的年份和月份，按雇員受雇日所在月排序，將最早年份的項(xiàng)目排在最前面SELECT ename , YEAR(hiredate) _year , MONTH(hiredate) _monthFROM empORDER BY hiredate3顯示所有雇員的姓名以及滿10年服務(wù)年限的日期SELECT ename , hiredateFROM empWHERE DATEDIFF(YEAR, hiredate, GETDATE()) 103顯示所有雇員的服務(wù)年限：總的年數(shù)或總的月數(shù)或總的天數(shù)SELECT ename , DATEDIFF(YEAR, hiredate, GETDATE()) _years , DATEDIFF(MONTH, hiredate, GETDATE()) _months , DATEDIFF(DAY, hiredate, GETDATE()) _daysFROM 3列出按計(jì)算的字段排序的所有雇員的年薪.即：按照年薪對(duì)雇員進(jìn)行排序,年薪指雇員每月的總收入總共12個(gè)月的累加SELECT ename , CAST(sal + ISNULL(m, 0) AS NUMERIC) * 12 sal_yearFROM ORDER BY sal_year 3列出年薪前5名的雇員SELECT TOP 5 ename , CAST(sal + ISNULL(m, 0) AS NUMERIC) * 12 sal_yearFROM ORDER BY sal_year DESC列出薪金水平處于第四位的雇員注意子查詢一定要起別名SELECT *FROM ( SELECT ename , sal , rank() OVER ( ORDER BY sal DESC ) AS grade FROM emp ) AS aWHERE = 43列出年薪低于10000的雇員SELECT *FROM WHERE ( sal + ISNULL(m, 0) ) * 12 10000 3列出雇員的平均月薪和平均年薪SELECT CAST(SUM(sal + ISNULL(m, 0)) / COUNT(ename) AS NUMERIC) 平均月薪 , CAST(SUM(( sal + ISNULL(m, 0) ) * 12) / COUNT(ename) AS NUMERIC) 平均年薪FROM emp3列出部門(mén)名稱和這些部門(mén)的雇員,同時(shí)列出那些沒(méi)有雇員的部門(mén)（重要）SELECT dname , enameFROM dept LEFT JOIN emp ON = ORDER BY dname列出那些沒(méi)有雇員的部門(mén) SELECT dnameFROM emp RIGHT JOIN dept ON = WHERE IS NULL列出每個(gè)部門(mén)的信息以及該部門(mén)中雇員的數(shù)量（重要）SELECT dept.*, COUNT() 人數(shù)FROM dept LEFT JOIN ON = GROUP BY , ORDER BY 4列出薪金比SMITH多的所有雇員SELECT , FROM empWHERE sal ( SELECT sal FROM emp WHERE ename = 39。1顯示不帶有39。MANAGER39。, 39。200131239。