SQL Queries

sql_queries.sql

-- GIVE ALL THE PATIENT NAMES
SELECT first_name, last_name FROM patients;

-- GIVE ALL THE DOCTOR NAMES
SELECT first_name, last_name FROM doctors;

-- GIVE ME ALL YOU GOT ON PATIENTS
SELECT * FROM patients;

-- GIVE ME ALL THE PATIENTS AGED 21
SELECT * FROM patients WHERE age = 21;

-- GIVE ME ALL DOCTORS OF CARDIOVASCULAR SURGERY SPECIALTY
SELECT * FROM doctors
WHERE specialty = "Cardiovascular Surgery";

-- GIVE ME ALL SURGERY DOCTORS
SELECT * FROM doctors
WHERE specialty LIKE "%Surgery";

-- GIVE ME ALL CARDIOVASCULAR SURGERY DOCTORS NAMED BENJAMIN
SELECT * FROM doctors
WHERE specialty = "Cardiovascular Surgery"
AND first_name = "Benjamin";

-- GIVE ME ALL PATIENTS ORDERED BY AGE
SELECT * FROM patients ORDER BY age ASC; -- Ascending
SELECT * FROM patients ORDER BY age DESC; -- Descending


-- HOW MANY DOCTORS DO I HAVE?
SELECT COUNT(*) FROM doctors;

-- COUNT CARDIAC SURGERY DOCTORS
SELECT COUNT(*) FROM doctors
WHERE specialty LIKE "%Surgery";

-- COUNT ALL DOCTORS PER SPECIALTY
SELECT COUNT(*), specialty FROM doctors
GROUP BY specialty;

-- COUNT ALL DOCTORS PER SPECIALTY AND ORDER BY HIGHEST COUNT
SELECT COUNT(*) AS count, specialty FROM doctors
GROUP BY specialty
ORDERED BY count;

-- GIVE ME ALL THE INHABITANTS FROM PARIS
SELECT * FROM inhabitants
JOIN cities ON cities.id = inhabitants.city_id
WHERE cities.name = "Paris";


-- GIVE ME ALL THE ADULTS LIVING IN PARIS
SELECT * FROM inhabitants AS i
JOIN cities AS c ON c.id = i.city_id
WHERE c.name = "Paris"
AND i.age >= 18;

-- FOR EACH CONSULTATION, GIVE ME ITS DATE, PATIENT AND DOCTOR NAMES
SELECT c.date, d.first_name, d.last_name,
p.first_name, p.last_name
FROM consultations c
JOIN doctors d ON d.id = c.doctor_id
JOIN patients p ON p.id = c.patient_id;

-- CREATING TABLES
CREATE TABLE `programming_languages` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` VARCHAR
);

-- INSERTING RECORDS
INSERT INTO programming_languages ('name') VALUES ('Ruby');