queries.sql

USE tutor_db;

-- Find the subjects that users teach
SELECT user.name, subject.name as subject_name
FROM user_subject
INNER JOIN user on user.id=user_subject.user_id
INNER JOIN subject on subject.id=user_subject.subject_id;

-- Get availability for users where they aren't already booked.
SELECT name, day, time, booked
from user_availability
INNER JOIN user on user.id=user_availability.user_id
INNER JOIN (
  SELECT availability.id, time.time, day.day
    FROM availability
    INNER JOIN day on day.id=availability.day_id
    INNER JOIN time on time.id=availability.time_id
) t on t.id=user_availability.availability_id
WHERE booked = false;

-- Get the user name, subject name, day and time when users are available
SELECT user.name, subject.name as subject_name, day, time
FROM (
  SELECT user_availability.user_id, user_availability.availability_id, user_subject.subject_id
  FROM user_availability
  INNER JOIN user_subject on user_subject.user_id=user_availability.user_id
    WHERE booked=false
) t
INNER JOIN user on user.id=t.user_id
INNER JOIN subject on subject.id=t.subject_id
INNER JOIN (
  SELECT availability.id, time.time, day.day
  FROM availability
  INNER JOIN day on day.id=availability.day_id
  INNER JOIN time on time.id=availability.time_id
) u on u.id=t.user_id;

schemas.sql

CREATE DATABASE IF NOT EXISTS tutor_db;

USE tutor_db;

CREATE TABLE user (
  id INT AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  role VARCHAR(100) NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE subject (
  id INT AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE user_subject (
  id INT AUTO_INCREMENT,
  user_id INT NOT NULL,
  subject_id INT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY (subject_id) REFERENCES subject(id),
  FOREIGN KEY (user_id) REFERENCES user(id),
  CONSTRAINT uc_user_subject UNIQUE (user_id, subject_id)
);

CREATE TABLE day (
  id INT AUTO_INCREMENT,
  day VARCHAR(100) NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE time (
  id INT AUTO_INCREMENT,
  time VARCHAR(100) NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE availability (
  id INT AUTO_INCREMENT,
  day_id INT NOT NULL,
  time_id INT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(day_id) REFERENCES day(id),
  FOREIGN KEY(time_id) REFERENCES time(id),
  CONSTRAINT uc_availability UNIQUE (day_id, time_id)
);

CREATE TABLE user_availability (
  id INT AUTO_INCREMENT,
  user_id INT NOT NULL,
  availability_id INT NOT NULL,
  booked BOOLEAN DEFAULT false,
  PRIMARY KEY(id),
  FOREIGN KEY(user_id) REFERENCES user(id),
  FOREIGN KEY(availability_id) REFERENCES availability(id),
  CONSTRAINT uc_user_availability UNIQUE (user_id, availability_id)
);

seeds.sql

USE tutor_db;

INSERT INTO day (day) VALUES
('Monday'), ('Tuesday'), ('Wednesday'), ('Thursday'), ('Friday'), ('Saturday'), ('Sunday');

INSERT INTO time (time) VALUES
('9'), ('10'), ('11'), ('12'), ('13'), ('14'), ('15'), ('16'), ('17');

INSERT INTO availability(day_id, time_id) VALUES
(1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9),
(2, 1), (2, 2), (2, 3), (2, 4), (2, 5), (2, 6), (2, 7), (2, 8), (2, 9),
(3, 1), (3, 2), (3, 3), (3, 4), (3, 5), (3, 6), (3, 7), (3, 8), (3, 9),
(4, 1), (4, 2), (4, 3), (4, 4), (4, 5), (4, 6), (4, 7), (4, 8), (4, 9),
(5, 1), (5, 2), (5, 3), (5, 4), (5, 5), (5, 6), (5, 7), (5, 8), (5, 9),
(6, 1), (6, 2), (6, 3), (6, 4), (6, 5), (6, 6), (6, 7), (6, 8), (6, 9),
(7, 1), (7, 2), (7, 3), (7, 4), (7, 5), (7, 6), (7, 7), (7, 8), (7, 9);

INSERT INTO user (name, role) VALUES
('brian', 'tutor'),
('randy', 'tutor'),
('andrew', 'tutor');

INSERT INTO subject (name) values
('math'),
('spanish'),
('history');

INSERT INTO user_subject (user_id, subject_id) values
(1, 1),
(1, 2),
(2, 3),
(3, 1);

INSERT INTO user_availability (user_id, availability_id) VALUES
(1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9),
(2, 10), (2, 11), (2, 12), (2, 13), (2, 14), (2, 15), (2, 16), (2, 17), (2, 18),
(3, 19), (3, 20), (3, 21), (3, 22), (3, 1), (3, 2), (3, 3), (3, 4);