12-Test03.md

Test 03

-- Create db School

-- Create table teachers
-- teachers columns teacher_id primary key, first_name, last_name, homeroom_number,
-- department, email unique, phone unique
create table teachers(
    teacher_id serial primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    homeroom_number integer not null,
    department varchar(50) not null,
    email varchar(100) not null unique,
    phone varchar(50) not null unique
);

-- Create table students
-- students columns student_id primary key, first_name, last_name, homeroom_number,
-- phone not null unique, email unique, and graduation_year
create table students(
    student_id serial primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    homeroom_number integer not null,
    phone varchar(50) not null unique,
    email varchar(100) unique,
    graduation_year integer not null
);

-- Insert student Mark Watney, id = 1, phone 777-555-1234, email null, graduates 2035, homeroom 5
insert into students(
    first_name, last_name, homeroom_number, phone, graduation_year)
values(
    'Mark', 'Watney', 5, '777-555-1234', 2035
);

-- insert teacher Jonas Salk, id = 1, homeroom 5, biology department, email jsalk@school.org, phone 777-555-4321
insert into teachers(
    first_name, last_name, homeroom_number, department, email, phone)
values(
    'Jonas', 'Salk', 5, 'Biology', 'jsalk@school.org', '777-555-4321'
);