########################################## # # MySQL (2) : In class exercise # # Use the sample database (sample_data.sql) # if you are required to use a database # ########################################## ###### Question No. 1 ################################################## #################################################### # # Show patients P_id, P_FirstName, P_LastName, and City_ID # for those whose City_id is 4 # (hint: use pat_info; expect 3 rows) # #################################################### SELECT pat_info.P_id, pat_info.P_FirstName, pat_info.P_LastName, pat_info.City_id FROM pat_info WHERE pat_info.City_id = 4; +------+-------------+------------+---------+ | P_id | P_FirstName | P_LastName | City_id | +------+-------------+------------+---------+ | 6 | William | Jordan | 4 | | 37 | Mina | Namiri | 4 | | 45 | Zinc | Goldman | 4 | +------+-------------+------------+---------+ ###### Question No. 2 ################################################## #################################################### # # Show all patients with their city information # (hint: use pat_info, city_info; expect 50 rows) # #################################################### SELECT * FROM pat_info, city_info WHERE pat_info.city_id = city_info.city_id; +------+-------------+--------------+---------+----------+--------+-----------+---------+-------------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | City_id | City_Name | +------+-------------+--------------+---------+----------+--------+-----------+---------+-------------+ | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 1 | Halifax | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 1 | Halifax | | 3 | Sara | Henson | 3 | 6 | 2 | 16 | 3 | Toronto | | 4 | John | McDonnald | 5 | 8 | 3 | 17 | 5 | Quebec | | 5 | Michael | Robinson | 1 | 13 | 6 | 18 | 1 | Halifax | | 6 | William | Jordan | 4 | 10 | 4 | 19 | 4 | Montreal | | 7 | Susan | McKinsy | 1 | 2 | 5 | 20 | 1 | Halifax | | 8 | Mehdi | Kharrazi | 2 | 1 | 9 | 21 | 2 | Vancouer | | 9 | John | McKinsy | 1 | 9 | 10 | 22 | 1 | Halifax | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | 3 | Toronto | | 11 | Pat | Bentatar | 7 | 25 | 8 | 24 | 7 | Calgary | | 12 | Abraham | Lincoln | 3 | 26 | 27 | 25 | 3 | Toronto | | 13 | Brian | Adam | 5 | 27 | 13 | 26 | 5 | Quebec | | 14 | Catherin | Catholicy | 7 | 28 | 15 | 33 | 7 | Calgary | | 15 | Demi | Moore | 12 | 29 | 23 | 34 | 12 | Boston | | 16 | Ebi | Farahanzadeh | 11 | 30 | 26 | 42 | 11 | Chicago | | 17 | Fery | Sea | 12 | 31 | 14 | 43 | 12 | Boston | | 18 | Graham | Bell | 12 | 32 | 12 | 32 | 12 | Boston | | 19 | Hamilton | Green | 1 | 33 | 4 | 44 | 1 | Halifax | | 20 | Isaac | Killiam | 1 | 34 | 25 | 35 | 1 | Halifax | | 21 | Josef | Koopy | 1 | 35 | 1 | 50 | 1 | Halifax | | 22 | Karim | Abdoljabar | 11 | 36 | 35 | 49 | 11 | Chicago | | 23 | Liliam | Toram | 1 | 37 | 14 | 31 | 1 | Halifax | | 24 | Mandy | Moore | 6 | 38 | 14 | 51 | 6 | Winnipig | | 25 | Nancy | McLachlan | 9 | 39 | 26 | 63 | 9 | New York | | 26 | Keyty | Orr | 8 | 40 | 24 | 36 | 8 | Sydney | | 27 | Castadilla | Almondo | 7 | 44 | 35 | 41 | 7 | Calgary | | 28 | Sam | Hill | 1 | 53 | 11 | 30 | 1 | Halifax | | 29 | Robin | Carrier | 1 | 54 | 12 | 40 | 1 | Halifax | | 30 | Romeo | Khoseo | 8 | 57 | 34 | 48 | 8 | Sydney | | 31 | Diana | Fedrensinco | 7 | 56 | 15 | 39 | 7 | Calgary | | 32 | Lama | Buda | 9 | 60 | 18 | 58 | 9 | New York | | 33 | Cristina | Augelira | 6 | 45 | 23 | 29 | 6 | Winnipig | | 34 | Faith | Hill | 10 | 61 | 12 | 59 | 10 | Los Angeles | | 35 | Micheal | Jackson | 5 | 62 | 17 | 47 | 5 | Quebec | | 36 | Steve | Wanderhal | 11 | 65 | 16 | 38 | 11 | Chicago | | 37 | Mina | Namiri | 4 | 99 | 33 | 57 | 4 | Montreal | | 38 | Withney | Houston | 12 | 63 | 19 | 28 | 12 | Boston | | 39 | Oliseagan | Dysee | 3 | 70 | 32 | 56 | 3 | Toronto | | 40 | Sampali | Sirini | 7 | 46 | 22 | 55 | 7 | Calgary | | 41 | Steve | Waterson | 8 | 49 | 21 | 37 | 8 | Sydney | | 42 | Nisan | Yagamachi | 9 | 82 | 20 | 52 | 9 | New York | | 43 | Yjie | Lee | 8 | 84 | 16 | 46 | 8 | Sydney | | 44 | Tuet | Iaswithin | 7 | 50 | 30 | 53 | 7 | Calgary | | 45 | Zinc | Goldman | 4 | 95 | 31 | 54 | 4 | Montreal | | 46 | Uve | Evalinson | 3 | 47 | 19 | 27 | 3 | Toronto | | 47 | Peter | Yu | 1 | 101 | 16 | 62 | 1 | Halifax | | 48 | Bill | Clincton | 1 | 51 | 29 | 60 | 1 | Halifax | | 49 | Woo | Xingho | 1 | 52 | 28 | 61 | 1 | Halifax | | 50 | Qu | Minxoush | 1 | 48 | 15 | 45 | 1 | Halifax | +------+-------------+--------------+---------+----------+--------+-----------+---------+-------------+ ###### Question No. 3 ################################################## #################################################### # # Show the Patient's P_id, P_FirstName, P_LastName, and City_Name # for those whose City_Name is 'Halifax' # (hint: use pat_info, city_info; expect 15 rows) # #################################################### SELECT pat_info.P_id, pat_info.P_FirstName, pat_info.P_LastName, city_info.City_Name FROM pat_info, city_info WHERE pat_info.City_id = city_info.City_id AND city_info.City_Name = 'Halifax'; +------+-------------+------------+-----------+ | P_id | P_FirstName | P_LastName | City_Name | +------+-------------+------------+-----------+ | 1 | Peter | Johnsons | Halifax | | 2 | Mike | Jackson | Halifax | | 5 | Michael | Robinson | Halifax | | 7 | Susan | McKinsy | Halifax | | 9 | John | McKinsy | Halifax | | 19 | Hamilton | Green | Halifax | | 20 | Isaac | Killiam | Halifax | | 21 | Josef | Koopy | Halifax | | 23 | Liliam | Toram | Halifax | | 28 | Sam | Hill | Halifax | | 29 | Robin | Carrier | Halifax | | 47 | Peter | Yu | Halifax | | 48 | Bill | Clincton | Halifax | | 49 | Woo | Xingho | Halifax | | 50 | Qu | Minxoush | Halifax | +------+-------------+------------+-----------+ ###### Question No. 4 ################################################## #################################################### # # Show patients P_id, P_FirstName, P_LastName, and City_Name # for those whose first name contains the letter 'j' # and live in 'Toronto' # (hint: use pat_info; expect 1 row) # #################################################### SELECT pat_info.P_id, pat_info.P_FirstName, pat_info.P_LastName, city_info.City_Name FROM pat_info, city_info WHERE pat_info.City_id = city_info.City_id AND pat_info.P_FirstName LIKE '%j%' AND city_info.City_Name = 'Toronto'; +------+-------------+------------+-----------+ | P_id | P_FirstName | P_LastName | City_Name | +------+-------------+------------+-----------+ | 10 | John | McDonnald | Toronto | +------+-------------+------------+-----------+ ###### Question No. 5 ################################################## #################################################### # # Show the number of patients who reside in 'Halifax' # (hint: use pat_info, city_info; expect 1 row) # #################################################### SELECT COUNT(*) FROM pat_info, city_info WHERE pat_info.City_id = city_info.City_id AND city_info.City_Name = 'Halifax'; +----------+ | COUNT(*) | +----------+ | 15 | +----------+ ###### Question No. 6 ################################################## #################################################### # # Show the Patient's P_id, P_FirstName, P_LastName and # their related Doctor's D_id, D_FirstName and D_LastName # for all of the patients # (hint: use pat_info, doc_info, pat_doc_relate; expect 80 rows) # #################################################### SELECT pat_info.P_id, pat_info.P_FirstName, pat_info.P_LastName, doc_info.D_id, doc_info.D_FirstName, doc_info.D_LastName FROM pat_info, doc_info, pat_doc_relate WHERE pat_info.P_id = pat_doc_relate.P_id AND doc_info.D_id = pat_doc_relate.D_id; +------+-------------+--------------+------+-------------+------------+ | P_id | P_FirstName | P_LastName | D_id | D_FirstName | D_LastName | +------+-------------+--------------+------+-------------+------------+ | 1 | Peter | Johnsons | 4 | Marry | Johnson | | 1 | Peter | Johnsons | 5 | Yu | Lee | | 1 | Peter | Johnsons | 6 | Nancy | McIssac | | 2 | Mike | Jackson | 1 | Hadi | Kharrazi | | 3 | Sara | Henson | 9 | Susan | Waterloo | | 4 | John | McDonnald | 1 | Hadi | Kharrazi | | 4 | John | McDonnald | 2 | John | McDonnald | | 4 | John | McDonnald | 10 | Samuel | Jackson | | 5 | Michael | Robinson | 5 | Yu | Lee | | 6 | William | Jordan | 2 | John | McDonnald | | 7 | Susan | McKinsy | 6 | Nancy | McIssac | | 8 | Mehdi | Kharrazi | 3 | Robin | Dorby | | 8 | Mehdi | Kharrazi | 7 | John | Peterson | | 9 | John | McKinsy | 10 | Samuel | Jackson | | 10 | John | McDonnald | 4 | Marry | Johnson | | 11 | Pat | Bentatar | 6 | Nancy | McIssac | | 12 | Abraham | Lincoln | 7 | John | Peterson | | 12 | Abraham | Lincoln | 8 | Jane | Peterson | | 13 | Brian | Adam | 9 | Susan | Waterloo | | 14 | Catherin | Catholicy | 2 | John | McDonnald | | 14 | Catherin | Catholicy | 6 | Nancy | McIssac | | 15 | Demi | Moore | 4 | Marry | Johnson | | 15 | Demi | Moore | 5 | Yu | Lee | | 16 | Ebi | Farahanzadeh | 6 | Nancy | McIssac | | 17 | Fery | Sea | 8 | Jane | Peterson | | 18 | Graham | Bell | 4 | Marry | Johnson | | 18 | Graham | Bell | 5 | Yu | Lee | | 19 | Hamilton | Green | 4 | Marry | Johnson | | 20 | Isaac | Killiam | 3 | Robin | Dorby | | 20 | Isaac | Killiam | 6 | Nancy | McIssac | | 21 | Josef | Koopy | 2 | John | McDonnald | | 22 | Karim | Abdoljabar | 6 | Nancy | McIssac | | 23 | Liliam | Toram | 5 | Yu | Lee | | 23 | Liliam | Toram | 6 | Nancy | McIssac | | 23 | Liliam | Toram | 9 | Susan | Waterloo | | 24 | Mandy | Moore | 4 | Marry | Johnson | | 25 | Nancy | McLachlan | 10 | Samuel | Jackson | | 26 | Keyty | Orr | 3 | Robin | Dorby | | 26 | Keyty | Orr | 6 | Nancy | McIssac | | 26 | Keyty | Orr | 10 | Samuel | Jackson | | 27 | Castadilla | Almondo | 8 | Jane | Peterson | | 28 | Sam | Hill | 9 | Susan | Waterloo | | 29 | Robin | Carrier | 2 | John | McDonnald | | 29 | Robin | Carrier | 3 | Robin | Dorby | | 30 | Romeo | Khoseo | 2 | John | McDonnald | | 30 | Romeo | Khoseo | 4 | Marry | Johnson | | 31 | Diana | Fedrensinco | 3 | Robin | Dorby | | 31 | Diana | Fedrensinco | 5 | Yu | Lee | | 32 | Lama | Buda | 5 | Yu | Lee | | 33 | Cristina | Augelira | 5 | Yu | Lee | | 33 | Cristina | Augelira | 6 | Nancy | McIssac | | 33 | Cristina | Augelira | 10 | Samuel | Jackson | | 34 | Faith | Hill | 7 | John | Peterson | | 35 | Micheal | Jackson | 8 | Jane | Peterson | | 36 | Steve | Wanderhal | 6 | Nancy | McIssac | | 37 | Mina | Namiri | 6 | Nancy | McIssac | | 37 | Mina | Namiri | 7 | John | Peterson | | 37 | Mina | Namiri | 10 | Samuel | Jackson | | 38 | Withney | Houston | 4 | Marry | Johnson | | 38 | Withney | Houston | 7 | John | Peterson | | 39 | Oliseagan | Dysee | 5 | Yu | Lee | | 39 | Oliseagan | Dysee | 7 | John | Peterson | | 40 | Sampali | Sirini | 5 | Yu | Lee | | 41 | Steve | Waterson | 2 | John | McDonnald | | 41 | Steve | Waterson | 5 | Yu | Lee | | 42 | Nisan | Yagamachi | 4 | Marry | Johnson | | 43 | Yjie | Lee | 10 | Samuel | Jackson | | 44 | Tuet | Iaswithin | 1 | Hadi | Kharrazi | | 44 | Tuet | Iaswithin | 4 | Marry | Johnson | | 45 | Zinc | Goldman | 2 | John | McDonnald | | 46 | Uve | Evalinson | 1 | Hadi | Kharrazi | | 47 | Peter | Yu | 1 | Hadi | Kharrazi | | 47 | Peter | Yu | 7 | John | Peterson | | 48 | Bill | Clincton | 1 | Hadi | Kharrazi | | 48 | Bill | Clincton | 3 | Robin | Dorby | | 49 | Woo | Xingho | 4 | Marry | Johnson | | 49 | Woo | Xingho | 10 | Samuel | Jackson | | 50 | Qu | Minxoush | 1 | Hadi | Kharrazi | | 50 | Qu | Minxoush | 7 | John | Peterson | | 50 | Qu | Minxoush | 10 | Samuel | Jackson | +------+-------------+--------------+------+-------------+------------+ ###### Question No. 7 ################################################## #################################################### # # Show the Patient's P_id, P_FirstName, P_LastName and # their related Doctor's D_id, D_FirstName and D_LastName for # all of the patients who their P_FirstName starts with 'P' # (hint: use pat_info, doc_info, pat_doc_relate; expect 6 rows) # #################################################### SELECT pat_info.P_id, pat_info.P_FirstName, pat_info.P_LastName, doc_info.D_id, doc_info.D_FirstName, doc_info.D_LastName FROM pat_info, doc_info, pat_doc_relate WHERE pat_info.P_id = pat_doc_relate.P_id AND doc_info.D_id = pat_doc_relate.D_id AND pat_info.P_FirstName LIKE 'P%'; +------+-------------+------------+------+-------------+------------+ | P_id | P_FirstName | P_LastName | D_id | D_FirstName | D_LastName | +------+-------------+------------+------+-------------+------------+ | 1 | Peter | Johnsons | 4 | Marry | Johnson | | 1 | Peter | Johnsons | 5 | Yu | Lee | | 1 | Peter | Johnsons | 6 | Nancy | McIssac | | 11 | Pat | Bentatar | 6 | Nancy | McIssac | | 47 | Peter | Yu | 1 | Hadi | Kharrazi | | 47 | Peter | Yu | 7 | John | Peterson | +------+-------------+------------+------+-------------+------------+ ###### Question No. 8 ################################################## #################################################### # # Show 'Peter Johnsons' P_id, P_FirstName, P_LastName and # the number of doctors he has. # (hint: use pat_info, pat_doc_relate; expect 1 row) # #################################################### SELECT pat_info.P_id, pat_info.P_FirstName, pat_info.P_LastName, COUNT(pat_doc_relate.D_id) FROM pat_info, pat_doc_relate WHERE pat_info.P_id = pat_doc_relate.P_id AND pat_info.P_FirstName = 'Peter' AND pat_info.P_LastName = 'Johnsons' GROUP BY pat_doc_relate.P_id; +------+-------------+------------+----------------------------+ | P_id | P_FirstName | P_LastName | COUNT(pat_doc_relate.D_id) | +------+-------------+------------+----------------------------+ | 1 | Peter | Johnsons | 3 | +------+-------------+------------+----------------------------+ ###### Question No. 9 ################################################## #################################################### # # Show everything (Patient Information and Lab # Information) from those patients who have lab tests # (hint: use pat_info, lab_info; expect 29 rows) # #################################################### SELECT * FROM pat_info, lab_info WHERE pat_info.P_id = lab_info.P_id +------+-------------+------------+---------+----------+--------+-----------+------+------+-----+------+--------+------------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | L_id | P_id | RBC | WBC | PLT | Date | +------+-------------+------------+---------+----------+--------+-----------+------+------+-----+------+--------+------------+ | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 1 | 1 | 4.8 | 8.1 | 248000 | 2005-05-09 | | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 2 | 1 | 4.3 | 10.9 | 325000 | 2005-05-19 | | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 3 | 1 | 5.6 | 11 | 129000 | 2005-06-03 | | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 4 | 1 | 3.4 | 6.9 | 399000 | 2005-06-24 | | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 5 | 1 | 7.4 | 10.2 | 198000 | 2005-09-10 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 6 | 2 | 5.2 | 13 | 452000 | 2005-04-10 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 7 | 2 | 4.5 | 12.5 | 525000 | 2005-04-12 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 8 | 2 | 6.7 | 11.9 | 540000 | 2005-04-29 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 9 | 2 | 5.6 | 12.9 | 478000 | 2005-05-12 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 10 | 2 | 5.7 | 13.8 | 433000 | 2005-08-03 | | 3 | Sara | Henson | 3 | 6 | 2 | 16 | 11 | 3 | 3.4 | 5.7 | 320000 | 2005-07-08 | | 3 | Sara | Henson | 3 | 6 | 2 | 16 | 12 | 3 | 3.3 | 5.9 | 327000 | 2005-07-16 | | 4 | John | McDonnald | 5 | 8 | 3 | 17 | 13 | 4 | 5 | 8.9 | 195000 | 2005-08-30 | | 5 | Michael | Robinson | 1 | 13 | 6 | 18 | 14 | 5 | 5.8 | 9 | 275000 | 2005-03-09 | | 5 | Michael | Robinson | 1 | 13 | 6 | 18 | 15 | 5 | 5.9 | 9.8 | 287000 | 2005-06-19 | | 5 | Michael | Robinson | 1 | 13 | 6 | 18 | 16 | 5 | 6 | 9.9 | 299000 | 2005-09-19 | | 6 | William | Jordan | 4 | 10 | 4 | 19 | 17 | 6 | 7.2 | 6.1 | 324000 | 2005-02-14 | | 6 | William | Jordan | 4 | 10 | 4 | 19 | 18 | 6 | 6.9 | 6 | 365000 | 2005-03-14 | | 6 | William | Jordan | 4 | 10 | 4 | 19 | 19 | 6 | 6.7 | 6.2 | 356000 | 2005-04-15 | | 6 | William | Jordan | 4 | 10 | 4 | 19 | 20 | 6 | 6.6 | 6.2 | 312000 | 2005-05-14 | | 7 | Susan | McKinsy | 1 | 2 | 5 | 20 | 21 | 7 | 3.5 | 5.4 | 223000 | 2005-04-17 | | 7 | Susan | McKinsy | 1 | 2 | 5 | 20 | 22 | 7 | 3.7 | 5.3 | 324000 | 2005-08-09 | | 8 | Mehdi | Kharrazi | 2 | 1 | 9 | 21 | 23 | 8 | 5.6 | 5.9 | 123000 | 2005-09-10 | | 8 | Mehdi | Kharrazi | 2 | 1 | 9 | 21 | 24 | 8 | 4.7 | 6 | 67000 | 2005-09-11 | | 9 | John | McKinsy | 1 | 9 | 10 | 22 | 25 | 9 | 4.9 | 8.7 | 354000 | 2005-09-12 | | 9 | John | McKinsy | 1 | 9 | 10 | 22 | 26 | 9 | 5 | 8.8 | 322000 | 2005-09-13 | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | 27 | 10 | 4.4 | 3.4 | 23000 | 2005-08-04 | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | 28 | 10 | 4.4 | 3.1 | 68000 | 2005-09-11 | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | 29 | 10 | 4.5 | 2.9 | 78000 | 2005-09-13 | +------+-------------+------------+---------+----------+--------+-----------+------+------+-----+------+--------+------------+ ###### Question No. 10 ################################################## #################################################### # # Show all patients and their lab results (if available) # (hint: use pat_info, lab_info; expect 69 rows) # #################################################### SELECT * FROM pat_info LEFT JOIN lab_info ON (pat_info.P_id = lab_info.P_id); +------+-------------+--------------+---------+----------+--------+-----------+------+------+------+------+--------+------------+ | P_id | P_FirstName | P_LastName | City_id | Phone_id | Kin_id | Client_id | L_id | P_id | RBC | WBC | PLT | Date | +------+-------------+--------------+---------+----------+--------+-----------+------+------+------+------+--------+------------+ | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 1 | 1 | 4.8 | 8.1 | 248000 | 2005-05-09 | | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 2 | 1 | 4.3 | 10.9 | 325000 | 2005-05-19 | | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 3 | 1 | 5.6 | 11 | 129000 | 2005-06-03 | | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 4 | 1 | 3.4 | 6.9 | 399000 | 2005-06-24 | | 1 | Peter | Johnsons | 1 | 4 | 6 | 14 | 5 | 1 | 7.4 | 10.2 | 198000 | 2005-09-10 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 6 | 2 | 5.2 | 13 | 452000 | 2005-04-10 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 7 | 2 | 4.5 | 12.5 | 525000 | 2005-04-12 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 8 | 2 | 6.7 | 11.9 | 540000 | 2005-04-29 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 9 | 2 | 5.6 | 12.9 | 478000 | 2005-05-12 | | 2 | Mike | Jackson | 1 | 13 | 6 | 15 | 10 | 2 | 5.7 | 13.8 | 433000 | 2005-08-03 | | 3 | Sara | Henson | 3 | 6 | 2 | 16 | 11 | 3 | 3.4 | 5.7 | 320000 | 2005-07-08 | | 3 | Sara | Henson | 3 | 6 | 2 | 16 | 12 | 3 | 3.3 | 5.9 | 327000 | 2005-07-16 | | 4 | John | McDonnald | 5 | 8 | 3 | 17 | 13 | 4 | 5 | 8.9 | 195000 | 2005-08-30 | | 5 | Michael | Robinson | 1 | 13 | 6 | 18 | 14 | 5 | 5.8 | 9 | 275000 | 2005-03-09 | | 5 | Michael | Robinson | 1 | 13 | 6 | 18 | 15 | 5 | 5.9 | 9.8 | 287000 | 2005-06-19 | | 5 | Michael | Robinson | 1 | 13 | 6 | 18 | 16 | 5 | 6 | 9.9 | 299000 | 2005-09-19 | | 6 | William | Jordan | 4 | 10 | 4 | 19 | 17 | 6 | 7.2 | 6.1 | 324000 | 2005-02-14 | | 6 | William | Jordan | 4 | 10 | 4 | 19 | 18 | 6 | 6.9 | 6 | 365000 | 2005-03-14 | | 6 | William | Jordan | 4 | 10 | 4 | 19 | 19 | 6 | 6.7 | 6.2 | 356000 | 2005-04-15 | | 6 | William | Jordan | 4 | 10 | 4 | 19 | 20 | 6 | 6.6 | 6.2 | 312000 | 2005-05-14 | | 7 | Susan | McKinsy | 1 | 2 | 5 | 20 | 21 | 7 | 3.5 | 5.4 | 223000 | 2005-04-17 | | 7 | Susan | McKinsy | 1 | 2 | 5 | 20 | 22 | 7 | 3.7 | 5.3 | 324000 | 2005-08-09 | | 8 | Mehdi | Kharrazi | 2 | 1 | 9 | 21 | 23 | 8 | 5.6 | 5.9 | 123000 | 2005-09-10 | | 8 | Mehdi | Kharrazi | 2 | 1 | 9 | 21 | 24 | 8 | 4.7 | 6 | 67000 | 2005-09-11 | | 9 | John | McKinsy | 1 | 9 | 10 | 22 | 25 | 9 | 4.9 | 8.7 | 354000 | 2005-09-12 | | 9 | John | McKinsy | 1 | 9 | 10 | 22 | 26 | 9 | 5 | 8.8 | 322000 | 2005-09-13 | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | 27 | 10 | 4.4 | 3.4 | 23000 | 2005-08-04 | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | 28 | 10 | 4.4 | 3.1 | 68000 | 2005-09-11 | | 10 | John | McDonnald | 3 | 18 | 7 | 23 | 29 | 10 | 4.5 | 2.9 | 78000 | 2005-09-13 | | 11 | Pat | Bentatar | 7 | 25 | 8 | 24 | NULL | NULL | NULL | NULL | NULL | NULL | | 12 | Abraham | Lincoln | 3 | 26 | 27 | 25 | NULL | NULL | NULL | NULL | NULL | NULL | | 13 | Brian | Adam | 5 | 27 | 13 | 26 | NULL | NULL | NULL | NULL | NULL | NULL | | 14 | Catherin | Catholicy | 7 | 28 | 15 | 33 | NULL | NULL | NULL | NULL | NULL | NULL | | 15 | Demi | Moore | 12 | 29 | 23 | 34 | NULL | NULL | NULL | NULL | NULL | NULL | | 16 | Ebi | Farahanzadeh | 11 | 30 | 26 | 42 | NULL | NULL | NULL | NULL | NULL | NULL | | 17 | Fery | Sea | 12 | 31 | 14 | 43 | NULL | NULL | NULL | NULL | NULL | NULL | | 18 | Graham | Bell | 12 | 32 | 12 | 32 | NULL | NULL | NULL | NULL | NULL | NULL | | 19 | Hamilton | Green | 1 | 33 | 4 | 44 | NULL | NULL | NULL | NULL | NULL | NULL | | 20 | Isaac | Killiam | 1 | 34 | 25 | 35 | NULL | NULL | NULL | NULL | NULL | NULL | | 21 | Josef | Koopy | 1 | 35 | 1 | 50 | NULL | NULL | NULL | NULL | NULL | NULL | | 22 | Karim | Abdoljabar | 11 | 36 | 35 | 49 | NULL | NULL | NULL | NULL | NULL | NULL | | 23 | Liliam | Toram | 1 | 37 | 14 | 31 | NULL | NULL | NULL | NULL | NULL | NULL | | 24 | Mandy | Moore | 6 | 38 | 14 | 51 | NULL | NULL | NULL | NULL | NULL | NULL | | 25 | Nancy | McLachlan | 9 | 39 | 26 | 63 | NULL | NULL | NULL | NULL | NULL | NULL | | 26 | Keyty | Orr | 8 | 40 | 24 | 36 | NULL | NULL | NULL | NULL | NULL | NULL | | 27 | Castadilla | Almondo | 7 | 44 | 35 | 41 | NULL | NULL | NULL | NULL | NULL | NULL | | 28 | Sam | Hill | 1 | 53 | 11 | 30 | NULL | NULL | NULL | NULL | NULL | NULL | | 29 | Robin | Carrier | 1 | 54 | 12 | 40 | NULL | NULL | NULL | NULL | NULL | NULL | | 30 | Romeo | Khoseo | 8 | 57 | 34 | 48 | NULL | NULL | NULL | NULL | NULL | NULL | | 31 | Diana | Fedrensinco | 7 | 56 | 15 | 39 | NULL | NULL | NULL | NULL | NULL | NULL | | 32 | Lama | Buda | 9 | 60 | 18 | 58 | NULL | NULL | NULL | NULL | NULL | NULL | | 33 | Cristina | Augelira | 6 | 45 | 23 | 29 | NULL | NULL | NULL | NULL | NULL | NULL | | 34 | Faith | Hill | 10 | 61 | 12 | 59 | NULL | NULL | NULL | NULL | NULL | NULL | | 35 | Micheal | Jackson | 5 | 62 | 17 | 47 | NULL | NULL | NULL | NULL | NULL | NULL | | 36 | Steve | Wanderhal | 11 | 65 | 16 | 38 | NULL | NULL | NULL | NULL | NULL | NULL | | 37 | Mina | Namiri | 4 | 99 | 33 | 57 | NULL | NULL | NULL | NULL | NULL | NULL | | 38 | Withney | Houston | 12 | 63 | 19 | 28 | NULL | NULL | NULL | NULL | NULL | NULL | | 39 | Oliseagan | Dysee | 3 | 70 | 32 | 56 | NULL | NULL | NULL | NULL | NULL | NULL | | 40 | Sampali | Sirini | 7 | 46 | 22 | 55 | NULL | NULL | NULL | NULL | NULL | NULL | | 41 | Steve | Waterson | 8 | 49 | 21 | 37 | NULL | NULL | NULL | NULL | NULL | NULL | | 42 | Nisan | Yagamachi | 9 | 82 | 20 | 52 | NULL | NULL | NULL | NULL | NULL | NULL | | 43 | Yjie | Lee | 8 | 84 | 16 | 46 | NULL | NULL | NULL | NULL | NULL | NULL | | 44 | Tuet | Iaswithin | 7 | 50 | 30 | 53 | NULL | NULL | NULL | NULL | NULL | NULL | | 45 | Zinc | Goldman | 4 | 95 | 31 | 54 | NULL | NULL | NULL | NULL | NULL | NULL | | 46 | Uve | Evalinson | 3 | 47 | 19 | 27 | NULL | NULL | NULL | NULL | NULL | NULL | | 47 | Peter | Yu | 1 | 101 | 16 | 62 | NULL | NULL | NULL | NULL | NULL | NULL | | 48 | Bill | Clincton | 1 | 51 | 29 | 60 | NULL | NULL | NULL | NULL | NULL | NULL | | 49 | Woo | Xingho | 1 | 52 | 28 | 61 | NULL | NULL | NULL | NULL | NULL | NULL | | 50 | Qu | Minxoush | 1 | 48 | 15 | 45 | NULL | NULL | NULL | NULL | NULL | NULL | +------+-------------+--------------+---------+----------+--------+-----------+------+------+------+------+--------+------------+ ###### Question No. 11 ################################################## #################################################### # # Show the patient's P_id, P_FirstName, P_LastName and # thier maximum RBC and WBC for all patients # (hint: use pat_info, lab_info; expect 10 rows) # #################################################### SELECT pat_info.P_id, pat_info.P_FirstName, pat_info.P_LastName, ROUND(MAX(lab_info.RBC),1) RBCmax, ROUND(MAX(lab_info.WBC),1) WBCmax FROM pat_info, lab_info WHERE pat_info.P_id = lab_info.P_id GROUP BY lab_info.P_id; +------+-------------+------------+--------+--------+ | P_id | P_FirstName | P_LastName | RBCmax | WBCmax | +------+-------------+------------+--------+--------+ | 1 | Peter | Johnsons | 7.4 | 11.0 | | 2 | Mike | Jackson | 6.7 | 13.8 | | 3 | Sara | Henson | 3.4 | 5.9 | | 4 | John | McDonnald | 5.0 | 8.9 | | 5 | Michael | Robinson | 6.0 | 9.9 | | 6 | William | Jordan | 7.2 | 6.2 | | 7 | Susan | McKinsy | 3.7 | 5.4 | | 8 | Mehdi | Kharrazi | 5.6 | 6.0 | | 9 | John | McKinsy | 5.0 | 8.8 | | 10 | John | McDonnald | 4.5 | 3.4 | +------+-------------+------------+--------+--------+ ###### Question No. 12 ################################################## #################################################### # # Show the Patient's P_id, P_FirstName, P_LastName, # rounded (2 decimal) average RBC and WBC, # rounded (0 decimal) average PLT # for the patients who have a lab test available # (hint: use pat_info, lab_info; expect 10 rows) # #################################################### SELECT pat_info.P_id, pat_info.P_FirstName, pat_info.P_LastName, ROUND(AVG(lab_info.RBC),2) RBC, ROUND(AVG(lab_info.WBC),2) WBC, ROUND(AVG(lab_info.PLT),0) PLT FROM pat_info, lab_info WHERE pat_info.P_id = lab_info.P_id GROUP BY lab_info.P_id; +------+-------------+------------+------+-------+--------+ | P_id | P_FirstName | P_LastName | RBC | WBC | PLT | +------+-------------+------------+------+-------+--------+ | 1 | Peter | Johnsons | 5.10 | 9.42 | 259800 | | 2 | Mike | Jackson | 5.54 | 12.82 | 485600 | | 3 | Sara | Henson | 3.35 | 5.80 | 323500 | | 4 | John | McDonnald | 5.00 | 8.90 | 195000 | | 5 | Michael | Robinson | 5.90 | 9.57 | 287000 | | 6 | William | Jordan | 6.85 | 6.12 | 339250 | | 7 | Susan | McKinsy | 3.60 | 5.35 | 273500 | | 8 | Mehdi | Kharrazi | 5.15 | 5.95 | 95000 | | 9 | John | McKinsy | 4.95 | 8.75 | 338000 | | 10 | John | McDonnald | 4.43 | 3.13 | 56333 | +------+-------------+------------+------+-------+--------+ ###### Question No. 13 ################################################## #################################################### # # Show the patient's P_id, P_FirstName, P_LastName and # his/her rounded (2 decimal) average RBC and WBC # while the Patient's P_FirstName is 'Peter' and his # P_LastName is 'Johnsons' # (hint: use pat_info, lab_info; expect 1 row) # #################################################### SELECT pat_info.P_id, pat_info.P_FirstName, pat_info.P_LastName, ROUND(AVG(lab_info.RBC),2) RBC, ROUND(AVG(lab_info.WBC),2) WBC FROM pat_info, lab_info WHERE pat_info.P_id = lab_info.P_id AND pat_info.P_FirstName = 'Peter' AND pat_info.P_LastName = 'Johnsons' GROUP BY lab_info.P_id; +------+-------------+------------+----------------------------+----------------------------+ | P_id | P_FirstName | P_LastName | ROUND(AVG(lab_info.RBC),2) | ROUND(AVG(lab_info.WBC),2) | +------+-------------+------------+----------------------------+----------------------------+ | 1 | Peter | Johnsons | 5.10 | 9.42 | +------+-------------+------------+----------------------------+----------------------------+ ###### Question No. 14 ################################################## #################################################### # # Show all patient's P_id, P_FirstName, P_LastName, and RBC # sorted ascending on P_FirstName and then descending on RBC # (hint: use pat_info, lab_info; expect 29 rows) # #################################################### SELECT pat_info.P_id, pat_info.P_FirstName, pat_info.P_LastName, lab_info.RBC FROM pat_info, lab_info WHERE pat_info.P_id = lab_info.P_id ORDER BY pat_info.P_FirstName ASC, lab_info.RBC DESC; +------+-------------+------------+-----+ | P_id | P_FirstName | P_LastName | RBC | +------+-------------+------------+-----+ | 4 | John | McDonnald | 5 | | 9 | John | McKinsy | 5 | | 9 | John | McKinsy | 4.9 | | 10 | John | McDonnald | 4.5 | | 10 | John | McDonnald | 4.4 | | 10 | John | McDonnald | 4.4 | | 8 | Mehdi | Kharrazi | 5.6 | | 8 | Mehdi | Kharrazi | 4.7 | | 5 | Michael | Robinson | 6 | | 5 | Michael | Robinson | 5.9 | | 5 | Michael | Robinson | 5.8 | | 2 | Mike | Jackson | 6.7 | | 2 | Mike | Jackson | 5.7 | | 2 | Mike | Jackson | 5.6 | | 2 | Mike | Jackson | 5.2 | | 2 | Mike | Jackson | 4.5 | | 1 | Peter | Johnsons | 7.4 | | 1 | Peter | Johnsons | 5.6 | | 1 | Peter | Johnsons | 4.8 | | 1 | Peter | Johnsons | 4.3 | | 1 | Peter | Johnsons | 3.4 | | 3 | Sara | Henson | 3.4 | | 3 | Sara | Henson | 3.3 | | 7 | Susan | McKinsy | 3.7 | | 7 | Susan | McKinsy | 3.5 | | 6 | William | Jordan | 7.2 | | 6 | William | Jordan | 6.9 | | 6 | William | Jordan | 6.7 | | 6 | William | Jordan | 6.6 | +------+-------------+------------+-----+ ###### Question No. 15 ################################################## #################################################### # # Show how much is the avarage RBC in each city # (if the data for that city if available) # (hint: use pat_info, city_info, lab_info; expect 5 rows) # #################################################### SELECT city_info.City_Name, ROUND(AVG(lab_info.RBC),2) FROM pat_info, city_info, lab_info WHERE pat_info.P_id = lab_info.P_id AND pat_info.City_id = city_info.City_id GROUP BY pat_info.City_id; +-----------+----------------------------+ | City_Name | ROUND(AVG(lab_info.RBC),2) | +-----------+----------------------------+ | Halifax | 5.18 | | Vancouer | 5.15 | | Toronto | 4.00 | | Montreal | 6.85 | | Quebec | 5.00 | +-----------+----------------------------+