lab11.sql

-- question 1

CREATE PROCEDURE proc_customerOrder @date DATE, @orderCount INT
AS BEGIN
	SELECT
		Orders.OrderNum,
		Orders.OrderDate,
		Orders.CustomerNum,
		COUNT(OrderLine.NumOrdered) AS AmountOrder,
		SUM(OrderLine.QuotedPrice) AS SumPrice
	FROM
		Orders
		INNER JOIN OrderLine ON OrderLine.OrderNum = Orders.OrderNum
	GROUP BY Orders.OrderNum, Orders.OrderDate, Orders.CustomerNum
	HAVING Orders.OrderDate = @date AND COUNT(OrderLine.NumOrdered) >= @orderCount
END;

-- question 2
CREATE OR ALTER PROCEDURE proc_insertOrder @amount INT, @part NVARCHAR (10), @customerNum INT
AS 
BEGIN
INSERT INTO Orders (OrderNum, OrderDate, CustomerNum) 
	VALUES(
	 	(SELECT MAX(OrderNum) + 1 FROM Orders), 
		GETDATE (), 
		@customerNum);
INSERT INTO OrderLine (OrderNum, PartNum, QuotedPrice) 
	VALUES (
		(SELECT MAX(OrderNum) FROM Orders),
		@part,
		(SELECT Part.Price * @amount FROM Part WHERE Part.PartNum = @part)
		);
END;