MS SQL SERVER QUESTION

Use this Database Please: https://codeshare.io/2pJJD6

-- Your goal is to convert the following MS SQL Server build-in fuctions using OES2 database into several user defined functions and produce the same result.

SELECT

C.CUSTOMER_NO,

C.LNAME + ' , ' + C.FNAME AS FULLNAME,

SUM(O.SUBTOTAL) AS total_gross,

COUNT(O.ORDER_NO) AS Num_Orders,

ROUND(SUM(O.TOTAL_AMT), 2) AS SALES_TOTAL

FROM ORDERS O

INNER JOIN CUSTOMER C

ON C.CUSTOMER_NO = O.CUSTOMER_NO

GROUP BY C.CUSTOMER_NO, C.LNAME + ' , ' + C.FNAME;

:user defined functon names.

udf_FullName

udf_total_gross

udf_Num_Orders

udf_sales_total

-- Your goal is to convert the following MS SQL Server DML into several stored procedures using CUSTOMER table.

--1: Create CUSTOMER table

CREATE TABLE CUSTOMER (

CustomerID INT NOT NULL,

Title VARCHAR(50) NULL,

FName VARCHAR(50) NOT NULL,

MName VARCHAR(50) NULL,

LName VARCHAR(50) NOT NULL,

Phone VARCHAR(15) NULL

CONSTRAINT PK_CUSTOMER PRIMARY KEY(CustomerID)

);

--insert data

INSERT [dbo].[CUSTOMER] ([CustomerID], [Title], [FName], [MName], [LName], [Phone]) VALUES (1, N'Mr.', N'John', N'P.', N'Doe', N'245-555-0172');

INSERT [dbo].[CUSTOMER] ([CustomerID], [Title], [FName], [MName], [LName], [Phone]) VALUES (2, N'Mr.', N'Matt', N'O.', N'Daniel', N'245-555-0177');

INSERT [dbo].[CUSTOMER] ([CustomerID], [Title], [FName], [MName], [LName], [Phone]) VALUES (3, N'Mr.', N'Mike', N'M.', N'Daniel', N'245-555-0179');

--2: Create a store procedure that inserts customer record into customer table

--name procedure: Customer_INSERT

INSERT INTO CUSTOMER (CustomerID,Title,FName,MName,LName,Phone) VALUES (4,'Mr.','Orlando','N.','Gee','245-555-0173');

--3: Create a store procedure that updates customer telephone number

--name procedure: Customer_UPDATE

UPDATE CUSTOMER

SET Phone = '507-999-9999'

WHERE CustomerID = 2;

--4: Create a store procedure that selects customer by customerId

--name procedure: Customer_select

SELECT * FROM CUSTOMER WHERE CustomerID = 4;

--5: Create a store procedure that returns customer's full name using customerId

--name procedure: Customer_Name_Select

SELECT LNAME + ' , ' + FNAME as FullLName

FROM CUSTOMER

WHERE CustomerID = 1;

--6: Create a store procedure that deletes customer record by customerId

--name procedure: Customer_DELETE

DELETE CUSTOMER

WHERE CustomerID = 3;