SQL Exercise: Let’s practice Filtering Data

In this exercise, I will be using AdventureWorks 2008 Database and will write my SQL queries using SSMS 18. All the exercises are from the book: Beginning T-SQL 2008

---

Q1: Write a query using a WHERE clause that displays all the employees listed in the
HumanResources.Employee table who have the job title Research and Development Engineer.
Display the business entity ID number, the login ID, and the title for each one.

SELECT BusinessEntityID, LoginID, JobTitle
FROM HumanResources.Employee
WHERE JobTitle = 'Research and Development Engineer';

Q2: Write a query using a WHERE clause that displays all the names in Person.Person with the middle name J. Display the first, last, and middle names along with the ID numbers.

SELECT FirstName, LastName, MiddleName, BusinessEntityID
FROM Person.Person
WHERE MiddleName = 'J';

Q3: Write a query displaying all the columns of the Production.ProductCostHistory table from the rows that were modified on June 17, 2003.

SELECT *
FROM Production.ProductCostHistory
WHERE ModifiedDate = '2007-06-17';

Q4: Rewrite the query you wrote in question 1, changing it so that the employees who do not have the title Research and Development Engineer are displayed.

We could write the SQL in multiple ways

• Using !=
• Using <>

SELECT BusinessEntityID, LoginID, JobTitle
FROM HumanResources.Employee
WHERE JobTitle != 'Research and Development Engineer';

SELECT BusinessEntityID, LoginID, JobTitle
FROM HumanResources.Employee
WHERE JobTitle <> 'Research and Development Engineer';

Q5: Write a query that displays all the rows from the Person.Person table where the rows were modified after December 29, 2000. Display the business entity ID number, the name columns, and the modified date.

SELECT BusinessEntityID, FirstName, MiddleName, LastName, ModifiedDate
FROM Person.Person
WHERE ModifiedDate > '2000-12-29';

Q6: Rewrite the last query so that the rows that were not modified on December 29, 2000, are displayed.

SELECT BusinessEntityID, FirstName, MiddleName, LastName, ModifiedDate
FROM Person.Person
WHERE ModifiedDate <> '2000-12-29';

Q7: Rewrite the query from question 5 so that it displays the rows modified during December 2000.

SELECT BusinessEntityID, FirstName, MiddleName, LastName, ModifiedDate
FROM Person.Person
WHERE ModifiedDate BETWEEN '2000-12-01' AND '2000-12-31';

Q8: Rewrite the query from question 5 so that it displays the rows that were not modified during December 2000.

SELECT BusinessEntityID, FirstName, MiddleName, LastName, ModifiedDate
FROM Person.Person
WHERE ModifiedDate NOT BETWEEN '2000-12-01' AND '2000-12-31';

---

I will be adding more examples so keep an eye out. Also, feel free to drop in your suggestions and questions.