set-up-db.sql

CREATE DATABASE todolist;

USE todolist;

CREATE TABLE user (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(30),
  lastname VARCHAR(30)
);

CREATE TABLE task (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  description VARCHAR(30) NOT NULL,
  urgency INT UNSIGNED,
  user_id INT UNSIGNED,
  date TIMESTAMP
);

ALTER TABLE task ADD CONSTRAINT FOREIGN KEY (user_id ) REFERENCES user(id);

INSERT INTO user (firstname, lastname) VALUES
  ("Anna", "Peterson"),
  ("Ryan", "Hudson"),
  ("Liam", "Rampling");

INSERT INTO task (description, urgency, user_id, date) VALUES
  ("do the dishes", 1, 2, sysdate()),
  ("finish homework", 1, 1, sysdate()),
  ("learn Java", 2, 2, sysdate()),
  ("do Pilates", 1, 3, sysdate());

SELECT description, urgency, firstname 
FROM task INNER JOIN user ON task.user_id = user.id;