abandonedusername

--drop tables
drop table faculty;
drop table staff;
drop table employee;
drop table person;

--create person table
create table person(
    person_id int generated always as identity start with 100 primary key,
    first_name varchar(20),

abandonedusername

drop table staff;
drop table faculty;
drop table employee;
drop table student;
drop table person;

------------------------set the default date format-------------------------
alter session set nls_date_format = 'YYYY-MM-DD';

------------------------- person table -------------------------

abandonedusername

drop table part; 

create table part(
    part_no int primary key,
    description varchar(50)
);

insert into part values(104, 'Headlamp Lens');
insert into part values(107, 'High-beam bulb');
insert into part values(108, 'Low-beam bulb');

abandonedusername

-- these statements may fail the first time you run this script becuase the tables do not yet exist
drop table appointment;
drop table room;
drop table employee;
drop table client;
drop table counselor;

/**create tables**/
create table Counselor(
	CounselorID int GENERATED ALWAYS as IDENTITY(START with 100) primary key,

abandonedusername

-- create a schema (kind of like a folder) within the database for RAFT. Then set it as the default schema for the rest of this script.
CREATE SCHEMA IF NOT EXISTS raft;
SET search_path TO raft;


-- drop tables if they exist so we can start over
drop table if exists appointment;
drop table if exists room;
drop table if exists employee;

abandonedusername

-------------------------Drop tables in case you have created them------------------------
drop view all_movie_data;
drop table actor_nomination;
drop table award;
drop table actor_in_role;
drop table actor;
drop table role;
drop table character;
drop table movie;

abandonedusername

create schema if not exists raft;
SET search_path TO raft;

-- drop tables in reverse order
drop table if exists appointment;
drop table if exists room;
drop table if exists client;
drop table if exists counselor;

abandonedusername

create table employee(
    employee_id int generated always as identity start with 100 primary key,
    first_name varchar(20),
    last_name varchar(30) not null,
    title varchar(20),
    supervisor_id int references employee not null
);

insert into employee (first_name, last_name, title, supervisor_id) values('Joseph','Quimby','Mayor',100);
insert into employee (first_name, last_name, title, supervisor_id) values('Gary','Chalmers','Superintendent',100);