SQL exercise: Phonebook Table 電話簿

利用 MySQL 建立
1. 數據庫  database
2. 資料表格  table

create database ablmcc;

create table phonebook(
	id	int(6) auto_increment, 
	name	varchar(20),
	phone	varchar(10),
	city	varchar(10),
	primary key (id)
);

insert into phonebook (name, phone, city) values
("Ada"   ,"20668156","Tuen Mun"),
("Brian" ,"20905064","Aberdeen"),
("Chris" ,"20155438","Wan Chai"),
("Eric"  ,"20155438","Wan Chai"),
("Peter" ,"20264193","Mong Kok"),
("Eva"   ,"20842491","Tai Po"),
("Fred"  ,"20066451","Sha-tin"),
("Greg"  ,"20440702","Kwun Tong"),
("Howard","20807069","Sai Kung"),
("Irene" ,"20131940","Central"),
("Jack"  ,"20104341","Lam Tin"),
("Jacky" ,"20698009","Mong Kok"),
("Erica" ,"20698009","Mong Kok"),
("Eugene","20095689","Sha-tin"),
("Maggie","20602244","Aberdeen"),
("Tony"  ,"20776180","Aberdeen"),
("Stony" ,"26009550","Sha-tin"),
("Rita"  ,"20931175","Fanling");

資料查詢 Query

1. show all columns and rows from the phonebook table
列出電話簿內所有紀錄

2. show the number of rows/records in the table
電話簿內總共有多少筆紀錄

3. show the name and phone columns for all rows
a. 列出電話簿內所有姓名及電話

b. 列出電話簿內所有姓名及電話，必須按電話次序排列

c. 列出電話簿內所有電話號碼(不可重覆)，必須按電話次序排列

4. show the name column from the phonebook table where id=2
找出電話簿內id=2的姓名

5. show all people who live in "Aberdeen"
a. 列出所有居住在"Aberdeen"者的資料

5. show all people who live in "Sha-tin" or "Tai Po"
b. 列出所有居住在"Sha-tin"或"Tai Po"者的資料

c. 列出所有非居住在"Sha-tin"及"Tai Po"者的資料

5. show all rows from the phonebook table where the phone# is '20602244'
   and city is "Aberdeen"
d. 列出所有居住在"Aberdeen"及電話為"20602244"者的資料

6. show the name column for all rows from the phonebook table where the 
   name value starts with 'Ja'.
a. 列出所有姓名以"Ja"開始者的姓名

b. 列出所有姓名以"ony"結束者的姓名

c. 列出所有姓名包含"ri"者的姓名

7.
a. 列出所有電話以"201"開始者的個人資料

b. 列出所有姓名為"Eric"者的個人資料

c. 列出所有姓名為"Eric"或"Erica"者的個人資料

d. 列出所有姓名以"Eric"開始者的個人資料

8. 列出所有姓名以"A","B","C"開始者的個人資料

9. insert a new row (Alice, 99887766, Central)
   into the phonebook table
插入新記錄 (Alice, 99887766, Central)

10. update row 7 in the table,
    setting the name column to 'Frank'
把第7筆記錄(id=7)的名字改為 Frank

11. update row 1 in the table,
    setting the name column to 'Sarah' and
   the city column to 'Sai Kung'
把第1筆記錄(id=7)的名字改為 Sarah 及 城市改為 Sai Kung

12. change city name from 'Sha-tin' to 'Shatin'
把所有城市由 Sha-tin 改為 Shatin

13. delete all people from the phonebook who lives in Shatin
刪除所有居住於 Shatin 的記錄

id	name	phone	city
1	Ada	20668156	TuenMun
2	Brian	20905064	Aberdeen
3	Chris	20155438	Wan Chai
4	Eric	20155438	Wan Chai
5	Peter	20264193	Mong Kok
6	Eva	20842491	Tai Po
7	Fred	20066451	Shatin
8	Greg	20440702	Kwun Tong
9	Howard	20807069	Sai Kung
10	Irene	20131940	Central
11	Jack	20104341	Lam Tin
12	Jacky	20698009	Mong Kok
13	Erica	20698009	Mong Kok
14	Eugene	20095689	Shatin
15	Maggie	20602244	Aberdeen
16	Tony	20776180	Aberdeen
17	Stony	26009550	Shatin
18	Rita	20931175	Fanling