The community centre in your neighborhood runs a comic library system, in which all registered users can borrow up to four comic books for up to two weeks at any one time. Recently the centre is computerizing the system. A preliminary design of the library database system is as follows.
The table members stores the data of each registered member of the library system.
The table borrow_records stores the data concerning the borrow records of the copies, including the borrow date, expected return date (which is always two weeks after the borrow date), and whether the book has been returned or not (the default value should be FALSE).
Lets assume that the above database design has already been implemented using MS Access. Users are now ready to obtain useful information from the database. Please respond to the following data requests by creating corresponding SQL commands for information retrieval.
Question
- A list of members who have overdue books. List these member IDs, full names and mobile numbers. Label the queries as q-01, q-02 , q-10.
-L-U members 1 member_id name gender HKID address1 address2 mobile number comics 1 booktitle language publisher author category copies 1 copy_id booktitle language cost purchase_date borrow_records 1 member_id 1 copy_id I borrow_date returned Sort & Filter Records member_id, name gender, HKID 1 Tam, Tin Lok A123456(4) 2 Ho, Kam Chuen M 2145897(1) 3 Wong, Tak Chung M Z556633(A) 4 Lee, Long Pak M B421421(5) 5 Cheng, Chung Yan F 1425698(3) 6 Lo, I Ting F X457841(1) 7 Chow, Chun Ning M Q412596(A) 8 Lai, Chun Kit M C546841(1) 9 Ng, Ho Cheong M S458963(5) 10 Wu, Lai Jing F V459821(8) 11 Chan, Lai Nga F C123456(1) 12 Lam, Mei Yan F D654321(0) 13 Chan, Lai Nan M E459863(3) 14 Man, man Fong F V456723(6) 16 (New) Find Window address1 1212, Tip Ling House, Bee Estate 1321, Tip Ho House, Bee Estate 14B, Hei Mong Buildng 2F, Ho King House, Siu King Court 123, Tai Tsz Road 1302, Block C, Sun Yuen Long Centre 903, Hung Uh Road 2203, Block A, Fortune Centre 921, Wu King House, King Nga Estate 2223, Hong Nga Villa 1423, Golden Villa Road 5D, Ho King House, Siu King Court 1423, Golden Villa Road 3212, Block E, Sun Tuen Mun Centre Text Formatting address2 - mobile_nun Yuen Long 9123-4567 Yuen Long 9234-5678 Tin Shui Wai 9345-6789 Tuen Mun 9456-7890 Yuen Long 9012-3456 Yuen Long 9111-2222 Yuen Long 9222-3333 Tin Shui Wai 9333-4444 Tuen Mun 9444-5555 Tin Shui Wai 9555-6666 Tuen Mun 9666-7777 Tuen Mun 9777-8899 Tuen Mun 9978-4569 Tuen Mun 8124-7895 + Click to Add member_id 1 1 1 1 1 1 1 2 2 3 3 3 3 3 6 nowwwwwNN copy_id - borrow_dati - returned 2 7/1/2018 5 2/1/2018 6 11/6/2018 7 19/2/2020 12 2/1/2018 12 7/1/2018 14 19/2/2020 1 2/1/2017 12 20/2/2017 1 15/8/2018 8 24/10/2019 12 17/2/2010 15 19/2/2017 15 19/10/2018 1 1/2/2020 2 15/2/2020 8 11/2/2020 O 6 11 O -L-U members 1 member_id name gender HKID address1 address2 mobile number comics 1 booktitle language publisher author category copies 1 copy_id booktitle language cost purchase_date borrow_records 1 member_id 1 copy_id I borrow_date returned Sort & Filter Records member_id, name gender, HKID 1 Tam, Tin Lok A123456(4) 2 Ho, Kam Chuen M 2145897(1) 3 Wong, Tak Chung M Z556633(A) 4 Lee, Long Pak M B421421(5) 5 Cheng, Chung Yan F 1425698(3) 6 Lo, I Ting F X457841(1) 7 Chow, Chun Ning M Q412596(A) 8 Lai, Chun Kit M C546841(1) 9 Ng, Ho Cheong M S458963(5) 10 Wu, Lai Jing F V459821(8) 11 Chan, Lai Nga F C123456(1) 12 Lam, Mei Yan F D654321(0) 13 Chan, Lai Nan M E459863(3) 14 Man, man Fong F V456723(6) 16 (New) Find Window address1 1212, Tip Ling House, Bee Estate 1321, Tip Ho House, Bee Estate 14B, Hei Mong Buildng 2F, Ho King House, Siu King Court 123, Tai Tsz Road 1302, Block C, Sun Yuen Long Centre 903, Hung Uh Road 2203, Block A, Fortune Centre 921, Wu King House, King Nga Estate 2223, Hong Nga Villa 1423, Golden Villa Road 5D, Ho King House, Siu King Court 1423, Golden Villa Road 3212, Block E, Sun Tuen Mun Centre Text Formatting address2 - mobile_nun Yuen Long 9123-4567 Yuen Long 9234-5678 Tin Shui Wai 9345-6789 Tuen Mun 9456-7890 Yuen Long 9012-3456 Yuen Long 9111-2222 Yuen Long 9222-3333 Tin Shui Wai 9333-4444 Tuen Mun 9444-5555 Tin Shui Wai 9555-6666 Tuen Mun 9666-7777 Tuen Mun 9777-8899 Tuen Mun 9978-4569 Tuen Mun 8124-7895 + Click to Add member_id 1 1 1 1 1 1 1 2 2 3 3 3 3 3 6 nowwwwwNN copy_id - borrow_dati - returned 2 7/1/2018 5 2/1/2018 6 11/6/2018 7 19/2/2020 12 2/1/2018 12 7/1/2018 14 19/2/2020 1 2/1/2017 12 20/2/2017 1 15/8/2018 8 24/10/2019 12 17/2/2010 15 19/2/2017 15 19/10/2018 1 1/2/2020 2 15/2/2020 8 11/2/2020 O 6 11 O
