Exercises Week 2

formula1.sql

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