資料庫系統_答案 Database System Week3

Presentation on theme: "資料庫系統_答案 Database System Week3"— Presentation transcript:

1 資料庫系統_答案 Database System Week3

2 練習 列出所有手機不是NULL的學生的手機與姓名 查詢學生的手機及姓名，但是將手機前面的0以+886取代(請排除NULL的學生)
(使用Relpace有問題，試試其他函數) 列出所有在星期六出生的學生 將學生的身分證字號以MD5雜湊函數顯示出來 計算出學生的生日跟現在的時間平均差距幾天

3 練習題答案 SELECT Name,Phone FROM databasesystem1061.student WHERE Phone IS NOT NULL; SELECT Name,CONCAT('+886',TRIM(LEADING '0' FROM phone)) as '手機號碼' FROM databasesystem1061.student WHERE Phone IS NOT NULL; SELECT Name FROM databasesystem1061.student WHERE DAYOFWEEK(Birthday)=7; SELECT MD5(IDCardNum) as '雜湊函數' FROM databasesystem1061.student ; SELECT AVG(datediff(NOW(),Birthday)) as '跟現在平均相差天數' FROM databasesystem1061.student ;

4 模擬考題目 請列出使用者帳號GT1fO9L61v的好友帳號列表(僅顯示friendid)
請列出發送訊息的時間介於 至 的訊息id(僅顯示messageid) 請列出所有貼文中按讚數量大於20的貼文，並將按讚數量的欄位名稱改成LikeNumber(僅顯示 postid,LikeNumber) 請查看目前”沒有”按”任何貼文”讚的帳號(userid) 請列出社團id為7的成員中”電話號碼與生日都有填”的”名單及資料”並以”加入時間”由早到晚做 排序(userid,datetime,firstname,lastname,phone,location,birthday,gender)

5 題目範圍 WHERE 條件查詢 Between AND Group by , Having , Count() , 比較運算子 , AS
NOT IN 與 Subquery JOIN , AND , ORDER BY , NULL , NOT

6 模擬考答案 SELECT friendid FROM communitywebsite.friendsof WHERE userid = 'GT1fO9L61v'; SELECT messageid FROM communitywebsite.messqgerecord where datetime between ' ' AND ' ' ; SELECT postid,COUNT(postid) FROM communitywebsite.postlike group by postid having count(postid)>20;

7 模擬考答案 SELECT userid FROM users WHERE userid NOT IN ( SELECT userid FROM pagelike );

8 模擬考答案 SELECT groupmember.userid,datetime,firstname,lastname,phone,location, birthday , gender FROM groupmember INNER JOIN information ON (groupmember.userid = information.userid AND groupmember.groupid=7 AND phone IS NOT NULL AND birthday IS NOT null )ORDER BY datetime ASC;