Need help in Sql queries ! Thanx :) /* Question #1 - Without adding any addition

Question

Need help in Sql queries ! Thanx :)

/*

Question #1 -

Without adding any additional tables to the FROM statement below,

add the column "PackageTypeName" (from Warehouse.PackageTypes) to the

results

*/

SELECT

IL.PackageTypeID,

SUM(IL.ExtendedPrice)

FROM

Sales.Invoices I

INNER JOIN Sales.InvoiceLines IL ON I.InvoiceID = IL.InvoiceID

GROUP BY

IL.PackageTypeID

/*

Question #2

Write a query that returns each salesperson's best year and worst year,

based on the Invoice tables. Use the ExtendedPrice column from the

InvoiceLines table for each line's contribution (I don't want you

to calculate the contribution as we've done in other queries).

Order the results by the lowest of the worst years first.

You are not allowed to use a union, case statement, or windowed function.

You will get 10 rows back - first and last records look like the following:

14 Lily Code 2422204.13 6574146.27

15 Taj Shand 2896948.01 6045415.13

*/

/*

Question #3 -

The sales manager wants to look at the current year (2016 in this case) sales

compared to the lifetime sales for each salesperson. This is calculated by

aggregating the sales for 2016 (based on the Invoice tables) divided by

the sum of all sales for all time. Order the list in descending order of the

% of life time sales that 2016 represents. Your query will return 10 records

and the top two results look like:

PersonID FullName 2016Sales %OfLifeTimeSales

15 Taj Shand 2896948.01 0.145703

16 Archer Lamble 2850011.20 0.138150

You are not allowed to use a union, case statement, or windowed function.

*/

Explanation / Answer

1.SELECT
IL.PackageTypeID,PackageTypeName
SUM(IL.ExtendedPrice)
FROM
Sales.Invoices I
INNER JOIN Sales.InvoiceLines IL ON I.InvoiceID = IL.InvoiceID
INNERJOIN Warehouse.PackageTypes PT ON IL.PackageTypeID=PT.PackageTypeID
GROUP BY
IL.PackageTypeID

2.SELECT SALESPERSONID,BESTYEAR,WORSTYEAR
FROM INVOICE I
INNER JOIN
(SELECT MAX(ExtendedPrice),YEAR AS BESTYEAR
FROM INVOICELINES
GROUP BY SALESPERSONID,YEAR) A
I.SALESPERSONID=A.SALESPERSONID
SELECT MIN(EXTENDEDPRICE),YEAR AS WORSTYEAR
FROM INVOICELINES
GROUP BY SALESPERSONID,YEAR)B
I.SALESPERSONID=B.SALESPERSONID

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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