程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> Sql學習第一天——SQL 練習題(建表/sql語句)

Sql學習第一天——SQL 練習題(建表/sql語句)

編輯:更多數據庫知識

題目:來自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

結果如圖所示:

--第一次想到的答案,突然發現少了一個來自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

結果如圖所示:

--查詢發現是正確的。

--分析查看不帶條件的左連接
復制代碼 代碼如下:
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

結果如圖所示:

--書中給的標准答案是:
復制代碼 代碼如下:
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

結果如圖所示:

--書中給的只是多了一個order by 進行定義了排序方式(以numorders這一列的升序進行排序)

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved