教你怎麼用EXCEL練習SQL

01-26

SQL 作為數據分析的入門是必須的。她的語法結構比較簡單，我們需要經常去練習來熟悉她。那如果是在辦公室又不能裝資料庫來練習。用excel 建立資料庫並練習SQL 是最簡單方便的。

工具鏈接請點這裡。密碼為：uzbr

這個Excel SQL 練習工具我是用VBA調用ADO編寫的。所以SQL的語法和用ACCESS做SQL是一樣的。本文分為以下幾個部分。

如何使用 Excel SQL 練習工具
關係圖與excel資料庫的講解
SQL練習實例
總結

如何使用 Excel SQL 練習工具

1.首先解壓壓縮包到任意文件夾，並得到名為「SQLpractice」的文件夾

2.打開文件里裡面的「SQL practice.xlsm」

3.點擊左上角」允許編輯「（我用的是英文版則點擊"Enable Content"）

4. 先在單元格B2中編輯用來查詢的SQL（1.紅色圈中部分），再點擊（2.藍色圈中部分）就可以在左邊區域顯示查詢內容。

我們來看一個簡單的例子：

在B2中輸入SQL並點擊「Run SQL」

就得到了我們的查詢結果：

關係圖與excel資料庫的講解

與我們用的MYSQL 相似。我用excel建立了一個資料庫，但是不同的是預先不用寫好主鍵，外鍵等關係。所以資料庫的完整性不能保證。先打開文件夾中另一個文件"DATABASE.xlsx"。第一個表就是我們的關係圖。（關係是用手繪的...稍微有點丑。。）

ERD：

主鍵與外鍵信息請參照顏色。紅色為主鍵，藍色為外鍵。

SalesOrders 為銷售訂單表。有4個欄位分別為OrderId（銷售訂單號）, EmployeeId（銷售員編號）, RequreDate（要求到貨日期）, Shipdate（實際發貨日）。

SODetails 為銷售訂單詳情。有3個欄位分別為OrderId(對應SalesOrders中的銷售訂單號)，Productid（產品號），Qty（銷售數量）。

PurchaseOrders 為採購訂單。有4個欄位為Poid（採購訂單編號）， Employeeid（採購員編號）， RequireDate（需求日期），Shipdate（發貨日期）。

PODetails 為採購訂單詳情。有3個欄位為POid（對應PurchaseOrders 中的採購訂單號），Productid（產品號），Qty（採購數量）。

Products 為產品表。有4個欄位為Prodid（產品號），Name（產品名稱），Price（銷售價格），Cost（採購價格）。

Employees 為員工表。有5個欄位為Empid（員工工號），Name（姓名），Managerid（上司編號），Depid（所屬部門編號），Salary（工資）。

最後一個表為Department 為部門表。有2個欄位為Depid（部門編號），Name（部門名稱）。

後面的每個數據表為對應資料庫的表。

SQL練習實例

為在工具中寫了11個例子我們來分析下。首先說下在這裡運行SQL有幾個與MYSQL不一樣的地方。

調用表需要用中括弧加表名再加上$. 例如 select * from [employees$]
使用多次left join, right join 或者inner join時需要加（）隔開。例如

select *

from ([table$] t1 left join [table2$] t2 on t1.k1=t2.k1)

left join [table3$] t3 on t3.k1=t1.k1

*註：「[table$] t1」後面t1 為查詢表的簡化標記來方便查詢。

1. 銷售訂單總數。想要得到的查詢結果：

SQL：

SELECT count(orderid) as TotalSO

FROM [salesorders$]

2. 銷售訂單詳情。想要得到的查詢結果（只截取的開頭數據）：

SQL：

SELECT so.orderid, emp.name, so.requiredate, so.shipdate ,p.name, sod.qty,p.price,(sod.qty*p.price) as total

from (([salesorders$] so

left join [sodetails$] sod on so.orderid=sod.orderid)

left join [products$] p on p.prodid=sod.productid)

left join [employees$] emp on emp.empid=so.employeeid

3. 產品銷售額排序。想要得到的查詢結果（只截取的開頭數據）：

SQL：

select p.name, sum( (p.price*sod.qty)) as totalvalue

from [sodetails$] sod

left join [products$] p on sod.productid=p.prodid

group by p.name

order by sum( (p.price*sod.qty)) desc

4. 每個銷售訂單凈利潤。想要得到的查詢結果（只截取的開頭數據）：

SQL:

select sod.orderid ,sum ((p.price-p.cost) * sod.qty) as profit

from [sodetails$] sod left join [products$] p on sod.productid=p.prodid

group by sod.orderid

order by sum ((p.price-p.cost) * sod.qty) desc

5. 找出沒有按時寄出的訂單與責任人。想要得到的查詢結果:

SQL：

select orderid, name

from[salesorders$] so

left join [employees$] emp on emp.empid=so.employeeid

where requiredate<shipdate

6. 當前產品庫存量。想要得到的查詢結果:

SQL:

select p.name , sum(t1.qty) as onhand

from [products$] p

left join (

select productid, -qty as qty

from [sodetails$]

union all

select productid, qty

from [podetails$]) t1 on p.prodid=t1.productid

group by p.name

order by sum(t1.qty) desc

7. 每個部門人數。想要得到的查詢結果:

SQL:

select dep.name, count(empid) as num_of_emp from [department$] dep left join [employees$] emp on emp.depid=dep.depid group by dep.name order by count(empid) desc

8. 每個部門裡年收入最高的員工。想要得到的查詢結果:

SQL:

select emp.name, salary, dep.name

from [employees$] emp

left join [department$] dep on emp.depid=dep.depid

where emp.salary = (

select max(salary)

from [employees$] emp2

where emp.depid=emp2.depid)

9. 年收入比他們上司公司高的員工。想要得到的查詢結果:

SQL:

select emp.empid,emp.name, emp.salary,emp2.name

from [employees$] emp

left join [employees$] emp2 on emp.managerid=emp2.empid

where emp.salary>emp2.salary

10. 2015年每個月的採購總額。想要得到的查詢結果:

SQL:

select month(shipdate)& "/2015" as month_of_2015 , sum(qty*cost) as totalcost

from ([purchaseorders$] po

left join [podetails$] pod on po.poid=pod.poid)

left join [products$] p on p.prodid=pod.productid

where year(shipdate)=2015

group by month(shipdate)

11. 銷售員銷量排行榜。想要得到的查詢結果:

SQL：

select emp.name, sum(p.price*sod.qty) as totalsales

from (([salesorders$] so

left join [employees$] emp on emp.empid=so.employeeid)

left join [sodetails$] sod on sod.orderid=so.orderid)

left join [products$] p on sod.productid=p.prodid

group by emp.name

order by sum(p.price*sod.qty) desc

總結

作為SQL練習的一個小結，通過VBA建立excel資料庫熟悉來資料庫的構成，以及學習來常用SQL功能的使用。

有對VBA功能實現感興趣的同學可以私信為。或者需要為幫助寫VBA小程序的也可以聯繫我。