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

Clarify the role of each legally mandated attendee on the Individualized Education Program team

This discussion assesses your ability to clarify the role of each legally mandated attendee on the Individualized Education Program team. This assessment also supports your achievement of Course Learning Outcome….

Describe the performance of “Salt Peanuts” provided in your Module 3 Playlist

n 100 words or more, describe the performance of “Salt Peanuts” provided in your Module 3 Playlist. Being a live performance, there are certain characteristics that we don’t have in….

Analyze the fixed and variable costs of a firm, how those costs have changed over time, and how those changes have impacted the firm’s overall health and sustainability

The purpose of this milestone is for students to explore the various costs their firm faces and to describe their firm’s market. Using the concepts and tools developed in Modules….