SQL Week 1 Practice Problems

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, and BusinessEntityID from the Person.Person table.

SQL Code Highlighting

SELECT
    *
FROM
    AdventureWorks2019.Person.Person;

SQL Code Highlighting

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 by CustomerID in descending order.
Write a query to select ProductID, Name, and ListPrice from the Production.Product table, ordered by ListPrice in ascending order.

SQL Code Highlighting

SELECT
    *
FROM
    AdventureWorks2019.Sales.Customer
ORDER BY
    CustomerID DESC;

SQL Code Highlighting

SELECT
    ProductID,
    Name,
    ListPrice
FROM
    AdventureWorks2019.Production.Product
ORDER BY
    ListPrice ASC;

Alias Names

Write a query to select FirstName and LastName from the Person.Person table with aliases FN and LN for the columns, respectively.
Write a query to select Name and ListPrice from the Production.Product table with aliases ProductName and Price, respectively.

SQL Code Highlighting

SELECT
    FirstName AS FN,
    LastName AS LN
FROM
    AdventureWorks2019.Person.Person;

SQL Code Highlighting

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 the Color is ‘Black’.
Write a query to select all employees from the HumanResources.Employee table who were hired after January 1, 2005.

SQL Code Highlighting

SELECT
    *
FROM
    AdventureWorks2019.Production.Product
WHERE
    Color = 'Black';

SQL Code Highlighting

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 the ListPrice is greater than 1000.
Write a query to select all employees from the HumanResources.Employee table where the JobTitle is ‘Production Technician – WC60’.

SQL Code Highlighting

SELECT
    *
FROM
    AdventureWorks2019.Production.Product
WHERE
    ListPrice > 1000;

SQL Code Highlighting

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 the Color is ‘Black’ OR the ListPrice is greater than 1000.
Write a query to select all products from the Production.Product table where the Color is ‘Black’ AND the ListPrice is greater than 1000.

SQL Code Highlighting

SELECT
    *
FROM
    AdventureWorks2019.Production.Product
WHERE
    Color = 'Black'
    OR ListPrice > 1000;

SQL Code Highlighting

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 the Color is ‘Black’ AND the ListPrice is greater than 1000 OR the ProductLine is ‘T’ (indicating Touring products). Ensure proper use of parentheses to correctly group the conditions.

SQL Code Highlighting

SELECT
    *
FROM
    AdventureWorks2019.Production.Product
WHERE
    (
        Color = 'Black'
        AND ListPrice > 1000
    )
    OR ProductLine = 'T';