Skip to content

Instantly share code, notes, and snippets.

@islahh
Last active September 17, 2023 09:52
Show Gist options
  • Save islahh/1e4a281fc02cc113adefcd1aba3b0506 to your computer and use it in GitHub Desktop.
Save islahh/1e4a281fc02cc113adefcd1aba3b0506 to your computer and use it in GitHub Desktop.
SQL query Knowledge
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment