【T-SQL基础】01.单表查询-几道sql查询题

概述:

本系列【T-SQL基础】主要是针对T-SQL基础的总结。

本篇主要总结了常见的对单表查询的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

是一个表达式,可以解析为与 datedatepart 相加的 int。用户定义的变量是有效的。

如果您指定一个带小数的值,则将小数截去且不进行舍入。

   date

是一个表达式,可以解析为 timedatesmalldatetimedatetimedatetime2datetimeoffset 值。date 可以是表达式、列表达式、用户定义的变量或字符串文字。如果表达式是字符串文字,则它必须解析为一个 datetime 值。为避免不确定性,请使用四位数年份。有关两位数年份的信息,请参阅 two digit year cutoff 选项。

3.返回值

datepart 参数

dayofyeardayweekday 返回相同的值。

每个 datepart 及其缩写都返回相同的值。

如果 datepartmonthdate 月份比返回月份的天数多,因而 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'); SELECT DATEADD(year,-2147483649, '2006-07-31');

date 参数

date 参数不能增加至其数据范围之外的值。在下面的语句中,与 date 值相加的 number 值超出了 date 数据类型的范围。将返回如下错误消息:“将值添加到 'datetime' 列导致溢出。”

SELECT DATEADD(year,2147483647, '2006-07-31'); SELECT DATEADD(year,-2147483647, '2006-07-31');

DATEDIFF:

返回指定的 startdateenddate 之间所跨的指定 datepart 边界的计数(带符号的整数)。

语法:

DATEDIFF ( datepart , startdate , enddate )

参数

    datepart

是指定所跨边界类型的 startdateenddate 的一部分。下表列出了所有有效的 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

是一个表达式,可以解析为 timedatesmalldatetimedatetimedatetime2datetimeoffset 值。date 可以是表达式、列表达式、用户定义的变量或字符串文字。从 enddate 减去 startdate

为避免不确定性,请使用四位数年份。有关两位数年份的信息,请参阅two digit year cutoff 选项。

   enddate

请参阅 startdate

返回类型

int

返回值

每个 datepart 及其缩写都返回相同的值。

如果返回值超出 int 的范围(-2,147,483,648 到 +2,147,483,647),则会返回一个错误。对于 millisecondstartdateenddate 之间的最大差值为 24 天 20 小时 31 分钟 23.647 秒。对于 second,最大差值为 68 年。

如果为 startdateenddate 都只指定了时间值,并且 datepart 不是时间 datepart,则会返回 0。

在计算返回值时不使用 startdateendate 的时区偏移量部分。

由于 smalldatetime 仅精确到分钟,因此将 smalldatetime 值用作 startdateenddate 时,返回值中的秒和毫秒将始终设置为 0。

如果只为某个日期数据类型的变量指定时间值,则所缺日期部分的值将设置为默认值:1900-01-01。如果只为某个时间或日期数据类型的变量指定日期值,则所缺时间部分的值将设置为默认值:00:00:00。如果 startdateenddate 中有一个只含时间部分,另一个只含日期部分,则所缺时间和日期部分将设置为各自的默认值。

如果 startdateenddate 属于不同的日期数据类型,并且其中一个的时间部分或秒小数部分精度比另一个高,则另一个的所缺部分将设置为 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/ 关于作者:专注于微软平台的项目开发。如有问题或建议,请多多赐教! 版权声明:本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接。 特此声明:所有评论和私信都会在第一时间回复。也欢迎园子的大大们指正错误,共同进步。或者直接私信我 声援博主:您的鼓励是作者坚持原创和持续写作的最大动力!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏熊二哥

那些年我们写过的T-SQL(上篇)

在当今这个多种不同数据库混用,各种不同语言不同框架融合的年代(一切为了降低成本并高效的提供服务),知识点多如牛毛。虽然大部分SQL脚本可以使用标准SQL来写,但...

209100
来自专栏Jackson0714

【T-SQL基础】02.联接查询

39690
来自专栏芋道源码1024

数据库中间件 Sharding-JDBC 源码分析 —— SQL 解析(三)之查询SQL

1. 概述 2. SelectStatement 2.1 AbstractSQLStatement 2.2 SQLToken 3. #query() 3.1 ...

40780
来自专栏熊二哥

那些年我们写过的T-SQL(中篇)

中篇的重点在于,在复杂情况下使用表表达式的查询,尤其是公用表表达式(CTE),也就是非常方便的WITH AS XXX的应用,在SQL代码,这种方式至少可以提高一...

19270
来自专栏后端技术探索

分表查询统计的一个具体案例

问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

13610
来自专栏me的随笔

T-SQL基础(一)之简单查询

SQL: Structured Query Language,结构化查询语言,是一种在关系型数据库中用于管理数据的标准语言。SQL是一种声明式编程语言,即只需表...

21820
来自专栏程序猿

SQL Server基础SQL脚本之内外连接、交叉连接;函数、子查询

代码大概200行左右 本系列,几乎都是代码,记得当时写的时候用的是微软的官方实例数据库AdventureWorks_Data.mdf、AdventureWor...

33940
来自专栏后端技术探索

分表查询统计的一个具体案例

问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

9610
来自专栏Web项目聚集地

Oracle知识点总结(一)

这里用1,也是为了方便,当然如果数据量较大的话,也可以提高速度,因为写count(*)的话会所有列扫描,这里用1的话或者用字段名的话,只扫描你写的那个列其实1就...

9510
来自专栏一个爱吃西瓜的程序员

学习SQL【10】-SQL高级处理

所谓高级处理,从用户的角度来讲,就是那些对数值进行排序,计算销售总额等我们熟悉的处理;从SQL的角度来讲,就是近几年才添加的新功能,这些新功能使得SQL的工作范...

39050

扫码关注云+社区

领取腾讯云代金券