Building Queries to Model Data

Querying Data with SQL

Analysts often combine multiple data sets into one useful result. Querying is a core acquisition activity and can also support reusable views, pipelines, and reporting data sets.

Key idea

Start by identifying the fields and tables you need, then use the appropriate keys and join rules to combine them. After acquisition, transformations can prepare the result for analysis.

SELECT

Retrieves fields from one or more tables for viewing or analysis.

UPDATE

Changes existing field values in selected records.

INSERT

Adds records or copies values from another table.

DELETE

Removes complete records that meet specified criteria.

SELECT ProductID, ProductTypeID, ProductName,
       ProductCost, ProductListPrice, StarRating
FROM Inventory.dbo.ToyProducts;

Interactive DML Operation Demo

Click an operation to see how the same small table changes. Use Reset to restore the starting data.

Keys, Relationships & Joins

A key field such as CustomerID can connect normalized tables. The join type determines which matched and unmatched records appear in the result.

Relationship check

Validate that the selected tables and key fields make sense before joining. Existing referential integrity can prevent an order from referring to a customer that does not exist.

Interactive Key → Relationship → Join Demo

Use the buttons at your own pace. The demonstration first identifies the key, then builds the relationship, and finally joins Customers to Orders.

Join Types

Choose the join based on the records the result must retain. Click each type to review its behavior.

Interactive Join Explorer

Customers A, B, and C are in the left table. Orders exist for B, C, and an unmatched customer D. Switch join types to see which rows survive.

Filtering, Grouping & Aggregation

Use WHERE to isolate records before grouping. GROUP BY organizes rows into groups for aggregate calculations, while HAVING filters grouped results.

WHERE & LIKE

Exact filters can use =. Pattern filters can use LIKE with SQL wildcards such as %.

SELECT ProductTypeID, ProductTypeName
FROM ToyProductsTypes
WHERE ProductTypeName LIKE '%figur%';

GROUP BY & HAVING

Aggregate functions summarize groups. HAVING filters the grouped result.

SELECT ProductID,
       AVG(StarRating) AS [Avg Rating],
       COUNT(CT_Rates) AS [Total Count]
FROM ToyStarRatings
GROUP BY ProductID
HAVING ProductID = 15;

SUM

Adds values to produce a total.

COUNT

Counts records.

DISTINCT COUNT

Counts each unique value once.

AVERAGE

Totals values and divides by the number of values.

MAX / MIN

Returns the largest or smallest value.

Interactive query pipeline

Work through the query one operation at a time. The table changes to show what each clause or aggregate does.

Transpose, Pivot, Unpivot & Append

Data can be reshaped when its current layout is not suitable for analysis. A pivot-style table places categories such as months across columns; unpivoting converts those columns into vertical records. Transposing reverses the direction of a data layout, while appending combines compatible data sets into a larger set.

Transpose

Transpose means reversing the direction or orientation of data. It is useful when information received horizontally needs to be restructured for another layout.

Pivot

A pivot presents summarized data in a cross-tab layout with row, column, and value areas. For example, products can appear as rows and months as columns.

Unpivot

Unpivot converts wide pivot-style columns into a vertical record set. In the sales example, month columns become a Month field and their values become a Revenue field.

Append

Append combines rows from compatible data sets. An inline append leaves the combined result, while an intermediate append retains the original sets and creates a new combined set.

Why reshape data?

Pivot layouts are effective for reporting, but record format is often more flexible for additional analysis and visualizations. Unpivoting can recover a Product · Month · Revenue structure from a wide sales summary.

Interactive data reshaping demonstration

Use the controls at your own pace to see how the same sample sales data changes shape, then append another year's records.

Merging & UNION

Merging connects data sets through common fields and join rules. UNION stacks the results of multiple SELECT statements; UNION ALL retains repeated rows.

SELECT ProductID, ProductName, SaleDate, QuantitySold, Revenue, '2018' AS SalesYear FROM Sales2018
UNION
SELECT ProductID, ProductName, SaleDate, QuantitySold, Revenue, '2019' AS SalesYear FROM Sales2019
UNION
SELECT ProductID, ProductName, SaleDate, QuantitySold, Revenue, '2020' AS SalesYear FROM Sales2020;

UNION ALL

Use UNION ALL when repeated records should be retained rather than removed from the combined result.

Update, Insert & Delete Safely

Data-changing statements are powerful. Confirm the target records first, follow organizational change practices, and use transaction controls where appropriate.

UPDATE

UPDATE ToyProducts
SET ProductCost = 5,
    ProductListPrice = 7
WHERE ProductID = 25;

After execution, verify that the number of affected rows matches the intended selection.

INSERT

INSERT INTO ToyStarRatings
(ProductID, StarRating, CT_Rates, RatingDate)
SELECT ProductID, Star_Rating,
       Number_of_Ratings, Rating_Date
FROM StarRatingImport;

DELETE

SELECT * FROM ToyProducts
WHERE ProductID = 26;

DELETE FROM ToyProducts
WHERE ProductID = 26;

SELECT first to confirm the intended records before deleting them.

DELETE vs. TRUNCATE

DELETE can target records with WHERE and, in the source material's comparison, can be undone with other commands. TRUNCATE removes all records and cannot use WHERE.

Interactive safe-change workflow

Work through each operation at your own pace. The demonstration starts by verifying the target with SELECT before changing the sample data.

Knowledge Check

Test the major querying, joining, filtering, reshaping, and data-manipulation concepts.

Score: 0 / 0

InfoTech