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.

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'

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

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

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

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
```

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

Show how many patients have a birth_date with 2010 as the birth year.

 select count(*)

 from patients

 where year(patients.birth_date) = 2010

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)

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)

Show the total number of admissions
```
select count(*)

from admissions
```
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
```

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

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

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

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'

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

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

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;

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

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

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;

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

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

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
```

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

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

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;
```

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

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'

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
```

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)

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

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

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;

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

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

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
```
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
```

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!