Untitled
unknown
plain_text
a year ago
3.5 kB
4
Indexable
Never
#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