How are the WHERE clause and the HAVING clause similar? How are they different

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

Globalization of markets

Possible Goals If we were to consider possible fi nancial goals, we might come up with some ideas like the following: • Survive in business. • Avoid fi nancial distress….

financial management

Co-operative (Co-op) A co-operative is an enterprise that is equally owned by its members who share the benefi ts of co-operation, based on how much they use the co-operative’s services.5….

Globalization of markets and advanced communications and computer technology

A CORPORATION BY ANOTHER NAME The corporate form of organization has many variations around the world. The exact laws and regulations differ from country to country, of course, but the….