linkedin.sql

CREATE TABLE users (
user_id INT NOT NULL,
first_name TEXT,
last_name TEXT,
username VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255),
PRIMARY KEY (user_id)
)

CREATE TABLE schools (
school_id INT NOT NULL,
name TEXT,
type TEXT,
location TEXT,
founded_year INT,
PRIMARY KEY (school_id)
)

CREATE TABLE companies (
company_id INT NOT NULL,
name TEXT,
industry TEXT,
location TEXT,
founded_year INT,
PRIMARY KEY (company_id)
)

CREATE TABLE user_connections (
id INT NOT NULL,
user1 INT,
user2 INT,
matual BOOLEAN,
PRIMARY KEY (id),
FOREIGN KEY (user1) REFERENCES Users(user_id),
FOREIGN KEY (user2) REFERENCES Users(user_id)
)

CREATE TABLE user_companies (
id INT NOT NULL,
user_id INT,
company_id INT,
start_date DATE,
end_date DATE,
title TEXT,
current_employment BOOLEAN,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (company_id) REFERENCES Companies(company_id)
)

CREATE TABLE user_schools (
id INT NOT NULL,
user_id INT,
school_id INT,
start_date DATE,
end_date DATE,
graduated BOOLEAN,
degree TEXT,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (school_id) REFERENCES Schools(school_id)
)

-- A user, Alan Garber, whose username is “alan” and password is “password”.
INSERT INTO Users (user_id, first_name, last_name, username, password) VALUES (1,'Alan', 'Garber', 'alan', 'password');

-- A user, Reid Hoffman whose username is “reid” and password is “password”.
INSERT INTO Users (user_id, first_name, last_name, username, password) VALUES (2,'Reid', 'Hoffman', 'reid', 'password');

-- A school, Harvard University, which is a university located in Cambridge, Massachusetts, founded in 1636.
INSERT INTO schools (school_id, name, type, location, founded_year) VALUES (1,'Harvard University', 'University', 'Cambridge, Massachusetts', '1636');

-- A company, LinkedIn, which is a technology company headquartered in Sunnyvale, California.
INSERT INTO companies (company_id , name, industry , location) VALUES (1,'LinkedIn', 'technology', 'Sunnyvale, California');

-- Alan Garber’s undergraduate education at Harvard, pursuing a BA from September 1st, 1973 to June 1st, 1976.
INSERT INTO user_schools (id, user_id , school_id , start_date, end_date, degree, graduated) VALUES (1,1,1, '1973-09-01', '1976-06-01', 'BA', true);

-- Reid Hoffman’s employment with LinkedIn as its CEO and Chairman, from January 1st, 2003 to February 1st, 2007.
INSERT INTO user_companies (id, user_id , company_id , start_date, end_date, title , current_employment) VALUES (1,2,1, '2003-01-01', '2007-02-01', 'CEO', false);