zach sucks

use mccants;

SET SQL_SAFE_UPDATES = 0;


DROP TABLE IF EXISTS jobs;
DROP TABLE IF EXISTS skillss;
DROP TABLE IF EXISTS company_addresses;
DROP TABLE IF EXISTS education;
DROP TABLE IF EXISTS sharedresume;
DROP TABLE IF EXISTS pastschools;
DROP TABLE IF EXISTS schools;
DROP TABLE IF EXISTS company;
DROP TABLE IF EXISTS resume;
DROP TABLE IF EXISTS users;


CREATE TABLE users(FirstName varchar(50) NOT NULL,LastName varchar(50) not null,email varchar(50) NOT NULL UNIQUE PRIMARY KEY);
CREATE TABLE education(gpa varchar(7),useremail varchar(50) not null,sname varchar(50) not null,startdate date,enddate date,foreign key (useremail) references users(email));
create TABLE schools(name varchar(50) not null unique primary key ,address varchar(250)not null);
create table pastschools(useremail varchar(50) not null,sname varchar (50) not null,startdate date,enddate date,foreign key(sname) references schools(name),foreign key(useremail) references users(email));
create table company(name varchar(50) not null unique primary key);
create table company_addresses(name varchar(50) not null,address varchar(50) not null,foreign key(name) references company(name));
create table jobs(useremail varchar(50)not null,company varchar(50) not null,profile varchar(250),start date,ending date,foreign key(useremail) references users(email),foreign key(company) references company(name));
create table skillss(name varchar(50) not null,description varchar (250) not null,useremail varchar(50)not null,foreign key(useremail) references users(email));
create table resume(useremail varchar(50),resume_id int NOT NULL AUTO_INCREMENT primary key,foreign key(useremail) references users(email));
create table sharedresume(useremail varchar(50),resume_id int,foreign key(useremail) references users(email),foreign key(resume_id) references resume(resume_id));


INSERT INTO users(FirstName,LastName, email)
values ('larck', 'andballs', '123@gmail.com'),
('steve', 'balls', '234@gmail.com'),
('bill', 'harden', '345@gmail.com'),
('balls', 'andies', '456@gmail.com');


INSERT INTO schools(name,address)
values('harvard','1234 tigers st MA USA'),
('stanford','3333 pizza st ca USA'),
('cal','1234 burger st ca USA'),
('MIT','3333 tigers st MA USA');

INSERT INTO education(gpa,useremail,sname)
values('5','123@gmail.com','stanford'),
('7','234@gmail.com','stanford'),
('3','345@gmail.com','stanford'),
('2','456@gmail.com','harvard');

INSERT INTO company(name)
values('google'),
('tiger'),
('bing'),
('yahoo');

INSERT INTO company_addresses(name,address)
values('google','1234 hickup st ca'),
('tiger','111 jay st ca'),
('bing','555 pizza ave ca'),
('yahoo','333 locks st ka'),
('google','999 hickup st ja'),
('tiger','777 jay st ca'),
('bing','344 pizza ave ja'),
('yahoo','666 locks st ma');

INSERT INTO pastschools(useremail,sname,startdate,enddate)
values('123@gmail.com','harvard','2009-09-01','2012-05-01'),
('234@gmail.com','mit','2006-09-01','2009-05-01'),
('345@gmail.com','harvard','2012-09-01','2016-05-01'),
('456@gmail.com','stanford','2010-09-01','2014-05-01');

INSERT INTO jobs(useremail,company, profile,start,ending)
values('123@gmail.com','google','programing and stuff with pizza','2009-09-01','2012-05-01'),
('234@gmail.com','yahoo','programing and stuff with pizza','2012-09-01','2014-05-01'),
('345@gmail.com','bing','programing and stuff with pizza','1999-09-01','2012-05-01'),
('456@gmail.com','tiger','programing and stuff with pizza','1991-09-01','2012-05-01');

INSERT INTO skillss(name, description, useremail)
values('lumberjacking','chopping wood and studd int the woods','123@gmail.com'),
('compters','chopping wood and studd int the woods','234@gmail.com'),
('jessus','relugion andchopping wood and studd int the woods','345@gmail.com'),
('korrean','korrean wood chopping wood and studd int the woods','456@gmail.com');

INSERT INTO sharedresume(useremail)
values('123@gmail.com'),('123@gmail.com'),('123@gmail.com'),('123@gmail.com');