SQL Exercise: Let’s practice Filtering with Multiple Predicates

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 displaying the order ID, order date, and total due from the Sales.SalesOrderHeader table. Retrieve only those rows where the order was placed during the month of September 2001 and the total due exceeded $1,000.

SELECT SalesOrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2001-09-01' AND '2001-09-30' AND TotalDue > 1000;

Q2: Change the query in question 1 so that only the dates September 1–3, 2001, are retrieved. See whether you can figure out three different ways to write this query.

SELECT SalesOrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2001-09-01' AND '2001-09-03' AND TotalDue > 1000;

SELECT SalesOrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE OrderDate IN ('2001-09-01', '2001-09-02', '2001-09-03') AND TotalDue > 1000;

SELECT SalesOrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE (OrderDate >= '2001-09-01' AND OrderDate <= '2001-09-03')	AND TotalDue > 1000;

Q3: Write a query displaying the sales orders where the total due exceeds $1,000. Retrieve only those rows where the salesperson ID is 279 or the territory ID is 6.

SELECT SalesOrderID, OrderDate, TotalDue, SalesPersonID, TerritoryID
FROM Sales.SalesOrderHeader
WHERE TotalDue > 1000 AND (SalesPersonID = 279 OR TerritoryID = 6);

Q4: Change the query in question 3 so that territory 4 is included.

SELECT SalesOrderID, OrderDate, TotalDue, SalesPersonID, TerritoryID
FROM Sales.SalesOrderHeader
WHERE TotalDue > 1000 AND (SalesPersonID = 279 OR TerritoryID IN (6, 4));

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