Question
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
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;
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started