rzHheagu

1--COMPLETE
2--This function is used to check whether an Email Id is available for use or not
3CREATE FUNCTION ufn_CheckEmailId(@EmailId VARCHAR(50))
4RETURNS BIT
5AS
6BEGIN
7	IF EXISTS (SELECT * FROM Users WHERE EmailId = @EmailId)
8		RETURN 0
9	ELSE
10		RETURN 1
11
12	RETURN 0
13END
14GO
15
16--COMPLETE
17--This function is used to validate the user credentials and return appropriate values
18CREATE FUNCTION ufn_ValidateUserCredentials(@UserId VARCHAR(50), @UserPassword VARCHAR(15))
19RETURNS TINYINT
20AS
21BEGIN
22	DECLARE @VerifiedUserRoleId TINYINT
23	IF NOT EXISTS (SELECT * FROM Users WHERE EmailId = @UserId AND UserPassword = @UserPassword)
24		RETURN -1
25	SELECT @VerifiedUserRoleId = RoleId FROM Users WHERE EmailId = @UserId AND UserPassword = @UserPassword
26	RETURN @VerifiedUserRoleId
27END
28GO
29
30--COMPLETE
31--This function is used to return the details of the purchases made by a particular customer
32CREATE FUNCTION ufn_FetchCustomerPurchases(@EmailId VARCHAR(50))
33RETURNS TABLE
34AS
35	RETURN SELECT P.ProductName, PD.QuantityPurchased, (PD.QuantityPurchased * P.Price) AS TotalAmount, PD.DateOfPurchase, P.CategoryId 
36	FROM PurchaseDetails AS PD JOIN Products AS P ON P.ProductId = PD.ProductId WHERE PD.EmailId = @EmailId;
37GO
38
39--COMPLETE
40--This function is used to return the details of all the purchases made by all the customers
41CREATE FUNCTION ufn_FetchPurchasedProducts()
42RETURNS TABLE
43AS
44	RETURN SELECT P.ProductId, P.ProductName, P.Price, C.CategoryName, PD.QuantityPurchased, (PD.QuantityPurchased*P.Price) AS TotalAmount, PD.DateOfPurchase
45	FROM Products AS P
46	JOIN PurchaseDetails AS PD ON PD.ProductId = P.ProductId 
47	JOIN Categories AS C ON P.CategoryId = C.CategoryId
48GO
49
50--COMPLETE
51--This function is used to retrieve all the categories
52CREATE FUNCTION ufn_GetCategories()
53RETURNS TABLE
54AS
55	RETURN SELECT CategoryId, CategoryName FROM Categories
56GO
57
58--COMPLETE
59--This function is used to retrieve the card details based on the card number
60CREATE FUNCTION ufn_GetCardDetails(@CardNumber NUMERIC(16,0))
61RETURNS TABLE
62AS
63	RETURN SELECT NameOnCard, CardType, CVVNumber, ExpiryDate, Balance FROM CardDetails WHERE CardNumber = @CardNumber
64GO