-- sqlite
.tables

-- mysql

-- psql
\c students     -- connect to student db
\d              -- shows all tables
\d foo          -- shows info about foo table
\i tables.sql   -- import and run entire file

--  -------- CREATION --------------
CREATE DATABASE school;
DROP DATABASE school;

-- Create table
CREATE TABLE Students (
  -- colname TYPE OPTIONS
  id SERIAL PRIMARY KEY,            -- autoincrementing integer
  fname VARCHAR(50),
  lname VARCHAR(50),
  dob DATE,
  -- constraints
);

CREATE TABLE Address (
  id SERIAL PRIMARY KEY,
  street VARCHAR(50),
  city VARCHAR(50),
  state CHAR(2),
  zip CHAR(5)
);

CREATE TABLE StudentAddressess (
  student_id INTEGER REFERENCES Students (id),  -- Foreign Key
  addr_id INTEGER REFERENCES Addresses (id),
  active BOOLEAN;
  PRIMARY KEY (student_id, addr_id)   -- Composite primary key
);


INSERT INTO students (name, ssn, dob) VALUES ('bob', '123456789', '01-01-1999');

UPDATE students SET name = 'tom', dob = '01-02-1999';

UPDATE students
SET name = 'tom', dob = '01-02-1999'
WHERE id = 12;

-- Delete a student
DELETE FROM students
WHERE id = 12;

-- Kill the table entirely
DROP TABLE students;

--  -------- QUERIES --------------

-- JOINS


-- Self join

CREATE TABLE Residences (
  student_id REFERENCES Students (id),
  building VARCHAR(50),
  room VARCHAR(20),
);

-- Create a list of roomates. Watch for dupes
Select a.student_id, b.student_id, a.building, a.room
FROM Residences a, Residences b
WHERE
  a.building = b.building AND
  a.room = b.room AND
  a.student_id < b.student_id -- avoid dupes and same id