Writing SQL Queries

Writing SQL Queries

TTSQLQY3

Introductory

3 Days

Course Overview

A company’s success hinges on responsible, accurate database management. Organizations rely on highly available data to complete all sorts of tasks, from creating marketing reports and invoicing customers to setting financial goals. Data professionals like analysts, developers and architects are tasked with creating, optimizing, managing and analyzing data from databases – with little room for error. When databases aren’t built or maintained correctly, it’s easy to mishandle or lose valuable data. Our SQL Programming and Database Training Series provides students with the skills they require to develop, analyze and maintain data and in correctly structured,  modern and secure databases.

 

SQL is the cornerstone of all relational database operations. In this hands-on course, you learn to exploit the full potential of the SELECT statement to write robust queries using the best query method for your application, test your queries, and avoid common errors and pitfalls. It also teaches alternative solutions to given problems, enabling you to choose the most efficient solution in each situation.

 

The course describes the ANSI/ISO SQL standard, but also identifies deviations from the standard in the two most widely used database products, Oracle and Microsoft SQL Server.

Course Objectives

This “skills-centric” course is about 50% hands-on lab and 50% lecture, designed to train attendees core SQL programming and database skills, coupling the most current, effective techniques with the soundest industry practices. Throughout the course students will be led through a series of progressively advanced topics, where each topic consists of lecture, group discussion, comprehensive hands-on lab exercises, and lab review.

 

Our engaging instructors and mentors are highly experienced practitioners who bring years of current "on-the-job" experience into every classroom.  Working within in an engaging, hands-on learning environment, guided by our expert team, attendees will learn to:

  • Maximize the potential of SQL to build powerful, complex and robust SQL queries
  • Query multiple tables with inner joins, outer joins and self joins
  • Construct recursive common table expressions
  • Summarize data using aggregation and grouping
  • Execute analytic functions to calculate ranks
  • Build simple and correlated subqueries
  • Thoroughly test SQL queries to avoid common errors
  • Select the most efficient solution to complex SQL problems

Course Prerequisites

This is an introductory- level course appropriate for those who are developing applications using relational databases, or who are using SQL to extract and analyze data from databases and need to use the full power of SQL queries. Attendees are required to have a basic understanding of SQL.

 

Take Before: Attendees should have incoming experience equivalent to the topics in the course(s) below, or should have attended these as a pre-requisite:

  • TTSQLB3  Introduction to SQL Basics – 3 days

 

Take After: Our core SQl and database training courses provide students with a solid foundation for continued learning based on role, goals, or their areas of specialty.  Our learning paths offer a wide variety of follow-on courses such as:

  • Intermediate or Advanced SQL programming topics
  • Database or tooling topics (SQL Server, Oracle, OBIEE, TOAD, etc)
  • Database security topics
  • Data Analytics / Big Data training – Spark, Hadoop, etc.
  • Please contact us for recommended next steps tailored to your longer-term education, project, role or development objectives.

Course Agenda

Please note that this list of topics is based on our standard course offering, evolved from typical industry uses and trends. We’ll work with you to tune this course and level of coverage to target the skills you need most. Topics, agenda and labs are subject to change, and may adjust during live delivery based on audience needs and skill-level.

 

 

  1. Introduction and Overview
  • SQL fundamentals
  • Why SQL can be both easy and difficult
  • Recommendations for thorough testing

 

  1. Retrieving data with SELECT
  • Expressions
  • Literals
  • Handling NULLs properly

 

  1. Executing queries
  • Analyzing query plans
  • Enhancing query performance
  • Retrieving partly results with FETCH and OFFSET
  • Selecting the best alternatives
  • Avoiding errors and pitfalls
  • Querying Multiple Tables

 

  1. Implementing various types of joins
  • Inner joins
  • Cross joins
  • Left, right and full outer joins
  • Equijoins vs. theta joins
  • The performance implications of joins
  • Adding filter conditions to outer joins

 

  1. Writing self joins
  • Joining a table to itself
  • Chaining self joins
  • Solving time-interval problems

 

  1. Combining queries with set operators
  • UNION
  • UNION ALL
  • INTERSECT
  • EXCEPT
  • Aggregate Functions

 

  1. Summarizing data with aggregate functions
  • COUNT
  • SUM
  • AVG
  • MIN
  • MAX
  • Managing NULLs
  • identifying duplicates

 

  1. Grouping data
  • GROUP B
  • Applying conditions with HAVING
  • Calculating moving averages
  • Building crosstab reports

 

  1. Extending group queries
  • Nesting grouped aggregates
  • Joins and grouping
  • Introducing subtotals with CUBE and ROLLUP
  • Performing Extensive Analysis with Analytic Functions

 

  1. The OVER clause
  • Specifying the ordering before applying the function
  • Splitting the result set into logical partitions
  • Calculating ranks
  • RANK and DENSE_RANK
  • ROW_NUMBER with ordered sets
  • Calculating percentiles

 

  1. Extending the use of aggregates
  • Partitioning in multiple levels
  • Computing running totals
  • Comparing row and aggregate values
  • Top-N queries
  • Defining sliding window boundaries

 

  1. Building Subqueries

 

  1. Self-contained subqueries
  • Subqueries in conditions and column expressions
  • Creating multilevel subqueries
  • Avoiding problems when subqueries return NULLs
  • Handling multirow subquery results
  • Finding gaps in number series

 

  1. Correlated subqueries
  • Accessing values from the outer query
  • EXISTS vs. IN
  • Identifying duplicates
  • Avoiding accidental correlation

 

  1. Common table expressions
  • Reusable subqueries
  • Recursive subqueries
  • Traversing hierarchies
  • Breaking Down Complex Queries
  • Overcoming SQL limitations
  • Reducing complexity and improving performance

Course Materials

Each student will receive a Student Guide with course notes, code samples, software tutorials, diagrams and related reference materials and links (as applicable). Our courses also include step by step hands-on lab instructions and and solutions, clearly illustrated for users to complete hands-on work in class, and to revisit to review or refresh skills at any time. Students will also receive the project files (data sets or code, as applicable) and solutions required for the hands-on work.

Raise the bar for advancing technology skills

Attend a Class!

Live scheduled classes are listed below or browse our full course catalog anytime

Special Offers

We regulary offer discounts for individuals, groups and corporate teams. Contact us

Custom Team Training

Check out custom training solutions planned around your unique needs and skills.

EveryCourse Extras

Exclusive materials, ongoing support and a free live course refresh with every class.

Attend a Course

Please see the current upcoming available open enrollment course dates posted below. Please feel free to Register Online below, or call 844-475-4559 toll free to connect with our Registrar for assistance. If you need additional date options, please contact us for scheduling.

Course Title Days Date Time Price
Writing SQL Queries 3 Days Mar 29 to Mar 31 10:00 AM to 06:00 PM EST $1,795.00 Register
Writing SQL Queries 3 Days May 3 to May 5 10:00 AM to 06:00 PM EST $1,795.00 Register
Writing SQL Queries 3 Days Jun 14 to Jun 16 10:00 AM to 06:00 PM EST $1,795.00 Register
Writing SQL Queries 3 Days Jul 26 to Jul 28 10:00 AM to 06:00 PM EST $1,795.00 Register
Writing SQL Queries 3 Days Aug 30 to Sep 1 10:00 AM to 06:00 PM EST $1,795.00 Register

New Site, BIG Savings!
We're celebrating the launch of our lonnngggg awaited new site with with *50% off all 2021 Public Classes* booked by March 31!  Check out our Current Offers for Individuals, Teams and Organizations to Learn for Less!

See our latest Offers and Promotions

Learn. Explore. Advance!

Extend your training investment! Recorded sessions, free re-sits and after course support included with Every Course
Trivera MiniCamps
Gain the skills you need with less time in the classroom with our short course, live-online hands-on events
Trivera QuickSkills: Free Courses and Webinars
Training on us! Keep your skills current with free live events, courses & webinars
Trivera AfterCourse: Coaching and Support
Expert level after-training support to help organizations put new training skills into practice on the job

The voices of our customers speak volumes

Special Offers
Limited Offer for most courses.

SAVE 50%

Learn More