SQL 1 – Find the number of employees by job title. (HumanResources.Employee) (67

Question

SQL

1 – Find the number of employees by job title. (HumanResources.Employee) (67 rows)

2 - Find the total number of Male and Female Engineers and total number of Engineers (HumanResources.Employee) (Hint: If someone has an engineering role, the job title will have the word engineer in it) (HumanResources.Employee) (3 rows)

3 - Find the customers that have placed more than 20 orders? (SalesOrderHeader)(14 rows)

4 - Find the number of products of each rainbow color.(Production.Product) (Hint: Blank or null is not a color on the rainbow)(3 rows)

5 - For each product find the number of items sold(Indicated by Order Qty for each Order for a given product) and the total amount for that products sale (Indicated by Line Total. (Sales.SalesOrderDetail) (266 rows)

Explanation / Answer

Solution:

Question (1):

The number of Employees can be found by count() function. As the number of employees is needed by job title therefore we have to group the tuples of the table by using group by. Hence the answer is:

select jobTitle,count(*) from Employee group by jobTitle;

Question (2):

For this question we also apply group by clause over sex and jobTitle.Therefore the answer is:

select sex,jobTitle,count(*) from Employee group by sex,jobTitle having jobTitle = 'engineer';

Total number of engineers can be found as:

select jobTitle,count(*) from Employee group by jobTitle having jobTitle = 'engineer';

Question (3):

In this solution we find the name of the customer who placed order more than 20. Therefore the answer is:

select name from customer where order >20;

Question (4):

In this solution we use group by clause over colour where color of each product matches colors in rainbow. Therefore the answer is:

select color, count(*) from Product group by color having color = 'red' or color = 'orange' or color = 'yellow' or color = 'green' or color = 'blue' or color = 'indigo' or color = 'violet';

Qestion (5):

In this solution we use group by clause over product. Then we can find total number of products sold. Therefore the answer is:

select product,OrderQty, count(*) from SalesOrderDetail group by product, OrderQty;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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