【T-SQL基础】01.单表查询-几道sql查询题
概述:
本系列【T-SQL基础】主要是针对T-SQL基础的总结。
【T-SQL基础】06.透视、逆透视、分组集
【T-SQL基础】07.数据修改
【T-SQL基础】09.可编程对象
----------------------------------------------------------
【T-SQL进阶】01.好用的SQL TVP~~独家赠送[增-删-改-查]的例子
----------------------------------------------------------
【T-SQL性能调优】02.Transaction Log的使用和性能问题
【T-SQL性能调优】03.执行计划
【T-SQL性能调优】04.死锁分析
持续更新......欢迎关注我!
本篇主要总结了常见的对单表查询的SQL查询题目。
首先我们必须了解SQL查询的各字句在逻辑上按以下顺序进行处理:
1.FROM
2.WHERE
3.Group BY
4.HAVING
5.SELECT
6.ORDER BY
在做下面的题目之前,我们可以先把环境准备好,以下的SQL脚本可以帮助大家创建数据库,创建表,插入数据。
下载脚本文件:TSQLFundamentals2008.zip
题目:
1.返回2007年6月生成的订单:
(30 row(s) affected)
本题考察的是过滤日期范围
参考答案:
方案一:
如果要过滤日期范围(比如,整年或正月),比较自然的方法就是使用YEAR和MONTH之类的函数。
SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE YEAR(orderdate)= 2007 AND MONTH(orderdate)=6;
不过,应该小心的一点是:在大多数情况下,当对过滤条件中的列应用了一定的处理后,就不能以有效的方式来使用索引了。为了潜在地有效利用索引,就需要对为此进行调整,以便对过滤条件中的列不进行处理。
使用一个范围过滤条件:
orderdate >= '20070601'
AND orderdate < '20070701'
方案二:
SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate >= '20070601' AND orderdate < '20070701';
2.返回每个月的最后一天生成的订单
涉及的表:Sales.Orders表
本题主要考察DATEADD和DATEDIFF的用法
DATEADD:
将指定 number 时间间隔(有符号整数)与指定 date 的指定 datepart 相加后,返回该 date。
1.语法
DATEADD (datepart , number , date ) |
2.参数:
datepart
datepart |
缩写 |
year |
yy, yyyy |
quarter |
qq, q |
month |
mm, m |
dayofyear |
dy, y |
day |
dd, d |
week |
wk, ww |
weekday |
dw, w |
hour |
hh |
minute |
mi, n |
second |
ss, s |
millisecond |
ms |
microsecond |
mcs |
nanosecond |
ns |
number
是一个表达式,可以解析为与 date 的 datepart 相加的 int。用户定义的变量是有效的。
如果您指定一个带小数的值,则将小数截去且不进行舍入。
date
是一个表达式,可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值。date 可以是表达式、列表达式、用户定义的变量或字符串文字。如果表达式是字符串文字,则它必须解析为一个 datetime 值。为避免不确定性,请使用四位数年份。有关两位数年份的信息,请参阅 two digit year cutoff 选项。
3.返回值
datepart 参数
dayofyear、day 和 weekday 返回相同的值。
每个 datepart 及其缩写都返回相同的值。
如果 datepart 为 month 且 date 月份比返回月份的天数多,因而 date 中的日在返回月份中不存在,则返回返回月份的最后一天。例如,9 月份有 30 天;因此,下面两个语句返回 2006-09-30 00:00:00.000:
SELECT DATEADD(month, 1, '2006-08-30')
SELECT DATEADD(month, 1, '2006-08-31')
number 参数
number 参数不能超出 int 的范围。在下面的语句中,number 的参数超出 int 范围 1。将返回如下错误消息:“将表达式转换为数据类型 int 时出现算术溢出错误。”
SELECT DATEADD(year,2147483648, '2006-07-31'); |
|
date 参数
date 参数不能增加至其数据范围之外的值。在下面的语句中,与 date 值相加的 number 值超出了 date 数据类型的范围。将返回如下错误消息:“将值添加到 'datetime' 列导致溢出。”
SELECT
DATEADD(year,2147483647, '2006-07-31'); |
|
DATEDIFF:
返回指定的 startdate 和 enddate 之间所跨的指定 datepart 边界的计数(带符号的整数)。
语法:
DATEDIFF ( datepart , startdate , enddate ) |
参数
datepart
是指定所跨边界类型的 startdate 和 enddate 的一部分。下表列出了所有有效的 datepart 参数。用户定义的变量等效项是无效的。
datepart |
缩写 |
year |
yy, yyyy |
quarter |
qq, q |
month |
mm, m |
dayofyear |
dy, y |
day |
dd, d |
week |
wk, ww |
hour |
hh |
minute |
mi, n |
second |
ss, s |
millisecond |
ms |
microsecond |
mcs |
nanosecond |
ns |
startdate
是一个表达式,可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值。date 可以是表达式、列表达式、用户定义的变量或字符串文字。从 enddate 减去 startdate。
为避免不确定性,请使用四位数年份。有关两位数年份的信息,请参阅two digit year cutoff 选项。
enddate
请参阅 startdate。
返回类型
int
返回值
每个 datepart 及其缩写都返回相同的值。
如果返回值超出 int 的范围(-2,147,483,648 到 +2,147,483,647),则会返回一个错误。对于 millisecond,startdate 与 enddate 之间的最大差值为 24 天 20 小时 31 分钟 23.647 秒。对于 second,最大差值为 68 年。
如果为 startdate 和 enddate 都只指定了时间值,并且 datepart 不是时间 datepart,则会返回 0。
在计算返回值时不使用 startdate 或 endate 的时区偏移量部分。
由于 smalldatetime 仅精确到分钟,因此将 smalldatetime 值用作 startdate 或 enddate 时,返回值中的秒和毫秒将始终设置为 0。
如果只为某个日期数据类型的变量指定时间值,则所缺日期部分的值将设置为默认值:1900-01-01。如果只为某个时间或日期数据类型的变量指定日期值,则所缺时间部分的值将设置为默认值:00:00:00。如果 startdate 和 enddate 中有一个只含时间部分,另一个只含日期部分,则所缺时间和日期部分将设置为各自的默认值。
如果 startdate 和 enddate 属于不同的日期数据类型,并且其中一个的时间部分或秒小数部分精度比另一个高,则另一个的所缺部分将设置为 0。
参考答案:
方案一:
SELECT * FROM Sales.Orders WHERE MONTH(DATEADD(DAY,1,orderdate)) <> MONTH(orderdate)
方案二:
SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate = DATEADD(month, DATEDIFF(month, '20051231', orderdate), '20051231');
得到每月的最后一天的日期:
DATEADD(month, DATEDIFF(month, '20051231', orderdate), '20051231')
3.返回姓氏(last name)中包含字母'a'两次获更多次的雇员
涉及的表:HR.Employees表
本题主要考察%(百分号)通配符的用法
百分号代表任意长度的字符串,包括空字符串。
参考答案:
SELECT empid, firstname, lastname FROM HR.Employees WHERE lastname LIKE '%a%a%';
4.返回总价格(数量*单价)大于10000的所有订单,并按总价格排序
涉及的表:Sales.OrderDetails表
参考答案:
SELECT orderid, SUM(unitprice*qty) AS totalValue FROM Sales.OrderDetails GROUP BY orderid HAVING SUM(unitprice*qty)>10000 ORDER BY totalValue desc;
5.返回2007年平均运费最高的发货国家
涉及的表:Sales.Orders表
参考答案:
SELECT TOP(3) shipcountry, AVG(freight) AS avgfreight FROM Sales.Orders WHERE orderdate >= '20070101' AND orderdate < '20080101' GROUP BY shipcountry ORDER BY avgfreight DESC;
6.为每个顾客单独根据订单日期的顺序(用order ID作为附加属性)来计算其订单的行号。
涉及的表:Sales.Orders表
(830 row(s) affected)
参考答案:
SELECT custid , orderdate , orderid , ROW_NUMBER() OVER ( PARTITION BY custid ORDER BY orderdate, orderid ) AS rownum FROM Sales.Orders ORDER BY custid , rownum;
7.构造一个SELECT语句,让它根据每个雇员的友好称谓,而返回其性别。对于'Ms.'和'Mrs',则返回Female:对于'Mr',则返回'Male';对于其他情况(例如,'Dr.',则返回'Unknown'。
涉及的表:HR.Employees表
1.搜索格式的CASE表达式
SELECT empid , firstname , lastname , titleofcourtesy , CASE WHEN titleofcourtesy IN ( 'Ms.', 'Mrs.' ) THEN 'Female' WHEN titleofcourtesy = 'Mr.' THEN 'Male' ELSE 'Unknown' END AS gender FROM HR.Employees
2.简单的CASE表达式格式
SELECT empid , firstname , lastname , titleofcourtesy , CASE titleofcourtesy WHEN 'Ms.' THEN 'Female' WHEN 'Mrs.' THEN 'Female' WHEN 'Mr.' THEN 'Male' ELSE 'Unknown' END AS gender; FROM HR.Employees
8.返回每个客户的客户ID和所在区域。对输出中的行按区域排序,NULL值排在最后(在所有非NULL值之后)。
注意,T-SQL中NULL值的默认行为是把NULL值排在前面(所有非NULL值之前)。
涉及的表:Sales.Customers表。
参考答案:
SELECT custid , region FROM Sales.Customers ORDER BY CASE WHEN region IS NULL THEN 1 ELSE 0 END , region;
参考资料:
《SQL2008技术内幕:T-SQL语言基础》
作 者:
Jackson0714
出 处:http://www.cnblogs.com/jackson0714/
关于作者:专注于微软平台的项目开发。如有问题或建议,请多多赐教!
版权声明:本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接。
特此声明:所有评论和私信都会在第一时间回复。也欢迎园子的大大们指正错误,共同进步。或者直接私信我
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角【推荐】一下。您的鼓励是作者坚持原创和持续写作的最大动力!