Explore Product Data with T-SQL Queries

Step 1: Retrieve and Review Product Data

Exploring data is the first step in working with any database. Before you can filter or analyze information, you need to understand what data is available and how it’s structured.

In this step, you’ll connect to a preloaded SQL Server database called ProductsDB, which contains a table named Products. This table represents a product inventory catalog for a fictional company. It includes details such as product names, categories, prices, inventory quantities, ratings, and descriptions.
You’ll begin by writing your very first T-SQL queries to:

• View a small sample of the data to get a quick overview of the table.
• Retrieve specific columns to create a cleaner, report-ready dataset.
• Build familiarity with the table you’ll use throughout the lab.

What You’ll Learn in This Step

In this step, you’ll learn to:

• Connect to the SQL Server database and run your first queries.
• Use SELECT statements to return all rows and columns.
• Apply the TOP keyword to limit the number of rows displayed.
• Retrieve only the most important columns for reporting.

Note: The SQL Server environment has already been set up for you. Your focus will be on writing and executing queries, no installation or configuration is required.

info> You will write all of your T-SQL answers directly in the step-one.sql file provided in this environment.

When you are ready to run the query, simply press Run in the Terminal tab.
This will automatically execute the contents of step-one.sql against the database and display the results.
You do not need to type any commands in the terminal manually.

sqlcmd -S localhost -U SA -P 'P@ssword1'

### View a Sample of the Product Inventory

When you first begin exploring a new dataset, it’s usually not practical to view every record all at once. Large tables can contain thousands of rows, which makes it difficult to focus on structure and identify patterns. Instead, you start by looking at a small subset of data. This gives you a snapshot of the table and helps you verify that the database is set up correctly.

In SQL, the SELECT statement is used to retrieve data from a table. By default, it returns every row and every column, which can be overwhelming. To limit the number of rows returned, SQL provides a keyword you can place immediately after SELECT. This keyword specifies the maximum number of rows to include in the results, giving you a manageable preview of the data.

Here’s a general example:

SELECT TOP 5 *
FROM table_name;
GO

In this query:

• SELECT begins the statement and tells the database you want to retrieve data.
• TOP 5 limits the results to five rows.
• * includes all columns in the output.
• FROM table_name identifies which table to retrieve data from.

This approach is especially useful when working with large datasets or when you only need to confirm that a table contains the data you expect. It’s a fast way to check your environment before moving on to more complex analysis.

Next, you’ll apply this concept to preview a real table by limiting the output to a specific number of rows. ### Select Key Product Attributes for a Report

When you create reports or summaries, you rarely need every column in a table. Showing too much data can make results harder to read and interpret. Instead, you focus on the columns that matter most to your audience. For example, a product report might only need identifiers, names, categories, and prices. Selecting just these columns produces a cleaner and more relevant output.

The SELECT statement lets you list the exact columns you want to include in your query results. Instead of using * to return every column, you separate specific column names with commas. This keeps the query efficient and the output easy to understand.

Here’s a general example:

SELECT column_name1, column_name2, column_name3
FROM table_name;
GO

In this query:

• Each column_name represents a specific field you want to include.
• The order of the columns in the query determines how they appear in the results.
• FROM table_name identifies where the data comes from.

This technique is especially useful when creating reports or dashboards, where clarity and performance are critical.

Next, you’ll use this approach to retrieve only the columns that are essential for a simple product report.

Step 2: Shape and Format Query Output

Raw data is rarely presented exactly as it exists in the database. For reports and analysis, you often need to make changes so the information is easier to understand and share. Formatting data might include renaming columns for clarity, using built-in functions to modify how data appears, or identifying unique values to better understand the variety of items in a dataset.

What You’ll Learn in This Step

• Rename columns using aliases.
• Transform and format data using SQL functions.
• Identify unique values in a table with DISTINCT. ---

info> You will write all of your T-SQL answers directly in the step-two.sql file provided in this environment.

When you are ready to run the query, simply press Run in the Terminal tab. This will automatically execute the contents of step-two.sql against the database and display the results. You do not need to type any commands in the terminal manually.

--- ### Rename Columns with Aliases

When you run a query, the results display the column names exactly as they exist in the database. Sometimes these names are unclear or too technical for a report. You can improve readability by creating temporary display names, called aliases, for the columns in your query results. This does not change the actual database, it only affects how the query output is labeled.

In SQL, you use the AS keyword to define an alias. The alias follows the column name and appears only in the query output.

Here’s a general example:

SELECT column_name AS new_name
FROM table_name;
GO

• column_name is the original name stored in the database.
• AS new_name renames the column for the query results.
• FROM table_name specifies the table to retrieve data from.

Aliases are especially helpful when generating reports, so stakeholders see clear and descriptive headings instead of raw database field names.

Next, you’ll apply aliases to rename two specific columns in the Products table. ### Format Product Names and Find Their Length

Sometimes you need to transform how data appears in query results without permanently changing the stored data. SQL provides built-in functions to modify or analyze values as they are retrieved.

Two common functions for working with text are:

• UPPER(): Converts text to all uppercase letters
• LEN(): Counts the number of characters in a text value

These functions can be used directly inside a SELECT statement and combined with aliases to make the output clearer.

Here’s a general example:

SELECT UPPER(column_name) AS UppercaseValue,
       LEN(column_name) AS ValueLength
FROM table_name;
GO

• UPPER(column_name) converts the text to uppercase.
• LEN(column_name) counts the characters in the value.
• AS assigns clear labels to each column in the output.

These functions are especially useful when standardizing text for reporting or when checking data quality, such as ensuring product names meet length requirements.

Next, you’ll use these functions to format and analyze product names. ### Identify All Unique Product Categories

Tables often contain repeated values across many rows. For example, a category like "Electronics" might appear hundreds of times for different products. If you want to see a list of unique categories, you need a way to remove duplicates from the results.

The DISTINCT keyword in SQL removes duplicate values from a column, returning only one instance of each unique value.

Here’s a general example:

SELECT DISTINCT column_name
FROM table_name;
GO

• DISTINCT ensures that duplicate entries are removed.
• The query output will display each unique value only once.

This is particularly useful for summarizing data or getting an overview of the variety of entries within a column.

Next, you’ll use DISTINCT to identify the unique product categories in the inventory.

Step 3: Filter Query Results with the WHERE Clause

As datasets grow, raw query results often contain more information than you need. When answering business questions, it’s essential to focus on only the relevant records. Filtering lets you narrow down results to match specific conditions, such as price ranges, product categories, or inventory levels.

What You’ll Learn in This Step

In this step, you’ll use the WHERE clause to:

• Narrow results to rows that match specific conditions.
• Combine conditions using AND, OR, and NOT operators.
• Identify rows with missing data using NULL checks.

info> You will write all of your T-SQL answers directly in the step-three.sql file provided in this environment.

When you are ready to run the query, simply press Run in the Terminal. This will automatically execute the contents of step-three.sql against the database and display the results. You do not need to type any commands in the terminal manually.

--- ### Find High-value Products

The WHERE clause allows you to filter query results by applying a condition to each row. Only rows where the condition evaluates to true will be returned in the output.

You can use comparison operators such as:

• = equal to
• > greater than
• < less than
• >= greater than or equal to
• <= less than or equal to

When comparing numeric columns, the operator is placed between the column name and the value.

Example:

SELECT column_name
FROM table_name
WHERE column_name > value;
GO

This query returns only the rows where the column’s value is greater than the specified number. ### Find Products Using Multiple Criteria

The WHERE clause can evaluate multiple conditions at once. You combine conditions using logical operators:

• AND – All conditions must be true for the row to be included.
• OR – At least one condition must be true for the row to be included.

When combining conditions, each comparison is written separately and connected by the appropriate logical operator.

Example:

SELECT *
FROM table_name
WHERE column1 = 'ValueA'
  AND column2 < 100;
GO

This query only returns rows where column1 equals ValueA and column2 is less than 100. ### Find Products That Are Not a Specific Category

The NOT operator reverses a condition, selecting rows where the condition is not true. This is useful when you need to exclude a particular value from the results.

Example:

SELECT *
FROM table_name
WHERE column_name NOT IN ('ValueA');
GO

Alternatively, you can combine NOT with an equality operator:

WHERE column_name <> 'ValueA'

Find Products from Different Categories

The OR operator allows you to filter rows that match any one of several conditions. Each condition is written separately and connected with OR. This is helpful when you want to see data for multiple specific values.

Example:

SELECT *
FROM table_name
WHERE column_name = 'ValueA'
   OR column_name = 'ValueB';
GO

This query returns rows where column_name matches either ValueA or ValueB. ### Find Products with Missing Descriptions

Some records in a database may have missing values. In SQL, missing data is represented by NULL. To check for NULL values, you must use the IS NULL or IS NOT NULL operators. Standard equality operators like = will not work for NULL values.

Example:

SELECT *
FROM table_name
WHERE column_name IS NULL;
GO

This query returns rows where the column contains no value.

Step 4: Sort and Limit Results for Analysis

An unsorted list of products can be difficult to interpret. Sorting allows you to organize query results in a meaningful order, such as showing the highest-rated or lowest-priced products first. You can also limit how many rows are returned, which is useful for reports and dashboards where only a specific subset of data is needed.

What You’ll Learn in This Step

In this step, you’ll learn to:

• Sort query results in ascending or descending order.
• Apply multiple sort conditions for layered organization.
• Use OFFSET and FETCH to page through large datasets.
• Combine filters, sorting, and limits to create focused reports. ---

info> You will write all of your T-SQL answers directly in the step-four.sql file provided in this environment.

When you are ready to run the query, simply press Run in the Terminal tab. This will automatically execute the contents of step-four.sql against the database and display the results. You do not need to type any commands in the terminal manually.

--- ### Sort Products by Rating

The ORDER BY clause in SQL is used to sort query results. By default, ORDER BY sorts values in ascending order (ASC), meaning smallest to largest for numbers or A to Z for text. You can explicitly specify:

• ASC for ascending order
• DESC for descending order

When you sort by a single column, you list that column after the ORDER BY keyword.

Example:

SELECT column_name
FROM table_name
ORDER BY column_name DESC;
GO

This query retrieves values sorted from highest to lowest.

Sort Products by Multiple Columns

The ORDER BY clause can sort by more than one column. When you specify multiple columns, SQL applies sorting in the order they are listed:

1. The first column determines the primary sort order.
2. Rows with the same value in the first column are sorted by the second column, and so on.

Each column can have its own ASC or DESC direction.

Example:

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC, column2 DESC;
GO

This query sorts by column1 alphabetically and then sorts rows with matching column1 values by column2 in descending order. ### Find the 5 Cheapest Products

When creating reports, you often need only the top or bottom few records, such as the five cheapest products or the ten highest-rated items. You can use the TOP keyword to limit how many rows are returned. When using TOP, combine it with ORDER BY to ensure the correct rows are retrieved.

Example:

SELECT TOP 5 column_name
FROM table_name
ORDER BY column_name ASC;
GO

This query retrieves the five rows with the lowest values in the specified column. ### Retrieve a "Page" of Data for a Web Store

When displaying query results in a web application, data is often shown in pages. SQL provides the OFFSET and FETCH clauses to skip a certain number of rows and then retrieve a specific number of rows.

Example:

SELECT column_name
FROM table_name
ORDER BY column_name
OFFSET 20 ROWS
FETCH NEXT 20 ROWS ONLY;
GO

• OFFSET 20 ROWS skips the first 20 rows
• FETCH NEXT 20 ROWS ONLY retrieves the next 20 rows
• ORDER BY must be included to ensure consistent paging ### Create a Final Report

Complex reports often combine filtering, sorting, and formatting. By combining multiple SQL features, you can create queries that answer very specific business questions.

For this task:

• Use the WHERE clause to filter by category and rating.
• Apply the OR operator to include products in either of two categories.
• Use the AND operator to require that rating meets a minimum threshold.
• Apply the UPPER function to display product names in uppercase.
• Use TOP with ORDER BY to limit the results to the highest-rated products.

Example of combining techniques:

SELECT TOP 10 UPPER(name) AS ProductName, category, rating
FROM table_name
WHERE (category = 'ValueA' OR category = 'ValueB')
  AND rating > 3
ORDER BY rating DESC;
GO