AdventureWorks Database: Calculate sales targets per month for salespeople
SQL Query - AdventureWorks: Exercise-140 with Solution
140. From the following tables write a query in SQL to calculate sales targets per month for salespeople.
Sample table: Sales.SalesPerson
Sample table: HumanResources.Employee
Sample table: Person.Person
Sample Solution:
SELECT s.BusinessEntityID AS SalesPersonID, FirstName, LastName, SalesQuota, SalesQuota/12 AS "Sales Target Per Month"
FROM Sales.SalesPerson AS s
JOIN HumanResources.Employee AS e
ON s.BusinessEntityID = e.BusinessEntityID
JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID;
Sample Output:
salespersonid|firstname|lastname |salesquota|Sales Target Per Month| -------------+---------+-----------------+----------+----------------------+ 274|Stephen |Jiang | | | 275|Michael |Blythe | 300000| 25000.000000000000| 276|Linda |Mitchell | 250000| 20833.333333333333| 277|Jillian |Carson | 250000| 20833.333333333333| 278|Garrett |Vargas | 250000| 20833.333333333333| 279|Tsvi |Reiter | 300000| 25000.000000000000| 280|Pamela |Ansman-Wolfe | 250000| 20833.333333333333| 281|Shu |Ito | 250000| 20833.333333333333| 282|José |Saraiva | 250000| 20833.333333333333| 283|David |Campbell | 250000| 20833.333333333333| 284|Tete |Mensa-Annan | 300000| 25000.000000000000| 285|Syed |Abbas | | | 286|Lynn |Tsoflias | 250000| 20833.333333333333| 287|Amy |Alberts | | | 288|Rachel |Valdez | 250000| 20833.333333333333| 289|Jae |Pak | 250000| 20833.333333333333| 290|Ranjit |Varkey Chudukatil| 250000| 20833.333333333333|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Tax difference between highest and lowest-taxed state or province.
Next: Find unit price, ID number, and modulus of division.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join