SQL practice questions

I have been preparing for interviews by solving SQL questions from 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

    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'

    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)

    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'

    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.

    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'

    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.

    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.

    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

    select *
   
    from patients
   
    where patients.patient_id in (1,45,534,879,1000)
   

10. Show the total number of admissions

    select count(*)
    
    from admissions
    

11. Show all the columns from admissions where the patient was admitted and discharged on the same day.

    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.

    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'?

    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

    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'

    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.

    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.

    SELECT patients.first_name
    
    FROM patients
    
    GROUP BY patients.first_name
    
    HAVING COUNT(patients.first_name) = 1;
    

    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.

    select patients.patient_id, patients.first_name
    
    from patients
    
    where patients.first_name like 's%s' and
    
    len(patients.first_name) >= 6
    

    (OR)

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    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

    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.

    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’

    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.

    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.

    select *
    
    from admissions
    
    where admissions.patient_id = 542 
    
    order by admissions.admission_date desc
    
    limit 1
    

    (OR)

    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.

    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.

    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.

    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.

    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.

    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.

    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.

    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.)

    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!