formula1.sql

gistfile1.txt

formula1.sql

CREATE TABLE Driver (
    DriverID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Nationality VARCHAR(50),
    DateOfBirth DATE
);
 
CREATE TABLE Team (
    TeamID INT PRIMARY KEY,
    TeamName VARCHAR(100) NOT NULL,
    Base VARCHAR(100),
    Principal VARCHAR(100)
);

CREATE TABLE Race (
    RaceID INT PRIMARY KEY,
    RaceName VARCHAR(100) NOT NULL,
    Location VARCHAR(100),
    Date DATE
);

CREATE TABLE Result (
    ResultID INT PRIMARY KEY,
    DriverID INT,
    RaceID INT,
    Position INT,
    Points DECIMAL(5, 2),
    FOREIGN KEY (DriverID) REFERENCES Driver(DriverID),
    FOREIGN KEY (RaceID) REFERENCES Race(RaceID)
);

CREATE TABLE Participation (
    ParticipationID INT PRIMARY KEY,
    DriverID INT,
    TeamID INT,
    Season YEAR,
    FOREIGN KEY (DriverID) REFERENCES Driver(DriverID),
    FOREIGN KEY (TeamID) REFERENCES Team(TeamID)
);

linkedin.sql

CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    firstName VARCHAR(50) NOT NULL,
    lastName VARCHAR(50) NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(50) NOT NULL
);

CREATE TABLE Schools (
    SchoolID INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(50) NOT NULL,
    location VARCHAR(100),
    foundedYear YEAR
);
CREATE TABLE Companies (
    CompanyID INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    industry VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);
CREATE TABLE Connections (
    ConnectionID INT PRIMARY KEY AUTO_INCREMENT,
    user1ID INT,
    user2ID INT,
    connectionDate DATE,
    FOREIGN KEY (user1ID) REFERENCES Users(UserID),
    FOREIGN KEY (user2ID) REFERENCES Users(UserID)
);
CREATE TABLE UserSchoolAffiliations (
    AffiliationID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT,
    SchoolID INT,
    startDate DATE,
    endDate DATE,
    degree VARCHAR(50),
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (SchoolID) REFERENCES Schools(SchoolID)
);
CREATE TABLE UserCompanyAffiliations (
    AffiliationID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT,
    CompanyID INT,
    startDate DATE,
    endDate DATE,
    title VARCHAR(100),
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID)
);

-- 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');

-- Insert Schools
INSERT INTO Schools (name, type, location, foundedYear) 
VALUES ('Harvard University', 'University', 'Cambridge, Massachusetts', 1636);


INSERT INTO Companies (name, industry, location) 
VALUES ('LinkedIn', 'Technology', 'Sunnyvale, California');


INSERT INTO UserSchoolAffiliations (UserID, SchoolID, startDate, endDate, degree) 
VALUES ((SELECT UserID FROM Users WHERE username='alan'), 
        (SELECT SchoolID FROM Schools WHERE name='Harvard University'), 
        '1973-09-01', '1976-06-01', 'BA');

INSERT INTO UserCompanyAffiliations (UserID, CompanyID, startDate, endDate, title) 
VALUES ((SELECT UserID FROM Users WHERE username='reid'), 
        (SELECT CompanyID FROM Companies WHERE name='LinkedIn'), 
        '2003-01-01', '2007-02-01', 'CEO and Chairman');