2023年SQLServer基础查询练习附答案.doc

--创建数据库

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
GO

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,'2023-12-17', 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'allen', 'SALESMAN', 7698,'2023-2-20', 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,'2023-2-22', 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,'2023-4-2', 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,'2023-9-28',1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,'2023-5-1', 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,'2023-6-9', 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'scott', 'ANALYST', 7566,'2023-12-9',3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'king', 'PRESIDENT', NULL,'2023-11-17',5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,'2023-9-8', 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,'2023-1-12',1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,'2023-3-12',950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,'2023-3-12',3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,'2023-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)总数大于2023的所有雇员信息
SELECT *
FROM emp
WHERE ( sal + ISNULL(comm, 0) ) > 2023

--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中所有办事员，
--既不是经理又不是办事员但其薪金>=2023的所有雇员的具体资料
SELECT *
FROM dbo.emp
WHERE ( JOB = 'MANAGER'
AND DEPTNO = 10
)
OR ( JOB = 'CLERK'
AND DEPTNO = 20
)
OR ( JOB NOT IN ( 'MANAGER', 'CLERK' )
AND SAL >= 2023
)
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、找出早于2023之前受雇的雇员
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、显示所有雇员的姓名以及满2023服务年限的日期
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