DROP TABLE JobService
DROP TABLE Service
DROP TABLE JobSupply
DROP TABLE Supply
DROP TABLE SupplyCategory
DROP TABLE Job
DROP TABLE Payment
DROP TABLE Client
DROP TABLE StaffTraining
DROP TABLE Training
DROP TABLE Staff
DROP TABLE StaffType

CREATE TABLE StaffType
(
StaffTypeCode INT IDENTITY (1,1) NOT NULL
CONSTRAINT pk_StaffType PRIMARY KEY clustered,
Description VARCHAR (100) NOT NULL,
Wage smallmoney NOT NULL
CONSTRAINT ck_Wage CHECK (Wage >=0)
CONSTRAINT df_Wage DEFAULT 20,
)

CREATE TABLE Staff
(
StaffID INT NOT NULL
CONSTRAINT pk_Staff PRIMARY KEY clustered,
FirstName VARCHAR (50) NOT NULL,
LastName VARCHAR (50) NOT NULL,
TrainingCredits SMALLINT NOT NULL,
StaffTypeCode INT NOT NULL
CONSTRAINT fk_StaffToStaffType REFERENCES StaffType(StaffTypeCode),
Phone VARCHAR(14) NOT NULL
CONSTRAINT ck_StaffPhone CHECK (Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

)

CREATE TABLE Training
(
TrainingID VARCHAR(20) NOT NULL
CONSTRAINT pk_Training PRIMARY KEY clustered,
Description VARCHAR(100) NOT NULL,
Credits tinyint NOT NULL
CONSTRAINT ck_Credits CHECK (Credits <=6)
CONSTRAINT df_Credits DEFAULT 3
)

CREATE TABLE StaffTraining
(
StaffID INT NOT NULL
CONSTRAINT fk_StaffTrainingToStaff REFERENCES Staff(StaffID),
TrainingID VARCHAR(20) NOT NULL
CONSTRAINT fk_StaffTrainingToTraining REFERENCES Training(TrainingID),
CompletionDate datetime NOT NULL,
CONSTRAINT pk_StaffTraining PRIMARY KEY clustered (StaffID, TrainingID)
)
CREATE TABLE Client
(
ClientID INT IDENTITY (1,1) NOT NULL
CONSTRAINT pk_Client PRIMARY KEY clustered,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Phone CHAR (14) NOT NULL
CONSTRAINT ck_Phone CHECK (Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
Balance money NOT NULL CONSTRAINT df_Balance DEFAULT  0
)

CREATE TABLE Job
(
JobNumber INT IDENTITY (1,1) NOT NULL
CONSTRAINT pk_Job PRIMARY KEY clustered,
DATE datetime NOT NULL,
Address VARCHAR(100) NOT NULL,
City VARCHAR (50) NOT NULL,
Province CHAR(2) NOT NULL
CONSTRAINT ck_Province CHECK (Province LIKE '[a-z][a-z]'),
PostalCode CHAR(7) NOT NULL
CONSTRAINT ck_PostalCode CHECK (PostalCode LIKE '[a-z][0-9][a-z] [0-9][a-z][0-9]'),
SubTotal money NOT NULL,
GST money NOT NULL,
Total money NOT NULL,
StaffID INT NOT NULL
CONSTRAINT fk_JobToStaff REFERENCES Staff(StaffID),
ClientID INT NOT NULL
CONSTRAINT fk_JobToClient REFERENCES Client(ClientID),
CONSTRAINT ck_SubTotalandTotal CHECK (Total > Subtotal)
)

CREATE TABLE SupplyCategory
(
SupplyCategoryCode VARCHAR (5) NOT NULL
CONSTRAINT pk_SupplyCategory PRIMARY KEY clustered,
Description VARCHAR(100) NOT NULL,
StorageRoom VARCHAR (5) NOT NULL
)

CREATE TABLE Supply
(
SupplyCode VARCHAR(8) NOT NULL
CONSTRAINT pk_Supply PRIMARY KEY clustered,
Description VARCHAR(100) NOT NULL,
SupplyCategoryCode VARCHAR(5) NOT NULL
CONSTRAINT fk_SupplyToSupplyCategory REFERENCES SupplyCategory(SupplyCategoryCode)
)

CREATE TABLE JobSupply
(
JobNumber INT NOT NULL
CONSTRAINT fk_JobSupplyToJob REFERENCES Job(JobNumber),
SupplyCode VARCHAR(8) NOT NULL
CONSTRAINT fk_JobSupplyToSupply REFERENCES Supply(SupplyCode),
Quantity SMALLINT NOT NULL,
CONSTRAINT pk_JobSupply PRIMARY KEY clustered (JobNumber, SupplyCode)
)

CREATE TABLE Service
(
ServiceCode VARCHAR(15) NOT NULL
CONSTRAINT pk_Service PRIMARY KEY clustered,
Description VARCHAR(100) NOT NULL,
CostPerHour smallmoney NOT NULL
)

CREATE TABLE JobService
(
JobNumber INT NOT NULL
CONSTRAINT fk_JobServiceToJob REFERENCES Job(JobNumber),
ServiceCode VARCHAR(15) NOT NULL
CONSTRAINT fk_JobServiceToService REFERENCES Service(ServiceCode),
Notes VARCHAR(200) NOT NULL,
hours INT NOT NULL,
ActualCostPerHour smallmoney NOT NULL,
ExtCost smallmoney NOT NULL,
CONSTRAINT pk_JobService PRIMARY KEY clustered (JobNumber, ServiceCode)
)

CREATE TABLE Payment
(
PaymentID INT NOT NULL IDENTITY (1,1) CONSTRAINT pk_Payment PRIMARY KEY clustered,
DATE datetime NOT NULL,
Amount smallmoney NOT NULL,
ClientID INT NOT NULL
CONSTRAINT fk_PaymentToClient REFERENCES Client(ClientID)
)

ALTER TABLE Client
ADD
Email VARCHAR(100) NULL
CONSTRAINT ck_Email CHECK (Email LIKE '%@%.%')

ALTER TABLE Staff
ADD
Available CHAR(1) NOT NULL
CONSTRAINT ck_Available CHECK (Available LIKE '[YN]')
CONSTRAINT df_Available DEFAULT 'Y'

ALTER TABLE Job
ADD
CONSTRAINT df_Province DEFAULT 'AB' FOR Province

--Non clustered indexes here

CREATE nonclustered INDEX IX_StaffTraining_TrainingID
ON StaffTraining (TrainingID)
CREATE nonclustered INDEX IX_StaffTraining_StaffID
ON StaffTraining (StaffID)
CREATE nonclustered INDEX IX_Staff_StaffTypeCode
ON Staff (StaffTypeCode)
CREATE nonclustered INDEX IX_Job_ClientID
ON Job (ClientID)
CREATE nonclustered INDEX IX_Job_StaffID
ON Job (StaffID)
CREATE nonclustered INDEX IX_JobService_JobNumber
ON JobService (JobNumber)
CREATE nonclustered INDEX IX_JobService_ServiceCode
ON JobService (ServiceCode)
CREATE nonclustered INDEX IX_JobSupply_JobNumber
ON Jobsupply (JobNumber)
CREATE nonclustered INDEX IX_JobSupply_SupplyCode
ON JobSupply (SupplyCode)

--Lab 2 insert script
--IMPORTANT! If you need to run this script more than once you must drop and recreate your tables first to reset the identity properties.
DELETE JobService
DELETE Service
DELETE JobSupply
DELETE Supply
DELETE SupplyCategory
DELETE Job
DELETE Client
DELETE StaffTraining
DELETE Training
DELETE Staff
DELETE StaffType




INSERT INTO StaffType (Description,Wage)
VALUES ('Painter',25)
INSERT INTO StaffType (Description,Wage)
VALUES ('Sander',20)
INSERT INTO StaffType (Description,Wage)
VALUES ('Builder',30)
INSERT INTO StaffType (Description,Wage)
VALUES ('Demolition',35)
INSERT INTO StaffType (Description,Wage)
VALUES ('Cleaning',15)


INSERT INTO Staff (StaffID,FirstName,LastName,TrainingCredits,StaffTypeCode,phone)
VALUES (11111,'Jason','Painter',12,1,'(780) 111-2222')
INSERT INTO Staff (StaffID,FirstName,LastName,TrainingCredits,StaffTypeCode,phone)
VALUES (22222,'Suzy','Cleaner',12,5,'(780) 111-3333')
INSERT INTO Staff (StaffID,FirstName,LastName,TrainingCredits,StaffTypeCode,phone)
VALUES (33333,'Alex','Boom',13,4,'(780) 111-4444')
INSERT INTO Staff (StaffID,FirstName,LastName,TrainingCredits,StaffTypeCode,phone)
VALUES (44444,'Adam','Scraper',15,2,'(780) 111-5551')
INSERT INTO Staff (StaffID,FirstName,LastName,TrainingCredits,StaffTypeCode,phone)
VALUES (55555,'Bob','LaBuilder',21,3,'(780) 111-6666')

INSERT INTO Training (TrainingID, Description, Credits)
VALUES ('Paint101','Introduction to Painting',3)
INSERT INTO Training (TrainingID, Description, Credits)
VALUES ('Finishing123','Sanding and Finishing',6)
INSERT INTO Training (TrainingID, Description, Credits)
VALUES ('Cleaning224','Cleaning and Clearing',3)
INSERT INTO Training (TrainingID, Description, Credits)
VALUES ('Demolition101','Making Things go Boom!',4)
INSERT INTO Training (TrainingID, Description, Credits)
VALUES ('Safety104','Basic Safety Protocols',3)
INSERT INTO Training (TrainingID, Description, Credits)
VALUES ('Repairs105','Basic Repairs',3)
INSERT INTO Training (TrainingID, Description, Credits)
VALUES ('ClientConflict202','Dealing with Grumpy Clients',6)
INSERT INTO Training (TrainingID, Description, Credits)
VALUES ('Building321','Basic Building Concepts',6)


INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (11111,'Paint101','Jan 1 2016')
INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (11111,'Safety104','Jan 4 2016')
INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (11111,'ClientConflict202','Jan 7 2016')

INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (22222,'Cleaning224','Jan 1 2016')
INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (22222,'Safety104','Jan 4 2016')
INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (22222,'ClientConflict202','Jan 7 2016')

INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (33333,'Demolition101','Jan 1 2016')
INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (33333,'Safety104','Jan 4 2016')
INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (33333,'ClientConflict202','Jan 7 2016')

INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (44444,'Finishing123','Jan 1 2016')
INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (44444,'Safety104','Jan 4 2016')
INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (44444,'ClientConflict202','Jan 7 2016')

INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (55555,'Building321','Jan 1 2016')
INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (55555,'Safety104','Jan 4 2016')
INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (55555,'ClientConflict202','Jan 7 2016')
INSERT INTO StaffTraining(StaffID, TrainingID, CompletionDate)
VALUES (55555,'Finishing123','Jan 7 2016')

INSERT INTO Client (FirstName, LastName, Phone)
VALUES ('Peggy', 'Sue','(780) 111-1111')
INSERT INTO Client (FirstName, LastName, Phone)
VALUES ('Maggie', 'May','(780) 222-2222')
INSERT INTO Client (FirstName, LastName, Phone)
VALUES ('Billy', 'Jean','(780) 111-1111')
INSERT INTO Client (FirstName, LastName, Phone)
VALUES ('Bobby', 'McGee','(780) 111-1111')
INSERT INTO Client (FirstName, LastName, Phone)
VALUES ('Tom', 'Dooly','(780) 111-1111')
INSERT INTO Client (FirstName, LastName, Phone)
VALUES ('Mary', 'Jane','(780) 111-1111')
INSERT INTO Client (FirstName, LastName, Phone)
VALUES ('Jimmy', 'Mack','(780) 111-1111')
INSERT INTO Client (FirstName, LastName, Phone)
VALUES ('Eleanor', 'Rigby','(780) 111-1111')

INSERT INTO Service (ServiceCode,Description,CostPerHour)
VALUES ('Prime1','Priming',20)
INSERT INTO Service (ServiceCode,Description,CostPerHour)
VALUES ('Painting1','Painting',20)
INSERT INTO Service (ServiceCode,Description,CostPerHour)
VALUES ('Construction10','Basic Reconstruction',40)
INSERT INTO Service (ServiceCode,Description,CostPerHour)
VALUES ('Construction20','Advanced Construction',60)
INSERT INTO Service (ServiceCode,Description,CostPerHour)
VALUES ('Demolition','Demolition of Property',30)
INSERT INTO Service (ServiceCode,Description,CostPerHour)
VALUES ('Sanding1','Surface Sanding',20)
INSERT INTO Service (ServiceCode,Description,CostPerHour)
VALUES ('Cleaning1','Basic Cleaning',20)
INSERT INTO Service (ServiceCode,Description,CostPerHour)
VALUES ('Garbage1','Removal of Graffit Refuse',25)




INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('Jan 1 2016','12345 Anywhere Street','Edmonton', 'AB','T3D 1S5',120,26,126,11111,1)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (1,'Prime1','Prime fence beside the house',3,20,60)
INSERT INTO JobService (JobNumber, ServiceCode, Notes,hours, ActualCostPerHour, extcost)
VALUES (1,'Painting1','Prime fence beside the house',3,20,60)

INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('Jan 2 2016','12345 First Street','Edmonton', 'AB','T3A 1A5',160,8,168,11111,1)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (2,'Sanding1','Sand the wall',5,20,100)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (2,'Painting1','Paint the wall',3,20,60)

INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('Jan 3 2016','12345 Second Street','Edmonton', 'AB','T3B 1B5',20,1,21,11111,1)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (3,'Painting1','Paint the door',1,20,20)

INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('Jan 3 2016','12345 Third Street','Edmonton', 'AB','T3C 1C5',40,2,42,22222,2)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (4,'Cleaning1','Clean the wall',2,20,40)


INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('Jan 4 2016','12345 Fourth Street','Edmonton', 'AB','T3D 1D5',80,4,84,22222,2)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (5,'Cleaning1','Clean the fence',4,20,80)


INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('February 5 2016','12345 Fifth Street','Edmonton', 'AB','T3E 1E5',40,2,42,11111,3)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (6,'Painting1','Paint the sign',2,20,40)


INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('Jan 6 2016','12345 Sixth Street','Edmonton', 'AB','T3F 1F5',60,3,63,33333,5)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (7,'Demolition','Take down the sign',2,30,60)


INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('Jan 7 2016','12345 Seventh Street','Edmonton', 'AB','T3G 1G5',110,5.5,115.5,33333,1)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (8,'Demolition','Clean the wall',2,30,60)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (8,'Garbage1','Clean the wall',2,25,50)

INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('February 8 2016','12345 Eighth Street','Edmonton', 'AB','T3H 1H5',40,2,42,44444,6)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (9,'Sanding1','Sand the blue fence',2,20,40)


INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('February 27 2016','12345 Ninth Street','Edmonton', 'AB','T3I 1I5',20,1,21,44444,7)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (10,'Sanding1','Sand the table',1,20,20)


INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('February 28 2016','12345 Tenth Street','Edmonton', 'AB','T3J 1J5',200,10,210,55555,8)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (11,'Construction10','Rebiuld Table',5,40,200)


INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('March 1 2016','12345 Eleventh Street','Edmonton', 'AB','T3K 1K5',600,30,630,55555,3)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (12,'Construction20','Build new shed',10,60,600)


INSERT INTO Job (DATE, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
VALUES ('March 3 2016','12345 Twelth Street','Edmonton', 'AB','T3L 1L5',120,6,126,11111,8)
INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
VALUES (13,'Cleaning1','Clean the whole house!',12,10,120)


INSERT INTO SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
VALUES ('P5','5 Gallon Paint Products','A101')
INSERT INTO SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
VALUES ('P10','10 Gallon Paint Products','A101')
INSERT INTO SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
VALUES ('P20','20 Gallon Paint Products','A105')
INSERT INTO SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
VALUES ('S101','Sanding Supplies','S101')
INSERT INTO SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
VALUES ('B123','Demolition Equipment','DM212')
INSERT INTO SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
VALUES ('CL10','All Cleaning Supplies','B232')
INSERT INTO SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
VALUES ('B100','Building and Construction Materials','B202')
INSERT INTO SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
VALUES ('PR100','Primers','A211')
INSERT INTO SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
VALUES ('W100','Water','W211')


INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('PR104','Grey Primer','PR100')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('RP5','5 Gallon RedPaint','P5')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('BP10','10 Gallon Blue Paint','P10')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('GP20','20 Gallon Green Paint','P20')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('SP10','Sanding Paper','S101')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('PR105','White Primer','PR100')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('PR106','Black Primer','PR100')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('D100','Large Hammer','B123')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('DS100','Drywall Saw','B123')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('L100','Ladder','B100')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('N100','Nails','B100')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('C100','Soap','CL10')
INSERT INTO Supply (SupplyCode,Description, SupplyCategoryCode)
VALUES ('S100','Sponge','CL10')

INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(1,'BP10',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(1,'PR104',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(2,'PR104',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(2,'SP10',4)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(3,'RP5',1)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(4,'C100',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(4,'S100',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(5,'C100',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(5,'S100',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(6,'RP5',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(7,'DS100',1)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(8,'D100',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(9,'SP10',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(10,'SP10',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(11,'D100',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(11,'N100',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(12,'L100',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(12,'D100',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(12,'N100',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(13,'C100',2)
INSERT INTO JobSupply (JobNumber,SupplyCode,Quantity)
VALUES(13,'S100',2)

SELECT * FROM JobService
SELECT * FROM  Service
SELECT * FROM  JobSupply
SELECT * FROM  Supply
SELECT * FROM  SupplyCategory
SELECT * FROM  Job
SELECT * FROM  Client
SELECT * FROM  StaffTraining
SELECT * FROM  Training
SELECT * FROM  Staff
SELECT * FROM  StaffType

--1
CREATE PROCEDURE AddClient (@FirstName VARCHAR(10) = NULL, @LastName VARCHAR(50) = NULL, @Phone CHAR (14) NOT NULL)
AS
IF @FirstName IS NULL OR @LastName IS NULL OR @Phone IS NULL
    BEGIN
    RaisError ('You must provide a FirstName, LastName, and Phone number',16,1)
    END
ELSE
    BEGIN
    INSERT INTO Client (FirstName, LastName, Phone)
    VALUES (@FirstName, @LastName, @Phone)
    SELECT ClientID FROM Client WHERE FirstName = @FirstName
    END
RETURN
GO

--2
CREATE PROCEDURE UpdateTraining (@TrainingID VARCHAR(10) = NULL, @Description VARCHAR(50) = NULL, @Credits CHAR (14) NOT NULL)
AS
IF @TrainingID IS NULL OR @Description IS NULL OR @Credits IS NULL
    BEGIN
    RaisError ('You must provide a FirstName, LastName, and Phone number',16,1)
    END
IF NOT EXISTS (SELECT * FROM Training WHERE TrainingID = @TrainingID)
    BEGIN
    RaisError ('That training is not available.', 16, 1)
    END
IF EXISTS (SELECT * FROM Training WHERE Description = @Description)
    BEGIN
    RaisError ('Training description already exists.')
    END
ELSE
    BEGIN
    UPDATE Training
    SET Description = @Description, Credits = @Credits
    WHERE TrainingID = @TrainingID
    END
RETURN
GO

-----Use this for above
CREATE PROCEDURE UpdateClub (@ClubID VARCHAR(10) = NULL, @Clubname VARCHAR(50) = NULL)
AS
IF @ClubID IS NULL OR @Clubname IS NULL
    BEGIN
    RaisError ('You must provide a clubID and clubname',16,1)
    END
ELSE
    BEGIN
    IF NOT EXISTS (SELECT * FROM Club WHERE ClubId = @ClubID)
        BEGIN
        RaisError('That Club does not exist',16,1)
        END
    ELSE
        BEGIN
        UPDATE Club
        SET ClubName = @Clubname
        WHERE ClubId = @ClubID
        IF @@ERROR <>0
            BEGIN
            RaisError ('update failed!',16,1)
            END
        END
    END
RETURN

--3
CREATE PROCEDURE DeleteSupplyCategory (@SupplyCategoryCode VARCHAR (5) = NULL)
AS
IF @SupplyCategoryCode IS NULL
    BEGIN
    RaisError ('You must provide a SupplyCategoryCode',16,1)
    END
IF NOT EXISTS (SELECT * FROM SupplyCategory WHERE SupplyCategoryCode = @SupplyCategoryCode)
    BEGIN
    RaisError ('This Supply Category Code is not in the database.',16,1)
    END
IF EXISTS (SELECT * FROM SupplyCategory INNER JOIN supply ON supply.supplycategorycode = supplycategory.supplycategorycode)
    BEGIN
    RaisError ('A supply currently has this supply category code!',16,1)
    END
ELSE
    BEGIN
    DELETE supplycategory WHERE supplycategorycode = @SupplyCategoryCode
    END
RETURN

--4
CREATE PROCEDURE LookUpStaffTraining (@StaffID INT = NULL)
AS
IF @StaffID IS NULL
    BEGIN
    RaisError ('You must provide a Staff ID',16,1)
    END
IF NOT EXISTS (SELECT * FROM Staff WHERE StaffID = @StaffID)
    BEGIN
    RaisError ('That Staff ID does not exist',16,1)
    END
ELSE
    BEGIN
    SELECT DISTINCT Firstname + " " + LastName 'Name', TrainingID, CompletionDate, Description, Credits FROM Staff
    INNER JOIN StaffTraining ON Staff.StaffID = StaffTraining.StaffID
    WHERE StaffID = @StaffID
    END
RETURN

--5
CREATE PROCEDURE NoJobs()
AS
    BEGIN
    SELECT FirstName, LastName, Phone FROM Client WHERE jobid IS NULL IN (SELECT JobNumber FROM Job)
    END
RETURN

--6
CREATE PROCEDURE LookUpStaff (@LastName VARCHAR (50) = NULL)
AS
IF @LastName IS NULL
    BEGIN
    RaisError ('You must provide a Last Name', 16,1)
    END
ELSE
    BEGIN
    SELECT StaffID, Firstname, Lastname, TrainingCredits, Description FROM Staff INNER JOIN StaffType ON Staff.StaffID = StaffType.StaffID WHERE Staff.Lastname LIKE ('%@LastName')
    END
RETURN

--7 needs ActualCostPerHour ExtCost but there's no constant?
CREATE PROCEDURE AddJobService (@JobNumber INT = NULL, @ServiceCode VARCHAR (15) = NULL, @Notes VARCHAR (200) = NULL, Hours INT = NULL)
AS
IF @JobNumber IS NULL OR @ServiceCode IS NULL OR @Notes IS NULL OR @Hours IS NULL
    BEGIN
    RaisError ('You must provide all information',16,1)
    END
IF NOT EXISTS (SELECT * FROM Job WHERE JobNumber = @JobNumber)
    BEGIN
    RaisError ('That job does not exist', 16,1)
    END
IF EXISTS (SELECT * FROM Job WHERE JobNumber = @JobNumber)
    BEGIN
    INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours)
    VALUES (@JobNumber, @ServiceCode, @Notes, @Hours)
    END
RETURN

--8
ALTER PROCEDURE AddJobService (