Project 1

Anh Tran, Samson Nguyen, Mohammad Abdellatif - Spring 2021

Project 1 for CSE 3330 by Team 18.

Tools used for the project: MySQL Workbench 8.0 CE

To create the tables and load the data, we used MySQL to make a model schema (File -> New Model). We used the data we were given and Figure 3.2 in the textbook (Ramez Elmarsi, Shamkant B. Navathe - Fundamentals of Database Systems-Pearson (2015)) to design the tables, name the columns and choose their data types.

To load the data into the tables, we imported each of text documents in the provided folder EntryFiles-1 into the inserts tab of the corresponding tables. Anh Tran was the main contributor up to here.

To generate the SQL CREATE statements, we used MySQL Workbench's forward engineering function (File -> Export -> Forward Engineer SQL CREATE Script), with DROP statements before each CREATE statement, as well as INSERT statements for tables. We then copied the generated code into a local database and ran it. Some of the generated code had to be modified to make foreign key constraints.

We ran into some trouble with bad inputs due to characters such as "\'", " ", and ",", so we had to comb over the entry files so that the code would accept them properly, then clear and reload the data into our model and regenerate the SQL CREATE script. Mohammad Abdellatif contributed most of the effort cleaning up the entry files and importing it to the database.

The SQL SELECT statements were written and ran in a MySQL Workbench query tab. Samson Nguyen wrote and tested the SQL SELECT statements.

Source code of SQL CREATE statements generated from MySQL Workbench:

-- MySQL Script generated by MySQL Workbench
-- Wed Mar  3 13:52:30 2021
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`DEPARTMENT`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`DEPARTMENT` ;

CREATE TABLE IF NOT EXISTS `mydb`.`DEPARTMENT` (
  `Name` VARCHAR(45) NOT NULL,
  `Depart_num` INT NOT NULL,
  `Manager_ssn` INT NULL,
  `Start_date` CHAR(11) NULL,
  PRIMARY KEY (`Name`, `Depart_num`),
  INDEX `Manager_ssn_idx` (`Manager_ssn` ASC) VISIBLE,
  INDEX `Depart_num_idx` (`Depart_num` ASC) VISIBLE
  )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`EMPLOYEE`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`EMPLOYEE` ;

CREATE TABLE IF NOT EXISTS `mydb`.`EMPLOYEE` (
  `Fname` VARCHAR(45) NULL,
  `Minit` VARCHAR(1) NULL,
  `Lname` VARCHAR(45) NULL,
  `Ssn` INT NOT NULL,
  `Bdate` CHAR(11) NULL,
  `Address` CHAR(45) NULL,
  `Sex` CHAR(1) NULL,
  `Salary` INT NULL,
  `Supervisor` INT NULL,
  `Depart_num` INT NULL,
  PRIMARY KEY (`Ssn`),
  INDEX `Depart_num_idx` (`Depart_num` ASC) VISIBLE,
  INDEX `Super_ssn_idx` (`Supervisor` ASC) VISIBLE
  )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`DEPT_LOCATIONS`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`DEPT_LOCATIONS` ;

CREATE TABLE IF NOT EXISTS `mydb`.`DEPT_LOCATIONS` (
  `Depart_num` INT NOT NULL,
  `Location` VARCHAR(45) NOT NULL,
  INDEX `Depart_num_idx` (`Depart_num` ASC) VISIBLE,
  INDEX `Location_idx` (`Location` ASC) VISIBLE,
  PRIMARY KEY (`Depart_num`, `Location`)
  )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`PROJECT`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`PROJECT` ;

CREATE TABLE IF NOT EXISTS `mydb`.`PROJECT` (
  `Name` VARCHAR(45) NOT NULL,
  `Number` INT NOT NULL,
  `Location` VARCHAR(45) NULL,
  `Depart_num` INT NULL,
  PRIMARY KEY (`Name`, `Number`),
  INDEX `Number_idx` (`Number` ASC) VISIBLE,
  INDEX `Depart_num_idx` (`Depart_num` ASC) VISIBLE,
  INDEX `Location_idx` (`Location` ASC) VISIBLE
  )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`WORKS_ON`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`WORKS_ON` ;

CREATE TABLE IF NOT EXISTS `mydb`.`WORKS_ON` (
  `Ssn` INT NOT NULL,
  `Project_Number` INT NOT NULL,
  `Hours` FLOAT NULL,
  PRIMARY KEY (`Ssn`, `Project_Number`),
  INDEX `Project_Number_idx` (`Project_Number` ASC) VISIBLE
  )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `mydb`.`DEPARTMENT`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`DEPARTMENT` (`Name`, `Depart_num`, `Manager_ssn`, `Start_date`) VALUES ('Research', 5, 333445555, '22-MAY-1978');
INSERT INTO `mydb`.`DEPARTMENT` (`Name`, `Depart_num`, `Manager_ssn`, `Start_date`) VALUES ('Administration', 4, 987654321, '01-JAN-1985');
INSERT INTO `mydb`.`DEPARTMENT` (`Name`, `Depart_num`, `Manager_ssn`, `Start_date`) VALUES ('Headquarters', 1, 888665555, '19-JUN-1971');
INSERT INTO `mydb`.`DEPARTMENT` (`Name`, `Depart_num`, `Manager_ssn`, `Start_date`) VALUES ('Software', 6, 111111100, '15-MAY-1999');
INSERT INTO `mydb`.`DEPARTMENT` (`Name`, `Depart_num`, `Manager_ssn`, `Start_date`) VALUES ('Hardware', 7, 444444400, '15-MAY-1998');
INSERT INTO `mydb`.`DEPARTMENT` (`Name`, `Depart_num`, `Manager_ssn`, `Start_date`) VALUES ('Sales', 8, 555555500, '01-JAN-1997');
INSERT INTO `mydb`.`DEPARTMENT` (`Name`, `Depart_num`, `Manager_ssn`, `Start_date`) VALUES ('HR', 9, 112244668, '01-FEB-1989');
INSERT INTO `mydb`.`DEPARTMENT` (`Name`, `Depart_num`, `Manager_ssn`, `Start_date`) VALUES ('Networking', 3, 110110110, '15-MAY-2009');
INSERT INTO `mydb`.`DEPARTMENT` (`Name`, `Depart_num`, `Manager_ssn`, `Start_date`) VALUES ('QA', 11, 913323708, '2-FEWB-2010');

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`EMPLOYEE`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('James', 'E', 'Borg', 888665555, '10-NOV-1927', '450 Stone Houston TX', 'M', 55000, null, 1);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Franklin', 'T', 'Wong', 333445555, '08-DEC-1945', '638 Voss Houston TX', 'M', 40000, 888665555, 5);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Jennifer', 'S', 'Wallace', 987654321, '20-JUN-1931', '291 Berry Bellaire TX', 'F', 43000, 888665555, 4);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Jared', 'D', 'James', 111111100, '10-OCT-1966', '123 Peachtr Atlanta GA', 'M', 85000, null, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Alex', 'D', 'Freed', 444444400, '09-OCT-1950', '4333 Pillsbury Milwaukee WI', 'M', 89000, null, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('John', 'C', 'James', 555555500, '30-JUN-1975', '766 Bloomington Sacramento CA', 'M', 81000, null, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('John', 'B', 'Smith', 123456789, '09-Jan-1955', '731 Fondren Houston TX', 'M', 30000, 333445555, 5);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Alicia', 'J', 'Zelaya', 999887777, '19-JUL-1958', '3321 Castle Spring TX', 'F', 25000, 987654321, 4);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Ramesh', 'K', 'Narayan', 666884444, '15-SEP-1952', '971 Fire Oak Humble TX', 'M', 38000, 333445555, 5);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Joyce', 'A', 'English', 453453453, '31-JUL-1962', '5631 Rice Oak Houston TX', 'F', 25000, 333445555, 5);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Ahmad', 'V', 'Jabbar', 987987987, '29-MAR-1959', '980 Dallas Houston TX', 'M', 25000, 987654321, 4);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Jon', 'C', 'Jones', 111111101, '14-NOV-1967', '111 Allgood Atlanta GA', 'M', 45000, 111111100, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Justin', null, 'Mark', 111111102, '12-JAN-1966', '2342 May Atlanta GA', 'M', 40000, 111111100, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Brad', 'C', 'Knight', 111111103, '13-FEB-1968', '176 MainSt. Atlanta GA', 'M', 44000, 111111100, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Evan', 'E', 'Wallis', 222222200, '16-JAN-1958', '134 Pelham Milwaukee WI', 'M', 92000, null, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Josh', 'U', 'Zell', 222222201, '22-MAY-1954', '266 McGrady Milwaukee WI', 'M', 56000, 222222200, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Andy', 'C', 'Vile', 222222202, '21-JUN-1944', '1967 Jordan Milwaukee WI', 'M', 53000, 222222200, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Tom', 'G', 'Brand', 222222203, '16-DEC-1966', '112 ThirdSt Milwaukee WI', 'M', 62500, 222222200, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Jenny', 'F', 'Vos', 222222204, '11-NOV-1967', '263 Mayberry Milwaukee WI', 'F', 61000, 222222201, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Chris', 'A', 'Carter', 222222205, '21-MAR-1960', '565 Jordan Milwaukee WI', 'F', 43000, 222222201, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Kim', 'C', 'Grace', 333333300, '23-OCT-1970', '667 MillsAve Sacramento CA', 'F', 79000, null, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Jeff', 'H', 'Chase', 333333301, '07-JAN-1970', '15 Bradbury Sacramento CA', 'M', 44000, 333333300, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Bonnie', 'S', 'Bays', 444444401, '19-JUN-1956', '111 Hollow Milwaukee WI', 'F', 70000, 444444400, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Alec', 'C', 'Best', 444444402, '18-JUN-1966', '233 Solid Milwaukee WI', 'M', 60000, 444444400, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Sam', 'S', 'Snedden', 444444403, '31-JUL-1977', '97 WindySt Milwaukee WI', 'M', 48000, 444444400, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Nandita', 'K', 'Ball', 555555501, '16-APR-1969', '222 Howard Sacramento CA', 'M', 62000, 555555500, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Bob', 'B', 'Bender', 666666600, '17-APR-1968', '8794 Garfield Chicago IL', 'M', 96000, null, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Jill', 'J', 'Jarvis', 666666601, '14-JAN-1966', '6234 Lincoln Chicago IL', 'F', 36000, 666666600, 9);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Kate', 'W', 'King', 666666602, '16-APR-1966', '1976 BooneTrace Chicago IL', 'F', 44000, 666666600, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Lyle', 'G', 'Leslie', 666666603, '09-JUN-1963', '417 HancockAve Chicago IL', 'M', 41000, 666666601, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Billie', 'J', 'King', 666666604, '01-JAN-1960', '556 Washington Chicago IL', 'F', 38000, 666666603, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Megan', 'G', 'Jones', 254937381, '02-MAR-1961', '528 StoneCT Chicago IL', 'F', 62000, 666666600, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Jon', 'A', 'Kramer', 666666605, '22-AUG-1964', '1988 WindyCreek Seattle WA', 'M', 41500, 666666603, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Ray', 'H', 'King', 666666606, '16-AUG-1949', '213 Delk Road Seattle WA', 'M', 44500, 666666604, 9);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Gerald', 'D', 'Small', 666666607, '15-MAY-1962', '122 Ball Street Dallas TX', 'M', 29000, 666666602, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Arnold', 'A', 'Head', 666666608, '19-MAY-1967', '233 Spring St Dallas TX', 'M', 33000, 666666602, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Helga', 'C', 'Pataki', 666666609, '11-MAR-1969', '101 Holyoke St Dallas TX', 'F', 32000, 666666602, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Naveen', 'B', 'Drew', 666666610, '23-MAY-1970', '198 Elm St Philadelphia PA', 'M', 34000, 666666607, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Carl', 'E', 'Reedy', 666666611, '21-JUN-1977', '213 Ball St Philadelphia PA', 'M', 32000, 666666610, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Sammy', 'G', 'Hall', 666666612, '11-JAN-1970', '433 Main Street Miami FL', 'M', 37000, 666666611, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Red', 'A', 'Bacher', 666666613, '21-MAY-1980', '196 Elm Street Miami FL', 'M', 33500, 666666612, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Roy', 'C', 'Lewallen', 999999999, '02-MAR-1977', '14 Wynncrest Street Dallas TX', 'M', 75500, 666666600, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('John', 'T', 'Ed', 222333444, '18-FEB-1981', '4505 West St Rochester NY', 'M', 30000, 555555501, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Jennifer', 'A', 'Joy', 223344667, '19-MAY-1976', '1204 Main St Miami FL', 'F', 45000, 666666613, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Kim', 'G', 'Ted', 444222666, '15-APR-1968', '3648 Tree Cir Austin TX', 'M', 50000, 999999999, 9);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Juan', 'G', 'Linda', 112244668, '23-JUN-1965', '1210 Apple St Austin TX', 'F', 55000, null, 9);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Jose', 'H', 'Barbara', 343434344, '28-FEB-1955', '905 East St Kleen TX', 'F', 35000, 444444400, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('willie', 'D', 'Mary', 234234234, '20-DEC-1961', '101 South St Arlington TX', 'F', 12000, 112244668, 9);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Erin', 'A', 'Maxfield', 242535609, '22-DEC-1971', '123 Copper St Arlington TX', 'F', 72000, 555555500, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Johny', 'C', 'Smith', 122344668, '26-JAN-1972', '1221 Diploma Dr. Arlington TX', 'M', 65000, 999999999, 9);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Sunil', 'D', 'Gupta', 110110110, '01-FEB-2001', '4312 Bowen Rd Arlington TX', 'M', 80000, 111111100, 3);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Penny', 'G', 'Wolowitz', 673466642, '21-JAN-1974', '42 South Blvd Miami FL', 'F', 17000, 222333444, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Michael', 'A', 'Morgan', 636669233, '11-MAY-1984', '26 Sunset Blvd Miami FL', 'M', 73500, 666666612, 5);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Sheldon', 'C', 'Cucuou', 849934919, '22-MAR-1974', '11 Hollywood Blvd Dallas TX', 'M', 35500, 888665555, 8);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Debra', 'T', 'Hall', 202843824, '11-MAR-1983', '45 NY St Rochester NY', 'F', 30000, 555555501, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Jisha', 'A', 'Carpenter', 292740167, '29-MAY-1985', '194 Beachdr Miami FL', 'F', 15000, 666666613, 1);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Gregory', 'G', 'Laurie', 444212096, '15-SEP-1969', '78 Tree Cir Houston TX', 'M', 90000, 444444400, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Lisa', 'G', 'House', 101248268, '29-JUN-1975', '12 Maple St Austin TX', 'F', 85000, null, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Leonard', 'H', 'Moody', 349273344, '09-FEB-1973', '908 Greek Row Austin TX', 'M', 45000, 444444400, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Jake', 'D', 'Sheen', 245239264, '25-DEC-1954', '20 North Co Arlington TX', 'M', 52000, 112244668, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Wilson', 'A', 'Holmes', 242916639, '02-JUN-1971', '21 South Co Arlington TX', 'M', 72500, 555555500, 4);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Cameron', 'D', 'Thirteen', 111422203, '04-MAY-2001', '22 Univ Blvd Arlington TX', 'F', 80000, 987987987, 4);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Joseph', 'K', 'Kirkish', 913323708, '04-MAR-1996', '22 UT Blvd Austin TX', 'M', 95000, null, 7);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Andrea', 'M', 'Sondrini', 614370310, '30-DEC-1996', '1450 Worthington St Houston TX', 'F', 65000, 444444401, 5);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Cindy', 'K', 'Burklow', 432765098, '23-FEB-1984', '2015 Neil Ave Miami FL', 'F', 45000, 444444402, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Zach', 'A', 'Geller', 913353347, '15-AUG-1990', '333 Pike Way Seattle WA', 'M', 55000, 444444403, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Alex', 'C', 'Yu', 001614905, '17-OCT-1980', '626 Mary St Dallas TX', 'M', 50000, 444444400, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Richard', 'T', 'Koelbel', 214370999, '3-APR-1976', '50 Elk St Chicago IL', 'M', 85000, 999999999, 4);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Christina', 'S', 'Hisel', 241625699, '5-JUL-1986', '37 Church Row Rochester NY', 'F', 45000, 123456789, 6);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('Percy', 'M', 'Liang', 398172999, '12-JUN-1991', '14 Maple St Austin TX', 'M', 55000, null, 9);
INSERT INTO `mydb`.`EMPLOYEE` (`Fname`, `Minit`, `Lname`, `Ssn`, `Bdate`, `Address`, `Sex`, `Salary`, `Supervisor`, `Depart_num`) VALUES ('James', 'U', 'Miller', 906218888, '27-MAY-1978', '13 Fifth St Seattle WA', 'M', 75000, 999999999, 5);

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`DEPT_LOCATIONS`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (1, 'Houston');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (4, 'Stafford');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (5, 'Bellaire');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (5, 'Sugarland');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (5, 'Houston');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (6, 'Atlanta');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (6, 'Sacramento');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (7, 'Milwaukee');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (8, 'Chicago');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (8, 'Dallas');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (8, 'Philadephia');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (8, 'Seattle');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (8, 'Miami');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (9, 'Arlington');
INSERT INTO `mydb`.`DEPT_LOCATIONS` (`Depart_num`, `Location`) VALUES (11, 'Austin');

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`PROJECT`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('ProductX', 1, 'Bellaire', 5);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('ProductY', 2, 'Sugarland', 5);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('ProductZ', 3, 'Houston', 5);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('Computerization', 10, 'Stafford', 4);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('Reorganization', 20, 'Houston', 1);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('Newbenefits', 30, 'Stafford', 4);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('OperatingSystem', 61, 'Sacramento', 6);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('DatabaseSystems', 62, 'Atlanta', 6);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('Middleware', 63, 'Atlanta', 6);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('Advertizing', 70, 'Arlington', 9);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('InkjetPrinters', 91, 'Milwaukee', 7);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('LaserPrinters', 92, 'Milwaukee', 7);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('Human1', 95, 'Arlington', 9);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('SearchEngine', 22, 'Arlington', 6);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('MotherBoard', 29, 'Milwaukee', 7);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('EntityAnnot', 4, 'Houston', 5);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('ConfigMgmt', 11, 'Atlanta', 6);
INSERT INTO `mydb`.`PROJECT` (`Name`, `Number`, `Location`, `Depart_num`) VALUES ('DataMining', 13, 'Sacramento', 6);

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`WORKS_ON`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (123456789, 1, 32.5);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (123456789, 2, 7.5);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666884444, 3, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (453453453, 1, 20.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (453453453, 2, 20.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (333445555, 2, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (333445555, 3, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (333445555, 10, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (333445555, 20, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (242535609, 70, 20.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (242535609, 62, 20.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999887777, 30, 30.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999887777, 10, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (987987987, 10, 35.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (987987987, 30, 5.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (987654321, 30, 20.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (987654321, 20, 15.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (888665555, 20, 5.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (111111100, 61, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (111111101, 61, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (111111102, 61, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (111111103, 61, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (222222200, 62, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (222222201, 62, 48.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (222222202, 62, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (222222203, 62, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (222222204, 62, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (222222205, 62, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (333333300, 63, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (333333301, 63, 46.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (444444400, 91, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (444444401, 91, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (444444402, 91, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (444444403, 91, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (555555500, 92, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (555555501, 92, 44.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666601, 91, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666603, 91, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666604, 91, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666605, 92, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666606, 91, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666607, 61, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666608, 62, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666609, 63, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666610, 61, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666611, 61, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666612, 61, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666613, 61, 30.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666613, 62, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (666666613, 63, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 1, 2.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 2, 2.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 3, 4.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 10, 4.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 20, 4.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 30, 4.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 61, 4.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 62, 4.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 63, 4.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 70, 2.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 91, 2.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 92, 1.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (999999999, 95, 3.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (254937381, 70, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (222333444, 91, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (223344667, 63, 20.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (444222666, 62, 25.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (112244668, 95, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (343434344, 63, 40.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (234234234, 95, 35.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (913323708, 92, 33.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (636669233, 4, 11.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (614370310, 3, 45.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (849934919, 95, 23.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (432765098, 63, 25.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (444212096, 63, 25.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (913353347, 22, 30.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (292740167, 1, 25.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (202843824, 11, 20.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (673466642, 22, 4.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (101248268, 29, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (245239264, 11, 25.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (242916639, 4, 5.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (906218888, 29, 15.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (001614905, 13, 18.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (245239264, 10, 25.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (349273344, 29, 15.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (242916639, 11, 20.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (214370999, 10, 35.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (111422203, 4, 45.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (398172999, 70, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (241625699, 61, 4.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (122344668, 3, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (122344668, 20, 10.0);
INSERT INTO `mydb`.`WORKS_ON` (`Ssn`, `Project_Number`, `Hours`) VALUES (122344668, 30, 25.0);

ALTER TABLE `mydb`.`DEPARTMENT` ADD CONSTRAINT `Manager_ssn`
	FOREIGN KEY (`Manager_ssn`)
    REFERENCES `mydb`.`EMPLOYEE` (`Ssn`);
    
ALTER TABLE `mydb`.`EMPLOYEE` ADD CONSTRAINT `Employee_depart_num`
    FOREIGN KEY (`Depart_num`)
    REFERENCES `mydb`.`DEPARTMENT` (`Depart_num`);
ALTER TABLE `mydb`.`EMPLOYEE` ADD CONSTRAINT `Supervisor`
    FOREIGN KEY (`Supervisor`)
    REFERENCES `mydb`.`EMPLOYEE` (`Ssn`);
    
ALTER TABLE `mydb`.`DEPT_LOCATIONS` ADD CONSTRAINT `Dept_location_depart_num`
    FOREIGN KEY (`Depart_num`)
    REFERENCES `mydb`.`DEPARTMENT` (`Depart_num`);
    
ALTER TABLE `mydb`.`PROJECT` ADD CONSTRAINT `Project_depart_num`
    FOREIGN KEY (`Depart_num`)
    REFERENCES `mydb`.`DEPARTMENT` (`Depart_num`);
ALTER TABLE `mydb`.`PROJECT` ADD CONSTRAINT `Location`
    FOREIGN KEY (`Location`)
    REFERENCES `mydb`.`DEPT_LOCATIONS` (`Location`);

ALTER TABLE `mydb`.`WORKS_ON` ADD CONSTRAINT `Ssn`
    FOREIGN KEY (`Ssn`)
    REFERENCES `mydb`.`EMPLOYEE` (`Ssn`);
ALTER TABLE `mydb`.`WORKS_ON` ADD CONSTRAINT `Project_Number`
    FOREIGN KEY (`Project_Number`)
    REFERENCES `mydb`.`PROJECT` (`Number`);

COMMIT;

Source code of SQL SELECT statements and query results:

/* Enter a department name, and retrieve all the names and salaries of all employees who work in
   that department */
SELECT 
    Fname, Minit, Lname, Salary
FROM
    EMPLOYEE
        NATURAL JOIN
    DEPARTMENT
WHERE
    Name = 'Research';

Fname	Minit	Lname	Salary
John	B	Smith	30000
Franklin	T	Wong	40000
Joyce	A	English	25000
Andrea	M	Sondrini	65000
Michael	A	Morgan	73500
Ramesh	K	Narayan	38000
James	U	Miller	75000

/* Enter an employee last name and first name and retrieve a list of projects names/hours per week
   that the employee works on. */
SELECT 
    P.Name 'proj name', W.Hours 'hrs/week'
FROM
    EMPLOYEE AS E
        NATURAL JOIN
    WORKS_ON AS W
        JOIN
    PROJECT AS P ON P.Number = W.Project_Number
WHERE
    E.Fname = 'John' AND E.Lname = 'Smith';

proj name	hrs/week
ProductX	32.5
ProductY	7.5

/* Enter a department name and retrieve the total of all employee salaries who work in the department. */
SELECT 
    SUM(E.Salary)
FROM
    EMPLOYEE AS E
        NATURAL JOIN
    DEPARTMENT AS D
WHERE
    D.Name = 'Research';

SUM(E.Salary)
346500

/* For each department, retrieve the department name and the number (count) of employees who work in
   that department. Order the result by number of employees in descending order. */
SELECT 
    D.Name 'department name', COUNT(E.Ssn) 'no of employees'
FROM
    DEPARTMENT AS D
        JOIN
    EMPLOYEE AS E ON D.Depart_num = E.Depart_num
GROUP BY D.Name
ORDER BY COUNT(E.Ssn) DESC;

department name	no of employees
Sales	18
Software	16
Hardware	14
HR	7
Research	7
Administration	6
Headquarters	2
Networking	1

/* For each employee who is a supervisor, retrieve the employee first and last name and the number
   (count) of employees that are supervised. Order the result in descending order. */
SELECT 
    S.Fname 'first name',
    S.Lname 'last name',
    COUNT(E.Supervisor) 'no. employees supervised'
FROM
    EMPLOYEE AS S,
    EMPLOYEE AS E
WHERE
    S.Ssn = E.Supervisor
GROUP BY S.Ssn
ORDER BY COUNT(E.Supervisor) DESC;

first name	last name	no. employees supervised
Alex	Freed	7
Jared	James	4
Bob	Bender	4
Roy	Lewallen	4
Evan	Wallis	3
Franklin	Wong	3
John	James	3
Kate	King	3
James	Borg	3
Juan	Linda	2
Josh	Zell	2
Nandita	Ball	2
Lyle	Leslie	2
Sammy	Hall	2
Red	Bacher	2
Jennifer	Wallace	2
John	Smith	1
John	Ed	1
Kim	Grace	1
Bonnie	Bays	1
Alec	Best	1
Sam	Snedden	1
Jill	Jarvis	1
Billie	King	1
Gerald	Small	1
Naveen	Drew	1
Carl	Reedy	1
Ahmad	Jabbar	1

Name		Name	Last commit message	Last commit date
Latest commit History 1 Commit
README.md		README.md
proj1_queries.sql		proj1_queries.sql
proj_1_CREATE.sql		proj_1_CREATE.sql

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Repository files navigation

Project 1

About

Uh oh!

Releases

Packages

mosham08/MySQL_Creating_Tables

Folders and files

Latest commit

History

Repository files navigation

Project 1

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Packages