You develop a new stored procedure for an existing database. You create two tables named Customer and Orders. The tables have the following definitions:
CREATE TABLE Customer
(CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerName nvarchar(255) NOT NULL,
CustomerAddress nvarchar (1024) NOT NULL)
CREATE TABLE Orders
(OrderID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL FOREIGN KEY REFERENCES Customer(CustomerID),
OrderDetails nvarchar(MAX))
Users are restricted from accessing table objects directly. You need to ensure that users are able to retrieve customer data. You need to create a stored procedure that meets the following requirements:
•Returns a row that contains the name, address, and number of orders made by a customer by specifying the CustomerID value.
•Returns a row even if the customer has made no orders.
•Does not return a row if the CustomerID does not exist.
Which Transact-SQL statement or statements should you use?
A. INSERT INTO Tickets VALUES (4, ‘sales’, ‘open’, 0), (5, ‘support’, ‘open’, 0), (6, ‘support’, ‘open’,1)
B. UPDATE Tickets SET IsArchived = 1 WHERE TicketId IN (1, 2, 3)
C. CREATE PROCEDURE p_GetTotalOrdersByCustomer (@customerid int)
AS
SELECT c.CustomerName, c.CustomerAddress, TotalOrders = COUNT(o.OrderID)
FROM Customer c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, c.CustomerAddress
WHERE c.CustomerID = @customerid
GO
D. CREATE PROCEDURE p_GetTotalOrdersByCustomer (@customerid int)
AS
SELECT c.CustomerName, c.CustomerAddress,TotalOrders = SUM(o.OrderID)
FROM Customer c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, c.CustomerAddress
GO
E. INSERT INTO SupportTickets
VALUES (4, ‘support’, ‘open’, 0),
(5, ‘support’, ‘in progress’, 0),
(6, ‘support’, ‘closed’, 0)
F. INSERT INTO Tickets
VALUES (4, ‘support’, ‘open’, 0),
(5, ‘support’, ‘in progress’, 0),
(6, ‘support’, ‘closed’, 0)
G. CREATE PROCEDURE p_GetTotalOrdersByCustomer (@customerid int)
AS
SELECT c.CustomerName, c.CustomerAddress, TotalOrders = ISNULL(o.TotalOrders, 0)
FROM Customer c
INNER JOIN (SELECT CustomerID, TotalOrders = COUNT(OrderID)
FROM Orders
WHERE CustomerID = @customerid
GROUP BY CustomerID) o ON c.CustomerID = o.CustomerID
WHERE c.CustomerID = @customerid
GO
H. CREATE PROCEDURE p_GetTotalOrdersByCustomer (@customerid int)
AS SELECT c.CustomerName, c.CustomerAddress, TotalOrders = ISNULL(o.TotalOrders, 0)
FROM Customer
