当前位置：首页 > 资讯 > 技术文档

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

时间：2021-01-25 09:30 编辑：来源：阅读：
扫一扫，手机访问

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

[b]题目:来自Madrid且订单数少于3的消费者 [/b] [b]建表: [/b]

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);

------------------------------------------------------------------------------------------------------------------------------ [b]做题分析： [/b]

[u]复制代码[/u] 代码如下:

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

[b]结果如图所示：[/b] [b][img]http://files.jb51.net/file_images/article/201303/2013032211080774.png[/img] [/b] [b]--第一次想到的答案,突然发现少了一个来自Madrid的FISSA订单,FISSA订单数量为0,所以在Orders表中没有出现,所以上面的写法会少一个.[/b] [b]--推翻了上面的答案,又想到了用表的连接,而用内连接出现的情况会和上面的一样,所以我选择了左连接,如下: [/b]

[u]复制代码[/u] 代码如下:

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

[b]结果如图所示：[/b] [b][img]http://files.jb51.net/file_images/article/201303/2013032211080775.png[/img] [/b] [b]--查询发现是正确的。[/b] [b]--分析查看不带条件的左连接 [/b]

[u]复制代码[/u] 代码如下:

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

[u]复制代码[/u] 代码如下:

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

[b]结果如图所示：[/b] [b][img]http://files.jb51.net/file_images/article/201303/2013032211080776.png[/img] [/b] [b]--书中给的标准答案是： [/b]

[u]复制代码[/u] 代码如下:

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

[b]结果如图所示：[/b] [b][img]http://files.jb51.net/file_images/article/201303/2013032211080777.png[/img] [/b] [b]--[/b]书中给的只是多了一个order by 进行定义了排序方式(以numorders这一列的升序进行排序)

全部评论(0)

上一篇：SQL Server中Check约束的学习教程
下一篇：通过SQLSERVER重启SQLSERVER服务和计算机的方法

资讯排行榜
更多>>