########################################## # # MySQL (1) : In class exercise # # Use the sample database (sample_data.sql) # if you are required to use a database # ########################################## ###### Question No. 1 ################################################## ########################################## # # Normalize the following table on paper: # # +----------------+--------------------+-------------+-------------+---------------+------------+ # | p_name | p_phone | p_tx_1 | p_tx_2 | doc_name | doc_office | # +----------------+--------------------+-------------+-------------+---------------+------------+ # | David Harrison | 456-9900, 898-8877 | | | Robert Chen | 455 | # | Eric Lee | 456-5455 | Asprin | Methadon | Robert Chen | 455 | # | Peter Jackson | 456-8978, 675-9989 | Asprin | Diazepam | Sara Robinson | 578 | # | Susan Harrison | 455-4583 | Asprin | Propranolol | William Bush | 787 | # | Tom Serveas | 787-9989 | Levotyroxin | | Sara Robinson | 578 | # +----------------+--------------------+-------------+-------------+---------------+------------+ # # Assume that each patient has only one doctor. # You may need to create multiple tables. # (hint: 6 tables are required) # ########################################## +--------+-----------+-----------+--------+ | pat_id | pat_fname | pat_lname | doc_id | +--------+-----------+-----------+--------+ | 1 | David | Harrison | 1 | | 2 | Eric | Lee | 1 | | 3 | Peter | Jackson | 2 | | 4 | Susan | Harrison | 3 | | 5 | Tom | Serveas | 2 | +--------+-----------+-----------+--------+ +--------+-----------+-----------+------------+ | doc_id | doc_fname | doc_lname | doc_office | +--------+-----------+-----------+------------+ | 1 | Robert | Chen | 455 | | 2 | Sara | Robinson | 578 | | 3 | William | Bush | 787 | +--------+-----------+-----------+------------+ +----------+-----------+ | phone_id | phone_num | +----------+-----------+ | 1 | 456-9900 | | 2 | 898-8877 | | 3 | 456-5455 | | 4 | 456-8978 | | 5 | 675-9989 | | 6 | 455-4583 | | 7 | 787-9989 | +----------+-----------+ +--------+----------+ | pat_id | phone_id | +--------+----------+ | 1 | 1 | | 1 | 2 | | 2 | 3 | | 3 | 4 | | 3 | 5 | | 4 | 6 | | 5 | 7 | +--------+----------+ +-------+-------------+ | tx_id | tx_name | +-------+-------------+ | 1 | Asprin | | 2 | Methadon | | 3 | Diazepam | | 4 | Propranolol | | 5 | Levotyroxin | +-------+-------------+ +--------+-------+ | pat_id | tx_id | +--------+-------+ | 2 | 1 | | 2 | 2 | | 3 | 1 | | 3 | 3 | | 4 | 1 | | 4 | 4 | | 5 | 5 | +--------+-------+ ###### Question No. 2 ################################################## ########################################## # # Find all patients whose last name is 'McDonnald' # (hint: expect 2 rows) # ########################################## SELECT * FROM pat_info WHERE P_LastName = 'McDonnald'; +------+-------------+------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+------------+---------+----------+--------+-----------+ | 4 | John | McDonnald | 5 | 8 | 3 | 17 | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | +------+-------------+------------+---------+----------+--------+-----------+ ###### Question No. 3 ################################################## ########################################## # # Find all patients who live in City_id 3 # (hint: expect 5 rows) # ########################################## SELECT * FROM pat_info WHERE City_id = 3; +------+-------------+------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+------------+---------+----------+--------+-----------+ | 3 | Sara | Henson | 3 | 6 | 2 | 16 | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | | 12 | Abraham | Lincoln | 3 | 26 | 27 | 25 | | 39 | Oliseagan | Dysee | 3 | 70 | 32 | 56 | | 46 | Uve | Evalinson | 3 | 47 | 19 | 27 | +------+-------------+------------+---------+----------+--------+-----------+ ###### Question No. 4 ################################################## ########################################## # # Find all patients who live in 'Montreal' # (hint: expect 3 rows) # ########################################## SELECT * FROM pat_info WHERE City_id = 4; +------+-------------+------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+------------+---------+----------+--------+-----------+ | 6 | William | Jordan | 4 | 10 | 4 | 19 | | 37 | Mina | Namiri | 4 | 99 | 33 | 57 | | 45 | Zinc | Goldman | 4 | 95 | 31 | 54 | +------+-------------+------------+---------+----------+--------+-----------+ ###### Question No. 5 ################################################## ########################################## # # Find all patients whose first name is 'John' # but their last name is NOT 'McDonnald' # (hint: expect 1 row) # ########################################## SELECT * FROM pat_info WHERE P_FirstName = 'John' AND P_LastName <> 'McDonnald'; ###### Question No. 6 ################################################## ########################################## # # Find all patients whose first name starts # with the letter 'J' # (hint: expect 4 rows) # ########################################## SELECT * FROM pat_info WHERE P_FirstName LIKE 'J%'; +------+-------------+------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+------------+---------+----------+--------+-----------+ | 4 | John | McDonnald | 5 | 8 | 3 | 17 | | 9 | John | McKinsy | 1 | 9 | 10 | 22 | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | | 21 | Josef | Koopy | 1 | 35 | 1 | 50 | +------+-------------+------------+---------+----------+--------+-----------+ ###### Question No. 7 ################################################## ########################################## # # Find all patients whose first name contains # the letter 'a' and their id is more than 30 # (hint: expect 9 rows) # ########################################## SELECT * FROM pat_info WHERE P_FirstName LIKE '%a%' AND P_id > 30; +------+-------------+-------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+-------------+---------+----------+--------+-----------+ | 31 | Diana | Fedrensinco | 7 | 56 | 15 | 39 | | 32 | Lama | Buda | 9 | 60 | 18 | 58 | | 33 | Cristina | Augelira | 6 | 45 | 23 | 29 | | 34 | Faith | Hill | 10 | 61 | 12 | 59 | | 35 | Micheal | Jackson | 5 | 62 | 17 | 47 | | 37 | Mina | Namiri | 4 | 99 | 33 | 57 | | 39 | Oliseagan | Dysee | 3 | 70 | 32 | 56 | | 40 | Sampali | Sirini | 7 | 46 | 22 | 55 | | 42 | Nisan | Yagamachi | 9 | 82 | 20 | 52 | +------+-------------+-------------+---------+----------+--------+-----------+ ###### Question No. 8 ################################################## ########################################## # # Find all patients whose first name contains # the letter 'a' and their last name starts with 'j' # (hint: expect 2 rows) # ########################################## SELECT * FROM pat_info WHERE P_FirstName LIKE '%a%' AND P_LastName LIKE 'j%'; +------+-------------+------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+------------+---------+----------+--------+-----------+ | 6 | William | Jordan | 4 | 10 | 4 | 19 | | 35 | Micheal | Jackson | 5 | 62 | 17 | 47 | +------+-------------+------------+---------+----------+--------+-----------+ ###### Question No. 9 ################################################## ########################################## # # Find all patients whose first name contains # the letter 'e' and their last name does NOT # contain the letter 'e' # (hint: expect 11 rows) # ########################################## SELECT * FROM pat_info WHERE P_FirstName LIKE '%e%' AND P_LastName NOT LIKE '%e%'; +------+-------------+------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+------------+---------+----------+--------+-----------+ | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | | 5 | Michael | Robinson | 1 | 13 | 6 | 18 | | 8 | Mehdi | Kharrazi | 2 | 1 | 9 | 21 | | 14 | Catherin | Catholicy | 7 | 28 | 15 | 33 | | 21 | Josef | Koopy | 1 | 35 | 1 | 50 | | 26 | Keyty | Orr | 8 | 40 | 24 | 36 | | 35 | Micheal | Jackson | 5 | 62 | 17 | 47 | | 38 | Withney | Houston | 12 | 63 | 19 | 28 | | 44 | Tuet | Iaswithin | 7 | 50 | 30 | 53 | | 47 | Peter | Yu | 1 | 101 | 16 | 62 | +------+-------------+------------+---------+----------+--------+-----------+ ###### Question No. 10 ################################################## ########################################## # # Find all patients whose first name contains # the letter 'e' and their last name does NOT # contain the letters 'e', 'a' and 'o' # (hint: expect 1 row) # ########################################## SELECT * FROM pat_info WHERE P_FirstName LIKE '%e%' AND P_LastName NOT LIKE '%e%' AND P_LastName NOT LIKE '%a%' AND P_LastName NOT LIKE '%o%'; +------+-------------+------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+------------+---------+----------+--------+-----------+ | 47 | Peter | Yu | 1 | 101 | 16 | 62 | +------+-------------+------------+---------+----------+--------+-----------+ ###### Question No. 11 ################################################## ########################################## # # Find all patients whose last name starts with # the letter 'a' and sort them based on the # last names # (hint: expect 4 rows) # ########################################## SELECT * FROM pat_info WHERE P_LastName LIKE 'a%' ORDER BY P_LastName; +------+-------------+------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+------------+---------+----------+--------+-----------+ | 22 | Karim | Abdoljabar | 11 | 36 | 35 | 49 | | 13 | Brian | Adam | 5 | 27 | 13 | 26 | | 27 | Castadilla | Almondo | 7 | 44 | 35 | 41 | | 33 | Cristina | Augelira | 6 | 45 | 23 | 29 | +------+-------------+------------+---------+----------+--------+-----------+ ###### Question No. 12 ################################################## ########################################## # # Find all patients who live in city_id 1 # and sort them initially based on their last # name and then based on their first name # (hint: expect 15 rows) # ########################################## SELECT * FROM pat_info WHERE City_id = 1 ORDER BY P_LastName, P_FirstName; +------+-------------+------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+------------+---------+----------+--------+-----------+ | 29 | Robin | Carrier | 1 | 54 | 12 | 40 | | 48 | Bill | Clincton | 1 | 51 | 29 | 60 | | 19 | Hamilton | Green | 1 | 33 | 4 | 44 | | 28 | Sam | Hill | 1 | 53 | 11 | 30 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | | 20 | Isaac | Killiam | 1 | 34 | 25 | 35 | | 21 | Josef | Koopy | 1 | 35 | 1 | 50 | | 9 | John | McKinsy | 1 | 9 | 10 | 22 | | 7 | Susan | McKinsy | 1 | 2 | 5 | 20 | | 50 | Qu | Minxoush | 1 | 48 | 15 | 45 | | 5 | Michael | Robinson | 1 | 13 | 6 | 18 | | 23 | Liliam | Toram | 1 | 37 | 14 | 31 | | 49 | Woo | Xingho | 1 | 52 | 28 | 61 | | 47 | Peter | Yu | 1 | 101 | 16 | 62 | +------+-------------+------------+---------+----------+--------+-----------+ ###### Question No. 13 ################################################## ########################################## # # Find all patients whose first name is one # of these names: 'John', 'Sam', 'Peter' # and are sorted based on first names # (hint: expect 6 rows) # ########################################## SELECT * FROM pat_info WHERE P_FirstName IN ('John', 'Sam', 'Peter') ORDER BY P_FirstName; +------+-------------+------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+------------+---------+----------+--------+-----------+ | 4 | John | McDonnald | 5 | 8 | 3 | 17 | | 9 | John | McKinsy | 1 | 9 | 10 | 22 | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | | 47 | Peter | Yu | 1 | 101 | 16 | 62 | | 28 | Sam | Hill | 1 | 53 | 11 | 30 | +------+-------------+------------+---------+----------+--------+-----------+ ###### Question No. 14 ################################################## ########################################## # # Find only the first name and last name # of the patients whose first # name contain the letter ‘j’ but their # family name does NOT include the word # ‘donnald’ # (hint: expect 3 rows) # ########################################## SELECT P_FirstName, P_LastName FROM pat_info WHERE P_FirstName LIKE '%J%' AND P_LastName NOT LIKE '%donnald%'; +-------------+------------+ | P_FirstName | P_LastName | +-------------+------------+ | John | McKinsy | | Josef | Koopy | | Yjie | Lee | +-------------+------------+ ###### Question No. 15 ################################################## ########################################## # # Find the first 10 patients whose city_id # is 1 or 3 while sorting them based on # their family name. # (hint: expect 10 rows!) # ########################################## SELECT * FROM pat_info WHERE City_id=1 OR City_id=3 ORDER BY P_LastName LIMIT 10; +------+-------------+------------+---------+----------+--------+-----------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | +------+-------------+------------+---------+----------+--------+-----------+ | 29 | Robin | Carrier | 1 | 54 | 12 | 40 | | 48 | Bill | Clincton | 1 | 51 | 29 | 60 | | 39 | Oliseagan | Dysee | 3 | 70 | 32 | 56 | | 46 | Uve | Evalinson | 3 | 47 | 19 | 27 | | 19 | Hamilton | Green | 1 | 33 | 4 | 44 | | 3 | Sara | Henson | 3 | 6 | 2 | 16 | | 28 | Sam | Hill | 1 | 53 | 11 | 30 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | | 20 | Isaac | Killiam | 1 | 34 | 25 | 35 | +------+-------------+------------+---------+----------+--------+-----------+ ###### Question No. 16 ################################################## ########################################## # # Use the following query: # SELECT * FROM pat_info,city_info # WHERE pat_info.City_id = city_info.City_id # What do you understand from this # complex query? # ########################################## This query JOINS the realted rows from the pat_info and city_info tables based on the City_id attribute (column) ###### Question No. 17 ################################################## ########################################## # # Can you modify the last query so it # shows the phone information from all # of the patients? # (hint: expect 49 rows!) # ########################################## SELECT * FROM pat_info,phone_info WHERE pat_info.Phone_id = phone_info.Phone_id; +------+-------------+--------------+---------+----------+--------+-----------+----------+------------+--------------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | Phone_id | Phone_Home | Phone_Office | +------+-------------+--------------+---------+----------+--------+-----------+----------+------------+--------------+ | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 4 | 3245686 | 3241212 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 13 | 4553849 | 4563981 | | 3 | Sara | Henson | 3 | 6 | 2 | 16 | 6 | 6048978 | 6048793 | | 4 | John | McDonnald | 5 | 8 | 3 | 17 | 8 | 4445667 | | | 5 | Michael | Robinson | 1 | 13 | 6 | 18 | 13 | 4553849 | 4563981 | | 6 | William | Jordan | 4 | 10 | 4 | 19 | 10 | 3123333 | 3123432 | | 7 | Susan | McKinsy | 1 | 2 | 5 | 20 | 2 | 3246798 | 3258976 | | 8 | Mehdi | Kharrazi | 2 | 1 | 9 | 21 | 1 | 4486747 | 4443412 | | 9 | John | McKinsy | 1 | 9 | 10 | 22 | 9 | 4245615 | | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | 18 | 8724957 | 8724957 | | 11 | Pat | Bentatar | 7 | 25 | 8 | 24 | 25 | 2224252 | | | 12 | Abraham | Lincoln | 3 | 26 | 27 | 25 | 26 | 4534534 | 3453455 | | 14 | Catherin | Catholicy | 7 | 28 | 15 | 33 | 28 | 3243544 | 3656789 | | 15 | Demi | Moore | 12 | 29 | 23 | 34 | 29 | 3334456 | 3452345 | | 16 | Ebi | Farahanzadeh | 11 | 30 | 26 | 42 | 30 | 5674457 | 5346373 | | 17 | Fery | Sea | 12 | 31 | 14 | 43 | 31 | 4321354 | 4321521 | | 18 | Graham | Bell | 12 | 32 | 12 | 32 | 32 | 6543654 | 6734788 | | 19 | Hamilton | Green | 1 | 33 | 4 | 44 | 33 | 4676476 | 7634377 | | 20 | Isaac | Killiam | 1 | 34 | 25 | 35 | 34 | 7526566 | 7257422 | | 21 | Josef | Koopy | 1 | 35 | 1 | 50 | 35 | 9858958 | | | 22 | Karim | Abdoljabar | 11 | 36 | 35 | 49 | 36 | 8457845 | 8754848 | | 23 | Liliam | Toram | 1 | 37 | 14 | 31 | 37 | 5235466 | 5435666 | | 24 | Mandy | Moore | 6 | 38 | 14 | 51 | 38 | 6525666 | 6234566 | | 25 | Nancy | McLachlan | 9 | 39 | 26 | 63 | 39 | 4546325 | 4566566 | | 26 | Keyty | Orr | 8 | 40 | 24 | 36 | 40 | | 2564356 | | 27 | Castadilla | Almondo | 7 | 44 | 35 | 41 | 44 | 7437437 | 6743774 | | 28 | Sam | Hill | 1 | 53 | 11 | 30 | 53 | 6523777 | 6666236 | | 29 | Robin | Carrier | 1 | 54 | 12 | 40 | 54 | 1235467 | 1264527 | | 30 | Romeo | Khoseo | 8 | 57 | 34 | 48 | 57 | 5423455 | 5425443 | | 31 | Diana | Fedrensinco | 7 | 56 | 15 | 39 | 56 | 2354436 | 2346523 | | 32 | Lama | Buda | 9 | 60 | 18 | 58 | 60 | 4968445 | 4089699 | | 33 | Cristina | Augelira | 6 | 45 | 23 | 29 | 45 | 7643654 | 7636777 | | 34 | Faith | Hill | 10 | 61 | 12 | 59 | 61 | 4045676 | 4056785 | | 35 | Micheal | Jackson | 5 | 62 | 17 | 47 | 62 | 2024566 | 2072666 | | 36 | Steve | Wanderhal | 11 | 65 | 16 | 38 | 65 | 2343455 | 2343454 | | 37 | Mina | Namiri | 4 | 99 | 33 | 57 | 99 | 4056788 | | | 38 | Withney | Houston | 12 | 63 | 19 | 28 | 63 | 3525627 | | | 39 | Oliseagan | Dysee | 3 | 70 | 32 | 56 | 70 | 3565663 | 35653666 | | 40 | Sampali | Sirini | 7 | 46 | 22 | 55 | 46 | 8458458 | 8547845 | | 41 | Steve | Waterson | 8 | 49 | 21 | 37 | 49 | 6546766 | 6467777 | | 42 | Nisan | Yagamachi | 9 | 82 | 20 | 52 | 82 | 656543 | 3453566 | | 43 | Yjie | Lee | 8 | 84 | 16 | 46 | 84 | 5646666 | 3563566 | | 44 | Tuet | Iaswithin | 7 | 50 | 30 | 53 | 50 | 4564567 | 4532635 | | 45 | Zinc | Goldman | 4 | 95 | 31 | 54 | 95 | 6145747 | 614678 | | 46 | Uve | Evalinson | 3 | 47 | 19 | 27 | 47 | 4578458 | 4578488 | | 47 | Peter | Yu | 1 | 101 | 16 | 62 | 101 | 5987607 | 5097668 | | 48 | Bill | Clincton | 1 | 51 | 29 | 60 | 51 | 5654356 | 5235623 | | 49 | Woo | Xingho | 1 | 52 | 28 | 61 | 52 | 5432455 | 5234234 | | 50 | Qu | Minxoush | 1 | 48 | 15 | 45 | 48 | 5788854 | 7654788 | +------+-------------+--------------+---------+----------+--------+-----------+----------+------------+--------------+