Skip to content
Home » Blog » SQL Week 1 Practice Problems

SQL Week 1 Practice Problems

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

Overview

The SELECT statement is used to identify the fields that you wish to view in the query output. Let’s say that one table has 50 fields. If you want to view 10 of the 50 fields, you list them in this portion of the query. As you will find out later in this book, you will also perform mathematical and other functions in this section of the query. If you want to see every field in your output, use SELECT *.

The FROM statement is used to identify the tables that the query output will be selected from. This could include one or more tables. Tables are connected by joining on common fields. This topic will be discussed later.

Each table used will be listed below the word FROM and will contain three components: the database name, the schema name, and the table name. A database may be made up of a couple of tables or hundreds of tables. A schema sits within a database and is used to organize similar groups of tables. A table is used to store rows and columns (similar to a table in an Excel file).

The syntax is as follows: FROM [Database Name].[Schema Name].[Table Name]

Example: Display a person’s Title, FirstName, MiddleName and LastName from the AdventureWorks2019.Person.Person table.

SQL Code Highlighting 
SELECT
    Title,
    FirstName,
    MiddleName,
    LastName
FROM
    AdventureWorks2019.Person.Person;

Self Practice

  • Write a query to select all columns from the AdventureWorks2019.Person.Person table.
  • Write a query to select only the FirstName, LastName, and BusinessEntityID from the AdventureWorks2019.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

Overview

The ORDER BY clause allows us to sort a dataset by any number of columns in ascending or descending order.

At the very end of a query, you can accomplish this by adding ORDER BY column1, column2, etc. Ascending order is the default. If you want a column to be sorted in descending order, you can add DESC after the column name. For example, ORDER BY column1, column2 DESC. In this case, column1 will come in ascending order and column column2 will come in descending order.

Example: Display a person’s Title, FirstName, MiddleName and LastName from the AdventureWorks2019.Person.Person table then sort by LastName descending then FirstName ascending.

SQL Code Highlighting 
SELECT
    Title,
    FirstName,
    MiddleName,
    LastName
FROM
    AdventureWorks2019.Person.Person
ORDER BY
    LastName DESC,
    FirstName;

Self Practice

  • Write a query to select all columns from the AdventureWorks2019.Sales.Customer table, ordered by CustomerID in descending order.
  • Write a query to select ProductID, Name, and ListPrice from the AdventureWorks2019.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

Overview

Alias names are just like when a person has an alias name. It means that we can call a column or table something else.

Why would we want to call a column or table something else?

  1. Each column shown in the SELECT statement lives in a database table. Therefore, we need the database table name listed before each column in our SELECT statement. Without it, the query won’t know which table to pull each column from. When we reference a table in our SELECT statement, writing A before each column from the A table is a hell of a lot easier than writing DatabaseName.SchemaName.TableName before each column.
  2. We sometimes need to rename columns because database naming conventions aren’t always clean.
  3. When we create formulas on columns (in the SELECT statement), we need to specify a column name in our output.

It is very important to note that while you can reference a table alias name anywhere in a query, you are only able to reference a column alias name in the ORDER BY.

The syntax for a table alias is: DatabaseName.SchemaName.TableName TableAliasName.

The syntax for a column alias is: TableAliasName.ColumnName as ColumnAliasName.

Example: Alias name the AdventureWorks2019.Person.Person table as A and in the SELECT statement, alias name the FirstName as FName, MiddleName as MName, and LastName as LName.

SQL Code Highlighting 
SELECT
    A.FirstName as FName,
    A.MiddleName as MName,
    A.LastName as LName
FROM
    AdventureWorks2019.Person.Person A;

Self Practice

  • Write a query to select FirstName and LastName from the AdventureWorks2019.Person.Person table with column aliases FN and LN, respectively.
  • Write a query to select Name and ListPrice from the AdventureWorks2019.Production.Product table with column aliases ProductName and Price, respectively.
  • Write a query to select Name and ListPrice from the AdventureWorks2019.Production.Product table with column aliases ProductName and Price, respectively. In addition, give the AdventureWorks2019.Production.Product table an alias name of a. Be sure to attach the table’s alias name to the Name and ListPrice fields.
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;
SQL Code Highlighting 
SELECT
    a.Name AS ProductName,
    a.ListPrice AS Price
FROM
    AdventureWorks2019.Production.Product a;

Filtering Using the WHERE Statement

Overview

Filtering is one of the most important aspects of a SQL query. Filtering is as simple as it sounds. It’s just a way to eliminate unwanted records from your query results. When you are in the learning stages of writing SQL queries, I highly recommend that you write on a separate sheet of paper each of the tables that you plan to use in your query then think through every single filter and list them for each table. If you miss even one filter, you can possibly return millions of unwanted records.

Filters use standard operators such as =, <>, >, <, in, like, between.

You can include as many filters as you need, just separate them by AND or OR. If you use OR, please consider order of operations and use () at the beginning and end of the OR statement(s).

Common uses of filters in WHERE clause:

  1. Eliminate unwanted records (ex. old records, irrelevant records, etc.).
  2. Limit to specific date ranges.
  3. Limit to specific status codes.

Example: Filter our AdventureWorks2019.Person.Person table to anyone with a PersonType equal to EM (aka. ‘Employee’), a FirstName of Michael, and a ModifiedDate in 2009.

SQL Code Highlighting 
SELECT
    A.FirstName as FName,
    A.MiddleName as MName,
    A.LastName as LName
FROM
    AdventureWorks2019.Person.Person A
WHERE
    A.PersonType = 'EM'
    AND A.FirstName = 'Michael'
    AND A.ModifiedDate between '2009-01-01' and '2009-12-31';

Self Practice

  • Write a query to select all products from the AdventureWorks2019.Production.Product table where the Color is ‘Black’.
  • Write a query to select all employees from the AdventureWorks2019.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

Overview

As mentioned in the previous section, filtering is extremely important. This section highlights some of the most common operators to be used in filters. Note – this list is not comprehensive as there could be other types of operators.

In a filter, you can compare fields against other fields, fields against fixed/hard-coded values, and fields against calculations (ex. date look backs). Note – Ensure that you are using the same data types; otherwise, you will get a data type conversion error. For example, you can’t filter a string field by a number/date value.

Example: The below query shows filtering of the AdventureWorks2019.Sales.SalesOrderDetail table using operators such as equal to, greater than, less than and not equal to. This is just for reference, try running the query with one filter at a time so you can see each filter’s behavior.

SQL Code Highlighting 
SELECT
    A.SalesOrderID,
    A.SalesOrderDetailID,
    A.CarrierTrackingNumber,
    A.OrderQty,
    A.ProductID,
    A.SpecialOfferID,
    A.UnitPrice,
    A.UnitPriceDiscount,
    A.LineTotal,
    A.rowguid,
    A.ModifiedDate
FROM
    AdventureWorks2019.Sales.SalesOrderDetail A
WHERE
    A.OrderQty = 5 -- Equal to
    OR A.OrderQty > 5 -- Greater than
    OR A.OrderQty >= 5 -- Greater than or equal to
    OR A.OrderQty < 5 -- Less than
    OR A.OrderQty <= 5 -- Less than or equal to  
    OR A.OrderQty <> 5 -- Not equal to;

Example: The below query shows filtering using operators such as:

  • IN: Return records where a string or number exists within a specified list of possible values.
  • LIKE: Return records where a string has a partial match on a specified set of characters: ex. % before a specified string means any character(s) may exist before the specified string. % after a specified string means any character(s) may exist after the specified string. % before and after a specified string means any character(s) may exist before OR after the specified string.
  • BETWEEN: Return records where a date, string, or number falls within a specified range: ex. return all records where a date value falls between a range of dates.
We’ll filter the Person table using these operators. Again, practice with one filter at a time to see the behavior of each.

SQL Code Highlighting 
SELECT
    A.PersonType as [Person Type], -- Use brackets when you want to include a space in your alias name, 
    A.Title as Title,
    A.FirstName as [First Name],
    A.MiddleName as [Middle Name],
    A.LastName as [Last Name],
    A.ModifiedDate
FROM
    AdventureWorks2019.Person.Person A
WHERE
    A.FirstName IN ('Thomas','Michael','Mary') -- FirstName is either Thomas, Michael or Mary
    OR A.ModifiedDate BETWEEN '2009-01-01' AND '2009-12-31' -- ModifiedDate falls anywhere between Jan 1, 2009 and Dec 31, 2009
    OR A.LastName LIKE '%ra%' -- Lastname contains 'ra' with any characters falling before OR after 'ra';

Self Practice

  • Write a query to select all products from the AdventureWorks2019.Production.Product table where the ListPrice is greater than 1000.
  • Write a query to select all employees from the AdventureWorks2019.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

Overview

A very important (sometimes overlooked) aspect of filtering is understanding the syntax associated with OR and AND. Just like a calculator in math class, SQL might not always process things in the order that you desire so your syntax needs to be specific. It’s very simple, just use parentheses.

Example: Show anyone from the AdventureWorks2019.Person.Person table with a LastName that contains the string MI or TH. Please only include employees by returning PersonType values equal to EM. Don’t use parentheses for order of operations yet. You will do this in the next example to see the difference.

SQL Code Highlighting 
SELECT
    A.PersonType as [Person Type], 
    A.Title as Title,
    A.FirstName as [First Name],
    A.MiddleName as [Middle Name],
    A.LastName as [Last Name],
    A.ModifiedDate as [Modified Date]
FROM
    AdventureWorks2019.Person.Person A
WHERE
    A.lastName LIKE '%mi%'
    OR A.lastName LIKE '%th%'
    AND A.PersonType = 'EM';

Example: Show anyone from the AdventureWorks2019.Person.Person table with a LastName that contains the string MI or TH. Please only include employees by returning PersonType values equal to EM. Now, use parentheses around the two LastName filters for order of operations. Everything else about the query remains the same as the prior example. You will see different results with the parentheses applied.

SQL Code Highlighting 
SELECT
    A.PersonType as [Person Type], 
    A.Title as Title,
    A.FirstName as [First Name],
    A.MiddleName as [Middle Name],
    A.LastName as [Last Name],
    A.ModifiedDate as [Modified Date]
FROM
    AdventureWorks2019.Person.Person A
WHERE
    (
    A.lastName LIKE '%mi%'
    OR A.lastName LIKE '%th%'
    )
    AND A.PersonType = 'EM';

Self Practice

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

Conclusion

As we wrap up this week’s SQL homework assignments, we’ve delved into the foundational aspects of SQL, including basic SELECT statements, filtering with WHERE clauses, ordering results with ORDER BY, using aliases, and understanding the nuances of AND versus OR conditions. These exercises are designed to build your confidence and proficiency in SQL, equipping you with the skills necessary for more advanced queries in the future.

Remember, consistent practice is key to mastering SQL. If you find yourself needing extra help or wanting to deepen your understanding, don’t hesitate to sign up for our private or group training sessions. These sessions offer personalized guidance and a collaborative learning environment to further enhance your SQL skills. Stay tuned for next week’s assignments, and keep pushing your boundaries as you continue on your journey to becoming an SQL expert!

Leave a Reply

Your email address will not be published. Required fields are marked *