THIS IS AN EXAMPLE : List slGearPurchase.GearPurchaseID, slGearPurchase.Purchase
ID: 3876897 • Letter: T
Question
THIS IS AN EXAMPLE : List slGearPurchase.GearPurchaseID, slGearPurchase.PurchaseAmount, slGearPurchase.Description for all gear purchases.
Replace NULL values in Description with NA (Not Applicable).
Order the list by slGearPurchase.GearPurchaseID in ascending order.
ANSWER TO THE EXAMPLE :
SELECT slGearPurchase.GearPurchaseID, slGearPurchase.PurchaseAmount,
isNULL (slGearPurchase.Description,'NA (Not Applicable)') AS Description
FROM slGearPurchase
ORDER by slGearPurchase.GearPurchaseID ASC
NEED HELP WITH THOSE QUESTIONS 1-3
(1) For repairs having supplierID 1501 OR 1517 OR 1518, list slRepair.RepairDate, slRepair.RepairCost, slRepair.SupplierID.
Write this query using IN().
Order the list first by SupplierID in ascending order and then by repair cost in descending order.
(2) List slPax.PaxID, slPax.PaxFirstName, and slPax.PaxLastName for passengers with lastname Clark OR Sharma OR Patel.
Write this query using IN().
Order the list by PaxID in ascending order.
(3) List slSupplier.SupplierID and slSupplier.SupplierName for suppliers whose SupplierID is unequal <> to 1502 AND 1506 AND 1510 AND 1512 AND 1518.
Order the list by slSupplier.SupplierID in ascending order.
Explanation / Answer
/*THIS IS AN EXAMPLE : List slGearPurchase.GearPurchaseID, slGearPurchase.PurchaseAmount, slGearPurchase.Description for all gear purchases.
Replace NULL values in Description with NA (Not Applicable).
Order the list by slGearPurchase.GearPurchaseID in ascending order.
ANSWER TO THE EXAMPLE :
SELECT slGearPurchase.GearPurchaseID, slGearPurchase.PurchaseAmount,
isNULL (slGearPurchase.Description,'NA (Not Applicable)') AS Description
FROM slGearPurchase
ORDER by slGearPurchase.GearPurchaseID ASC*/
--NEED HELP WITH THOSE QUESTIONS 1-3
/*(1) For repairs having supplierID 1501 OR 1517 OR 1518, list slRepair.RepairDate, slRepair.RepairCost, slRepair.SupplierID.
Write this query using IN().
Order the list first by SupplierID in ascending order and then by repair cost in descending order. */
--if SupplierID is declared as integer in DATABASE use below code
SELECT slRepair.RepairDate, slRepair.RepairCost, slRepair.SupplierID FROM slRepair
WHERE slRepair.SupplierID IN (1501,1517,1518)
ORDER BY slRepair.SupplierID ASC,slRepair.RepairCost DESC ;
--if SupplierID is declared as String in DATABASE use below code
SELECT slRepair.RepairDate, slRepair.RepairCost, slRepair.SupplierID FROM slRepair
WHERE slRepair.SupplierID IN ('1501','1517','1518')
ORDER BY slRepair.SupplierID ASC,slRepair.RepairCost DESC ;
/*(2) List slPax.PaxID, slPax.PaxFirstName, and slPax.PaxLastName for passengers with lastname Clark OR Sharma OR Patel.
Write this query using IN().
Order the list by PaxID in ascending order. */
SELECT slPax.PaxID, slPax.PaxFirstName,slPax.PaxLastName FROM slPax
WHERE slPax.PaxLastName IN ('Clark','Sharma','Patel')
ORDER BY slPax.PaxID ASC;
/*(3) List slSupplier.SupplierID and slSupplier.SupplierName for suppliers whose SupplierID is unequal <> to 1502 AND 1506 AND 1510 AND 1512 AND 1518.
Order the list by slSupplier.SupplierID in ascending order.*/
--if SupplierID is declared as integer in DATABASE use below code
SELECT slSupplier.SupplierID,slSupplier.SupplierName FROM slSupplier
WHERE slSupplier.SupplierID NOT IN (1502,1506,1510,1512,1518)
ORDER BY slSupplier.SupplierID ASC;
--if SupplierID is declared as String in DATABASE use below code
SELECT slSupplier.SupplierID,slSupplier.SupplierName FROM slSupplier
WHERE slSupplier.SupplierID NOT IN ('1502','1506','1510','1512','1518')
ORDER BY slSupplier.SupplierID ASC;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.