-
Notifications
You must be signed in to change notification settings - Fork 0
Home
Welcome to the www.sql-practice.com wiki! All below answers are solved by ChatGPT
SELECT first_name, last_name, gender
FROM patients
WHERE gender = 'M';
SELECT first_name, last_name
FROM patients
WHERE allergies IS NULL;
Update the patients table for the allergies column. If the patient's allergies is null then replace it with 'NKA'
UPDATE patients
SET allergies = 'NKA'
WHERE allergies IS NULL;
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM patients;
SELECT p.first_name, p.last_name, pr.province_name
FROM patients p
JOIN province_names pr
ON p.province_id = pr.province_id;
SELECT COUNT(*) as num_patients
FROM patients
WHERE YEAR(birth_date) = 2010;
SELECT first_name, last_name, height
FROM patients
WHERE height = (SELECT MAX(height) FROM patients);
1,45,534,879,1000
SELECT *
FROM patients
WHERE patient_id IN (1, 45, 534, 879, 1000);
SELECT COUNT(*) as total_admissions
FROM admissions;
SELECT *
FROM admissions
WHERE DATE(admission_date) = DATE(discharge_date);
SELECT patient_id, COUNT(*) AS total_admissions
FROM admissions
WHERE patient_id = 579;
SELECT DISTINCT city
FROM patients
INNER JOIN province_names
ON patients.province_id = province_names.province_id
WHERE province_names.province_id = 'NS';
Write a query to find the first_name, last name and birth date of patients who has height greater than 160 and weight greater than 70
SELECT first_name, last_name, birth_date
FROM patients
WHERE height > 160 AND weight > 70;
Write a query to find list of patients first_name, last_name, and allergies from Hamilton where allergies are not null
SELECT first_name, last_name, allergies FROM patients WHERE city = 'Hamilton' AND allergies IS NOT NULL;
Based on cities where our patient lives in, write a query to display the list of unique city starting with a vowel (a, e, i, o, u). Show the result order in ascending by city.
SELECT DISTINCT city
FROM patients
WHERE city LIKE 'A%' OR city LIKE 'E%' OR city LIKE 'I%' OR city LIKE 'O%' OR city LIKE 'U%'
ORDER BY city ASC;
SELECT DISTINCT YEAR(birth_date) AS birth_year
FROM patients
ORDER BY birth_year ASC;
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(*) = 1
Here's the SQL query to retrieve patient_id and first_name from patients where their first_name starts and ends with 's' and is at least 6 characters long:
SELECT patient_id, first_name
FROM patients
WHERE first_name LIKE 's%s' AND LENGTH(first_name) >= 6;
Primary diagnosis is stored in the admissions table.
SELECT patients.patient_id, patients.first_name, patients.last_name
FROM patients
JOIN admissions ON patients.patient_id = admissions.patient_id
WHERE admissions.diagnosis = 'Dementia';
Order the list by the length of each name and then by alphbetically
SELECT first_name
FROM patients
ORDER BY LENGTH(first_name), first_name ASC
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
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_patients,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_patients
FROM patients;
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 ASC, first_name ASC, last_name ASC;
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
Order from most to least patients and then by city name ascending.
SELECT city, COUNT(*) as patient_count
FROM patients
GROUP BY city
ORDER BY patient_count DESC, city ASC;
SELECT allergies, COUNT(*) AS allergy_count
FROM patients
WHERE allergies IS NOT NULL
GROUP BY allergies
ORDER BY allergy_count 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;
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 full_name
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 total_height
FROM
patients
GROUP BY
province_id
HAVING
total_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_diff
FROM
patients
WHERE
last_name = 'Maroni';
We are looking for a specific patient. Pull all columns for the patient who matches the following criteria:
- First_name contains an 'r' after the first two letters.
- Identifies their gender as 'F'
- Born in February, May, or December
- Their weight would be between 60kg and 80kg
- Their patient_id is an odd number
- They are from the city 'Kingston'
SELECT *
FROM patients
WHERE first_name LIKE '__r%'
AND gender = 'F'
AND MONTH(birth_date) IN (2, 5, 12)
AND weight BETWEEN 60 AND 80
AND patient_id % 2 = 1
AND city = 'Kingston';
Show the percent of patients that have 'M' as their gender. Round the answer to the nearest hundreth number and in percent form.
SELECT CONCAT(ROUND((COUNT(CASE WHEN gender = 'M' THEN 1 END) * 100.0 / COUNT(*)), 2), '%') AS percent_male
FROM patients;
Show the total amount of patients in each weight group. Order the list by the weight group decending. For example, if they weight 100 to 109 they are placed in the 100 weight group, 110-119 = 110 weight group, etc.
SELECT
FLOOR(weight/10)*10 AS weight_group,
COUNT(*) AS total_patients
FROM
patients
GROUP BY
weight_group
ORDER BY
weight_group DESC;
For each day display the total amount of admissions on that day. Display the amount changed from the previous date.
SELECT
date,
admissions_count,
admissions_count - LAG(admissions_count) OVER (ORDER BY date) AS daily_change
FROM
(
SELECT
DATE(admission_date) AS date,
COUNT(*) AS admissions_count
FROM
admissions
GROUP BY
date
) AS daily_admissions
ORDER BY
date;
Sort the province names in ascending order in such a way that the province 'Ontario' is always on top.
SELECT
province_names.province_name
FROM
province_names
ORDER BY
CASE WHEN province_names.province_name = 'Ontario' THEN 0 ELSE 1 END,
province_names.province_name ASC;