SQLServer基础查询练习附答案.docx
《SQLServer基础查询练习附答案.docx》由会员分享，可在线阅读，更多相关《SQLServer基础查询练习附答案.docx（18页珍藏版）》请在冰豆网上搜索。
SQLServer基础查询练习附答案
--创立数据库
USE[master]
GO
CREATEDATABASE[test]ONPRIMARY
（NAME=N'test'，FILENAME=N'D:
\SQL\DataBase\test.mdf'，SIZE=3072KB，MAXSIZE=UNLIMITED，FILEGROWTH=1024KB）
LOGON
（NAME=N'test_log'，FILENAME=N'D:
\SQL\DataBase\test_log.ldf'，SIZE=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]SETANSI_NULL_DEFAULTOFF
GO
ALTERDATABASE[test]SETANSI_NULLSOFF
GO
ALTERDATABASE[test]SETANSI_PADDINGOFF
GO
ALTERDATABASE[test]SETANSI_WARNINGSOFF
GO
ALTERDATABASE[test]SETARITHABORTOFF
GO
ALTERDATABASE[test]SETAUTO_CLOSEOFF
GO
ALTERDATABASE[test]SETAUTO_CREATE_STATISTICSON
GO
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]SETRECOVERYSIMPLE
GO
ALTERDATABASE[test]SETMULTI_USER
GO
ALTERDATABASE[test]SETPAGE_VERIFYCHECKSUM
GO
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'，7902,'-12-17'，800，NULL，20）；
INSERTINTOEMPVALUES
（7499，'allen'，'SALESMAN'，7698,'-2-20'，1600，300，30）；
INSERTINTOEMPVALUES
（7521，'WARD'，'SALESMAN'，7698,'-2-22'，1250，500，30）；
INSERTINTOEMPVALUES
（7566，'JONES'，'MANAGER'，7839,'-4-2'，2975，NULL，20）；
INSERTINTOEMPVALUES
（7654，'MARTIN'，'SALESMAN'，7698,'-9-28',1250，1400，30）；
INSERTINTOEMPVALUES
（7698，'BLAKE'，'MANAGER'，7839,'-5-1'，2850，NULL，30）；
INSERTINTOEMPVALUES
（7782，'CLARK'，'MANAGER'，7839,'-6-9'，2450，NULL，10）；
INSERTINTOEMPVALUES
（7788，'scott'，'ANALYST'，7566,'-12-9',3000，NULL，20）；
INSERTINTOEMPVALUES
（7839，'king'，'PRESIDENT'，NULL,'-11-17',5000，NULL，10）；
INSERTINTOEMPVALUES
（7844，'TURNER'，'SALESMAN'，7698,'-9-8'，1500，0，30）；
INSERTINTOEMPVALUES
（7876，'ADAMS'，'CLERK'，7788,'-1-12',1100，NULL，20）；
INSERTINTOEMPVALUES
（7900，'JAMES'，'CLERK'，7698,'-3-12',950，NULL，30）；
INSERTINTOEMPVALUES
（7902，'FORD'，'ANALYST'，7566,'-3-12',3000，NULL，20）；
INSERTINTOEMPVALUES
（7934，'MILLER'，'CLERK'，7782,'-01-23',1300，NULL，10）；
INSERTINTODEPTVALUES（10，'ACCOUNTING'，'NEWYORK'）；
INSERTINTODEPTVALUES（20，'RESEARCH'，'DALLAS'）；
INSERTINTODEPTVALUES（30，'SALES'，'CHICAGO'）；
INSERTINTODEPTVALUES（40，'OPERATIONS'，'BOSTON'）；
--1、查询所有雇员
SELECT*
FROMemp
--2、查询所有部门
SELECT*
FROMdept
--3、查询没有佣金（COMM）所有雇员信息
SELECT*
FROMdbo.emp
WHERECOMMISNULL
--4、查询薪金（SAL）和佣金（COMM）总数不不大于所有雇员信息
SELECT*
FROMemp
WHERE（sal+ISNULL（comm，0））>
--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中所有办事员，
--既不是经理又不是办事员但其薪金>=所有雇员详细资料
SELECT*
FROMdbo.emp
WHERE（JOB='MANAGER'
ANDDEPTNO=10
）
OR（JOB='CLERK'
ANDDEPTNO=20
）
OR（JOBNOTIN（'MANAGER'，'CLERK'）
ANDSAL>=
）
ORDERBYJOB
--11、找出收取佣金雇员不同工作
SELECTDISTINCT
JOB
FROMdbo.emp
WHERECOMMISNOTNULL
--12、找出不收取佣金或收取佣金低于100雇员
SELECT*
FROMemp
WHEREISNULL（comm，0）<100
--13、找出早于之前受雇雇员
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'）ENAME
FROMdbo.emp
--20、显示所有雇员姓名前三个字符
SELECTSUBSTRING（ename，1，3）ENAME
FROMdbo.emp
--21、显示雇员详细资料，按姓名排序
SELECT*
FROMdbo.emp
ORDERBYENAME
--22、显示雇员姓名，依照其服务年限，将最老雇员排在最前面
SELECTENAME
FROMdbo.emp
ORDERBYHIREDATE
--23、显示所有雇员姓名、工作和薪金，按工作内工作降序顺序排序，
--而工作按薪金排序
SELECTename,
job,
sal
FROMemp
ORDERBYjobDESC,
Sal
--24、显示在一种月为30天状况下所有雇员日薪金,忽视小数
SELECTename名字,
CAST（sal/30ASNUMERIC）日薪
FROMdbo.emp
--25、找出在（任何年份）2月受聘所有雇员
SELECTename,
hiredate
FROMdbo.emp
WHEREMONTH（hiredate）=2
--26、对于每个雇员，显示其加入公司天数
SELECTename姓名,
DATEDIFF（DAY，hiredate，GETDATE（））天数
FROMdbo.emp
--27、列出至少有一种雇员所有部门
SELECT*
FROMdbo.dept
WHEREDEPTNOIN（SELECTDEPTNO
FROMdbo.emp）
--28、列出各种类别工作最低薪金
SELECTjob,
MIN（sal）minsal
FROMdbo.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
）
--31、列出各种工作类别最低薪金，并使最低薪金不不大于1500
SELECTjob,
MIN（sal）minsal
FROMdbo.emp
GROUPBYjob
HAVINGMIN（sal）>1500
--32、显示所有雇员姓名和加入公司年份和月份，
--按雇员受雇日所在月排序，将最早年份项目排在最前面
SELECTename,
YEAR（hiredate）_year,
MONTH（hiredate）_month
FROMemp
ORDERBYhiredate
--33、显示所有雇员姓名以及满服务年限日期
SELECTename,
hiredate
FROMemp
WHEREDATEDIFF（YEAR，hiredate，GETDATE（））>10
--34、显示所有雇员服务年限：
总年数或总月数或总天数
SELECTename,
DATEDIFF（YEAR，hiredate，GETDATE（））_years,
DATEDIFF（MONTH，hiredate，GETDATE（））_months,
DATEDIFF（DAY，hiredate，GETDATE（））_days
FROMdbo.emp
--35、列出按计算字段排序所有雇员年薪.
--即：
按照年薪对雇员进行排序,年薪指雇员每月总收入总共12个月累加
SELECTename,
CAST（sal+ISNULL（comm，0）ASNUMERIC）*12sal_year
FROMdbo.emp
ORDERBYsal_year
--36、列出年薪前名雇员
SELECTTOP5
ename,
CAST（sal+ISNULL（comm，0）ASNUMERIC）*12sal_year
FROMdbo.emp
ORDERBYsal_yearDESC
--列出薪金水平处在第四位雇员
--注意子查询一定要起别名
SELECT*
FROM（SELECTename,
sal,
rank（）OVER（ORDERBYsalDESC）ASgrade
FROMemp
）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
--列出那些没有雇员部门
SELECTdname
FROMemp
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.job
WHEREa.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
升级会员 