SQL query Knowledge

sql-query.sql

# Using SQL Len function
select patient_id, first_name from patients
where first_name like "s%" AND first_name like "%s"
AND len(first_name) >= 6;

# Using count
select first_name from patients
group by first_name 
having count(first_name)=1;

/* 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_patiant,
(select count(*) from patients where gender="F") AS female_patiant

/* 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 total_patients from patients
group by city
order by count(*) desc, city asc