/* Day 16: Transactions/concurrency
Tim Pierson, Dartmouth CS61, Spring 2020 
*/
-- create a new table for experimentation in nyc_inspections schema
use nyc_inspections;

-- extended example from MySQL Docs section 15.7.2.2 
CREATE TABLE Customers (a INT, b CHAR (20), INDEX (a));

-- Do two transactions and commit
START TRANSACTION;
INSERT INTO Customers VALUES (10, 'Heikki'); 
INSERT INTO Customers VALUES (11, 'Elvis');
COMMIT; -- transactions saved
SELECT * FROM Customers;


START TRANSACTION;            
INSERT INTO Customers VALUES (15, 'John');
INSERT INTO Customers VALUES (20, 'Paul');
DELETE FROM Customers WHERE b = 'Heikki';

-- Now we undo those last 2 inserts and the delete.
ROLLBACK;
SELECT * FROM Customers;

-- let's not leave this temporary table laying around!
DROP TABLE Customers;


-- transactions/concurrency examples
UPDATE CheckingAccount
SET Balance = Balance + 100 
WHERE AccountNumber = 123456;

UPDATE CheckingAccount
SET Balance = Balance + 150 
WHERE AccountNumber = 123456;


UPDATE Apply
SET Decision = 'Y'
Where StudentID IN (SELECT StudentID from Student WHERE GPA > 3.9);

UPDATE Student
SET GPA = 1.1*GPA
WHERE HighSchoolSize > 2500;



INSERT INTO Archive
	SELECT * FROM Apply WHERE Decision = 'N';
DELETE FROM Apply WHERE Decision = 'N';

SELECT COUNT(*) from Apply;
SELECT COUNT(*) from Archive;


START TRANSACTION;
UPDATE Products SET Quantity = 23 WHERE ProductID = 1558;
UPDATE Customers SET Balance = 615.73 WHERE CustomerID = 1001;
COMMIT;
