sql

Question 1

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.

Answer 1

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

Question 2

Show patient_id and first_name from patients where their first_name start and ends with ‘s’ and is at least 6 characters long.

Answer 2

SELECT
patient_id,
first_name
FROM patients
WHERE first_name LIKE ‘s____%s’;

Question 3

Show patient_id, first_name, last_name from patients whos diagnosis is ‘Dementia’.

Primary diagnosis is stored in the admissions table.

Answer 3

SELECT
patients.patient_id,
first_name,
last_name
FROM patients
JOIN admissions ON admissions.patient_id = patients.patient_id
WHERE diagnosis = ‘Dementia’;

Question 4

Display every patient’s first_name.
Order the list by the length of each name and then by alphbetically

Answer 4

SELECT first_name
FROM patients
order by
len(first_name),
first_name;

Question 5

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.

Answer 5

SELECT
(SELECT count() FROM patients WHERE gender=’M’) AS male_count,
(SELECT count() FROM patients WHERE gender=’F’) AS female_count;

Question 6

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.

Answer 6

SELECT
first_name,
last_name,
allergies
FROM patients
WHERE
allergies IN (‘Penicillin’, ‘Morphine’)
ORDER BY
allergies,
first_name,
last_name;

Question 7

Show patient_id, diagnosis from admissions. Find patients admitted multiple times for the same diagnosis.

Answer 7

SELECT
patient_id,
diagnosis
FROM admissions
GROUP BY
patient_id,
diagnosis
HAVING COUNT(*) > 1;

Question 8

Show the city and the total number of patients in the city.
Order from most to least patients and then by city name ascending.

Answer 8

SELECT
city,
COUNT(*) AS num_patients
FROM patients
GROUP BY city
ORDER BY num_patients DESC, city asc;

Question 9

Show first name, last name and role of every person that is either patient or doctor.
The roles are either “Patient” or “Doctor”

Answer 9

SELECT first_name, last_name, ‘Patient’ as role FROM patients
union all
select first_name, last_name, ‘Doctor’ from doctors;

Question 10

Show all allergies ordered by popularity. Remove NULL values from query.

Answer 10

SELECT
allergies,
COUNT(*) AS total_diagnosis
FROM patients
WHERE
allergies IS NOT NULL
GROUP BY allergies
ORDER BY total_diagnosis DESC

SELECT
allergies,
count()
FROM patients
WHERE allergies NOT NULL
GROUP BY allergies
ORDER BY count() DESC

SELECT
allergies,
count(allergies) AS total_diagnosis
FROM patients
GROUP BY allergies
HAVING
allergies IS NOT NULL
ORDER BY total_diagnosis DESC

Question 11

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.

Answer 11

SELECT
first_name,
last_name,
birth_date
FROM patients
WHERE
YEAR(birth_date) BETWEEN 1970 AND 1979
ORDER BY birth_date ASC;

SELECT
first_name,
last_name,
birth_date
FROM patients
WHERE
birth_date >= ‘1970-01-01’
AND birth_date < ‘1980-01-01’
ORDER BY birth_date ASC

SELECT
first_name,
last_name,
birth_date
FROM patients
WHERE year(birth_date) LIKE ‘197%’
ORDER BY birth_date ASC

Question 12

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

Answer 12

SELECT
CONCAT(UPPER(last_name), ‘,’, LOWER(first_name)) AS new_name_format
FROM patients
ORDER BY first_name DESC;

SELECT
UPPER(last_name) || ‘,’ || LOWER(first_name) AS new_name_format
FROM patients
ORDER BY first_name DESC;

Question 13

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.

Answer 13

SELECT
province_id,
SUM(height) AS sum_height
FROM patients
GROUP BY province_id
HAVING sum_height >= 7000

Question 14

Show the difference between the largest weight and smallest weight for patients with the last name ‘Maroni’

Answer 14

SELECT
(MAX(weight) - MIN(weight)) AS weight_delta
FROM patients
WHERE last_name = ‘Maroni’;

Question 15

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.

Answer 15

SELECT
DAY(admission_date) AS day_number,
COUNT(*) AS number_of_admissions
FROM admissions
GROUP BY day_number
ORDER BY number_of_admissions DESC

Question 16

Show all columns for patient_id 542’s most recent admission_date.

Answer 16

SELECT *
FROM admissions
WHERE patient_id = 542
GROUP BY patient_id
HAVING
admission_date = MAX(admission_date);

SELECT *
FROM admissions
WHERE
patient_id = ‘542’
AND admission_date = (
SELECT MAX(admission_date)
FROM admissions
WHERE patient_id = ‘542’
)

SELECT *
FROM admissions
WHERE patient_id = 542
ORDER BY admission_date DESC
LIMIT 1

SELECT *
FROM admissions
GROUP BY patient_id
HAVING
patient_id = 542
AND max(admission_date)

Question 17

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.

Answer 17

SELECT
patient_id,
attending_doctor_id,
diagnosis
FROM admissions
WHERE
(
attending_doctor_id IN (1, 5, 19)
AND patient_id % 2 != 0
)
OR
(
attending_doctor_id LIKE ‘%2%’
AND len(patient_id) = 3
)

Question 18

Show first_name, last_name, and the total number of admissions attended for each doctor.

Every admission has been attended by a doctor.

Answer 18

SELECT
first_name,
last_name,
count(*) as admissions_total
from admissions a
join doctors ph on ph.doctor_id = a.attending_doctor_id
group by attending_doctor_id

SELECT
first_name,
last_name,
count(*)
from
doctors p,
admissions a
where
a.attending_doctor_id = p.doctor_id
group by p.doctor_id;

Question 19

For each doctor, display their id, full name, and the first and last admission date they attended.

Answer 19

select
doctor_id,
first_name || ‘ ‘ || last_name as full_name,
min(admission_date) as first_admission_date,
max(admission_date) as last_admission_date
from admissions a
join doctors ph on a.attending_doctor_id = ph.doctor_id
group by doctor_id;