Skip to content
Home » SQL Week 1 Practice Problems

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';