Data Manipulation in SQL
| Course Details | ||||||||
|---|---|---|---|---|---|---|---|---|
|
||||||||
| Languages | ||||||||
|
||||||||
| Outcomes | ||||||||
|
||||||||
| Price & Terms | ||||||||
|
||||||||
| Technical | ||||||||
|
||||||||
| Specification | ||||||||
|
||||||||
Learn to categorize, filter and summarize relational data with CASE WHEN, subqueries, common table expressions and window functions in SQL.
Data manipulation in SQL picks up where joining and aggregating tables leave off, with queries that must categorize, filter and summarize data in several steps at once. You sort rows into categories with conditional logic, filter one query by the results of another, tidy long queries into named blocks, and calculate running totals and averages across groups of rows. Every exercise draws on the European Soccer Database, so the questions concern matches, teams and rivalries.
What You Will Achieve
- Build categorical columns, filtered counts and percentages by placing CASE WHEN inside aggregate functions
- Place subqueries in SELECT, FROM and WHERE clauses to filter rows and add calculated columns
- Choose between correlated subqueries, nested subqueries and common table expressions for a given data question
- Compute running totals and partitioned averages with OVER, PARTITION BY and sliding windows
Program Overview
Categorical Variables with CASE WHEN
Conditional logic that turns column values into categories and feeds filtered counts, sums and averages.
- Basic CASE statements comparing column values
- Filtering on a CASE statement
- CASE WHEN with COUNT, SUM and AVG
- Counts, percentages and fractions
Subqueries, Nested Queries and Common Table Expressions
Where a subquery belongs in a query, how correlated and nested versions work, and how CTEs keep multi-step queries organized.
- Scalar and list subqueries in WHERE
- Subqueries in FROM and SELECT
- Correlated and nested subqueries
- CTEs and deciding which technique to use
Window Functions and Running Totals
Passing aggregate functions along a dataset with OVER, then combining home and away team CTEs with a window function.
- The OVER clause
- PARTITION BY one or more columns
- Sliding windows
- Home and away team CTEs with a window function
Certificate Awarded (Paid)
Complete the course to earn it.
Job Outlook
These query techniques are used by data analysts, data scientists and developers who work with large datasets in relational databases and need answers that take several steps to reach.
Good for: SQL users who want cleaner ways to categorize, filter and summarize data within a single query, and who learn best by repeating each technique on one dataset.
Keep in mind: Though labeled Beginner, it is pitched at an intermediate level for people who already join and aggregate tables, and all practice uses the European Soccer Database.
Bottom line: Its soccer match exercises give CASE logic, subqueries, CTEs and window functions a steady, concrete workout on a single dataset.
Frequently Asked Questions
What dataset do the exercises use?
Every exercise runs on the European Soccer Database. Queries look at matches, teams and rivalries, so each new technique is tried on the same records and the results can be compared across chapters.
What do window functions add?
They pass aggregate functions along the dataset with OVER, so each row keeps its detail. The final chapter builds partitioned averages with PARTITION BY, running totals with sliding windows, and ends by combining home and away team CTEs with a window function.
How does the course help me pick between subqueries and CTEs?
A set of lessons solves one task, retrieving team names, three ways: with a subquery, with correlated subqueries and with common table expressions. A closing lesson then asks which technique suits the situation, so the comparison rests on a worked example.
User Reviews
Write your review
You must be logged in to post a review.
Reviews can only be posted by registered learners. Log in or create a free account, then you will return here to post.
There are no reviews yet.