Below is what I written currently. I need to write (a) using CASE and JOIN ON wr

Question

Below is what I written currently. I need to write (a) using CASE and JOIN ON write a select statement such that if ShipAdrsID is NULL, display 'Same" otherwise "ShipAddress, ShipState, ShipZip" from CustomerShipAddress_T table (b)SQL statment that will return total amount of payments in $1000.00 format (c) SQL statement that will return customer with the largest payment

SELECT Order_T.OrderID, Customer_T.CustomerID, Customer_T.CustomerName, Customer_T.CustomerAddress,
Customer_T.CustomerCity, Customer_T.CustomerState, Customer_T.CustomerPostalCode,
to_char(Order_T.OrderDate,'MONTH DD, RR HH:MI PM') AS "OrderDate",
(
CASE WHEN FulfillmentDate is NULL then 'TBD'
ELSE
to_char(Order_T.FulfillmentDate, 'MONTH DD, RR HH:MI PM')
END
) "FulfillmentDate",
Order_T.SalesPersonID, Order_T.ShipAdrsID,
to_char(Payment_T.PaymentDate, 'MONTH DD, RR HH:MI PM') AS "PaymentDate",
to_char(Payment_T.PaymentAmount, '$9,999.99') AS "PaymentAmount",
INITCAP(Payment_T.PaymentComment) AS "PaymentComment"
FROM Order_T, Customer_T, Payment_T, PaymentType_T
WHERE Order_T.CustomerID = Customer_T.CustomerID
AND Payment_T.OrderID = Order_T.OrderID
AND Payment_T.PaymentTypeID = PaymentType_T.PaymentTypeID;

Explanation / Answer

1.SELECT Order_T.OrderID, Customer_T.CustomerID, Customer_T.CustomerName, Customer_T.CustomerAddress,
Customer_T.CustomerCity, Customer_T.CustomerState, Customer_T.CustomerPostalCode,to_char(Order_T.OrderDate,'MONTH DD, RR HH:MI PM') AS "OrderDate",
FROM    Order_T, Customer_T, Payment_T, PaymentType_T
    JOIN sys.partitions p
        ON i.index_id = p.index_id
    JOIN sys.allocation_units a
        ON CASE
           WHEN ShipAdrsID is NULL
THEN 1
           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
           ELSE 0
           END = 1

2. SELECT Order_T.OrderID, Customer_T.CustomerID, Customer_T.CustomerName, Customer_T.CustomerAddress,
Customer_T.CustomerCity, Customer_T.CustomerState, Customer_T.CustomerPostalCode
FROM Order_T, Customer_T, Payment_T, PaymentType_T
WHERE Payment_T.PaymentAmount>1000
ORDER BY Name ASC;
GO

3.SELECT MAX()PaymentAmountID FROM Payment_T;

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

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