SQL Server基础查询练习(附答案).doc

-创建数据库USE masterGOCREATE DATABASE test ON PRIMARY ( NAME = Ntest, FILENAME = ND:SQL DataBasetest.mdf , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = Ntest_log, FILENAME = ND:SQL DataBasetest_log.ldf , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOALTER DATABASE test SET COMPATIBILITY_LEVEL = 90GOIF (1 = FULLTEXTSERVICEPROPERTY(IsFullTextInstalled)beginEXEC test.dbo.sp_fulltext_database action = enableendGOALTER DATABASE test SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE test SET ANSI_NULLS OFF GOALTER DATABASE test SET ANSI_PADDING OFF GOALTER DATABASE test SET ANSI_WARNINGS OFF GOALTER DATABASE test SET ARITHABORT OFF GOALTER DATABASE test SET AUTO_CLOSE OFF GOALTER DATABASE test SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE test SET AUTO_SHRINK OFF GOALTER DATABASE test SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE test SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE test SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE test SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE test SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE test SET QUOTED_IDENTIFIER OFF GOALTER DATABASE test SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE test SET DISABLE_BROKER GOALTER DATABASE test SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE test SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE test SET TRUSTWORTHY OFF GOALTER DATABASE test SET ALLOW_SNAPSHOT_ISOLATION OFF GOALTER DATABASE test SET PARAMETERIZATION SIMPLE GOALTER DATABASE test SET READ_COMMITTED_SNAPSHOT OFF GOALTER DATABASE test SET READ_WRITE GOALTER DATABASE test SET RECOVERY SIMPLE GOALTER DATABASE test SET MULTI_USER GOALTER DATABASE test SET PAGE_VERIFY CHECKSUM GOALTER DATABASE test SET DB_CHAINING OFF GO-创建表USE testCREATE TABLE emp ( EMPNO NUMERIC(5, 0) NOT NULL , ENAME NVARCHAR(10) , JOB NVARCHAR(9) , MGR NUMERIC(5, 0) , HIREDATE DATETIME , SAL NUMERIC(7, 2) , COMM NUMERIC(7, 2) , DEPTNO NUMERIC(2, 0), )CREATE TABLE dept ( DEPTNO NUMERIC(2) , DNAME NVARCHAR(14) , LOC NVARCHAR(13), )-插入数据INSERT INTO EMP VALUES (7369, SMITH, CLERK, 7902,2000-12-17, 800, NULL, 20); INSERT INTO EMP VALUES (7499, allen, SALESMAN, 7698,2001-2-20, 1600, 300, 30); INSERT INTO EMP VALUES (7521, WARD, SALESMAN, 7698,2001-2-22, 1250, 500, 30); INSERT INTO EMP VALUES (7566, JONES, MANAGER, 7839,2001-4-2, 2975, NULL, 20); INSERT INTO EMP VALUES (7654, MARTIN, SALESMAN, 7698,2001-9-28,1250, 1400, 30); INSERT INTO EMP VALUES (7698, BLAKE, MANAGER, 7839,2001-5-1, 2850, NULL, 30); INSERT INTO EMP VALUES (7782, CLARK, MANAGER, 7839,2001-6-9, 2450, NULL, 10); INSERT INTO EMP VALUES (7788, scott, ANALYST, 7566,2002-12-9,3000, NULL, 20); INSERT INTO EMP VALUES (7839, king, PRESIDENT, NULL,2001-11-17,5000, NULL, 10); INSERT INTO EMP VALUES (7844, TURNER, SALESMAN, 7698,2001-9-8, 1500, 0, 30); INSERT INTO EMP VALUES (7876, ADAMS, CLERK, 7788,2003-1-12,1100, NULL, 20); INSERT INTO EMP VALUES (7900, JAMES, CLERK, 7698,2001-3-12,950, NULL, 30); INSERT INTO EMP VALUES (7902, FORD, ANALYST, 7566,2001-3-12,3000, NULL, 20); INSERT INTO EMP VALUES (7934, MILLER, CLERK, 7782,2002-01-23,1300, NULL, 10); INSERT INTO DEPT VALUES (10, ACCOUNTING, NEW YORK); INSERT INTO DEPT VALUES (20, RESEARCH, DALLAS); INSERT INTO DEPT VALUES (30, SALES, CHICAGO); INSERT INTO DEPT VALUES (40, OPERATIONS, BOSTON); -1、查询所有的雇员SELECT *FROM emp-2、查询所有的部门SELECT *FROM dept -3、查询没有佣金（COMM）的所有雇员信息SELECT *FROM dbo.empWHERE COMM IS NULL-4、查询薪金(SAL)和佣金(COMM)总数大于2000的所有雇员信息SELECT *FROM empWHERE ( sal + ISNULL(comm, 0) ) 2000-5、选择部门30中的雇员SELECT * FROM emp WHERE deptno=30-6、列出所有办事员（CLERK）的姓名、编号和部门SELECT ename , empno , dname FROM emp JOIN dept ON emp.deptno = dept.deptnoWHERE emp.job = CLERK-7、找出佣金高于薪金的雇员SELECT *FROM empWHERE comm sal-8、找出佣金高于薪金60%的雇员SELECT *FROM empWHERE comm sal * 0.6-9、找出部门10中所有经理和部门20中的所有办事员的详细资料SELECT *FROM empWHERE ( job = MANAGER AND deptno = 10 ) OR ( job = CLERK AND deptno = 20 )ORDER BY job-10、找出部门10中所有经理、部门20中所有办事员，-既不是经理又不是办事员但其薪金=2000的所有雇员的详细资料SELECT * FROM dbo.emp WHERE ( JOB = MANAGER AND DEPTNO = 10 ) OR ( JOB = CLERK AND DEPTNO = 20 ) OR ( JOB NOT IN ( MANAGER, CLERK ) AND SAL = 2000 ) ORDER BY JOB-11、找出收取佣金的雇员的不同工作SELECT DISTINCT JOB FROM dbo.emp WHERE COMM IS NOT NULL-12、找出不收取佣金或收取的佣金低于100的雇员SELECT *FROM empWHERE ISNULL(comm, 0) 11-14、显示首字母大写的所有雇员的姓名SELECT enameFROM empWHERE ASCII(ename) BETWEEN 65 AND 90-15、显示正好为5个字符的雇员姓名SELECT enameFROM empWHERE LEN(ename) = 5-16、显示带有R的雇员姓名SELECT ENAMEFROM dbo.empWHERE ENAME LIKE %R%-17、显示不带有R的雇员姓名SELECT ENAMEFROM dbo.empWHERE ENAME NOT LIKE %R%-18、显示包含A的所有雇员的姓名及A在姓名字段中的位置SELECT ENAME , CHARINDEX(A, ENAME) A的位置FROM dbo.empWHERE ENAME LIKE %A%-19、显示所有雇员的姓名，用a替换所有A SELECT REPLACE(ename, A, a) ENAMEFROM dbo.emp-20、显示所有雇员的姓名的前三个字符SELECT SUBSTRING(ename, 1, 3) ENAMEFROM dbo.emp-21、显示雇员的详细资料，按姓名排序SELECT *FROM dbo.empORDER BY ENAME -22、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面SELECT ENAMEFROM dbo.empORDER BY HIREDATE-23、显示所有雇员的姓名、工作和薪金，按工作内的工作的降序顺序排序，-而工作按薪金排序SELECT ename , job , salFROM empORDER BY job DESC , Sal-24、显示在一个月为30天的情况下所有雇员的日薪金,忽略小数SELECT ename 名字 , CAST(sal / 30 AS NUMERIC) 日薪FROM dbo.emp -25、找出在（任何年份的）2月受聘的所有雇员SELECT ename , hiredateFROM dbo.empWHERE MONTH(hiredate) = 2 -26、对于每个雇员，显示其加入公司的天数SELECT ename 姓名 , DATEDIFF(DAY, hiredate, GETDATE() 天数FROM dbo.emp -27、列出至少有一个雇员的所有部门SELECT *FROM dbo.deptWHERE DEPTNO IN ( SELECT DEPTNO FROM dbo.emp )-28、列出各种类别工作的最低薪金SELECT job , MIN(sal) minsalFROM dbo.empGROUP BY job-29、列出各个部门的MANAGER（经理）的最低薪金SELECT ename , dname , MIN(sal) minsalFROM dbo.emp , dbo.deptWHERE emp.deptno = dept.deptno AND job = MANAGERGROUP BY dname , ename-30、列出薪金高于公司平均水平的所有雇员SELECT ename , salFROM dbo.empWHERE sal ( SELECT AVG(sal) FROM dbo.emp )-31、列出各种工作类别的最低薪金，并使最低薪金大于1500SELECT job , MIN(sal) minsalFROM dbo.empGROUP BY job HAVING MIN(sal) 1500 -32、显示所有雇员的姓名和加入公司的年份和月份，-按雇员受雇日所在月排序，将最早年份的项目排在最前面SELECT ename , YEAR(hiredate) _year , MONTH(hiredate) _monthFROM empORDER BY hiredate-33、显示所有雇员的姓名以及满10年服务年限的日期SELECT ename , hiredateFROM empWHERE DATEDIFF(YEAR, hiredate, GETDATE() 10-34、显示所有雇员的服务年限：总的年数或总的月数或总的天数SELECT ename , DATEDIFF(YEAR, hiredate, GETDATE() _years , DATEDIFF(MONTH, hiredate, GETDATE() _months , DATEDIFF(DAY, hiredate, GETDATE() _daysFROM dbo.emp -35、列出按计算的字段排序的所有雇员的年薪.-即：按照年薪对雇员进行排序,年薪指雇员每月的总收入总共12个月的累加SELECT ename , CAST(sal + ISNULL(comm, 0) AS NUMERIC) * 12 sal_yearFROM dbo.empORDER BY sal_year -36、列出年薪前5名的雇员SELECT TOP 5 ename , CAST(sal + ISNULL(comm, 0) AS NUMERIC) * 12 sal_yearFROM dbo.empORDER BY sal_year DESC-列出薪金水平处于第四位的雇员-注意子查询一定要起别名SELECT *FROM ( SELECT ename , sal , rank() OVER ( ORDER BY sal DESC ) AS grade FROM emp ) AS aWHERE a.grade = 4-37、列出年薪低于