sql Flashcards
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 first_name
FROM patients
GROUP BY first_name
HAVING COUNT(first_name) = 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
patient_id,
first_name
FROM patients
WHERE first_name LIKE ‘s____%s’;
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,
first_name,
last_name
FROM patients
JOIN admissions ON admissions.patient_id = patients.patient_id
WHERE diagnosis = ‘Dementia’;
Display every patient’s first_name.
Order the list by the length of each name and then by alphbetically
SELECT first_name
FROM patients
order by
len(first_name),
first_name;
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
(SELECT count() FROM patients WHERE gender=’M’) AS male_count,
(SELECT count() FROM patients WHERE gender=’F’) AS female_count;
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
first_name,
last_name,
allergies
FROM patients
WHERE
allergies IN (‘Penicillin’, ‘Morphine’)
ORDER BY
allergies,
first_name,
last_name;
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
city,
COUNT(*) AS num_patients
FROM patients
GROUP BY city
ORDER BY num_patients DESC, city asc;
Show first name, last name and role of every person that is either patient or doctor.
The roles are either “Patient” or “Doctor”
SELECT first_name, last_name, ‘Patient’ as role FROM patients
union all
select first_name, last_name, ‘Doctor’ from doctors;
Show all allergies ordered by popularity. Remove NULL values from query.
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
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
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
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(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;
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
province_id,
SUM(height) AS sum_height
FROM patients
GROUP BY province_id
HAVING sum_height >= 7000
Show the difference between the largest weight and smallest weight for patients with the last name ‘Maroni’
SELECT
(MAX(weight) - MIN(weight)) AS weight_delta
FROM patients
WHERE 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(admission_date) AS day_number,
COUNT(*) AS number_of_admissions
FROM admissions
GROUP BY day_number
ORDER BY number_of_admissions DESC
Show all columns for patient_id 542’s most recent admission_date.
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)
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
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
)
Show first_name, last_name, and the total number of admissions attended for each doctor.
Every admission has been attended by a doctor.
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;
For each doctor, display their id, full name, and the first and last admission date they attended.
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;
