-- DATABASE MANAGEMENT SYSTEMS 2020
-- LAB EXERCISE 4
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- INSERT QUERIES
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Using the branch number B0010, add a new branch office located in Swansea at an address of your choice
-- FILL IN THE BLANKS
INSERT INTO Branch (BranchNo, StreetAddress, City, Postcode, PhoneNo) 
VALUES ('B0010', '1 Somewhere St', 'Swansea', 'SA1 8PH', '01792481234')


-- Add yourself as a new member of staff with staff number S29030 at the branch created in the last step.
-- FILL IN THE BLANKS
INSERT INTO Staff (StaffNo, FirstName, LastName, Gender, DOB, Position, Salary, StartDate, BranchNo)
VALUES ('S29030', '...', '...', '...', '01-Jan-2000', '...', 50,000, GETDATE(), 'B0010')


-- Using the Owner number, CO19055 (Charlie, Oscar, 19055), create a new property owner.
-- FILL IN THE BLANKS
INSERT INTO [Owner] (OwnerNo, FirstName, LastName, StreetAddress, City, Postcode, PhoneNo, Email, [Password])
VALUES ('CO19055', '...', '...', '...', '...', 'SA1 8PY', '01792480001', 'charlie@somewhere.com', 'password')


-- Using the client number CR19044, add a new client with the name John O'Neill, registered with the branch created earlier.
-- NOTE: You have to escape the single quote character in the surname (0'Neil) because remember you quote string values in single quotes
-- Replace the quote with double quotes (O''Neil) otherwise the DBMS will not be able to identify where the string ends 
-- FILL IN THE BLANKS
INSERT INTO Client(ClientNo, FirstName, LastName, PhoneNo, Email, PropertyTypePref, MaxRent, BranchNo, RegistrationDate, RegisteredBy)
VALUES ('CR19044', 'John', 'O''Neil', '01792480003', 'johnoneil@somewhere.com', 'mansion', 100, 'B0010', GETDATE(), 'S29030')


-- TABLE ALTERATIONS
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Check the constraint on the Property rooms column  make sure it allows 0 rooms (for a studio property).
-- If not delete the constraint and recreate it to allow 0 rooms.
-- Syntax:
--		ALTER TABLE tableName DROP CONSTRAINT constraint_name
--		ALTER TABLE tableName ADD CONSTRAINT constraint_name CHECK ()

-- If you need to drop your constraint, run the following (FILL IN THE BLANKS)
ALTER TABLE Property DROP CONSTRAINT ...

-- To add the correct constraint
 ALTER TABLE Property
 ADD CONSTRAINT CHK_Property_Rooms CHECK(Rooms BETWEEN 0 AND 25)


-- Dream Home will like to add a new field to the Lease table to record how much of the deposit is returned to the client at the end of their lease.
-- Add a new field called DepositRefunded in the Lease table for this new requirement.

-- Okay, I may have gone a bit overboard with the size - it's for those renting mansions :)
ALTER TABLE Property
ADD DepositRefunded DECIMAL(10,2)


-- Make an alteration to the Staff table with the following CHECK constraint (if you DO NOT have it already)
-- Check that the Leaving Date for a Staff Member is either NULL OR it is AFTER the start date

ALTER TABLE Staff
ADD CONSTRAINT CHK_Staff_LeavingDate CHECK(LeavingDate IS NULL OR LeavingDate >= StartDate)


-- Dream Home recently needed to contact a member of staff's next of kin urgently, but realised they do not currently store next of kin details.
-- You have been asked to modify the staff table to enable them to capture Next of kin details (a name, relationship and phone number) going forwards

ALTER TABLE Staff
ADD NextOfKinName		VARCHAR(50),
NextOfKinRelationship	VARCHAR(15),
NextOfKinPhoneNo		VARCHAR(12)


-- Add a new member of staff with staff number S29031 at branch B0010 and include sample next of kin details
-- FILL IN THE BLANKS
INSERT INTO Staff (StaffNo, FirstName, LastName, Gender, DOB, Position, Salary, StartDate, BranchNo, NextOfKinName, NextOfKinRelationship, NextOfKinPhoneNo)
VALUES ('S29031', '...', '...', '...', '01-Jan-2000', '...', 20,000, GETDATE(), 'B0010', '...', '...', '...')




-- If you wish to DELETE the added next of kin details you can use the following to DROP (delete) the columns from the staff table
ALTER TABLE Staff
DROP COLUMN NextOfKinName, NextOfKinRelationship, NextOfKinPhoneNo

