examples.sql

-- READ first name and last name from ALL patients
SELECT first_name, last_name FROM patients
-- first_name  last_name
-- George      Abitbol
-- Michel      Hazavanicus



-- READ ALL COLUMNS (*) from ALL patients
SELECT * FROM patients
-- id first_name  last_name   social_security_number  age
-- 1  George      Abitbol     1 12 34 89 124 123      42
-- 2  Michel      Hazavanicus 1 94 91 12 123 492      12
-- 3  Sarah       Haz         1 94 91 12 123 494      13



-- COUNT all records in the patients table
--     aggregator
SELECT COUNT(*) FROM patients
-- COUNT(*)
-- 3



-- COUNT pediatric patients
--     aggregator
SELECT COUNT(*) FROM patients WHERE age < 19
-- COUNT(*)
-- 2



-- READ all patients that are 21 years old (continals -> WHERE)
SELECT * FROM patients WHERE age = 21
-- id first_name  last_name social_security_number  age
-- 3 James        James     23218792187 3912        21



-- READ all surgeons (have "Surgery" in the specialty column)
SELECT * FROM doctors WHERE specialty LIKE '%Surgery%'
-- id  first_name  last_name    specialty
-- 3   John        Doe          Cardiac Surgery
-- 4   Jane        Doe          Brain Surgery
-- 5   XXXX        XXX          Surgery of the Brain



-- READ all surgeon (have "Surgery" at the end of their specialty column)
SELECT * FROM doctors WHERE specialty LIKE '%Surgery'
-- id  first_name  last_name    specialty
-- 3   John        Doe          Cardiac Surgery
-- 4   Jane        Doe          Brain Surgery



-- READ all heart surgeons
SELECT * FROM doctors WHERE specialty = 'Cardiac Surgery'
-- id  first_name  last_name    specialty
-- 3   John        Doe          Cardiac Surgery


-- --------------------------------------
-- EXAMPLES FOR COUNT WITH GROUP BY
-- --------------------------------------
-- id  first_name  last_name    specialty
-- 3   John        Doe          Cardiac Surgery
-- 4   Jane        Doe          Brain Surgery
-- 5   Steve       Doe          Cardiac Surgery

-- GROUP BY only works with aggregators!!! (COUNT, ...)
SELECT COUNT(*)            FROM doctors
-- count
-- 3

SELECT COUNT(*)            FROM doctors GROUP BY specialty
-- count
-- 2
-- 1

SELECT COUNT(*), specialty FROM doctors GROUP BY specialty
-- COUNT(*) specialty
-- 2        Cardiac Surgery
-- 1        Brain Surgery

SELECT COUNT(*) AS specialty_count, specialty FROM doctors GROUP BY specialty ORDER BY specialty_count ASC
-- specialty_count     specialty
-- 1                   Brain Surgery
-- 2                   Cardiac Surgery



SELECT * FROM doctors WHERE specialty = 'Cardiac Surgery' AND first_name = 'Steve'
-- id  first_name  last_name    specialty
-- 5   Steve       Doe          Cardiac Surgery



SELECT * FROM doctors WHERE specialty = 'Cardiac Surgery' OR specialty = 'Brain Surgery'
-- id  first_name  last_name    specialty
-- 3   John        Doe          Cardiac Surgery
-- 4   Jane        Doe          Brain Surgery
-- 5   Steve       Doe          Cardiac Surgery



SELECT * FROM patients ORDER BY age ASC
-- id first_name  last_name   social_security_number  age
-- 2  Michel      Hazavanicus 1 94 91 12 123 492      25
-- 1  George      Abitbol     1 12 34 89 124 123      42
-- 5  James       La Brien    1 12 34 89 124 123      72



SELECT * FROM patients ORDER BY age DESC
-- id first_name  last_name   social_security_number  age
-- 5  James       La Brien    1 12 34 89 124 123      72
-- 1  George      Abitbol     1 12 34 89 124 123      42
-- 2  Michel      Hazavanicus 1 94 91 12 123 492      25



-- --------------------------------------
-- JOINING TABLES
-- --------------------------------------
-- ALL inhabitants from Paris
SELECT *
FROM inhabitants
JOIN cities ON cities.id = inhabitants.city_id
WHERE cities.name = "Paris"



-- ALL adult inhabitants from Paris
SELECT *
FROM inhabitants
JOIN cities ON cities.id = inhabitants.city_id
WHERE cities.name = "Paris"
AND inhabitants.age >= 18



-- Question: For each consultation, give me its
-- date, patient and doctor names
SELECT
  consultations.created_at,
  patients.first_name,
  patients.last_name,
  doctors.first_name,
  doctors.last_name
FROM consultations
JOIN doctors ON doctors.id = consultations.doctor_id
JOIN patients ON patients.id = consultations.patient_id

-- With aliases
SELECT
  c.created_at,
  p.first_name,
  p.last_name,
  d.first_name,
  d.last_name
FROM consultations AS c
JOIN patients AS p ON c.patient_id = p.id
JOIN doctors AS d ON c.doctor_id = d.id;