Robert Simon | June 3, 2024
Practice Topics
- Basic SELECT statements
- Sorting with ORDER BY
- Alias Names
- Filtering using the WHERE statement
- Common Types of Filters
- OR vs. AND
- Using AND and OR Together with Parentheses
Basic SELECT Statements
- Write a query to select all columns from the
Person.Person
table. - Write a query to select only the
FirstName
,LastName
, andBusinessEntityID
from thePerson.Person
table.
SELECT * FROM AdventureWorks2019.Person.Person;
SELECT FirstName, LastName, BusinessEntityID FROM AdventureWorks2019.Person.Person;
Sorting with ORDER BY
- Write a query to select all columns from the
Sales.Customer
table, ordered byCustomerID
in descending order. - Write a query to select
ProductID
,Name
, andListPrice
from theProduction.Product
table, ordered byListPrice
in ascending order.
SELECT * FROM AdventureWorks2019.Sales.Customer ORDER BY CustomerID DESC;
SELECT ProductID, Name, ListPrice FROM AdventureWorks2019.Production.Product ORDER BY ListPrice ASC;
Alias Names
- Write a query to select
FirstName
andLastName
from thePerson.Person
table with aliasesFN
andLN
for the columns, respectively. - Write a query to select
Name
andListPrice
from theProduction.Product
table with aliasesProductName
andPrice
, respectively.
SELECT FirstName AS FN, LastName AS LN FROM AdventureWorks2019.Person.Person;
SELECT Name AS ProductName, ListPrice AS Price FROM AdventureWorks2019.Production.Product;
Filtering Using the WHERE Statement
- Write a query to select all products from the
Production.Product
table where theColor
is ‘Black’. - Write a query to select all employees from the
HumanResources.Employee
table who were hired after January 1, 2005.
SELECT * FROM AdventureWorks2019.Production.Product WHERE Color = 'Black';
SELECT * FROM AdventureWorks2019.HumanResources.Employee WHERE HireDate > '2005-01-01';
Common Types of Filters
- Write a query to select all products from the
Production.Product
table where theListPrice
is greater than 1000. - Write a query to select all employees from the
HumanResources.Employee
table where theJobTitle
is ‘Production Technician – WC60’.
SELECT * FROM AdventureWorks2019.Production.Product WHERE ListPrice > 1000;
SELECT * FROM AdventureWorks2019.HumanResources.Employee WHERE JobTitle = 'Production Technician - WC60';
OR vs. AND
- Write a query to select all products from the
Production.Product
table where theColor
is ‘Black’ OR theListPrice
is greater than 1000. - Write a query to select all products from the
Production.Product
table where theColor
is ‘Black’ AND theListPrice
is greater than 1000.
SELECT * FROM AdventureWorks2019.Production.Product WHERE Color = 'Black' OR ListPrice > 1000;
SELECT * FROM AdventureWorks2019.Production.Product WHERE Color = 'Black' AND ListPrice > 1000;
Using AND and OR Together with Parentheses
- Write a query to select all products from the
Production.Product
table where theColor
is ‘Black’ AND theListPrice
is greater than 1000 OR theProductLine
is ‘T’ (indicating Touring products). Ensure proper use of parentheses to correctly group the conditions.
SELECT * FROM AdventureWorks2019.Production.Product WHERE ( Color = 'Black' AND ListPrice > 1000 ) OR ProductLine = 'T';