sql-recap.sql

-- Give me all patients aged 30
SELECT * FROM patients WHERE age = 30

-- Give me doctors' name and specialty whose specialty ends in magic and name contains strange
SELECT name, specialty
FROM doctors
WHERE specialty LIKE '%Magic'
AND name LIKE '%Strange%'

-- Give me the number of inhabitants in my table
SELECT COUNT(*) FROM inhabitants

-- Count the number of doctors whose specialty ends in Surgery
SELECT COUNT(*) AS c, specialty AS s
FROM doctors
WHERE s LIKE '%Surgey'
ORDER BY c DESC

-- Give me the inhabitants first and city name for inhabitants who live in Paris and are over 18
SELECT i.first_name, c.name
FROM inhabitants i
JOIN cities c ON c.id = i.city_id
WHERE c.name = 'Paris'
AND i.age >= 18

-- Give me consultations ordered by date with both the patient name and doctor name
SELECT c.date, p.first_name, p.last_name, d.name
FROM consultations c
JOIN patients p ON p.id = c.patient_id
JOIN doctors d ON d.id = c.doctor_id
ORDER BY c.date DESC

-- SELECT: give me something
-- FROM: the table from which I'd like to select data
-- AS: rename a column/ header

-- WHERE: specifying conditions for my selection (age = 18)
-- LIKE: where I want to search for words ('%surgey')
-- AND: when I want to use multiple conditions

-- COUNT: give me the number of rows returned in my query
-- ORDER BY: sort my query results
-- GROUP BY: return my query by group

-- JOIN table_name ON: also use table_name ON primary_key = foreign_key