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.

How do you know when to include a HAVING clause in your query
Order Now on customessaymasters.com


Subject: Marketing

Thank you very much, Great job! On time delivery! 

Victoria, ~ Sydney, AU.

Subject: Management

Well done, I got an A and I am so happy! Thanks alot! 

J. Williams ~ London, UK

Subject: Sociology

Excellent work and timely delivery! I won't hesitate to order again and I highly recommend. 

Jeniffer ~ New York, USA.

Subject: Nursing

The quality of my research paper was outstanding and the writer was very cooperative.

Rachel, A. ~ Australia