26-SQLite-獎金 綜合練習

youtube 說明欄內不允許半形的角括號（小於＜、大於＞）所以命令有大於＞、小於＜全部用全形的大於＞、小於＜替代；若您要直接copy 使用，別忘了將全形的大於＞、小於＜；改成半形的大於＞、小於＜；不然命令會有誤喔

本單元內容如下：
engdata.txt
create table year (id varchar(4),name varchar(20),area varchar(2),grade varchar(1),bonus integer);
insert into year values ("s001","john","north","A",85500);
insert into year values ("s005","mary","north","A",98000);
insert into year values ("s003","lee","center","B",70000);
insert into year values ("s007","kao","center","B",64000);
insert into year values ("s002","wang","south","C",250000);
insert into year values ("s004","victor","south","A",100000);
insert into year values ("s006","long","east","C",140000);
insert into year values ("s009","maria","north","B",10000);
1.engdata.txt檔內為 SQL命令；導入 dbexall 資料庫
D:\sqlite3＞sqlite3 dbexall ＜ engdata.txt
2.顯示所有記錄
sqlite＞ select * from year;

3.顯示北區(north)的姓名與區域
sqlite＞ select name,area from year where area=“north";
4.顯示各區 ,區域 及 年終加總
sqlite＞ .header on
sqlite＞ select area,sum(bonus) from year group by area;
area|sum(bonus)
center|134000
east|140000
north|193500
south|350000
5.顯示各等級 所發的年終加總
sqlite＞ select grade,sum(bonus) from year group by grade ;
grade|sum(bonus)
A|283500
B|144000
C|390000
6.顯示年終獎金發最多的區域及總金額
sqlite＞ select area ,sum(bonus) from year group by area order by sum(bonus) desc limit 1;
area|sum(bonus)
south|350000
7.脫離 sqlite3
8..用 記事本 建立 ex01 ， 內容如下:
create table ex01 (id varchar(4),name varchar(20),area varchar(2),grade varchar(1),bonus integer);
insert into ex01 values ("s001","john","north","A",85500);
insert into ex01 values ("s005","mary","north","A",98000);
insert into ex01 values ("s003","lee","center","B",70000);
insert into ex01 values ("s007","kao","center","B",64000);
insert into ex01 values ("s002","wang","south","C",250000);
insert into ex01 values ("s004","victor","south","A",100000);
insert into ex01 values ("s006","long","east","C",140000);
insert into ex01 values ("s009","maria","north","B",10000);
9.用 導入方式 在ex.db資料庫內，建立table ex01 及其資料
10.進入 sqlite3 設定header打開，並取出 ex.db資料庫
D:\sqlite3＞sqlite3 ex.db ＜ex01

D:\sqlite3＞sqlite3 -header ex.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
11.顯示目前資料庫的所有table (查詢ex01 table 建立了沒)
12.顯示table ex01 內所有記錄
sqlite＞ .table
ex01
sqlite＞ select * from ex01;

13.顯示table ex01結構
sqlite＞ .schema ex01
CREATE TABLE ex01 (id varchar(4),name varchar(20),area varchar(2),grade varchar(1),bonus integer);
sqlite＞ create table ex02 (id varchar(4),name varchar(20),area varchar(2),grade varchar(1),bonus integer);

14.建立table ex02 結構如下:
create table ex02 (id varchar(4),name varchar(20),area varchar(2),grade varchar(1),bonus integer);
15.將ex01 table area欄位內”北區”(north)的記錄， 加到 table ex02
16..查詢 table ex02 所有記錄
sqlite＞ insert into ex02 (id,name,area,grade,bonus) select id,name,area,grade,bonus from ex01 where area="north";
sqlite＞ select * from ex02;
id|name|area|grade|bonus
s001|john|north|A|85500
s005|mary|north|A|98000
s009|maria|north|B|10000
19.顯示table ex01 各區營業所(area)的人，每區(area)以一列呈現
sqlite＞ select area ,group_concat(name) from ex01 group by area;
area|group_concat(name)
center|lee,kao
east|long
north|john,mary,maria
south|wang,victor
20.table ex01 先根據 area排序(由小排到大)，再根據bonus 由大排到小
sqlite＞ select * from ex01 order by area ,bonus desc;
id|name|area|grade|bonus
s003|lee|center|B|70000
s007|kao|center|B|64000
s006|long|east|C|140000
s005|mary|north|A|98000
s001|john|north|A|85500
s009|maria|north|B|10000
s002|wang|south|C|250000
s004|victor|south|A|100000
21.table ex01內，姓名 第二個字為 “a” 的人
sqlite＞ select * from ex01 where name like "_a%";
id|name|area|grade|bonus
s005|mary|north|A|98000
s007|kao|center|B|64000
s002|wang|south|C|250000
s009|maria|north|B|10000
22. table ex02,出現中文名稱，姓名及 地區，如下，
各區顯示為 North District 、Central District、South Distric 、East District
sqlite＞ select name as 姓名 ,replace(area,area,area||" District") as 地區 from ex02;
姓名|地區
john|north District
mary|north District
maria|north District
wang|south District
victor|south District
long|east District
23. table ex02，grade欄位內容，將”C”改成”佳” (用replace()函數)；再改回“C"
sqlite＞ update ex02 set grade=replace(grade,"C","佳") where grade="C";
sqlite＞ select * from ex02;
sqlite＞ update ex02 set grade="C" where grade="佳";
sqlite＞ select * from ex02;

24.table ex02, 各區內容改成對應的North District 、Central District、South Distric 、East District
25. table ex02,增加一date 欄位，型態為 date型態
26. table ex02，顯示date 欄為null的記錄
27. table ex02,查詢 id 為s001或s005或s004 的記錄
28.table ex02，將id 為s001或s005或s004的日期設為 2021-10-10
29.table ex02，查詢 date 欄已有資料的紀錄
30.table ex02，將id 為s001或s005或s004 以外的日期設為 2021-12-25
31. table ex02，查詢日期為 2021-10-10 的記錄
32. table ex02，查詢日期為12月的記錄
33. table ex02，將id 為s001的日期設為 2020-1-5

相關演講:
20211004社群行銷之客戶關係管理
https://youtu.be/goWBKR7fLqw

相關活動:
▶▶▶▶▶▶▶▶活動資訊
▶臺灣雲市集 TCloud 計畫 最高可獲政府補助點數 30,000 點 1 點 = 新臺幣 1 元 叡揚雲端方案連結
https://reurl.cc/a9RVvQ

▶線上講座_找對雲端工具包，打造營運新活水 叡揚資訊 雲端及巨資事業群 賴俊仁 處長
https://reurl.cc/AkpA7Z

頻道
#ＳＱＬｉｔｅ頻道
https://www.youtube.com/playlist?list=PLWkbsqjwqW3gSEIUilE2KFXXu0sTWxh2u