Skip to content

Instantly share code, notes, and snippets.

@askingalot

askingalot/Workforce SQL Exercise.md

Last active November 7, 2019 15:23
Show Gist options
  • Save askingalot/3e145c634a5f09978ea84382d1b90a35 to your computer and use it in GitHub Desktop.
Save askingalot/3e145c634a5f09978ea84382d1b90a35 to your computer and use it in GitHub Desktop.
Download ZIP
Bangazon SQL Exercise
Raw
Workforce SQL Exercise.md

Write the appropriate SQL to answer the following questions

NOTE: Some of these questions will require googling and/or asking for help from your instructors.

  1. List each employee first name, last name and supervisor status along with their department name. Order by department name, then by employee last name, and finally by employee first name.
  2. List each department ordered by budget amount with the highest first.
  3. List each department name along with any employees (full name) in that department who are supervisors.
  4. List each department name along with a count of employees in each department.
  5. Write a single update statement to increase each department's budget by 20%.
  6. List the employee full names for employees who are not signed up for any training programs.
  7. List the employee full names for employees who are signed up for at least one training program and include the number of training programs they are signed up for.
  8. List all training programs along with the count employees who have signed up for each.
  9. List all training programs who have no more seats available.
  10. List all future training programs ordered by start date with the earliest date first.
  11. Assign a few employees to training programs of your choice.
  12. List the top 3 most popular training programs. (For this question, consider each record in the training program table to be a UNIQUE training program).
  13. List the top 3 most popular training programs. (For this question consider training programs with the same name to be the SAME training program).
  14. List all employees who do not have computers.
  15. List all employees along with their current computer information make and manufacturer combined into a field entitled ComputerInfo. If they do not have a computer, this field should say "N/A".
  16. List all computers that were purchased before July 2019 that are have not been decommissioned.
  17. List all employees along with the total number of computers they have ever had.
Raw
workforce.sql
DROP TABLE IF EXISTS EmployeeTraining;
DROP TABLE IF EXISTS TrainingProgram;
DROP TABLE IF EXISTS ComputerEmployee;
DROP TABLE IF EXISTS Computer;
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Department;
CREATE TABLE Department (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
[Name] VARCHAR(55) NOT NULL,
Budget INTEGER NOT NULL
);
CREATE TABLE Employee (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
FirstName VARCHAR(55) NOT NULL,
LastName VARCHAR(55) NOT NULL,
DepartmentId INTEGER NOT NULL,
IsSupervisor BIT NOT NULL DEFAULT(0),
CONSTRAINT FK_EmployeeDepartment FOREIGN KEY(DepartmentId) REFERENCES Department(Id)
);
CREATE TABLE Computer (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
PurchaseDate DATETIME NOT NULL,
DecomissionDate DATETIME,
Make VARCHAR(55) NOT NULL,
Manufacturer VARCHAR(55) NOT NULL
);
CREATE TABLE ComputerEmployee (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
EmployeeId INTEGER NOT NULL,
ComputerId INTEGER NOT NULL,
AssignDate DATETIME NOT NULL,
UnassignDate DATETIME,
CONSTRAINT FK_ComputerEmployee_Employee FOREIGN KEY(EmployeeId) REFERENCES Employee(Id),
CONSTRAINT FK_ComputerEmployee_Computer FOREIGN KEY(ComputerId) REFERENCES Computer(Id)
);
CREATE TABLE TrainingProgram (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
[Name] VARCHAR(255) NOT NULL,
StartDate DATETIME NOT NULL,
EndDate DATETIME NOT NULL,
MaxAttendees INTEGER NOT NULL
);
CREATE TABLE EmployeeTraining (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
EmployeeId INTEGER NOT NULL,
TrainingProgramId INTEGER NOT NULL,
CONSTRAINT FK_EmployeeTraining_Employee FOREIGN KEY(EmployeeId) REFERENCES Employee(Id),
CONSTRAINT FK_EmployeeTraining_Training FOREIGN KEY(TrainingProgramId) REFERENCES TrainingProgram(Id)
);
INSERT INTO Department (Name, Budget) VALUES ('HR', 300000);
INSERT INTO Department (Name, Budget) VALUES ('Executive Team', 2000000);
INSERT INTO Department (Name, Budget) VALUES ('Marketing', 540000);
INSERT INTO Department (Name, Budget) VALUES ('Occult Studies', 81200);
INSERT INTO Department (Name, Budget) VALUES ('Silly Walks', 3800000);
INSERT INTO Department (Name, Budget) VALUES ('IT', 45);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Allison', 'Patton', 1, 1);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Bennett', 'Foster', 2, 1);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Bobby', 'Brady', 3, 1);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Brantley', 'Jones', 4, 1);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Brian', 'Wilson', 5, 1);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Carl', 'Barringer', 6, 1);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Curtis', 'Crutchfield', 1, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Ellie', 'Ash', 2, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Eric', 'Taylor', 3, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Haroon', 'Iqbal', 4, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Jacquelyn', 'McCray', 5, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Joe', 'Snyder', 6, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Kelly', 'Coles', 1, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Kevin', 'Sadler', 2, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Maggie', 'Johnson', 3, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Matthew', 'Ross', 4, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Michael', 'Stiles', 5, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Michelle', 'Jimenez', 6, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Noah', 'Bartfield', 1, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Sarah', 'Fleming', 2, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('William', 'Wilkinson', 3, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Ali', 'Abdulle', 4, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Samuel', 'Alpren', 5, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Sam', 'Britt', 6, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Jameka', 'Echols', 1, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Josh', 'Hibray', 2, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Brian', 'Jobe', 3, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('William', 'Mathison', 4, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Clifton', 'Matuszeski', 5, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('William', 'Mitchell', 6, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Joel', 'Mondesir', 1, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Christopher', 'Morgan', 2, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Meagan', 'Mueller', 3, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Jonathan', 'Schaffer', 4, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Stephen', 'Senft', 5, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Alexander', 'Thacker', 6, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Anne', 'Vick', 1, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Rose', 'Wisotzky', 2, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Michael', 'Yankura', 3, 0);
INSERT INTO Employee (FirstName, LastName, DepartmentId, IsSupervisor) VALUES ('Selamawit', 'GebreKidan', 4, 0);
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2010-01-01', '2014-12-31', 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Macbook Pro', 'Apple');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Surface Pro', 'Microsoft');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Surface Pro', 'Microsoft');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Surface Pro', 'Microsoft');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Surface Pro', 'Microsoft');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Surface Pro', 'Microsoft');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Surface Pro', 'Microsoft');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Surface Pro', 'Microsoft');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Surface Pro', 'Microsoft');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Surface Pro', 'Microsoft');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Surface Pro', 'Microsoft');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2015-01-01', null, 'Surface Pro', 'Microsoft');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-01-01', null, 'Oryx', 'System76');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-07-01', null, 'XPS', 'Dell');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-07-01', null, 'XPS', 'Dell');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-07-01', null, 'XPS', 'Dell');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-07-01', null, 'XPS', 'Dell');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-07-01', null, 'XPS', 'Dell');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-07-01', null, 'XPS', 'Dell');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-07-01', null, 'XPS', 'Dell');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-07-01', null, 'XPS', 'Dell');
INSERT INTO Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) Values ('2019-07-01', null, 'XPS', 'Dell');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (1, 1, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (2, 2, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (3, 3, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (4, 4, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (5, 5, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (6, 6, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (7, 7, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (8, 8, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (9, 9, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (10, 10, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (11, 11, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (12, 12, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (13, 13, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (14, 14, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (15, 15, '2010-01-01', '2014-12-31');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (1, 16, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (2, 17, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (3, 18, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (4, 19, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (5, 20, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (6, 21, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (7, 22, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (8, 23, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (9, 24, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (10, 25, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (11, 26, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (12, 27, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (13, 28, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (14, 29, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (15, 30, '2015-01-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (19, 34, '2019-08-02', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (20, 44, '2019-08-02', '2019-08-04');
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (20, 45, '2019-08-04', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (21, 44, '2019-08-05', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (25, 31, '2019-08-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (29, 32, '2019-08-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (30, 33, '2019-08-01', null);
INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate) VALUES (39, 40, '2019-08-01', null);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Productivity and You', '2018-09-14', '2018-09-18', 20);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('HR Rules Are Important, Really', '2018-09-15', '2018-09-19', 15);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Making the most of your imposter syndrome', '2019-04-01', '2019-04-10', 100);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Grifting', '2019-05-01', '2019-05-04', 30);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Grifting', '2019-05-10', '2019-05-14', 30);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Grifting', '2019-06-01', '2019-06-04', 30);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Grifting', '2019-06-10', '2019-06-14', 30);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Grifting', '2019-07-01', '2019-07-04', 30);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Customer Service: Is it ok to yell at them?', '2019-12-01', '2019-12-05', 10);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Managing Your Manager', '2019-12-01', '2019-12-05', 10);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Goal Setting When No One Knows What''s Coming Next', '2020-01-10', '2020-01-14', 10);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Grifting', '2020-05-01', '2020-05-04', 30);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Grifting', '2020-05-10', '2020-05-14', 30);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Grifting', '2020-06-01', '2020-06-04', 30);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Grifting', '2020-06-10', '2020-06-14', 30);
INSERT INTO TrainingProgram (Name, StartDate, EndDate, MaxAttendees) VALUES ('Grifting', '2020-07-01', '2020-07-04', 30);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (1, 1);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (1, 2);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (1, 3);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (2, 2);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (2, 3);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (20, 8);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (20, 8);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (1, 9);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (2, 9);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (3, 9);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (4, 9);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (5, 9);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (6, 9);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (7, 9);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (8, 9);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (9, 9);
INSERT INTO EmployeeTraining (EmployeeId, TrainingProgramId) VALUES (10, 9);
@AdamSheaffer
Copy link

Looks good.

Just so no one accidentally adds these tables to the master database, can we include this:

USE MASTER
GO

IF NOT EXISTS (
    SELECT [name]
    FROM sys.databases
    WHERE [name] = N'BangazonAPI'
)
CREATE DATABASE BangazonAPI
GO

USE BangazonAPI
GO

Couple typos on items 8 and 17

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment