MS SQL SERVER QUESTION Use this Database Please: https://codeshare.io/2pJJD6 --
ID: 3606675 • Letter: M
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 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;
Explanation / Answer
Hi,
I have solved part 1 below. Part 2 cannot be solved in the same page. Please try posting part 2 separately.
Functions-
Ans 1-
create function udf_FullName()
returns varchar
as
declare @name as varchar(100);
begin
SELECT
@name=C.LNAME + ' , ' + C.FNAME
FROM ORDERS O
INNER JOIN CUSTOMER C
ON C.CUSTOMER_NO = O.CUSTOMER_NO;
return @name;
end;
Ans 2-
create function udf_total_gross()
return number
as
declare @total as number(10,2);
begin
SELECT
@total=SUM(O.SUBTOTAL)
FROM ORDERS O
INNER JOIN CUSTOMER C
ON C.CUSTOMER_NO = O.CUSTOMER_NO;
return @total;
end;
Ans 3-
create function udf_Num_Orders
return number
as
declare @total_orders as number(10);
begin
SELECT
@total_orders=count(o.orderno)
FROM ORDERS O
INNER JOIN CUSTOMER C
ON C.CUSTOMER_NO = O.CUSTOMER_NO;
return @total_orders;
end;
Ans 4-
create function udf_sales_total()
return number
as
declare @sales_total as number(10,2);
begin
SELECT
@sales_total=ROUND(SUM(O.TOTAL_AMT), 2)
FROM ORDERS O
INNER JOIN CUSTOMER C
ON C.CUSTOMER_NO = O.CUSTOMER_NO;
return @sales_total;
end;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.