Introduction to Writing SQL Queries

Maximize the Potential of SQL to Build Powerful, Complex and Robust SQL Queries

TTSQL003

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.

Learning Objectives

This course combines expert lecture, real-world demonstrations and group discussions with machine-based practical labs and exercises.  Working in a hands-on learning environment led by our expert practitioner, you’ll 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

Need different skills or topics?  If your team requires different topics or tools, additional skills or custom approach, this course may be further adjusted to accommodate.  We offer additional SQL, database, data analytics and other related courses which may be blended with this course for a track that best suits your learning objectives.

Course Objectives

This course combines expert lecture, real-world demonstrations and group discussions with machine-based practical labs and exercises.  Working in a hands-on learning environment led by our expert practitioner, 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

Need different skills or topics?  If your team requires different topics or tools, additional skills or custom approach, this course may be further adjusted to accommodate.  We offer additional SQL, database, data analytics and other related courses which may be blended with this course for a track that best suits your learning objectives.

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. In order to benefit from the hands-on labs, attendees should have prior experience in scripting or programming languages.

Please see the Related Courses tab for Pre-Requisite course specifics and links, links to similar courses you might review as an alternative, as well as suggested Next-Step Follow-On Courses and Learning Path recommendations.

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, participation and skill-level.

Introduction: Quick Tools Review

  • Introduction to SQL and its development environments
  • Using SQL*PLUS
  • Using SQL Developer

Using the SQL SELECT Statement

  • Capabilities of the SELECT statement
  • Arithmetic expressions and NULL values in the SELECT statement
  • Column aliases
  • Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
  • Use of the DESCRIBE command

Restricting and Sorting Data

  • Limiting the Rows
  • Rules of precedence for operators in an expression
  • Substitution Variables
  • Using the DEFINE and VERIFY command

Single-Row Functions

  • Describe the differences between single row and multiple row functions
  • Manipulate strings with character function in the SELECT and WHERE clauses
  • Manipulate numbers with the ROUND, TRUNC and MOD functions
  • Perform arithmetic with date data
  • Manipulate dates with the date functions

Conversion Functions and Expressions

  • Describe implicit and explicit data type conversion
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Nest multiple functions
  • Apply the NVL, NULLIF, and COALESCE functions to data
  • Decode/Case Statements

Using the Group Functions and Aggregated Data

  • Group Functions
  • Creating Groups of Data
  • Having Clause
  • Cube/Rollup Clause

SQL Joins and Join Types

  • Introduction to JOINS
  • Types of Joins
  • Natural join
  • Self-join
  • Non equijoins
  • OUTER join

Using Subqueries

  • Introduction to Subqueries
  • Single Row Subqueries
  • Multiple Row Subqueries

Using the SET Operators

  • Set Operators
  • UNION and UNION ALL operator
  • INTERSECT operator
  • MINUS operator
  • Matching the SELECT statements

Using Data Manipulation Language (DML) statements

  • Data Manipulation Language
  • Database Transactions
  • Insert
  • Update
  • Delete
  • Merge

Using Data Definition Language (DDL)

  • Data Definition Language
  • Create
  • Alter
  • Drop

Data Dictionary Views

  • Introduction to Data Dictionary
  • Describe the Data Dictionary Structure
  • Using the Data Dictionary views
  • Querying the Data Dictionary Views
  • Dynamic Performance Views

Creating Sequences, Synonyms, Indexes

  • Creating sequences
  • Creating synonyms
  • Creating indexes
  • Index Types

Creating Views

  • Creating Views
  • Altering Views
  • Replacing Views

Managing Schema Objects

  • Managing constraints
  • Creating and using temporary tables
  • Creating and using external tables

Retrieving Data Using Subqueries

  • Retrieving Data by Using a Subquery as Source
  • Working with Multiple-Column subqueries
  • Correlated Subqueries
  • Non-Correlated Subqueries
  • Using Subqueries to Manipulate Data
  • Using the Check Option
  • Subqueries in Updates and Deletes
  • In-line Views

Data Control Language (DCL)

  • System privileges
  • Creating a role
  • Object privileges
  • Revoking object privileges

Manipulating Data

  • Overview of the Explicit Default Feature
  • Using multitable INSERTs
  • Using the MERGE statement
  • Tracking Changes in Data

Course Materials

Student Materials: Each participant will receive a digital Student Guide and/or Course Notes, code samples, software tutorials, step-by-step written lab instructions (as applicable), diagrams and related reference materials and resource links. Students will also receive the project files (or code, if applicable) and solutions required for the hands-on work.

Hands-On Setup Made Simple! Our dedicated tech team will work with you to ensure our ‘easy-access’ cloud-based course environment, or local installation, is accessible, fully-tested and verified as ready to go well in advance of the course start date, ensuring a smooth start to class and effective learning experience for all participants. In some cases we can also help you install this course locally if preferred. Please inquire for details and options.

Every-Course Extras = High-Value & Long-Term Learning Support! All Public Schedule courses include our unique EveryCourse Extras package (Course Recordings, Live Instructor Follow-on Support, Free *Live* Course Refresh Re-Takes, early access to Special Offers, Free Courses & more). Please inquire for details.

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
Introduction to Writing SQL Queries 3 Days Oct 3 to Oct 5 10:00 AM to 06:00 PM EST $1,795.00 Enroll
Introduction to Writing SQL Queries 3 Days Nov 14 to Nov 16 10:00 AM to 06:00 PM EST $1,795.00 Enroll
Introduction to Writing SQL Queries 3 Days Dec 12 to Dec 14 10:00 AM to 06:00 PM EST $1,795.00 Enroll

Hot Summer Savings!
Buy One Get One Free!

Enroll by September 30 in any TWO public classes in 2022 for the price of ONE! 

Click for Details & Additional Offers

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