SQLServer基础查询练习附答案.docx

1、SQLServer基础查询练习附答案-创立数据库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

2、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 DATABA

3、SE 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_

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

5、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_COM

6、MITTED_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 NVARCH

7、AR(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,-12-17，800，NULL，20)；INSERT INTO EMP VALU

8、ES (7499，allen， SALESMAN， 7698,-2-20，1600， 300，30)；INSERT INTO EMP VALUES (7521，WARD， SALESMAN， 7698,-2-22，1250， 500，30)；INSERT INTO EMP VALUES (7566，JONES， MANAGER， 7839,-4-2， 2975，NULL，20)；INSERT INTO EMP VALUES (7654，MARTIN，SALESMAN， 7698,-9-28,1250，1400，30)；INSERT INTO EMP VALUES (7698，BLAKE， MA

9、NAGER， 7839,-5-1， 2850，NULL，30)；INSERT INTO EMP VALUES (7782，CLARK， MANAGER， 7839,-6-9，2450，NULL，10)；INSERT INTO EMP VALUES (7788，scott， ANALYST， 7566,-12-9,3000，NULL，20)；INSERT INTO EMP VALUES (7839，king， PRESIDENT，NULL,-11-17,5000，NULL，10)；INSERT INTO EMP VALUES (7844，TURNER，SALESMAN， 7698,-9-8，15

10、00， 0，30)；INSERT INTO EMP VALUES (7876，ADAMS， CLERK， 7788,-1-12,1100，NULL，20)；INSERT INTO EMP VALUES (7900，JAMES， CLERK， 7698,-3-12,950，NULL，30)；INSERT INTO EMP VALUES (7902，FORD， ANALYST， 7566,-3-12,3000，NULL，20)；INSERT INTO EMP VALUES (7934，MILLER，CLERK， 7782,-01-23,1300，NULL，10)；INSERT INTO DEPT

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

12、)总数不不大于所有雇员信息SELECT *FROM empWHERE ( sal + ISNULL(comm，0) ) -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 em

13、pWHERE 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中所有办事员，-既不是经理又不是办事员但其薪金=所有雇员详细资料SELECT * FROM dbo.emp WHERE ( JOB = MANAGER AND DEPTNO = 10 ) OR ( JOB = CLERK AND DEPTNO = 20 )

14、 OR ( JOB NOT IN ( MANAGER，CLERK ) AND SAL = ) 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 e

15、mpWHERE 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-

16、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天状况下所有雇

17、员日薪金,忽视小数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 DE

18、PTNO 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 s

19、al ( 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、显示所有雇员姓名以及满服务年限日期SELECT

20、 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个月累加SELE

21、CT ename , CAST(sal + ISNULL(comm，0) AS NUMERIC) * 12 sal_yearFROM dbo.empORDER BY sal_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 D

22、ESC ) AS grade FROM emp ) AS aWHERE a.grade = 4-37、列出年薪低于10000雇员SELECT *FROM dbo.empWHERE ( sal + ISNULL(comm，0) ) * 12 ( 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、列出入职日期早于其直接

23、上级所有雇员SELECT ename 雇员 , hiredate 雇员入职日期FROM emp eWHERE hiredate ( SELECT MAX(sal) FROM emp WHERE deptno = 30 ) -49、列出从事同一种工作但属于不同部门雇员不同组合SELECT a.ename 雇员A , b.ename 雇员B , a.job 雇员A工作 , b.job 雇员B工作 , a.deptno 雇员A部门 , b.deptno 雇员B部门FROM emp a JOIN emp b ON a.job = b.jobWHERE a.deptno != b.deptno AND a.job = b.jobORDER BY a.job-50、列出所有雇员雇员名称、部门名称和薪金（涉及没有雇员部门）SELECT dbo.dept.DNAME , dbo.emp.ENAME , dbo.emp.SALFROM dbo.emp RIGHT JOIN dbo.dept ON dbo.dept.DEPTNO = dbo.emp.DEPTNO