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.



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


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.


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




Person.Person p

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


CurrentFlag = 1 AND SalariedFlag = 0


“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

Reflection Exercise Instructions   (SEE ATTACHMENT FOR THE QUESTIONS) Writer: 470084   For each of the 3 questions you choose to answer, you must respond using a minimum of 250–285….

Create a mitigation plan to address challenges, the creation of a vision statement, and a description of how one would translate their vision into measurable objectives

Assignment: Selling the Vision Leadership is not magnetic personality—that can just as well be a glib tongue. It is not “making friends and influencing people”—that is flattery. Leadership is lifting….