基础查询练习附标准答案

1、-创建数据库USE masterGOCREATE DATABASE test ON PRIMARY ( NAME = N'test', FILENAME = N'D:SQL DataBasetest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'test_log', FILENAME = N'D:SQL DataBasetest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048

2、GB , FILEGROWTH = 10%)GOALTER DATABASE test SET COMPATIBILITY_LEVEL = 90GOIF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')beginEXEC test.dbo.sp_fulltext_database action = 'enable'endGOALTER DATABASE test SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE test SET ANSI_NULLS OFF GOALTER

3、 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_STATIS

4、TICS 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_TRIG

5、GERS 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 PARAMETE

6、RIZATION 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 TABL

7、E 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, 

8、9;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 IN

9、TO 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',

10、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',

11、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'

12、;, '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

13、(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

14、 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.de

15、ptno = 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 )ORD

16、ER 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

17、 DISTINCT JOB FROM dbo.emp WHERE COMM IS NOT NULL-12、找出不收取佣金或收取的佣金低于100的雇员SELECT *FROM empWHERE ISNULL(comm, 0) < 100-13、找出早于12年之前受雇的雇员SELECT *FROM dbo.empWHERE YEAR(GETDATE() - YEAR(HIREDATE) > 11-14、显示首字母大写的所有雇员的姓名SELECT enameFROM empWHERE ASCII(ename) BETWEEN 65 AND 90-15、显示正好为5个字符的雇员姓名SELE

18、CT 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

19、的位置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 E

20、NAMEFROM 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

21、) = 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 , MI

22、N(sal) minsalFROM dbo.emp , dbo.deptWHERE emp.deptno = dept.deptno AND job = 'MANAGER'GROUP 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 HAV

23、ING 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

24、 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

25、_year -36、列出年薪前名的雇员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、列出年薪低于10000的雇员SELECT *FROM dbo.e

26、mpWHERE ( sal + ISNULL(comm, 0) ) * 12 < 10000 -38、列出雇员的平均月薪和平均年薪SELECT CAST(SUM(sal + ISNULL(comm, 0) / COUNT(ename) AS NUMERIC) 平均月薪 , CAST(SUM( sal + ISNULL(comm, 0) ) * 12) / COUNT(ename) AS NUMERIC) 平均年薪FROM emp-39、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门（重要）SELECT dname , enameFROM dept LEFT JOIN emp O

27、N emp.deptno = dept.deptnoORDER BY dname-列出那些没有雇员的部门 SELECT dnameFROM emp RIGHT JOIN dept ON emp.deptno = dept.deptnoWHERE emp.ename IS NULL-40、列出每个部门的信息以及该部门中雇员的数量（重要）SELECT dept.*, COUNT(emp.ENAME) 人数FROM dept LEFT JOIN dbo.emp ON dbo.dept.DEPTNO = dbo.emp.DEPTNOGROUP BY dept.DNAME,dept.DEPTNO,dep

28、t.LOC ORDER BY dbo.dept.DEPTNO-41、列出薪金比"SMITH"多的所有雇员SELECT emp.ename , emp.salFROM empWHERE sal > ( SELECT sal FROM emp WHERE ename = 'smith' ) -42、列出所有雇员的姓名及其直接上级的姓名SELECT A.ENAME 雇员 , B.ENAME 直接上级FROM dbo.emp A LEFT JOIN dbo.emp B ON A.MGR = B.EMPNOORDER BY B.EMPNO-43、列出入职日期早于其直接上级的所有雇员SELECT ename 雇员 , hiredate 雇员入职日期FROM emp eWHERE hiredate < ( SELECT hiredate FROM emp WHERE empno = e.mgr )-44、列出所有办事员（"CLERK"）的姓名及其部门名称SELECT ename , dname , jobFROM emp e JOIN dep