INSERT INTO Customers VALUES ('Jonas', 'Gray', '23 Green Corner Street', 56743, 'Birmingham', '23 Green Corner Street', 56743, 'Birmingham', 'jonas.gray@hotmail.com', 0708123456)

INSERT INTO Customers VALUES ('Jane', 'Harolds', '10 West Street', 43213, 'London', '10 West Street', 43213, 'London', 'jane_h77@hotmail.com', 0701245512)

INSERT INTO Customers VALUES ('Peter', 'Birro', '12 Fox Street', 45681, 'New York', '89 Moose Plaza', 45321, 'Seattle', 'peter_the_great@hotmail.com', 0739484322)

INSERT INTO Customers VALUES ('Nicholas', 'Page', '34 Baker Street', 65681, 'San Diego', '34 Baker Street', 65681, 'San Diego', 'nicholas_p@hotmail.com', 3082143720)

INSERT INTO Customers VALUES ('Barbara', 'Butler', '4778 Emma Street', 44614, 'Los Angeles', '4778 Emma Street', 44614, 'Los Angeles', 'barbaraJButler@jourrapide.com', 3308540179)

INSERT INTO Customers VALUES ('Melissa', 'Behnke', '1655 Sweetwood Drive', 80220, 'Denver', '1655 Sweetwood Drive', 80220, 'Denver', 'melissaSBehnke@rhyta.com', 3033881135)


INSERT INTO Movies VALUES ('Interstellar', 'Christopher Nolan', 2014, 179, '')

INSERT INTO Movies VALUES ('Hobbit: Battle of the five armies', 'Peter Jackson', 2014, 179, '')

INSERT INTO Movies VALUES ('The Wolf of Wall Street', 'Martin Scorcese', 2013, 119, '')

INSERT INTO Movies VALUES ('Pulp fiction', 'Quentin Tarantino', 1994, 49, '')

INSERT INTO Movies VALUES ('The Godfather', 'Francis Ford Coppola', 1972, 79, '')

INSERT INTO Movies VALUES ('Nightcrawler', 'Dan Gilroy', 2014, 59, '')

INSERT INTO Movies VALUES ('Heat', 'Michael Mann', 1995, 129, '')

INSERT INTO Movies VALUES ('Get Out', 'Jordan Peele', 2017, 159, '')
INSERT INTO Movies VALUES ('Get Out', 'Jordan Peele', 2017, 159, '')



INSERT INTO Orders VALUES ('2015-01-01', 1)

INSERT INTO Orders VALUES ('2015-01-15', 1)

INSERT INTO Orders VALUES ('2014-12-20', 1)

INSERT INTO Orders VALUES ('2014-12-20', 1)


--SELECT TOP 1 Id FROM Orders ORDER BY OrderDate DESC --Get the latest inserted OrderId
--OrderRows
-- All four customers bought MovieId 3 (Wolf)
INSERT INTO OrderRows (OrderId, MovieId, Price) VALUES (1, 3, 119)
INSERT INTO OrderRows (OrderId, MovieId, Price) VALUES (4, 3, 119)
INSERT INTO OrderRows (OrderId, MovieId, Price) VALUES (4, 3, 119)
INSERT INTO OrderRows (OrderId, MovieId, Price) VALUES (4, 3, 119)

--Delete data
DELETE FROM Movies
DELETE FROM Orders
DELETE FROM OrderRows
DELETE FROM Customers

--Reset automatic ID's
DBCC CHECKIDENT ('[Customers]', RESEED, 0);
GO

DBCC CHECKIDENT ('[Movies]', RESEED, 0);
GO

DBCC CHECKIDENT ('[Orders]', RESEED, 0);
GO

DBCC CHECKIDENT ('[OrderRows]', RESEED, 0);
GO

--View data
SELECT * FROM Orders
SELECT * FROM Movies
SELECT * FROM OrderRows
SELECT * FROM Customers

--Update price on all movies released 2014
UPDATE Movies
SET price =	169		--price * 0.5 / This is for percentage
WHERE ReleaseYear = 2014

--5a
SELECT Firstname, Lastname, PhoneNo, EmailAddress
FROM Customers

--5b
SELECT Title
FROM Movies
ORDER BY ReleaseYear DESC;

--5c
SELECT Title, Price
FROM Movies
ORDER BY Price;

--5d
SELECT Firstname, Lastname, DeliveryAddress, DeliveryZip, DeliveryCity
FROM Customers
INNER JOIN Orders ON Customers.Id = Orders.CustomerId
INNER JOIN OrderRows ON Orders.Id = OrderRows.OrderId
INNER JOIN Movies ON OrderRows.MovieId = Movies.Id
WHERE Movies.Title = 'The wolf of wall street'

--5e
SELECT Orders.Id, OrderDate, Customers.Firstname, Customers.Lastname,
SUM(OrderRows.Price) as Total
FROM Orders
INNER JOIN OrderRows ON Orders.Id = OrderRows.OrderId
INNER JOIN Customers ON Customers.Id = Orders.CustomerId
GROUP BY Orders.id, Orders.OrderDate, Customers.Firstname, Customers.Lastname

Select MAX(Total)
order by
(SELECT Orders.Id, SUM(OrderRows.Price) as Total
FROM Orders
INNER JOIN OrderRows ON Orders.Id = OrderRows.OrderId
GROUP BY Orders.id)