-- DATABASE MANAGEMENT SYSTEMS 2020
-- LAB EXERCISE week 8
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Transactions
-- ~~~~~~~~~~~~~~~~~~~~

-- These exercises are not exactly practical and are just being used for you to practice transactions.

-- Transactions
-- ~~~~~~~~~~~~~~~~~~~~




-- QUESTION ONE
-- ~~~~~~~~~~~~~~~~~~~~~
/* 
	GO TO Moodle and download the CreateTicketsScript
	Run the SQL Statements on YOUR database
	It will create three new tables Ticket, TicketOrder and TicketPurchase with some sample data
	It will also create a Trigger (we will talk about triggers later on) that automatically updates the quantities in Ticket as tickets are purchased or returned (added to or removed from TicketPurchase)
*/


-- Run the following command to see the tickets available and the table structure
SELECT * FROM Ticket
SELECT * FROM TicketOrder
SELECT * FROM TicketPurchase

GO

/*
	Write an EXPLICIT transaction to create a new Ticket Order purchasing 
		* 2 tickets for the England vs Wales game (should be TicketID: 1)
		* 2 tickets for the Wales vs Scotland game (should be TicketID: 2)


	Note: 
		use of Try ... Catch block is optional

		You need to insert values in two tables (see revision question slide)

		Table 1 INSERT: Creating a new TicketOrder first
			PurchaseNo is an auto ID column (ignore, database will generate ID value)
			SET PurhchaseDateTime to current date and time
			SET PuchasedBy to SYSTEM_USER (no quotes)

		Table 2 INSERT: 
			IMPORTANT : Add the following line first before the inserts (this declares a variable called @PurchaseNo and sets its value to the last ID created by the database in the current user session)

					DECLARE @PurchaseNo INT = SCOPE_IDENTITY();
			SET PurchaseNo in second table to the variable @PurchaseNo - the last ID created by Database
			
			That is:
				DECLARE @PurchaseNo INT = SCOPE_IDENTITY();
				INSERT INTO TicketPurchase (PurchaseNo, ..., ...) VALUES (@PurchaseNo, ..., ...);
				INSERT INTO TicketPurchase (PurchaseNo, ..., ...) VALUES (@PurchaseNo, ..., ...);
*/





GO
-- Run the following commands again to see what has happened.
SELECT * FROM Ticket
SELECT * FROM TicketOrder
SELECT * FROM TicketPurchase


GO


-- QUESTION TWO
-- ~~~~~~~~~~~~~~~~

/*
	Now, using a TRY ... CATCH Block, create an EXPLICIT Transaction to create a new Ticket Order purchasing 
		* 2 tickets for England vs Wales game (should be TicketID: 1)
		* 5 tickets for the Wales vs Scotland game (should be TicketID: 2)


	If all the statements execute successfully, 
		COMMIT the transaction, 
	else 
		ABORT it	
	
	See NOTES above: same table INSERTs and use the variable for PurchaseNo
*/





GO
-- Run the following commands again to see what has happened.
SELECT * FROM Ticket
SELECT * FROM TicketOrder
SELECT * FROM TicketPurchase


-- After your tests, you can delete the tables from the database using the following
DROP Trigger IF EXISTS TicketPurchased

DROP TABLE IF EXISTS TicketPurchase
DROP TABLE IF EXISTS TicketOrder
DROP TABLE IF EXISTS Ticket







GO


-- QUESTION THREE
-- ~~~~~~~~~~~~~~~~

-- Write an SQL statement to show the max salary currently being earned. 
-- You may want to make a note of what the maximum salary is as you need to refer to it again shortly.


-- DreamHome is thinking of increasing salaries, but ...
-- Create an EXPLICIT transaction for the following:
--		update all staff salaries by 25%
--		If the update results in any members of staff earning more than 40000, rollback the transaction, otherwise commit it.
-- NOTE: You will need to use SQL's IF .. ELSE and a subquery for the check
-- Syntax: IF (Subquery) > Amount ... ELSE ...
/*
	For example
	IF (SELECT MAX(...) FROM ...) > ...
		BEGIN
			...
		END
	ELSE
		...
*/


-- Repeat the SQL statement to check what the maximum salary is. Did the pay rise commit or not?


GO