實作練習

(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的結構有哪些地方可以修改?

留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

MySQL使用者權限設定