# SQL practice questions I have been preparing for interviews by solving SQL questions from [https://www.sql-practice.com/](https://www.sql-practice.com/) to improve my SQL skills. This blog will have SQL problems with solutions, So I can look back when I need to revise later. 1. Show first name, last name, and gender of patients whose gender is 'M ```sql SELECT patients.first_name as first_name, patients.last_name as last_name,  patients.gender as gender from patients where gender = 'M' ``` 2. Show first name of patients that start with the letter 'C' ```sql SELECT patients.first_name as first_name from patients where first_name like 'C%' ``` 3. Show first name and last name of patients that weight within the range of 100 to 120 (inclusive) ```sql SELECT patients.first_name as first_name, patients.last_name as last_name from patients where weight between 100 and 120 ``` 4. Update the patients table for the allergies column. If the patient's allergies is null then replace it with 'NKA' ```sql SELECT patients.first_name as first_name, patients.last_name as last_name from patients where weight between 100 and 120 ``` 5. Show first name and last name concatenated into one column to show their full name. ```sql select concat(first_name,' ',last_name) as full_name from patients ``` 6. Show first name, last name, and the **full** province name of each patient. Example: 'Ontario' instead of 'ON' ```sql select patients.first_name, patients.last_name, province_names.province_name from patients join province_names on patients.province_id = province_names.province_id ``` 7. Show how many patients have a birth\_date with 2010 as the birth year. ```sql select count(*) from patients where year(patients.birth_date) = 2010 ``` 8. Show the first\_name, last\_name, and height of the patient with the greatest height. ```sql select patients.first_name,patients.last_name, patients.height from patients where patients.height = (select max(patients.height) from patients) ``` 9. Show all columns for patients who have one of the following patient\_ids: 1,45,534,879,1000 ```sql select * from patients where patients.patient_id in (1,45,534,879,1000) ``` 10. Show the total number of admissions ```sql select count(*) from admissions ``` 11. Show all the columns from admissions where the patient was admitted and discharged on the same day. ```sql select * from admissions where admissions.admission_date is admissions.discharge_date ``` 12. Show the patient id and the total number of admissions for patient\_id 579. ```sql select admissions.patient_id, count(*) from admissions where admissions.patient_id = 579 ``` 13. Based on the cities that our patients live in, show unique cities that are in province\_id 'NS'? ```sql select distinct patients.city from patients where patients.province_id = 'NS' ``` 14. Write a query to find the first\_name, last name and birth date of patients who has height greater than 160 and weight greater than 70 ```sql select patients.first_name,patients.last_name,patients.birth_date from patients where patients.height >160 and patients.weight > 70 ``` 15. Write a query to find list of patients first\_name, last\_name, and allergies where allergies are not null and are from the city of 'Hamilton' ```sql select patients.first_name,patients.last_name,patients.allergies from patients where patients.allergies is not null and patients.city = 'Hamilton' ``` 16. Show unique birth years from patients and order them by ascending. ```sql select distinct year(patients.birth_date) from patients order by year(patients.birth_date) asc ``` 17. Show unique first names from the patients table which only occurs once in the list. For example, if two or more people are named 'John' in the first\_name column then don't include their name in the output list. If only 1 person is named 'Leo' then include them in the output. ```sql SELECT patients.first_name FROM patients GROUP BY patients.first_name HAVING COUNT(patients.first_name) = 1; ``` ```sql SELECT first_name FROM (     SELECT       first_name,       count(first_name) AS occurrencies     FROM patients     GROUP BY first_name   ) WHERE occurrencies = 1 ``` 18. Show patient\_id and first\_name from patients where their first\_name start and ends with 's' and is at least 6 characters long. ```sql select patients.patient_id, patients.first_name from patients where patients.first_name like 's%s' and len(patients.first_name) >= 6 ``` (OR) ```sql SELECT   patient_id,   first_name FROM patients where   first_name like 's%'   and first_name like '%s'   and len(first_name) >= 6; ``` 19. Show patient\_id, first\_name, last\_name from patients whos diagnosis is 'Dementia'. Primary diagnosis is stored in the admissions table. ```sql select patients.patient_id, patients.first_name,patients.last_name from patients inner join admissions on admissions.patient_id = patients.patient_id where admissions.diagnosis = 'Dementia'; ``` 20. Display every patient's first\_name.Order the list by the length of each name and then by alphabetically. ```sql select patients.first_name from patients order by len(patients.first_name),patients.first_name asc ``` 21. Show the total amount of male patients and the total amount of female patients in the patients table.Display the two results in the same row. ```sql SELECT COUNT(CASE WHEN patients.gender = 'M' THEN 1 END) AS male_count, COUNT(CASE WHEN patients.gender = 'F' THEN 1 END) AS female_count FROM patients; ``` 22. Show first and last name, allergies from patients which have allergies to either 'Penicillin' or 'Morphine'. Show results ordered ascending by allergies then by first\_name then by last\_name. ```sql SELECT patients.first_name,patients.last_name,patients.allergies from patients where patients.allergies = 'Penicillin' or patients.allergies = 'Morphine' order by patients.allergies, patients.first_name, patients.last_name ASC ``` 23. Show patient\_id, diagnosis from admissions. Find patients admitted multiple times for the same diagnosis. ```sql select patient_id,diagnosis from admissions group by patient_id,diagnosis having count(*) > 1 ``` 24. Show the city and the total number of patients in the city. Order from most to least patients and then by city name ascending. ```sql select patients.city, count(*) from patients group by city order by count(*)desc, patients.city ``` 25. Show all allergies ordered by popularity. Remove NULL values from query. ```sql select patients.allergies, count(*) as popularity from patients where patients.allergies is not null group by patients.allergies order by popularity desc ``` 26. Show all patient's first\_name, last\_name, and birth\_date who were born in the 1970s decade. Sort the list starting from the earliest birth\_date. ```sql select patients.allergies, count(*) as popularity from patients where patients.allergies is not null group by patients.allergies order by popularity desc ``` 27. We want to display each patient's full name in a single column. Their last\_name in all upper letters must appear first, then first\_name in all lower case letters. Separate the last\_name and first\_name with a comma. Order the list by the first\_name in decending order EX: SMITH, jane ```sql SELECT CONCAT(UPPER(patients.last_name), ',', LOWER(patients.first_name)) AS full_name FROM patients ORDER BY patients.first_name DESC; ``` 28. Show the province\_id(s), sum of height; where the total sum of its patient's height is greater than or equal to 7,000. ```sql select patients.province_id, sum(patients.height)as height from patients group by patients.province_id having sum(patients.height) >= 7000 ``` 29. Show the difference between the largest weight and smallest weight for patients with the last name 'Maroni’ ```sql select max(patients.weight)- min(patients.weight) from patients where patients.last_name = 'Maroni' ``` 30. Show all of the days of the month (1-31) and how many admission\_dates occurred on that day. Sort by the day with most admissions to least admissions. ```sql select day(admissions.admission_date) as days, count(*) as count_days from admissions group by days order by count_days desc ``` 31. Show all columns for patient\_id 542's most recent admission\_date. ```sql select * from admissions where admissions.patient_id = 542  order by admissions.admission_date desc limit 1 ``` (OR) ```sql select * from admissions where admissions.patient_id = 542 and  admission_date= (select max(admission_date) from admissions where patient_id = 542) ``` 32. Show patient\_id, attending\_doctor\_id, and diagnosis for admissions that match one of the two criteria: 1\. patient\_id is an odd number and attending\_doctor\_id is either 1, 5, or 19. 2\. attending\_doctor\_id contains a 2 and the length of patient\_id is 3 characters. ```sql SELECT admissions.patient_id,        admissions.attending_doctor_id,        admissions.diagnosis FROM admissions WHERE      (admissions.patient_id % 2 != 0 AND admissions.attending_doctor_id IN (1, 5, 19))     OR     (CAST(admissions.attending_doctor_id AS TEXT) LIKE '%2%' AND length(CAST(admissions.patient_id AS TEXT)) = 3); ``` 33. Show first\_name, last\_name, and the total number of admissions attended for each doctor. Every admission has been attended by a doctor. ```sql select doctors.first_name, doctors.last_name, count(admissions.attending_doctor_id) from doctors join admissions on doctors.doctor_id = admissions.attending_doctor_id group by doctors.first_name, doctors.last_name ``` 34. For each doctor, display their id, full name, and the first and last admission date they attended. ```sql select doctors.doctor_id, concat(doctors.first_name,' ',doctors.last_name),  min(admissions.admission_date),max(admissions.admission_date) from doctors join admissions on admissions.attending_doctor_id = doctors.doctor_id group by doctor_id; ``` 35. Display the total amount of patients for each province. Order by descending. ```sql select province_names.province_name, count(patients.patient_id) from patients join province_names on province_names.province_id = patients.province_id group by province_names.province_name order by count(patients.patient_id) desc ``` 36. For every admission, display the patient's full name, their admission diagnosis, and their doctor's full name who diagnosed their problem. ```sql select concat(patients.first_name,' ',patients.last_name), admissions.diagnosis, concat(doctors.first_name,' ', doctors.last_name) from patients join admissions on admissions.patient_id = patients.patient_id join doctors on admissions.attending_doctor_id = doctors.doctor_id ``` 37. Display the first name, last name and number of duplicate patients based on their first name and last name. Ex: A patient with an identical name can be considered a duplicate. ```sql select patients.first_name, patients.last_name, count(*) from patients group by first_name, last_name having count(*) >1 ``` 38. Display patient's full name,height in the units feet rounded to 1 decimal,weight in the unit pounds rounded to 0 decimals, birth\_date, gender non abbreviated. Convert CM to feet by dividing by 30.48. Convert KG to pounds by multiplying by 2.205. ```sql select concat(patients.first_name,' ',patients.last_name),  round(patients.height/30.48,1),round(patients.weight*2.205,0),birth_date, case when gender = 'F' then 'FEMALE' else 'MALE' end from patients ``` 39. Show patient\_id, first\_name, last\_name from patients whose does not have any records in the admissions table. (Their patient\_id does not exist in any admissions.patient\_id rows.) ```sql select patients.patient_id, patients.first_name, patients.last_name from patients left join admissions on patients.patient_id = admissions.patient_id where admissions.patient_id is null ``` ## Conclusion These SQL problems not only improve my query-writing skills but also serve as a handy reference for future practice. I hope this collection helps others preparing for SQL interviews. Feel free to share your thoughts or questions in the comments!