SQL 1 - Get a list of all cities and state/province Id’s that every person lives

Question

SQL

1 - Get a list of all cities and state/province Id’s that every person lives in. Sort by city. (Person.Address) (613 rows)

2 - Create a query that shows how many products have the same number of days to manufacture and the same product line. Sort by number of days it takes to manufacture. (Production.Product) (12 rows)(Hint: the table has columns DaysToManufacture and ProductLine)

3- Find the number of products of each size for all products that have a size. (Production.Product) (18 rows)

4 - Find the number of people who received each type of email promotion. (Person.Person)(3 rows)

5 - Find the number of each person of each type. (Person.Person)(6 rows)

6 - For each employee select the Employee id, Leave hours and avg leave hours for that job title.(HumanResources.Employee)(Hint: use partition)(290 rows)

7 - Show the productID and total list price by color for all products that have a color. (Production.Product) (256 rows)

Explanation / Answer

Since the table structures and the column names are not provided, the column names are assumed here. Please refer the below queries as requested.

SELECT P.CITY,P.STATE FROM PERSONS P
ORDER BY CITY;

SELECT COUNT(P.Product_ID) NO_OF_SAME_PD_PRODUCTS, P.ProductLine,P.DaysToManufacture
FROM Production.Product P
GROUP BY P.ProductLine,P.DaysToManufacture;

SELECT COUNT(PRODUCT_ID), SIZE FROM Production.Product
GROUP BY SIZE;

SELECT P.PERSON_ID, P.EMAIL_PROMOTION FROM Person.Person P
GROUP BY P.EMAIL_PROMOTION;

SELECT P.PERSON_ID, P.TYPE FROM Person.Person P
GROUP BY P.TYPE;

SELECT P.PRODUCT_ID, P.COLOR,SUM(P.PRICE) TOTAL_PRICE_BY_)COLOR
FROM Production.Product P
WHERE P.COLOR IS NOT NULL
GROUP BY P.PRODUCT_ID, P.COLOR;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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