sql_join_example.sql

-- Create all the tables

CREATE TABLE doctors (
	id SERIAL PRIMARY KEY, 
	status SMALLINT NOT NULL, 
	doctor_name VARCHAR(512) NOT NULL
);


CREATE TABLE slots (
	id SERIAL PRIMARY KEY, 
	date DATE NOT NULL, 
	duration INT NOT NULL CHECK(duration>=0), 
	time TIME NOT NULL
);


CREATE TABLE appointments (
	id SERIAL PRIMARY KEY, 
	slot_id INT NOT NULL REFERENCES slots(id), 
	patient_name VARCHAR(512) NOT NULL, 
	doctor_id INT NOT NULL REFERENCES doctors(id),
	deleted_at DATE
);


-- Insert data into tables

INSERT INTO doctors (status, doctor_name)
VALUES
	(1, 'Rayhan'), 
	(1, 'Mobasshir'), 
	(1, 'Nawaz'), 
	(0, 'Hasib');


INSERT INTO slots (date, duration, time) 
VALUES 
	('2019-10-10', 2900, '01:01'),
	('2019-10-11', 1200, '02:01'),
	('2019-10-18', 1100, '03:01'),
	('2019-09-10', 200, '11:01'),
	('2019-08-01', 500, '01:31'),
	('2019-10-07', 300, '02:31'),
	('2019-10-02', 1200, '03:31'),
	('2019-09-10', 900, '04:01'),
	('2019-10-10', 350, '02:21');


INSERT INTO appointments (slot_id, patient_name, doctor_id)
VALUES
	(2, 'Tasin', 5), 
	(1, 'Nawab', 7), 
	(3, 'Rakib', 6), 
	(5, 'Hossain', 5), 
	(4, 'Saiful', 5), 
	(6, 'Sanowar', 7), 
	(7, 'D-Mma', 6);



------------------- QUESTIONS ------------------
-- (OK) Q1. Write a SQL query to fetch ALL appointments on October, ordered by slot_date_time in increasing order.
SELECT doctors.doctor_name, appointments.patient_name, slots.date + slots.time AS slot_date_time
FROM appointments 
    INNER JOIN slots ON appointments.slot_id=slots.id
    INNER JOIN doctors ON doctors.id=appointments.doctor_id
WHERE EXTRACT(MONTH FROM date) = 10
ORDER BY slot_date_time;


-- (OK) Q2. Write a SQL query to fetch the doctor with highest number of appointments.
SELECT doctor_name, appointment_count
FROM ( 
    SELECT doctor_id, COUNT(doctor_id) AS appointment_count
    FROM appointments GROUP BY doctor_id
) AS left_table
INNER JOIN 
doctors ON left_table.doctor_id=doctors.id;


-- (OK) Q3. Write a SQL query to show list of doctors with their total appointment durations in decreasing order.
SELECT doctors.id, doctors.doctor_name, SUM(slots.duration) as total_duration 
FROM appointments 
    INNER JOIN slots ON appointments.slot_id=slots.id
    INNER JOIN doctors ON doctors.id=appointments.doctor_id
GROUP BY doctors.id;