Untitled

#1 SELECT distinct(YEAR(birth_date)) FROM patients
order by birth_date
#2 SELECT first_name FROM patients
group by first_name
having count(first_name) =1
#3 SELECT patient_id, first_name FROM patients
WHERE first_name LIKE 's%s' AND LEN(first_name) >=6
#4 SELECT admissions.patient_id, first_name, last_name FROM patients
JOIN admissions ON admissions.patient_id=patients.patient_id
where diagnosis='Dementia'
#5 SELECT first_name FROM patients
order by LEN(first_name), first_name ASC
#6 SELECT  (select count(*) FROM patients WHERE gender='M') as male_count,
        (select count(*) FROM patients WHERE gender='F') AS female_count
#7 SELECT first_name, last_name, allergies from patients
WHERE allergies= 'Penicillin' OR allergies= 'Morphine'
ORDER BY allergies, first_name, last_name
#8 SELECT patient_id, diagnosis from admissions
group by patient_id, diagnosis
having count(*)>1
#9 SELECT city, COUNT(patient_id) as total_patients
from patients
group by city
Order by count(patient_id) desc, city asc
#10select first_name, last_name, 'patient' AS role from patients

union all 
select first_name, last_name, 'doctor' as role from doctors
#11SELECT allergies, COUNT(*) AS popularity
FROM patients
WHERE allergies IS NOT NULL
GROUP BY allergies
ORDER BY popularity DESC;
#12 SELECT first_name, last_name, birth_date FROM patients
where year(birth_date) between 1970 and 1979
order by birth_date
#13 SELECT CONCAT(UPPER(last_name), ',', LOWER(first_name)) AS full_name
FROM patients
ORDER BY first_name DESC
#14 SELECT province_id, SUM(height) AS total_height
FROM patients
GROUP BY province_id
HAVING SUM(height) >= 7000
#15 SELECT max(weight) - min(weight) 
FROM patients
where last_name= 'Maroni'
#16 SELECT DAY(admission_date) AS day, COUNT(*) AS num_admissions
FROM admissions
WHERE admission_date IS NOT NULL
GROUP BY DAY(admission_date)
ORDER BY num_admissions DESC;
#17 SELECT *
FROM admissions
WHERE patient_id = 542
ORDER BY admission_date DESC
LIMIT 1
#18SELECT patient_id, attending_doctor_id, diagnosis
FROM admissions
WHERE (patient_id % 2 <> 0 AND attending_doctor_id IN (1, 5, 19))
   OR (attending_doctor_id LIKE '%2%' AND LENGTH(patient_id) = 3);
#19SELECT doctors.first_name, doctors.last_name, count(admission_date) as total
FROM admissions
join doctors 
on admissions.attending_doctor_id= doctors.doctor_id
group by doctor_id
#20SELECT doctor_id, concat(first_name,' ', last_name) as full_name, MAX(admissions.admission_date) AS last_ad, Min(admissions.admission_date) AS first_ad   
FROM doctors
join admissions
on admissions.attending_doctor_id= doctors.doctor_id
group by doctor_id
#21 select province_names.province_name, count(patient_id) as patient_count
FROM patients
join province_names
on patients.province_id= province_names.province_id
group by province_name
order by patient_count DESC
#22 select concat(patients.first_name,' ', patients.last_name) as patient_name, admissions.diagnosis, concat(doctors.first_name,' ', doctors.last_name) as doctor_name
FROM patients
join admissions
on patients.patient_id= admissions.patient_id
join doctors
on admissions.attending_doctor_id= doctors.doctor_id
#23 select first_name, last_name, count(first_name and last_name) as num_duplicates
FROM patients
group by first_name, last_name
having count(*) >1
#24 select concat(first_name, ' ', last_name) as full_name, round(height/ 30.48, 1) as height, round(weight * 2.205 ,0) as weight, birth_date, 
CASE 
WHEN gender= 'M' THEN 'MALE'
ELSe 'FEMALE'
end
AS gender_type
FROM patients