Sql学习第一天——SQL 练习题(建表/sql语句)

时间:2024-01-22 05:44:39 

题目:来自Madrid且订单数少于3的消费者  

建表:


set nocount on --当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。当 SET NOCOUNT 为 OFF 时,返回计数
use SY
GO
if object_Id('dbo.Orders') is not null
drop table dbo.Orders
GO
if object_Id('dbo.Customers') is not null
drop table dbo.Customers
GO
create table dbo.Customers
(
customerid char(5) not null primary key ,
city varchar(10) not null
);
insert into dbo.Customers values('FISSA','Madrid');
insert into dbo.Customers values('FRNDO','Madrid');
insert into dbo.Customers values('KRLOS','Madrid');
insert into dbo.Customers values('MRPHS','Zion');

create table dbo.Orders
(
orderid int not null primary key ,
customerid char(5) null references customers(customerid)
)
insert into dbo.Orders values(1,'FRNDO');
insert into dbo.Orders values(2,'FRNDO');
insert into dbo.Orders values(3,'KRLOS');
insert into dbo.Orders values(4,'KRLOS');
insert into dbo.Orders values(5,'KRLOS');
insert into dbo.Orders values(6,'MRPHS');
insert into dbo.Orders values(7,null);


------------------------------------------------------------------------------------------------------------------------------

做题分析:


select customerid as 消费者,count(customerid) as 订单数
from dbo.Orders
where customerid in (
select customerid
from dbo.Customers
where city = 'Madrid')
group by customerid
having count(customerid) < 3


结果如图所示:

Sql学习第一天——SQL 练习题(建表/sql语句)

--第一次想到的答案,突然发现少了一个来自Madrid的FISSA订单,FISSA订单数量为0,所以在Orders表中没有出现,所以上面的写法会少一个.

--推翻了上面的答案,又想到了用表的连接,而用内连接出现的情况会和上面的一样,所以我选择了左连接,如下:


select C.customerid as 消费者,count(O.customerid) as 订单数
from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid
where C.city= 'Madrid'
group by C.customerid
having count(C.customerid) < 3


结果如图所示:

Sql学习第一天——SQL 练习题(建表/sql语句)

--查询发现是正确的。

--分析查看不带条件的左连接


select * from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid

select * from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid


结果如图所示:

Sql学习第一天——SQL 练习题(建表/sql语句)

--书中给的标准答案是:


select C.customerid , count(O.orderid) as numorders
from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid
where C.city= 'Madrid'
group by C.customerid
having count(O.orderid) < 3
order by numorders


结果如图所示:

Sql学习第一天——SQL 练习题(建表/sql语句)

--书中给的只是多了一个order by 进行定义了排序方式(以numorders这一列的升序进行排序)

标签:sql建表,练习题
0
投稿

猜你喜欢

  • python 引用传递和值传递详解(实参,形参)

    2023-10-25 15:57:44
  • Python爬虫框架Scrapy安装使用步骤

    2022-02-23 13:49:09
  • 如何优化网站图片以快速显示

    2008-04-05 10:09:00
  • 一个超级简单的python web程序

    2021-11-19 18:05:06
  • python得到单词模式的示例

    2021-04-22 08:25:48
  • 对python3.4 字符串转16进制的实例详解

    2022-03-29 16:15:17
  • 浅析Python字符串索引、切片、格式化

    2023-09-26 07:57:20
  • 利用golang的字符串解决leetcode翻转字符串里的单词

    2023-07-17 16:36:21
  • 利用XMLBean轻轻松松读写XML

    2008-09-04 11:25:00
  • MySQL order by与group by查询优化实现详解

    2024-01-24 23:27:48
  • django学习之ajax post传参的2种格式实例

    2022-07-09 02:21:57
  • Go 每日一库之termtables的使用

    2024-05-22 10:20:25
  • Oracle中PL/SQL的块与表达式

    2024-01-28 21:30:34
  • python操作redis的方法

    2021-03-27 05:14:03
  • Python谱减法语音降噪实例

    2023-07-26 05:19:55
  • Python获取DLL和EXE文件版本号的方法

    2023-09-07 11:43:23
  • MySQL8自增主键变化图文详解

    2024-01-25 19:08:38
  • 高效的MySQL分页

    2011-01-04 20:01:00
  • Python 中Operator模块的使用

    2021-04-02 05:30:34
  • 深入了解Vue中双向数据绑定原理

    2024-05-13 09:38:25
  • asp之家 网络编程 m.aspxhome.com