-- DATABASE SYSTEMS 2020
-- LAB EXERCISE week 6
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Updates
-- ~~~~~~~~~~~~~~~~~~~~
-- Naomi Scott currently has the lowest branch manager salary, increase her salary by 5%;
-- Increase her salary by 5%



-- Promote Angel Toulson to a Supervisior and increase her salary by 15%;



-- Property rental prices are continuing to increase, and particularly in London. 
-- Run an UPDATE query to increase all rental prices (weekday and weekend) for properties based in London by 3%.
-- Hint: first of all write a multi-table query using either a join to select all properties in London and their rental prices 
-- to help you work out the correct join statement



-- Nicholas	Swaby was not happy that he was not promoted with his colleague (you promoted her in question 2 above) and has decided to leave. 
-- Update the database to record her leaving date (today) and also reassign any properties that he is managing to any of his colleagues
-- in the same office, but not a manager or branch manager, or anyone already managing more than 5 properties
-- NOTE: You are likely to need more than one query to complete this - you can use SELECT statement(s) to find the information for the second part of the query or use a Subquery.




-- Inserting a new property with an image for the property as well
-- To insert a record with a blob, you would use the following syntax
/*
	INSERT TableName (Photo_Column_First, Other_Column_Names_List) 
	SELECT Photo.*, Other column values
	FROM OPENROWSET (BULK 'Photo Path), SINGLE_BLOB) Photo

	Photo is simply an alias for the blob to be inserted
*/

-- Download the barn-blue-sky-cabin-577697.jpg image from Moodle
-- Add a new property with the following details:
--		Property Ref: P180100,			OwnerID: CO19029,			Type: Cabin,		Address: 2 Amberley Drive, Langland, SA3 4PJ
--		Registered Branch: B0006,		Managed By:	S29008,			Status: Verified	Other details: as you wish

-- Check that the record and blob data was added

-- Image coutesy of Photo by Lukas Kloeppel from Pexels (https://www.pexels.com/photo/autumn-barn-blue-sky-bungalow-577697/) [accessed: 29-Oct-2019]

INSERT Property 
(
	Photo,
    PropertyRef, 
    OwnerID, 
	[Type],
	StreetAddress,
	City,
	Postcode,
	Sleeps,
	NoBedrooms,
	NoBathrooms,
	Rating,
    MinStayNights,
	RegisteredBranchNo,
	ManagedBy,
    [Status]
)
SELECT Photo.*, 'P180100', 'CO19029', , , , , , , , , , , , 
FROM OPENROWSET 
    (BULK 'C:\...\barn-blue-sky-cabin-577697.jpg', SINGLE_BLOB) Photo




-- Update the property booking table for the booking for Logan Morgan in November (Checking in) with the following comments: 
--			"Customer will be arriving very late at night". 
-- Change the CheckOutDate from the Nov 8th to Nov 10th
-- Increase the rental price to whatever you like (we will hopefully create a function to calculate this), and pay a 25% booking deposit
-- Hint: you will need a join for this, you may want to write a join to select the information first



-- Update the price for Internet access and Breakfast to different prices (NOT free) of your choice for the property at 23 Castletown Drive, CA11 9ES
-- You will need two update statements OR you can use the following case clause (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql)
-- Cost = CASE FeatureName WHEN 'Internet Access' THEN ... ELSE ... END 






-- Bookings can be cancelled by a customer or cancelled by Dream Home Away for other reasons (e.g. property needs urgent repairs or property is cancelled).
-- As long as the booking is cancelled on time by the customer or if it is cancelled by Dream Home Away, they get a refund amount
-- Add a new column to the Booking table to store the amount refunded when a booking is cancelled.



-- The owner of the caravan located at 30 Green Lane, Derby, DE1 1RP has decided that he/she no longer wishes to rent out his caravan via Dream Home Away
-- Update the status of the property to cancelled and update any FUTURE bookings to cancelled, refunding the full amount paid (assume payment has already been made in full)