In your output display (i) ProductID, (ii)ĪveragePriceReceived (i.e., average sales unit price in 2019 forĮach product where sales unit price is UnitPrice*(1-Discount) from
Using the OrderDetails, OrderHeaders, and Products tables,ĭetermine for each product the average price received in 2019 andĬompare this amount to the list price (the unit price in the ProductID, ProductName, and SumOfQualitySoldIn2019 for all Products Table, and (iii) SumOfQuantitySoldIn2019 (include null values forī) Using the Products table and ProductSales2019 view, show ProductID and (ii) ProductName for all products in the Products ProductSales2019 view (that you created in 8.a) instead of theĪ) Using the Products table and ProductSales2019 view, show forĪll products, the quantity sold in 2019. SumOfQuantitySoldIn2019 greater than 200.Ĭ) Create the same output you did in 8.b, but use the Store this query as a view calledī) Copy and paste query 7.b, but only show products with Show each ProductID and for each product show a newĬalculated field named SumOfQuantitySoldIn2019 defined as the total You can assume that all OrderHeaders have OrderDetails and that allī) Copy and paste query 7.a and only show orders placed in (from the OrderHeaders table), OrderDate, ProductID, and Quantity. This field SumOfQuantitySold), and (iv) the number of times it hasīeen sold (name this field NumberOfSales you can simply calculateĪ) Using the OrderHeaders and OrderDetails tables, show OrderID Quantity sold in each order), (iii) the total quantity sold (name Order detail rows and you do not need to adjust the average for the
Simply calculate the average for each product across the different Products associated with the supplier (name this fieldī) Using the OrderDetails table, create a query that showsįor each order the OrderID and the total quantity soldįor each product: (i) the ProductID, (ii) theĪverage sales unit price (name this field AverageUnitPrice you can (that have UnitsAvailable less than the ReorderLevel).Ħ.GROUP and AGGREGATE (e.g., COUNT, AVG, SUM):Ī) Using the Products table, create a query that showsįor each supplier: the SupplierID and the number of Using the ProductAvailability view that you created inĥa, create a query that only shows products that need to be ordered Troubleshoot a view at some point you can simply delete it andĪ) Copy and paste the query in 4a and create a view calledī. ProductName, UnitsInStock, UnitsOnOrder, ReorderLevel, theĬalculated field UnitsAvailable (copy and paste the query youĬreated in 4a), and a new calculated field named ReorderNeeded thatĭisplays "Yes" if UnitsAvailable < ReorderLevel and otherwise ProductName, UnitsInStock, UnitsOnOrder, ReorderLevel, and aĬalculated field named UnitsAvailable defined as UnitsInStock +ī) Use the Products table and create a query that shows Note that aliases are temporary.and not stored for reuse in aĪ) Use the Products table and create a query that shows Use the Customers table and create a query that shows allĤ.ALIASES and CASE. Use the products table and create a query that shows:Ī) products sorted by UnitsInStock in descending order.ī) the first four products in the products table (noĬ) the bottom 12 products in terms of UnitsInStock (i.e., fewest Use the OrderDetails table and create aĪ) all unique products sold (only show Product IDs in theī) all unique products sold with a unit price of more than $50.ģ.4) ORDER BY and/or SELECT TOP (note that SQLiteStudio uses Use the OrderHeaders table and create a query thatĪ) placed in 2019 (you can assume that there is no laterī) that were shipped after they were required.Ĭ) that have not yet been shipped (shipped date is null).ģ.3) DISTINCT.
0 kommentar(er)
