Last active
June 21, 2024 07:07
-
-
Save nibishakajean/de35926c1fadfef5fac752c097d0ecac to your computer and use it in GitHub Desktop.
Exercises Week 2
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Exercises Week 2 | |
--Exercise 3: Formula 1 | |
-- Creating Team table | |
CREATE TABLE Team ( | |
name VARCHAR(100) PRIMARY KEY, | |
country VARCHAR(100) | |
); | |
-- Creating Car table | |
CREATE TABLE Car ( | |
carNumber INT PRIMARY KEY, | |
weight FLOAT, | |
maxSpeed FLOAT, | |
teamName VARCHAR(100), | |
FOREIGN KEY (teamName) REFERENCES Team(name) | |
); | |
-- Creating Pilot table | |
CREATE TABLE Pilot ( | |
name VARCHAR(100), | |
birthDate DATE, | |
country VARCHAR(100), | |
carNumber INT, | |
PRIMARY KEY (name), | |
FOREIGN KEY (carNumber) REFERENCES Car(carNumber) | |
); | |
-- Creating Circuit table | |
CREATE TABLE Circuit ( | |
city VARCHAR(100), | |
country VARCHAR(100), | |
distance FLOAT, | |
PRIMARY KEY (city, country) | |
); | |
-- Creating Race table | |
CREATE TABLE Race ( | |
raceID INT PRIMARY KEY AUTO_INCREMENT, | |
date DATE, | |
numberOfLaps INT, | |
circuitCity VARCHAR(100), | |
circuitCountry VARCHAR(100), | |
FOREIGN KEY (circuitCity, circuitCountry) REFERENCES Circuit(city, country) | |
); | |
-- Creating Participation table | |
CREATE TABLE Participation ( | |
participationID INT PRIMARY KEY AUTO_INCREMENT, | |
pilotName VARCHAR(100), | |
raceID INT, | |
startingPosition INT, | |
finalPosition INT, | |
FOREIGN KEY (pilotName) REFERENCES Pilot(name), | |
FOREIGN KEY (raceID) REFERENCES Race(raceID) | |
); | |
----------------------------------------------------------------------------------------------------------------- | |
----------------------------------------------------------------------------------------------------------------- | |
--Exercise 4: LinkedIn | |
-- linkedin.sql | |
-- Create table for Users | |
CREATE TABLE Users ( | |
UserID INT AUTO_INCREMENT PRIMARY KEY, | |
FirstName VARCHAR(255) NOT NULL, | |
LastName VARCHAR(255) NOT NULL, | |
Username VARCHAR(255) UNIQUE NOT NULL, | |
Password VARCHAR(255) NOT NULL | |
); | |
-- Create table for Schools | |
CREATE TABLE Schools ( | |
SchoolID INT AUTO_INCREMENT PRIMARY KEY, | |
SchoolName VARCHAR(255) NOT NULL, | |
SchoolType VARCHAR(255) NOT NULL, | |
Location VARCHAR(255) NOT NULL, | |
YearFounded INT | |
); | |
-- Create table for Companies | |
CREATE TABLE Companies ( | |
CompanyID INT AUTO_INCREMENT PRIMARY KEY, | |
CompanyName VARCHAR(255) NOT NULL, | |
Industry VARCHAR(255) NOT NULL, | |
Location VARCHAR(255) NOT NULL | |
); | |
-- Create table for Connections (between users) | |
CREATE TABLE Connections ( | |
ConnectionID INT AUTO_INCREMENT PRIMARY KEY, | |
UserID1 INT NOT NULL, | |
UserID2 INT NOT NULL, | |
FOREIGN KEY (UserID1) REFERENCES Users(UserID), | |
FOREIGN KEY (UserID2) REFERENCES Users(UserID), | |
UNIQUE (UserID1, UserID2) | |
); | |
-- Create table for Affiliations with Schools | |
CREATE TABLE SchoolAffiliations ( | |
AffiliationID INT AUTO_INCREMENT PRIMARY KEY, | |
UserID INT NOT NULL, | |
SchoolID INT NOT NULL, | |
StartDate DATE NOT NULL, | |
EndDate DATE, | |
DegreeType VARCHAR(255), | |
FOREIGN KEY (UserID) REFERENCES Users(UserID), | |
FOREIGN KEY (SchoolID) REFERENCES Schools(SchoolID) | |
); | |
-- Create table for Affiliations with Companies | |
CREATE TABLE CompanyAffiliations ( | |
AffiliationID INT AUTO_INCREMENT PRIMARY KEY, | |
UserID INT NOT NULL, | |
CompanyID INT NOT NULL, | |
StartDate DATE NOT NULL, | |
EndDate DATE, | |
Title VARCHAR(255), | |
FOREIGN KEY (UserID) REFERENCES Users(UserID), | |
FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID) | |
); | |
-- Insert sample data | |
-- Insert users | |
INSERT INTO Users (FirstName, LastName, Username, Password) VALUES ('Alan', 'Garber', 'alan', 'password'); | |
INSERT INTO Users (FirstName, LastName, Username, Password) VALUES ('Reid', 'Hoffman', 'reid', 'password'); | |
SELECT * FROM Users u ; | |
-- Insert schools | |
INSERT INTO Schools (SchoolName, SchoolType, Location, YearFounded) VALUES ('Harvard University', 'University', 'Cambridge, Massachusetts', 1636); | |
SELECT * FROM Schools; | |
-- Insert companies | |
INSERT INTO Companies (CompanyName, Industry, Location) VALUES ('LinkedIn', 'Technology', 'Sunnyvale, California'); | |
SELECT * FROM Companies c ; | |
-- Insert affiliations with schools | |
INSERT INTO SchoolAffiliations (UserID, SchoolID, StartDate, EndDate, DegreeType) VALUES (1, 1, '1973-09-01', '1976-06-01', 'BA'); | |
SELECT * FROM SchoolAffiliations sa ; | |
-- Insert affiliations with companies | |
INSERT INTO CompanyAffiliations (UserID, CompanyID, StartDate, EndDate, Title) VALUES (2, 1, '2003-01-01', '2007-02-01', 'CEO and Chairman'); | |
SELECT * FROM CompanyAffiliations ca; | |
-- Insert mutual connection between Alan and Reid | |
INSERT INTO Connections (UserID1, UserID2) VALUES (1, 2); | |
INSERT INTO Connections (UserID1, UserID2) VALUES (2, 1); | |
SELECT * FROM Connections c ; | |
-- End of linkedin.sql |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment