Video accessible from your Account page after purchase.
Over 4 Hours of Video Instruction
SQL can do far more than just retrieve, filter, and aggregate data. Take your knowledge of SQL fundamentals to the next level by writing powerful queries that solve real-world data analysis problems!
Overview:
SQL is the language analysts use to talk to databases, and most enterprise data lives in relational databases like SQL Server. Knowing basic SQL gets you to the data. Knowing advanced SQL lets you do something meaningful with it before it ever leaves the database.
This course uses SQL Server and SSMS, both extremely common tools in the workplace, to move beyond simple retrieval and into valuable analysis. Analysts working with this skillset can rank and compare records, calculate running totals and moving averages, derive percentiles, identify gaps and patterns in sequential data, and build period-over-period comparisons, all directly in SQL, without exporting to a spreadsheet or statistical tool.
Skill Level:
Learn How To:
Course Requirement:
Who Should Take This Course:
About Pearson Video Training:
Pearson publishes expert-led video tutorials covering a wide selection of technology topics designed to teach you the skills you need to succeed. These professional and personal technology videos feature world-leading author instructors published by your trusted technology brands: Addison-Wesley, Cisco Press, Pearson IT Certification, Prentice Hall, Sams, and Que Topics include IT Certification, Network Security, Cisco Technology, Programming, Web Development, Mobile Development, and more. Learn more about Pearson Video training at http://www.informit.com/video.
Video Lessons are available for download for offline viewing within the streaming format. Look for the green arrow in each lesson.
Introduction
Lesson 1. Getting Oriented
1.1 Review SQL foundations
1.2 Solve data analysis problems with SQL
1.3 Set up SQL Server 2025 and SSMS
1.4 Explore the course dataset
Lesson 2. Understanding Common Table Expressions
2.1 Write your first CTE
2.2 Chain multiple CTEs together
2.3 Build recursive CTEs for hierarchies
2.4 Compare CTEs, subqueries, and temp tables
2.5 Challenge: Solve a problem that requires CTEs
2.6 Solution: Solve a problem that requires CTEs
Lesson 3. Learning Window Functions
3.1 Understand the OVER() clause
3.2 Rank rows with ROW_NUMBER, RANK, and DENSE_RANK
3.3 Bucket rows with NTILE
3.4 Partition data for group-level analysis
3.5 Look ahead and behind with LEAD and LAG
3.6 Pull first and last values per group
3.7 Frame windows with ROWS and RANGE
3.8 Calculate running totals
3.9 Build moving averages
3.10 Summarize data with descriptive statistics
3.11 Challenge: Identify top performers
3.12 Solution: Identify top performers
Lesson 4. Understanding Analytical Patterns
4.1 Work with dates and times in SQL
4.2 Find the top N rows per group
4.3 Detect gaps and islands in sequences
4.4 Analyze sessions from event data
4.5 Pivot rows into columns for reporting
4.6 Compare year-over-year trends
4.7 Challenge: Reseller sales report
4.8 Solution: Reseller sales report
Lesson 5. Understanding Performance Fundamentals
5.1 Trace how SQL Server executes a query
5.2 Read an execution plan
5.3 Measure queries with STATISTICS IO and TIME
5.4 Write SARGable queries
5.5 Choose indexes for analytical workloads
5.6 Challenge: Optimize an inefficient query
5.7 Solution: Optimize an inefficient query
Lesson 6. Analytical SQL in Practice
6.1 Avoid common performance anti-patterns
6.2 Decide when CTEs are appropriate
6.3 Choose between temp tables and table variables
6.4 Simplify row-by-row logic with APPLY
6.5 Apply query hints with care
6.6 Create multi-step pipelines
6.7 Prepare data for other tools
6.8 Use AI tools for data analysis
6.9 Challenge: Build a customer value pipeline
6.10 Solution: Build a customer value pipeline
Course Summary
