Schema Design and DBs - Batch 258

db_queries.sql

-- Give me the specialty of all doctors
SELECT specialty FROM doctors

-- Give me all cardiologists
SELECT * FROM doctors WHERE specialty = 'cardiology'

-- How many Surgeons are there?
SELECT COUNT(*) FROM doctors
WHERE specialty = "%Surgery"

-- Give me all the inhabitants from Paris
SELECT * FROM inhabitants i
JOIN cities c ON i.city_id = c.id
WHERE c.name = 'Paris'

-- Give all of the adults living in Paris
SELECT * FROM inhabitants i
JOIN cities c ON c.id = i.city_id
WHERE c.name = 'Paris'
AND i.age > 17

-- For each consultation, give me the
-- consultation date, patient and
-- doctors names
SELECT c.created_at, p.first_name, p.last_name, d.first_name, d.last_name
FROM consultations c
JOIN patients p ON p.id = c.patient_id
JOIN doctors d ON d.id = c.doctor_id