SQLServer基础查询练习附答案.docx
《SQLServer基础查询练习附答案.docx》由会员分享，可在线阅读，更多相关《SQLServer基础查询练习附答案.docx（19页珍藏版）》请在冰点文库上搜索。
SQLServer基础查询练习附答案
创建数据库
USE[master]
GO
CREATEDATABASE[test]ONPRIMARY
（NAME=N'test',FILENAME=N'D:
\SQL\DataBase\test.mdf',SIZE=3072KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB）
LOGON
SIZE=
（NAME=N'test_log',FILENAME=N'D:
\SQL\DataBase\test_log.ldf'
1024KB,MAXSIZE=2048GB,FILEGROWTH=10%）
GO
ALTERDATABASE[test]SETCOMPATIBILITY_LEVEL=90
GO
IF（1=FULLTEXTSERVICEPROPERTY（'IsFullTextInstalled'））begin
EXEC[test].[dbo].[sp_fulltext_database]@action='enable'end
GO
ALTERDATABASE[test]
GO
SETANSI_NULL_DEFAULTOFF
ALTERDATABASE[test]
GO
SETANSI_NULLSOFF
ALTERDATABASE[test]
GO
SETANSI_PADDINGOFF
ALTERDATABASE[test]
GO
SETANSI_WARNINGSOFF
ALTERDATABASE[test]
GO
SETARITHABORTOFF
ALTERDATABASE[test]
GO
SETAUTO_CLOSEOFF
ALTERDATABASE[test]
GO
SETAUTO_CREATE_STATISTICSON
ALTERDATABASE[test]
SETAUTO_SHRINKOFF
GO
ALTERDATABASE
[test]
SETAUTO_UPDATE_STATISTICSON
GO
ALTERDATABASE
[test]
SETCURSOR_CLOSE_ON_COMMITOFF
GO
ALTERDATABASE
[test]
SETCURSOR_DEFAULTGLOBAL
GO
ALTERDATABASE
[test]
SETCONCAT_NULL_YIELDS_NULLOFF
GO
ALTERDATABASE
[test]
SETNUMERIC_ROUNDABORTOFF
GO
ALTERDATABASE
[test]
SETQUOTED_IDENTIFIEROFF
GO
ALTERDATABASE
[test]
SETRECURSIVE_TRIGGERSOFF
GO
ALTERDATABASE
[test]
SETDISABLE_BROKER
GO
ALTERDATABASE
[test]
SETAUTO_UPDATE_STATISTICS_ASYNCOFF
GO
ALTERDATABASE
[test]
SETDATE_CORRELATION_OPTIMIZATIONOFF
GO
ALTERDATABASE
[test]
SETTRUSTWORTHYOFF
GO
ALTERDATABASE
[test]
SETALLOW_SNAPSHOT_ISOLATIONOFF
GO
ALTERDATABASE
[test]
SETPARAMETERIZATIONSIMPLE
GO
ALTERDATABASE
[test]
SETREAD_COMMITTED_SNAPSHOTOFF
GO
ALTERDATABASE
[test]
SETREAD_WRITE
GO
ALTERDATABASE[test]
GO
SETRECOVERYSIMPLE
ALTERDATABASE[test]
GO
SETMULTI_USER
ALTERDATABASE[test]
GO
SETPAGE_VERIFYCHECKSUM
ALTERDATABASE[test]
SETDB_CHAININGOFF
GO
--创建表
USEtest
CREATETABLEemp
（
EMPNONUMERIC（5,0）NOTNULL,
ENAMENVARCHAR（10）,JOBNVARCHAR（9）,MGRNUMERIC（5,0）,HIREDATEDATETIME,SALNUMERIC（7,2）,COMMNUMERIC（7,2）,DEPTNONUMERIC（2,0）,）
CREATETABLEdept
（
DEPTNONUMERIC
（2）,DNAMENVARCHAR（14）,LOCNVARCHAR（13）,）
--插入数据
INSERTINTOEMPVALUES
（7369,'SMITH','CLERK',
INSERTINTOEMPVALUES
7902
'2000-12-17'
800,NULL,
20）。
（7499,'allen','SALESMAN'
7698
'2001-2-20'
1600
300
30）。
INSERTINTOEMPVALUES
（7521,'WARD','SALESMAN',
7698
'2001-2-22'
1250,
500,
30
）。
INSERTINTOEMPVALUES
（7566,'JONES','MANAGER',
7839
'2001-4-2'
2975
NULL,
20
）。
INSERTINTOEMPVALUES
（7654,'MARTIN','SALESMAN'INSERTINTOEMPVALUES
7698
'2001-9-28'
1250,1400,
30）
（7698,'BLAKE','MANAGER',
7839
'2001-5-1'
2850,NULL,
30）。
INSERTINTOEMPVALUES
（7782,'CLARK','MANAGER',
7839
'2001-6-9'
2450,NULL,10）。
INSERTINTOEMPVALUES
（7788,'scott','ANALYST',
7566
'2002-12-9'
3000,NULL,20）。
INSERTINTOEMPVALUES
（7839,'king','PRESIDENT'
NULL,
2001-11-17'
5000,NULL,10
）。
INSERTINTOEMPVALUES
（7844,'TURNER','SALESMAN'
7698
'2001-9-8'
1500,0,
30）
INSERTINTOEMPVALUES
（7876,'ADAMS','CLERK',
7788
'2003-1-12'
1100,NULL,20）。
INSERTINTOEMPVALUES
（7900,'JAMES','CLERK',
7698
'2001-3-12'
950,NULL,30
）。
INSERTINTOEMPVALUES
（7902,'FORD','ANALYST',
7566
'2001-3-12'
3000,NULL,20
）。
INSERTINTOEMPVALUES
（7934,'MILLER','CLERK',
7782
'2002-01-23'
1300,NULL,
10）
--1、查询所有的雇员
SELECT*
FROMemp
--2、查询所有的部门
SELECT*
FROMdept
--3、查询没有佣金（COMM的所有雇员信息
SELECT*
FROMdbo.emp
WHERECOMMISNULL
--4、查询薪金（SAL）和佣金（COMM）总数大于2000的所有雇员信息
SELECT*
FROMemp
WHERE（sal+ISNULL（comm,0））>2000
--5、选择部门30中的雇员
SELECT*
FROMemp
WHEREdeptno=30
--6、列出所有办事员（"CLERK"）的姓名、编号和部门
SELECTename,
empno,
dname
FROMemp
JOINdeptONemp.deptno=dept.deptno
WHEREemp.job='CLERK'
--7、找出佣金高于薪金的雇员
SELECT*
FROMemp
WHEREcomm>sal
--8、找出佣金高于薪金60%的雇员
SELECT*
FROMemp
WHEREcomm>sal*0.6
--9、找出部门10中所有经理和部门20中的所有办事员的详细资料SELECT*
FROMemp
WHERE（job='MANAGER'
ANDdeptno=10
）
OR（job='CLERK'
ANDdeptno=20
）
ORDERBYjob
--10、找出部门10中所有经理、部门20中所有办事员，
--既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料SELECT*
FROMdbo.emp
WHERE（JOB='MANAGER'
ANDDEPTNO=10
）
OR（JOB='CLERK'
ANDDEPTNO=20
）
OR（JOBNOTIN（'MANAGER','CLERK'）
ANDSAL>=2000
）
ORDERBYJOB
--11、找出收取佣金的雇员的不同工作
SELECTDISTINCT
JOB
FROMdbo.emp
WHERECOMMISNOTNULL
--12、找出不收取佣金或收取的佣金低于100的雇员
SELECT*
FROMemp
WHEREISNULL（comm,0）<100
--13、找出早于12年之前受雇的雇员
SELECT*
FROMdbo.emp
WHEREYEAR（GETDATE（））-YEAR（HIREDATE）>11
--14、显示首字母大写的所有雇员的姓名
SELECTename
FROMemp
WHEREASCII（ename）BETWEEN65AND90
--15、显示正好为5个字符的雇员姓名
SELECTename
FROMemp
WHERELEN（ename）=5
--16、显示带有'R'的雇员姓名
SELECTENAME
FROMdbo.emp
WHEREENAMELIKE'%R%''
--17、显示不带有'R'的雇员姓名
SELECTENAME
FROMdbo.emp
WHEREENAMENOTLIKE'%R%'
--18、显示包含"A"的所有雇员的姓名及"A"在姓名字段中的位置
SELECTENAME,
CHARINDEX（'A',ENAME）A的位置
FROMdbo.emp
WHEREENAMELIKE'%A%'--19、显示所有雇员的姓名，用a替换所有'A'SELECTREPLACE（ename,'A','a'）ENAMEFROMdbo.emp
--20、显示所有雇员的姓名的前三个字符
SELECTSUBSTRING（ename,1,3）ENAMEFROMdbo.emp
--21、显示雇员的详细资料，按姓名排序
SELECT*FROMdbo.emp
ORDERBYENAME
--22、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面SELECTENAME
FROMdbo.empORDERBYHIREDATE
--23、显示所有雇员的姓名、工作和薪金，按工作内的工作的降序顺序排序，--而工作按薪金排序
SELECTename,
job,
sal
FROMemp
ORDERBYjobDESC,
Sal
--24、显示在一个月为30天的情况下所有雇员的日薪金,忽略小数SELECTename名字,
CAST（sal/30ASNUMERIC）日薪FROMdbo.emp
--25、找出在（任何年份的）2月受聘的所有雇员SELECTename,
hiredateFROMdbo.emp
WHEREMONTH（hiredate）=2
--26、对于每个雇员，显示其加入公司的天数
SELECTename姓名,DATEDIFF（DAY,hiredate,GETDATE（））天数FROMdbo.emp
--27、列出至少有一个雇员的所有部门
SELECT*
FROMdbo.dept
WHEREDEPTNOIN（SELECTDEPTNO
FROMdbo.emp）
--28、列出各种类别工作的最低薪金
SELECTjob,MIN（sal）minsalFROMdbo.emp
GROUPBYjob
--29、列岀各个部门的MANAGER（经理）的最低薪金
SELECTename,dname,
MIN（sal）minsal
FROMdbo.emp,dbo.dept
WHEREemp.deptno=dept.deptno
ANDjob='MANAGER'
GROUPBYdname,
ename
--30、列岀薪金高于公司平均水平的所有雇员SELECTename,
sal
FROMdbo.emp
WHEREsal>（SELECTAVG（sal）
FROMdbo.emp
）
1500
--31、列岀各种工作类别的最低薪金，并使最低薪金大于
SELECTjob,MIN（sal）minsalFROMdbo.emp
GROUPBYjob
HAVINGMIN（sal）>1500
--32、显示所有雇员的姓名和加入公司的年份和月份，
--按雇员受雇日所在月排序，将最早年份的工程排在最前面SELECTename,
YEAR（hiredate）_year,
MONTH（hiredate）_month
FROMemp
ORDERBYhiredate--33、显示所有雇员的姓名以及满10年服务年限的日期SELECTename,hiredate
FROMemp
WHEREDATEDIFF（YEAR,hiredate,GETDATE（））>10--34、显示所有雇员的服务年限：
总的年数或总的月数或总的天数SELECTename,
FROMdbo.emp--35、列出按计算的字段排序的所有雇员的年薪.
12个月的累加
--即：
按照年薪对雇员进行排序,年薪指雇员每月的总收入总共SELECTename,
CAST（sal+ISNULL（comm,0）ASNUMERIC）*12sal_yearFROMdbo.emp
ORDERBYsal_year
--36、列出年薪前名的雇员
SELECTTOP5ename,
CAST（sal+ISNULL（comm,0）ASNUMERIC）*12sal_yearFROMdbo.emp
ORDERBYsal_yearDESC
--列出薪金水平处于第四位的雇员
--注意子查询一定要起别名
SELECT*
FROM（SELECTename,
sal,
rank（）OVER（ORDERBYsalDESC）ASgradeFROMemp
）ASa
WHEREa.grade=4
--37、列出年薪低于10000的雇员
SELECT*FROMdbo.emp
WHERE（sal+ISNULL（comm,0））*12<10000
--38、列出雇员的平均月薪和平均年薪
SELECTCAST（SUM（sal+ISNULL（comm,0））/COUNT（ename）ASNUMERIC）平均月薪CAST（SUM（（sal+ISNULL（comm,0））*12）/COUNT（ename）ASNUMERIC）平均年薪FROMemp
--39、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门（重要）
SELECTdname,ename
FROMdept
LEFTJOINempONemp.deptno=dept.deptno
ORDERBYdname
--列出那些没有雇员的部门
SELECTdnameFROMemp
RIGHTJOINdeptONemp.deptno=dept.deptno
WHEREemp.enameISNULL
--40、列出每个部门的信息以及该部门中雇员的数量（重要）
SELECTdept.*,
COUNT（emp.ENAME）人数
FROMdept
LEFTJOINdbo.empONdbo.dept.DEPTNO=dbo.emp.DEPTNO
GROUPBYdept.DNAME,dept.DEPTNO,dept.LOCORDERBYdbo.dept.DEPTNO
--41、列出薪金比"SMITH"多的所有雇员
SELECTemp.ename,
emp.sal
FROMemp
WHEREsal>（SELECTsal
FROMemp
WHEREename='smith'
）
--42、列出所有雇员的姓名及其直接上级的姓名
SELECTA.ENAME雇员,
B.ENAME直接上级
FROMdbo.empA
LEFTJOINdbo.empBONA.MGR=B.EMPNO
ORDERBYB.EMPNO
--43、列出入职日期早于其直接上级的所有雇员
SELECTename雇员,
hiredate雇员入职日期
FROMempe
WHEREhiredate<（SELECThiredate
FROMemp
WHEREempno=e.mgr
）
--44、列出所有办事员（"CLERK"）的姓名及其部门名称
SELECTename,
dname,
job
FROMempe
JOINdeptdONe.deptno=d.deptno
WHEREjob='CLERK'
--45、列出从事"SALES"（销售）工作的雇员的姓名，假定不知道销售部的部门编号
SELECTename
FROMemp
WHEREdeptno=（SELECTdeptno
FROMdept
WHEREdname='sales'
）
--46、列出与"SCOTT"从事相同工作的所有雇员
SELECTename,
job
FROMemp
WHEREjob=（SELECTjob
FROMemp
WHEREename='SCOTT'
）
--47、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门中任何一个雇员的薪金
SELECTename,
sal,
deptno
FROMemp
WHEREsalIN（SELECTsal
FROMemp
WHEREdeptno=30）
--48、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门中所有雇员的薪金
SELECTename,
sal,
deptno
FROMemp
WHEREsal>（SELECTMAX（sal）
FROMemp
WHEREdeptno=30
--49、列出从事同一种工作但属于不同部门的雇员的不同组合SELECTa.ename雇员A,
b.ename雇员B,
a.job雇员A工作，
b.job雇员B工作，
a.deptno雇员A部门，
b.deptno雇员B部门
FROMempa
JOINempbONa.job=b.jobWHEREa.deptno!
=b.deptno
ANDa.job=b.job
ORDERBYa.job
--50、列出所有雇员的雇员名称、部门名称和薪金（包括没有雇员的部门）SELECTdbo.dept.DNAME,
dbo.emp.ENAME,
dbo.emp.SAL
FROMdbo.emp
RIGHTJOINdbo.deptONdbo.dept.DEPTNO=dbo.emp.DEPTNO
升级会员 