Make a SQL query based on the pictures below: Query: Create a stored procedure t

Question

Make a SQL query based on the pictures below:

Query: Create a stored procedure that takes as input the identifier for a gig and displays (outputs) the name of the freelancer, the total amount earned with the gig, and then the name of each client with the selected gig package and amount paid (minus refunds) for that gig. Implement exception handling.

Photos Capture.PNG See all photos Add to a creaticn % Edit & Create- Share t Query Builder TASK B.4 Query B.4..Eor each gig, the nunber of ratings and average star rating LECT GidCode, Ticle, COUNT( NUMBER OF RATINGS, AVG (Rating) AVG RATING FROM Gig G INNER JOIN Ereelancers F ON F.FreelancerID G.FreelancerID GROUP BY G.G1gCode, Titie: Query B.4.b. For each gig package, the price and the total money earned with it SELECT Go.GigCode, SUM(Anount) AS TOTAL NONEY PROM Gigorders G, Orders O, Transaction T WHERB GO,OrderID-o.orderID AND O-OrderID·T.OrderID AND ?.OrderTracking-T.OrderTracking GROUP BY GO.G1gCode received for work done as a freelancer. -Query B. 4.c. For each client, the total number of orders placed and the total amount spent on them. (This doe not include SELECT ID, FirstNane, Lastllame, coUNT UNBER OF ORDERS, SUM (Anoun) TOTAL AMOUII PROM User U, Client C, Order 0, Transaction T WHERZ U, ID-?.ID AND C.OrderTracking-o.orderTracking AND ?.0rderID-T.OrderID AND ?.OrderTracking-T.OrderTracking GROUP BY U,TD, ?1rstname, LastName: - Query B.4.d. For each freelancer, the total number of gigs completed for clients and total anount earned from them SELECT EreelancerID, COUNT() NUMBER OF GIGS, SUM (AMOUNT) TOIAL_AMOUNT PROM Gig G, GigOrders GO, Orders 0, Transaction T WHERE G.GigCode-GO.GigCode AND GO.OrderID-?.OrderID AD ?.OrderID-T.OrderID ?D Status-"Completed. GROUP BY ?reelancerID: Query B.4.e. For each gig category, the total number of orders and the total anount () earned/spent on them SELECT GígCode· COUNT(*) AS NUMBER O.-ORDERS, SUM (Amount) AS TOTAL AMOUNT EARED PROM GigPackage GE, Order 0, Transaction WHERE GP.OrderID- 0.0rdeID AND .0rderID T.0rderID AND .OrderTracking T.OrderTracking GROUP BY GP.GigCode: TASK B.5 View for B.4.a CREATE OR REPLACE VIEW Gig AS ERON Gig G INNER JOIN Freelancers F ON E. Ereelancer1D = G. Ereelancer1D GRO P Br G. GígCode, litle;; -- View for B.4.b CREATE OR REPLACE VIEW Gíg Package AS SELECT GO.GigCode, SUM(Amount, AS TOTALMO1EY FROM GigOrders GO? Orders o, Transaction ? wHERE GO.OrderTD = ?.OrderID AND ?.Order1D = ?.orderlD AND ?.OrderTr - View for B.4.c CRXATE OR REPLACE VIEW Client AS SELECT ID' F1rstName, LastName, COOT(*) NUMBER-OF-ORDERS, SUM [Amount ) TOTAL-AMOCTiT rRK, User U, Client C, Order o, Transaction T WEERB u.1D-?.ID AND C.orderTra CREATE OR REPLACE VIEN freelancer AS SELECT Freelancer!D, COUNT(*) NUMBER-OF-GIGS, SUM (AMOUNT) TOTAL-AMOUNT ERON Gig G, Gigorders GO, Orders 0, Transaction 1 WHERE G.GigCode GO, GígCode AND GO.O CRRATE O REPLACE VIE Gig Category AS SELECT G?gCode' C00NT(*) AS NUMBER OF ORDERS SUM(A ount} AS TOTAL AMOUNT EARNED PROM GigPackage GP Order O Transaction WHERE GP.OrderID·O·OrderID AND O - View for B.4.d - View for B.4.e TASK B.6 Stored Procedure Need help with B.6 O Type here to seardh 10:25 PM 4/29/2018

Explanation / Answer

CREATE PROCEDURE

@GigCode INT

AS

BEGIN

-- Exception handling using Try catch block

BEGIN TRY

-- Query to get frelancer data

SELECT F.FullName AS FreeLancerName, SUM(Amount) TOTAL_AMOUNT FROM Gig G, GigOrders GO, Orders O, Transaction T, FreeLancers F

WHERE G.GigCode = GO.GigCode AND G.GigCode = @GigCode

AND GO.OrderID = O.OrderID

AND O.OrderID = T.OrderID

AND F.FreeLancerID = G.FreelancerID

AND Status = 'Completed'

Group by F.FullName

-- Get data by gig package

SELECT U.FisrtName, U.LastName, SUM(Amount) [Total Amount Paid]

FROM GigPackage GP,USer U, Client C, Order O, Transaction T

WHERE GP.GigCode = @GigCode

AND GP.OrderID = O.OrderID

AND O.OrderID = T.OrderID

AND O.OrderTracking = C.OrderTracking

AND C.ID = U.ID

GROUP BY U.FisrtName, U.LastName

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

END

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.