This is a collection of working files for our project.

create_db.sql

/*
Create tables by considering the following:
1.       A primary key for the relation.
2.       Foreign key (referential integrity) constraints  
3.       NOT NULL constraints and default values as appropriate.
*/
use jl790a;

/*
DROP TABLE CourseRoster;
DROP TABLE EmployeeCourses;
DROP TABLE Courses;
DROP TABLE DepartmentEmployees;
DROP TABLE Students;
DROP TABLE FoodServices;
DROP TABLE Departments;
*/

CREATE TABLE CourseRoster (
  CourseID int NOT NULL,
  StudentID int NOT NULL,
  PRIMARY KEY (CourseID, StudentID)
);

CREATE TABLE EmployeeCourses (
  EmpID int NOT NULL,
  CourseID int NOT NULL,
  PRIMARY KEY (EmpID, CourseID)
);
 
CREATE TABLE Courses (
  CourseID int NOT NULL,
  CourseName varchar(255) NOT NULL,
  CourseDesc text NOT NULL,
  PRIMARY KEY (CourseID),
);

CREATE TABLE DepartmentEmployees (
  DeptID int NOT NULL,
  EmpID int NOT NULL,
  PRIMARY KEY (DeptID, EmpID)
);
 
CREATE TABLE Departments (
  DeptID int NOT NULL,
  Name varchar(255) NOT NULL,
  PRIMARY KEY (DeptID)
);
 
CREATE TABLE Employees (
  EmpID int NOT NULL,
  FirstName varchar(255) NOT NULL,
  LastName varchar(255) NOT NULL,
  Address varchar(255) NOT NULL,
  PhoneNumber varchar(255) NOT NULL,
  Email varchar(255) NOT NULL,
  BirthDate date NOT NULL,
  DeptID int FOREIGN KEY REFERENCES Departments(DeptID), 
  PRIMARY KEY (EmpID)
);

CREATE TABLE Students (
  StudentID int NOT NULL,
  FirstName varchar(255) NOT NULL,
  LastName varchar(255) NOT NULL,
  Address varchar(255) NOT NULL,
  PhoneNumber varchar(255) NOT NULL,
  Email varchar(255) NOT NULL,
  BirthDate date NOT NULL,
  PRIMARY KEY (StudentID)
);

CREATE TABLE FoodServices (
  ServiceID int NOT NULL,
  BalanceAmount varchar(255) NOT NULL,
  StudentID int NOT NULL,
  PRIMARY KEY (ServiceID),
  FOREIGN KEY (StudentID) REFERENCES Students
);

populate_tables.sql

-- Insert records into tables. Each table should have at least 20 records.
-- All the placeholders are now there, I just need to create all the fictitious data...

use jl790a;

-- Create 20 Courses
INSERT INTO Courses VALUES (0, 'CMPSC 140', 'Intro to relational databases.');
INSERT INTO Courses VALUES (1, 'CMPSC 125', 'Intro to Computer Science.');
INSERT INTO Courses VALUES (2, 'CMPSC 212', 'Advanced C++ with Data Structures');
INSERT INTO Courses VALUES (3, 'CMPSC 165', 'Java 1');
INSERT INTO Courses VALUES (4, 'MATH 222', 'Precalculus');
INSERT INTO Courses VALUES (5, 'ENGL 110', 'Composition 1');
INSERT INTO Courses VALUES (6, 'SCI 140', 'Intro to relational databases.');
INSERT INTO Courses VALUES (7, 'ACCT 140', 'Intro to relational databases.');
INSERT INTO Courses VALUES (8, 'BIO 140', 'Intro to relational databases.');
INSERT INTO Courses VALUES (9, 'HIST 140', 'Intro to relational databases.');
INSERT INTO Courses VALUES (10, 'PSYCH 110', 'Intro to Psychology');
INSERT INTO Courses VALUES (11, 'SOCIO 110', 'Intro to Sociology');
INSERT INTO Courses VALUES (12, 'ENGL 111', 'Composition 2');
INSERT INTO Courses VALUES (13, 'ENGL 113', 'Creative Writing');
INSERT INTO Courses VALUES (14, 'CMPSC 135', 'Intro to C++');
INSERT INTO Courses VALUES (15, 'CMPSC 140', 'Intro to relational databases.');
INSERT INTO Courses VALUES (16, 'CMPSC 140', 'Intro to relational databases.');
INSERT INTO Courses VALUES (17, 'CMPSC 140', 'Intro to relational databases.');
INSERT INTO Courses VALUES (18, 'CMPSC 140', 'Intro to relational databases.');
INSERT INTO Courses VALUES (19, 'CHILD 234', 'Childrens Books');

-- Create 20 DepartmentEmployees, Assigning each of the 20 Employees to one of the 20 departments.
INSERT INTO DepartmentEmployees VALUES (0, 0);
INSERT INTO DepartmentEmployees VALUES (1, 1);
INSERT INTO DepartmentEmployees VALUES (2, 2);
INSERT INTO DepartmentEmployees VALUES (3, 3);
INSERT INTO DepartmentEmployees VALUES (4, 4);
INSERT INTO DepartmentEmployees VALUES (5, 5);
INSERT INTO DepartmentEmployees VALUES (6, 6);
INSERT INTO DepartmentEmployees VALUES (7, 7);
INSERT INTO DepartmentEmployees VALUES (8, 8);
INSERT INTO DepartmentEmployees VALUES (9, 9);
INSERT INTO DepartmentEmployees VALUES (10, 10);
INSERT INTO DepartmentEmployees VALUES (11, 11);
INSERT INTO DepartmentEmployees VALUES (12, 12);
INSERT INTO DepartmentEmployees VALUES (13, 13);
INSERT INTO DepartmentEmployees VALUES (14, 14);
INSERT INTO DepartmentEmployees VALUES (15, 15);
INSERT INTO DepartmentEmployees VALUES (16, 16);
INSERT INTO DepartmentEmployees VALUES (17, 17);
INSERT INTO DepartmentEmployees VALUES (18, 18);
INSERT INTO DepartmentEmployees VALUES (19, 19);

-- Create 20 Departments
INSERT INTO Departments VALUES (0, 'Computer Science');
INSERT INTO Departments VALUES (1, 'Math');
INSERT INTO Departments VALUES (2, 'English');
INSERT INTO Departments VALUES (3, 'Accounting');
INSERT INTO Departments VALUES (4, 'Human Resources');
INSERT INTO Departments VALUES (5, 'Environemental');
INSERT INTO Departments VALUES (6, 'Science');
INSERT INTO Departments VALUES (7, 'Agriculture');
INSERT INTO Departments VALUES (8, 'Psychology');
INSERT INTO Departments VALUES (9, 'Sociology');
INSERT INTO Departments VALUES (10, 'History');
INSERT INTO Departments VALUES (11, 'Biology');
INSERT INTO Departments VALUES (12, 'Finance');
INSERT INTO Departments VALUES (13, 'Customer Service');
INSERT INTO Departments VALUES (14, 'Food');
INSERT INTO Departments VALUES (15, 'Legal');
INSERT INTO Departments VALUES (16, 'Custodial');
INSERT INTO Departments VALUES (17, 'Chair Members');
INSERT INTO Departments VALUES (18, 'Founders');
INSERT INTO Departments VALUES (19, 'Child Development');

-- Create 20 Employees
INSERT INTO Employees VALUES (0, 'Yongju', 'Son', '131 That Way', '555-5555', 'teacher@lab.icc.edu', '9999-12-31', 0);
INSERT INTO Employees VALUES (1, 'Mark', 'Peterson', '132 That Way', '555-5554', 'teacher1@lab.icc.edu', '9999-12-31', 1);
INSERT INTO Employees VALUES (2, 'Matt', 'Davids', '133 That Way', '555-5553', 'teacher2@lab.icc.edu', '9999-12-31', 2);
INSERT INTO Employees VALUES (3, 'David', 'Matthews', '134 That Way', '555-5552', 'teacher3@lab.icc.edu', '9999-12-31', 3);
INSERT INTO Employees VALUES (4, 'Sarah', 'Silverman', '135 That Way', '555-5551', 'teacher4@lab.icc.edu', '9999-12-31', 4);
INSERT INTO Employees VALUES (5, 'Tina', 'Fey', '136 That Way', '555-5550', 'teacher5@lab.icc.edu', '9999-12-31', 5);
INSERT INTO Employees VALUES (6, 'Joan', 'Rivers', '137 That Way', '555-5545', 'teacher6@lab.icc.edu', '9999-12-31', 6);
INSERT INTO Employees VALUES (7, 'Charles', 'Darwin', '138 That Way', '555-5535', 'teacher7@lab.icc.edu', '9999-12-31', 7);
INSERT INTO Employees VALUES (8, 'Charlie', 'Chaplan', '139 That Way', '555-5525', 'teacher8@lab.icc.edu', '9999-12-31', 8);
INSERT INTO Employees VALUES (9, 'Louis', 'CK', '140 That Way', '555-5515', 'teacher9@lab.icc.edu', '9999-12-31', 9);
INSERT INTO Employees VALUES (10, 'Ellen', 'Degeneres', '141 That Way', '555-5505', 'teacher10@lab.icc.edu', '9999-12-31',10);
INSERT INTO Employees VALUES (11, 'Amy', 'Schumer', '142 That Way', '555-5455', 'teacher11@lab.icc.edu', '9999-12-31',14);
INSERT INTO Employees VALUES (12, 'Lisa', 'Lampanelli', '143 That Way', '555-5355', 'teacher12@lab.icc.edu', '9999-12-31',11);
INSERT INTO Employees VALUES (13, 'Kathy', 'Griffin', '144 That Way', '555-5255', 'teacher13@lab.icc.edu', '9999-12-31',12);
INSERT INTO Employees VALUES (14, 'Aziz', 'Ansari', '155 That Way', '555-5155', 'teacher14@lab.icc.edu', '9999-12-31',13);
INSERT INTO Employees VALUES (15, 'Kevin', 'Hart', '165 That Way', '555-5055', 'teacher15@lab.icc.edu', '9999-12-31',15);
INSERT INTO Employees VALUES (16, 'Jerry', 'Seinfeld', '186 That Way', '555-4555', 'teacher16@lab.icc.edu', '9999-12-31',16);
INSERT INTO Employees VALUES (17, 'Eddie', 'Izzard', '111 That Way', '555-3555', 'teacher17@lab.icc.edu', '9999-12-31',17);
INSERT INTO Employees VALUES (18, 'George', 'Carlin', '112 That Way', '555-2555', 'teacher18@lab.icc.edu', '9999-12-31',18);
INSERT INTO Employees VALUES (19, 'Steve', 'Hughes', '1113 That Way', '555-1555', 'teacher19@lab.icc.edu', '9999-12-31',19);

-- Create 20 EmployeeCourses Assigning each of the 20 Employees to teach one of the 20 courses.
INSERT INTO EmployeeCourses VALUES (0, 0);
INSERT INTO EmployeeCourses VALUES (1, 1);
INSERT INTO EmployeeCourses VALUES (2, 2);
INSERT INTO EmployeeCourses VALUES (3, 3);
INSERT INTO EmployeeCourses VALUES (4, 4);
INSERT INTO EmployeeCourses VALUES (5, 5);
INSERT INTO EmployeeCourses VALUES (6, 6);
INSERT INTO EmployeeCourses VALUES (7, 7);
INSERT INTO EmployeeCourses VALUES (8, 8);
INSERT INTO EmployeeCourses VALUES (9, 9);
INSERT INTO EmployeeCourses VALUES (10, 10);
INSERT INTO EmployeeCourses VALUES (11, 11);
INSERT INTO EmployeeCourses VALUES (12, 12);
INSERT INTO EmployeeCourses VALUES (13, 13);
INSERT INTO EmployeeCourses VALUES (14, 14);
INSERT INTO EmployeeCourses VALUES (15, 15);
INSERT INTO EmployeeCourses VALUES (16, 16);
INSERT INTO EmployeeCourses VALUES (17, 17);
INSERT INTO EmployeeCourses VALUES (18, 18);
INSERT INTO EmployeeCourses VALUES (19, 19);

-- Create 20 Students
INSERT INTO Students VALUES (0, 'Jay', 'Looney', '132 This Way', '555-5554', 'student0@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (1, 'John', 'Jacob', '132 This Way', '555-5554', 'student1@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (2, 'Jacob', 'Johnson', '132 This Way', '555-5554', 'student2@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (3, 'Sarah', 'Williams', '132 This Way', '555-5554', 'student3@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (4, 'Mary', 'Ellen', '132 This Way', '555-5554', 'student4@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (5, 'Helen', 'Robinson', '132 This Way', '555-5554', 'student5@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (6, 'Lucy', 'Keller', '132 This Way', '555-5554', 'student6@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (7, 'Ross', 'Pevency', '132 This Way', '555-5554', 'student7@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (8, 'David', 'Davidson', '132 This Way', '555-5554', 'student8@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (9, 'Jamie', 'James', '132 This Way', '555-5554', 'student9@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (10, 'Jimmy', 'Fallon', '132 This Way', '555-5554', 'student10@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (11, 'Johnathan', 'Jingleheimer', '132 This Way', '555-5554', 'student11@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (12, 'Stephen', 'Focker', '132 This Way', '555-5554', 'student12@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (13, 'Priscilla', '', '132 This Way', '555-5554', 'student13@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (14, 'Ella', 'Tremain', '132 This Way', '555-5554', 'student14@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (15, 'Jack', 'Schmidt', '132 This Way', '555-5554', 'student15@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (16, 'Poignant', 'Phallus', '132 This Way', '555-5554', 'student16@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (17, 'Doug', 'Peterson', '132 This Way', '555-5554', 'student17@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (18, 'Jesus', 'Christ', '132 This Way', '555-5554', 'student18@lab.icc.edu', '9999-12-31');
INSERT INTO Students VALUES (19, 'Elizabeth', 'Mary', '132 This Way', '555-5554', 'student19@lab.icc.edu', '9999-12-31');

-- Create 20 CourseRoster(s) assigning each of the 20 Students to one of the 20 Courses.
INSERT INTO CourseRoster VALUES (0, 0);
INSERT INTO CourseRoster VALUES (1, 1);
INSERT INTO CourseRoster VALUES (2, 2);
INSERT INTO CourseRoster VALUES (3, 3);
INSERT INTO CourseRoster VALUES (4, 4);
INSERT INTO CourseRoster VALUES (5, 5);
INSERT INTO CourseRoster VALUES (6, 6);
INSERT INTO CourseRoster VALUES (7, 7);
INSERT INTO CourseRoster VALUES (8, 8);
INSERT INTO CourseRoster VALUES (9, 9);
INSERT INTO CourseRoster VALUES (10, 10);
INSERT INTO CourseRoster VALUES (11, 11);
INSERT INTO CourseRoster VALUES (12, 12);
INSERT INTO CourseRoster VALUES (13, 13);
INSERT INTO CourseRoster VALUES (14, 14);
INSERT INTO CourseRoster VALUES (15, 15);
INSERT INTO CourseRoster VALUES (16, 16);
INSERT INTO CourseRoster VALUES (17, 17);
INSERT INTO CourseRoster VALUES (18, 18);
INSERT INTO CourseRoster VALUES (19, 19);

-- Create 20 FoodServices with $1000 and Assign them to 20 Students
INSERT INTO FoodServices VALUES (0, '$1000', 0);
INSERT INTO FoodServices VALUES (1, '$1000', 1);
INSERT INTO FoodServices VALUES (2, '$1000', 2);
INSERT INTO FoodServices VALUES (3, '$1000', 3);
INSERT INTO FoodServices VALUES (4, '$1000', 4);
INSERT INTO FoodServices VALUES (5, '$1000', 5);
INSERT INTO FoodServices VALUES (6, '$1000', 6);
INSERT INTO FoodServices VALUES (7, '$1000', 7);
INSERT INTO FoodServices VALUES (8, '$1000', 8);
INSERT INTO FoodServices VALUES (9, '$1000', 9);
INSERT INTO FoodServices VALUES (10, '$1000', 10);
INSERT INTO FoodServices VALUES (11, '$1000', 11);
INSERT INTO FoodServices VALUES (12, '$1000', 12);
INSERT INTO FoodServices VALUES (13, '$1000', 13);
INSERT INTO FoodServices VALUES (14, '$1000', 14);
INSERT INTO FoodServices VALUES (15, '$1000', 15);
INSERT INTO FoodServices VALUES (16, '$1000', 16);
INSERT INTO FoodServices VALUES (17, '$1000', 17);
INSERT INTO FoodServices VALUES (18, '$1000', 18);
INSERT INTO FoodServices VALUES (19, '$1000', 19);

queries.sql

/*
Create 10 queries which should include the following, otherwise, some points will be taken off:
LIKE, IN, Between and,A lias
wildcards with [ ], %, _
Subqueries
Inner/Outer join
Group by, order by, Having
Aggregate functions
*/