sql-server基礎(chǔ)查詢練習(xí)(附答案)(更新版)
【正文】 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列出部門名稱和這些部門的雇員,同時(shí)列出那些沒有雇員的部門（重要）SELECT dname , enameFROM dept LEFT JOIN emp ON = ORDER BY dname列出那些沒有雇員的部門 SELECT dnameFROM emp RIGHT JOIN dept ON = WHERE IS NULL列出每個(gè)部門的信息以及該部門中雇員的數(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。 ) 4列出某些雇員的姓名和薪金，條件是他們的薪金等于部門30中任何一個(gè)雇員的薪金SELECT ename , sal , deptnoFROM empWHERE sal IN ( SELECT sal FROM emp WHERE deptno = 30 )4列出某些雇員的姓名和薪金，條件是他們的薪金高于部門30中所有雇員的薪金SELECT ename , sal , deptnoFROM empWHERE sal ( SELECT MAX(sal) FROM emp WHERE deptno = 30 ) 4列出從事同一種工作但屬于不同部門的雇員的不同組合SELECT 雇員A , 雇員B , 雇員A工作 , 雇員B工作 , 雇員A部門 , 雇員B部門FROM emp a JOIN emp b ON = WHERE != AND = ORDER BY 50、列出所有雇員的雇員名稱、部門名稱和薪金（包括沒有雇員的部門）SELECT , , FROM RIGHT JOIN ON =
范文總結(jié)相關(guān)推薦
鄂ICP備17016276號(hào)-1