Y9CyH7bs

1use mccants;
2
3SET SQL_SAFE_UPDATES = 0;
4
5
6DROP TABLE IF EXISTS jobs;
7DROP TABLE IF EXISTS skillss;
8DROP TABLE IF EXISTS company_addresses;
9DROP TABLE IF EXISTS education;
10DROP TABLE IF EXISTS sharedresume;
11DROP TABLE IF EXISTS pastschools;
12DROP TABLE IF EXISTS schools;
13DROP TABLE IF EXISTS company;
14DROP TABLE IF EXISTS resume;
15DROP TABLE IF EXISTS users;
16
17
18CREATE TABLE users(FirstName varchar(50) NOT NULL,LastName varchar(50) not null,email varchar(50) NOT NULL UNIQUE PRIMARY KEY);
19CREATE 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));
20create TABLE schools(name varchar(50) not null unique primary key ,address varchar(250)not null);
21create 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));
22create table company(name varchar(50) not null unique primary key);
23create table company_addresses(name varchar(50) not null,address varchar(50) not null,foreign key(name) references company(name));
24create 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));
25create table skillss(name varchar(50) not null,description varchar (250) not null,useremail varchar(50)not null,foreign key(useremail) references users(email));
26create table resume(useremail varchar(50),resume_id int NOT NULL AUTO_INCREMENT primary key,foreign key(useremail) references users(email));
27create table sharedresume(useremail varchar(50),resume_id int,foreign key(useremail) references users(email),foreign key(resume_id) references resume(resume_id));
28
29
30INSERT INTO users(FirstName,LastName, email)
31values ('larck', 'andballs', '123@gmail.com'),
32('steve', 'balls', '234@gmail.com'),
33('bill', 'harden', '345@gmail.com'),
34('balls', 'andies', '456@gmail.com');
35
36
37INSERT INTO schools(name,address)
38values('harvard','1234 tigers st MA USA'),
39('stanford','3333 pizza st ca USA'),
40('cal','1234 burger st ca USA'),
41('MIT','3333 tigers st MA USA');
42
43INSERT INTO education(gpa,useremail,sname)
44values('5','123@gmail.com','stanford'),
45('7','234@gmail.com','stanford'),
46('3','345@gmail.com','stanford'),
47('2','456@gmail.com','harvard');
48
49INSERT INTO company(name)
50values('google'),
51('tiger'),
52('bing'),
53('yahoo');
54
55INSERT INTO company_addresses(name,address)
56values('google','1234 hickup st ca'),
57('tiger','111 jay st ca'),
58('bing','555 pizza ave ca'),
59('yahoo','333 locks st ka'),
60('google','999 hickup st ja'),
61('tiger','777 jay st ca'),
62('bing','344 pizza ave ja'),
63('yahoo','666 locks st ma');
64
65INSERT INTO pastschools(useremail,sname,startdate,enddate)
66values('123@gmail.com','harvard','2009-09-01','2012-05-01'),
67('234@gmail.com','mit','2006-09-01','2009-05-01'),
68('345@gmail.com','harvard','2012-09-01','2016-05-01'),
69('456@gmail.com','stanford','2010-09-01','2014-05-01');
70
71INSERT INTO jobs(useremail,company, profile,start,ending)
72values('123@gmail.com','google','programing and stuff with pizza','2009-09-01','2012-05-01'),
73('234@gmail.com','yahoo','programing and stuff with pizza','2012-09-01','2014-05-01'),
74('345@gmail.com','bing','programing and stuff with pizza','1999-09-01','2012-05-01'),
75('456@gmail.com','tiger','programing and stuff with pizza','1991-09-01','2012-05-01');
76
77INSERT INTO skillss(name, description, useremail)
78values('lumberjacking','chopping wood and studd int the woods','123@gmail.com'),
79('compters','chopping wood and studd int the woods','234@gmail.com'),
80('jessus','relugion andchopping wood and studd int the woods','345@gmail.com'),
81('korrean','korrean wood chopping wood and studd int the woods','456@gmail.com');
82
83INSERT INTO sharedresume(useremail)
84values('123@gmail.com'),('123@gmail.com'),('123@gmail.com'),('123@gmail.com');