L06-Patients 1 L06-Patients Creating Tables Step 1: Create the Patients Table Write a CREATE TABLE statement with the following columns: date - The date of each patient visit DATE patient_id - A unique id for each patient /primary key VARCHAR(100) name - The patient's name age - Their age (numeric value) integer data type weight - Weight (can include decimals) float data type gender - Gender VARCHAR(10) location - Where they are from phone_number - A phone number to contact them VARCHAR(20) disease - Any known medical conditions/diseases doctor_name - The name of their treating doctor doctor_id - A unique id for each doctor INT Step 2: import the patients.csv file. Queries on the Tables Step 3: count the total number of patients. Write a SELECT COUNT(*) statement from the patients table. Step 4: find the oldest patient. Add a WHERE clause to filter based on age : WHERE age = (SELECT MAX(age) FROM patients) . L06-Patients 2 Step 5: list patients between two dates. Example: BETWEEN "2019-06-15" AND "2019-02- 13" . Step 6: list patients name and length of name. Use LENGTH() function Step 7: list patients name and gender abbrevation. Add a CASE statement to replace gender information: CASE gender WHEN 'Male' THEN 'M' ELSE 'F' END as gender . Step 8: concatenate patients name and doctors name as pd_name . CONCAT() function joins strings together. Step 9: list patients' age with logarithmic value. Use SELECT with age , and calculate the base-10 logarithm with LOG10(age) as log_age . Step 10: extract Year from Date. Use SELECT with date , and extract the year with YEAR(date) as year . Step 11: Indicate whether patient is a senior or not (Yes/No). Add a CASE statement: CASE WHEN age > 40 THEN 'Yes' ELSE 'No' END as age_classification .