SQLServer基础查询练习(附标准答案)

--创建数据库

USE[master]

GO

CREATE DATABASE[test]ON PRIMARY

(NAME=N'test',FILENAME=N'D:\SQL \DataBase\test.mdf',SIZE=3072KB, MAXSIZE=UNLIMITED,FILEGROWTH= 1024KB)

LOG ON

(NAME=N'test_log',FILENAME=N'D:\SQL \DataBase\test_log.ldf',SIZE = 1024KB,MAXSIZE= 2048GB,FILEGROWTH= 10%)

GO

ALTER DATABASE[test]SET COMPATIBILITY_LEVEL= 90

GO

IF (1 =FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC[test].[dbo].[sp_fulltext_database]@action='enable'

end

GO

ALTER DATABASE[test]SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE[test]SET ANSI_NULLS OFF

GO

ALTER DATABASE[test]SET ANSI_PADDING OFF

GO

ALTER DATABASE[test]SET ANSI_WARNINGS OFF

GO

ALTER DATABASE[test]SET ARITHABORT OFF

GO

ALTER DATABASE[test]SET AUTO_CLOSE OFF

GO

ALTER DATABASE[test]SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE[test]SET AUTO_SHRINK OFF

GO

ALTER DATABASE[test]SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE[test]SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE[test]SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE[test]SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE[test]SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE[test]SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE[test]SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE[test]SET DISABLE_BROKER

GO

ALTER DATABASE[test]SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO

ALTER DATABASE[test]SET DATE_CORRELATION_OPTIMIZATION OFF GO

ALTER DATABASE[test]SET TRUSTWORTHY OFF

GO

ALTER DATABASE[test]SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE[test]SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE[test]SET READ_COMMITTED_SNAPSHOT OFF

GO

ALTER DATABASE[test]SET READ_WRITE

ALTER DATABASE[test]SET RECOVERY SIMPLE

GO

ALTER DATABASE[test]SET MULTI_USER

GO

ALTER DATABASE[test]SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE[test]SET DB_CHAINING OFF

GO

--创建表

USE test

CREATE 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.emp

WHERE COMM IS NULL

--4、查询薪金(SAL)和佣金(COMM)总数大于2000的所有雇员信息

SELECT*

FROM emp

WHERE (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.deptno WHERE emp.job='CLERK'

--7、找出佣金高于薪金的雇员

SELECT*

FROM emp

WHERE comm>sal

--8、找出佣金高于薪金60%的雇员

SELECT*

FROM emp

WHERE comm>sal* 0.6

--9、找出部门10中所有经理和部门20中的所有办事员的详细资料SELECT*

FROM emp

WHERE (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 emp

WHERE ISNULL(comm, 0)< 100

--13、找出早于12年之前受雇的雇员

SELECT*

FROM dbo.emp

WHERE YEAR(GETDATE())-YEAR(HIREDATE)> 11

--14、显示首字母大写的所有雇员的姓名

SELECT ename

FROM emp

WHERE ASCII(ename)BETWEEN 65 AND 90

--15、显示正好为5个字符的雇员姓名

SELECT ename

FROM emp

WHERE LEN(ename)= 5

--16、显示带有'R'的雇员姓名

SELECT ENAME

FROM dbo.emp

WHERE ENAME LIKE'%R%''

--17、显示不带有'R'的雇员姓名

SELECT ENAME

FROM dbo.emp

WHERE ENAME NOT LIKE'%R%'

--18、显示包含"A"的所有雇员的姓名及"A"在姓名字段中的位置SELECT ENAME,

CHARINDEX('A',ENAME)A的位置

FROM dbo.emp

WHERE ENAME LIKE'%A%'

--19、显示所有雇员的姓名，用a替换所有'A'

SELECT REPLACE(ename,'A','a')ENAME

FROM dbo.emp

--20、显示所有雇员的姓名的前三个字符

SELECT SUBSTRING(ename, 1, 3)ENAME

FROM dbo.emp

--21、显示雇员的详细资料，按姓名排序

SELECT*

FROM dbo.emp

ORDER BY ENAME

--22、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面

SELECT ENAME

FROM dbo.emp

ORDER BY HIREDATE

--23、显示所有雇员的姓名、工作和薪金，按工作内的工作的降序顺序排序，--而工作按薪金排序

SELECT ename,

job,

sal

FROM emp

ORDER BY job DESC,

Sal

--24、显示在一个月为30天的情况下所有雇员的日薪金,忽略小数

SELECT ename名字,

CAST(sal/ 30 AS NUMERIC)日薪

FROM dbo.emp

--25、找出在（任何年份的）2月受聘的所有雇员

SELECT ename,

hiredate

FROM dbo.emp

WHERE MONTH(hiredate)= 2

--26、对于每个雇员，显示其加入公司的天数

SELECT ename姓名,

DATEDIFF(DAY,hiredate,GETDATE())天数

FROM dbo.emp

--27、列出至少有一个雇员的所有部门

SELECT*

FROM dbo.dept

WHERE DEPTNO IN(SELECT DEPTNO

FROM dbo.emp)

--28、列出各种类别工作的最低薪金

SELECT job,

MIN(sal)minsal

FROM dbo.emp

GROUP BY job

--29、列出各个部门的MANAGER（经理）的最低薪金

SELECT ename,

dname,

MIN(sal)minsal

FROM dbo.emp,

dbo.dept

WHERE emp.deptno=dept.deptno

AND job='MANAGER'

GROUP BY dname,

ename

--30、列出薪金高于公司平均水平的所有雇员

SELECT ename,

sal

FROM dbo.emp

WHERE sal>(SELECT AVG(sal)

FROM dbo.emp

)

--31、列出各种工作类别的最低薪金，并使最低薪金大于1500 SELECT job,

MIN(sal)minsal

FROM dbo.emp

GROUP BY job

HAVING MIN(sal)> 1500

--32、显示所有雇员的姓名和加入公司的年份和月份，

--按雇员受雇日所在月排序，将最早年份的项目排在最前面SELECT ename,

YEAR(hiredate)_year,

MONTH(hiredate)_month

FROM emp

ORDER BY hiredate

--33、显示所有雇员的姓名以及满10年服务年限的日期

SELECT ename,

hiredate

FROM emp

WHERE DATEDIFF(YEAR,hiredate,GETDATE())> 10

--34、显示所有雇员的服务年限：总的年数或总的月数或总的天数

SELECT ename,

DATEDIFF(YEAR,hiredate,GETDATE())_years,

DATEDIFF(MONTH,hiredate,GETDATE())_months,

DATEDIFF(DAY,hiredate,GETDATE())_days

FROM dbo.emp

--35、列出按计算的字段排序的所有雇员的年薪.

--即：按照年薪对雇员进行排序,年薪指雇员每月的总收入总共12个月的累加SELECT ename,

CAST(sal+ISNULL(comm, 0)AS NUMERIC)* 12 sal_year FROM dbo.emp

ORDER BY sal_year

--36、列出年薪前名的雇员

SELECT TOP 5

ename,

CAST(sal+ISNULL(comm, 0)AS NUMERIC)* 12 sal_year FROM dbo.emp

ORDER BY sal_year DESC

--列出薪金水平处于第四位的雇员

--注意子查询一定要起别名

SELECT*

FROM (SELECT ename,

sal,

rank()OVER (ORDER BY sal DESC)AS grade FROM emp

)AS a

WHERE a.grade= 4

--37、列出年薪低于10000的雇员

SELECT*

FROM dbo.emp

WHERE (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,

ename

FROM dept

LEFT JOIN emp ON emp.deptno=dept.deptno

ORDER BY dname

--列出那些没有雇员的部门

SELECT dname

FROM emp

RIGHT JOIN dept ON emp.deptno=dept.deptno

WHERE emp.ename IS NULL

--40、列出每个部门的信息以及该部门中雇员的数量（重要）

SELECT dept.*,

COUNT(emp.ENAME)人数

FROM dept

LEFT JOIN dbo.emp ON dbo.dept.DEPTNO=dbo.emp.DEPTNO

GROUP BY dept.DNAME,dept.DEPTNO,dept.LOC ORDER BY dbo.dept.DEPTNO

--41、列出薪金比"SMITH"多的所有雇员

SELECT emp.ename,

emp.sal

FROM emp

WHERE 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.EMPNO

ORDER BY B.EMPNO

--43、列出入职日期早于其直接上级的所有雇员

SELECT ename雇员,

hiredate雇员入职日期

FROM emp e

WHERE hiredate<(SELECT hiredate

FROM emp

WHERE empno=e.mgr

)

--44、列出所有办事员（"CLERK"）的姓名及其部门名称

SELECT ename,

dname,

job

FROM emp e

JOIN dept d ON e.deptno=d.deptno

WHERE job='CLERK'

--45、列出从事"SALES"（销售）工作的雇员的姓名，假定不知道销售部的部门编号SELECT ename

FROM emp

WHERE deptno=(SELECT deptno

FROM dept

WHERE dname='sales'

)

--46、列出与"SCOTT"从事相同工作的所有雇员

SELECT ename,

job

FROM emp

WHERE job=(SELECT job

FROM emp

WHERE ename='SCOTT'

)

--47、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门中任何一个雇员的薪金SELECT ename,

sal,

deptno

FROM emp

WHERE sal IN(SELECT sal

FROM emp

WHERE deptno= 30 )

--48、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门中所有雇员的薪金SELECT ename,

sal,

deptno

FROM emp

WHERE sal>(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.job

WHERE a.deptno!=b.deptno

AND a.job=b.job

ORDER BY a.job

--50、列出所有雇员的雇员名称、部门名称和薪金（包括没有雇员的部门）SELECT dbo.dept.DNAME,

dbo.emp.ENAME,

dbo.emp.SAL

FROM dbo.emp

RIGHT JOIN dbo.dept ON dbo.dept.DEPTNO=dbo.emp.DEPTNO