實作練習

(1)建立資料庫
create database myhome
character set utf8
collate utf8_general_ci;

建立mycategory
CREATE TABLE mycategory (
cid char(3) NOT NULL,
cname varchar(30),
cflag char(1),
PRIMARY KEY (cid) );

建立mymoney
CREATE TABLE mymoney (
mid char(10) NOT NULL,
cid char(3) NOT NULL,
mdate char(8),
mamount int(7),
mperson char(1),
PRIMARY KEY (mid) );

(2)歲末時想知道今年每個月的汽油費用開銷多少，應該如何寫出SQL? 

select sum(mamount) from mymoney where cid='003' group by substr(mdate,1,6);

※如果要知道「每年汽油費用開銷多少」，應該如何寫SQL呢?

(3)歲末時要知道今年每個月的收支狀況，應該如何寫出SQL? 

支出狀況 (cflag='0')

select sum(mamount),substr(mdate,1,6) as mm from mymoney a, mycategory b where a.cid=b.cid and cflag='0' group by mm having mm between '201401' and '201412' 

收入狀況 (cflag='1') 

select sum(mamount),substr(mdate,1,6) as mm from mymoney a, mycategory b where a.cid=b.cid and cflag='1' group by mm having mm between '201401' and '201412'

※如果要知道「2014年每月吃的支出」，應該如何寫SQL呢?
假設吃的費用其cid='001'

(4)歲末時要知道今年每個人每個月的支出狀況，應該如何寫出SQL? 

select mperson,sum(mamount) from mymoney a, mycategory b where a.cid=b.cid and cflag='0' and (mdate between '20140101' and '20141231') group by mperson, substr(mdate,1,6);

※如果要知道「2014年爸爸的月收入」，應該如何寫SQL呢?
假設爸爸mperson='1'

(5)想知道六月份的全家娛樂費用開銷多少，應該如何寫出SQL? 

select sum(mamount) from mymoney where cid='004' and (mdate between '20140601' and '20140630');

※如果要知道「2014年媽媽的月支出」，應該如何寫SQL呢?
假設媽媽mperson='0'

(6)想知道六月到十二月中全家吃飯費用每月平均開銷多少，應該如何寫出SQL? 

select sum(mamount)/7 from mymoney where cid='001' and (mdate between '20130601' and '20131231');

※如果要知道「2014年爸爸的平均月收入」，應該如何寫SQL呢?
假設爸爸mperson='1'

(7)六月中每餐吃飯平均費用是多少，應該如何寫出SQL? 

select avg(mamount) from mymoney WHERE CID='001' AND (mdate between '20140601' and '20140630') group by substr(mdate,1,6);

※如果要知道「2014年每月平均吃飯費用」，應該如何寫SQL呢?
假設吃的費用其cid='001'

(8)今年太太的月平均服裝費用是多少，應該如何寫出SQL? 

select avg(mamount) from mymoney where cid='002' and mperson='0' and (mdate between '20140101' and '201401231') group by substr(mdate,1,6);

※如果要知道「今年太太的月最高服裝費用是多少」，應該如何寫SQL呢?
假設服裝的費用其cid='002'

(9)今年平均每月可以存下多少錢，應該如何寫出SQL? 

收入總和 

select sum(mamount) from mymoney a, mycategory b where a.cid=b.cid and cflag='1' and (mdate between '20130101' and '20131231') ;

支出總和 

select sum(mamount) from mymoney a, mycategory b where a.cid=b.cid and cflag='0' and (mdate between '20130101' and '20131231') ;

(收入總和-支出總和)/12即是答案

另外練習範例

資料表 EMPL儲存XYZ公司的員工資料，其表單結構如下：

寫出下列各查詢的 SQL 語句或回答問題： 

(1)列出研發部門員工的名字和編號 (部門編號為 "RD"), 並以員工名字順
序由大到小排列。

(2) 列出月薪由二萬五千至二萬八千元的女性員工的名字。

(3) 列出各部門的員工數目及該部門員工的平均月薪。

(4) 列出各居住地區的員工數目。

(5) 列出所有以女性為主管的部門及主管名稱。

(6) 列出所有部門主管的月薪、部門名稱及主管名稱，並以月薪由大到小列
出。

(7) 列出所有部門最低的月薪及員工編號。

(8) 請問你認為資料表 EMPL的結構有哪些地方可以修改?