How do you know when to include a HAVING clause in your query

SQL Practice

1. WHERE versus HAVING.
How are the WHERE clause and the HAVING clause similar? How are they different? How do you know when to include a HAVING clause in your query?

2. GI_FacultyRecruiting database.
Use the GI_FacultyRecruiting database below for this question.

a. Finish the SELECT statement below to list the name of each department along with the name of the department’s chair person.

SELECT

FROM____________________________________________________

b. Does every department have to have a department chair? Does every department have to have an EO coordinator? How do you know?

c. What question does the following query answer? Think about the rows that should be returned from the query… Can the same department show up in more than one row of the result? Can the same position show up more than once? Will every department have to show up at least once? Will every position show up at least once? Explain.(You may have to investigate which table has the foreign key and whether the FK is required/optional.)

SELECTd.DeptName, PositionTitle, PositionStatus

FROMDepartment AS d

LEFTOUTERJOIN Position AS p ON d.DeptID =p.DeptID

d. What question does the following query answer? If there are four departments and five positions, how many rows would you expect to see in this result, and why?

SELECTd.DeptName, PositionTitle, PositionStatus

FROMDepartment AS d

RIGHTOUTERJOIN Position AS p ON d.DeptID =p.DeptID

e. What question does the following query answer?

SELECTd.DeptName, PositionTitle, PositionStatus

FROMDepartment AS d

LEFTOUTERJOIN Position AS p ON d.DeptID =p.DeptID

WHEREp.PositionID ISNULL

3. AdventureWorks2012 – Employees & Salespeople. 
Consider the portion of the AdventureWorks2012 database shown below.

a) Show the SELECT statement for the following task:

Who are the current sales people who were hired in (roughly) the last ten years and who have a sales quota? Show the salesperson’s name, sales quota, and commission earned this year, where the commission earned is equal to the salesperson’s commission percentage rate multiplied by their year-to-date (YTD) sales.

b) Explain what the following query does and why the query does NOT need a GROUP BY clause (isn’t there an aggregate function in the SELECT clause?), and why the column alias “Years with Company” can be used in the ORDER BY clause.

SELECT

FirstName +‘ ‘+ LastName AS “Employee Name”,

JobTitle,

DATEPART(YEAR,GETDATE())DATEPART(YEAR,HireDate)AS “Years with Company”

FROM

Person.Person p

INNERJOIN HumanResources.Employee e ONp.BusinessEntityID = e.BusinessEntityID

WHERE

CurrentFlag = 1 AND SalariedFlag = 0

ORDERBY

“Years with Company” DESC

c) Show the SELECT statement to answer the following question: How many employees are currently working for each department? Show the department name and the number of current employees.

find the cost of your paper
Order now to get your homework done

Give an example of how the values and mission statement help to shape planning

Prior to beginning this discussion, read Chapter 1 of the course text and complete the following SWOT Analysis activity. After completing the activity, attach it to your 1st post by….

discuss some internal and external factors that may influence the business in the future

Prior to beginning this discussion, read Chapter 1 of the course text and complete the following SWOT Analysis activity. After completing the activity, attach it to your 1st post by….