TTSQL003: Introduction to Writing SQL Queries
About this Course
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.
Introduction to Writing SQL Queries is a three-day, hands-on course that provides you with the skills and experienced required to use SQL to efficiently organize, retrieve, analyze, and manipulate data to deliver insightful reports, inform strategic decisions, and optimize various aspects of any business. Each module introduces critical concepts and practices, providing you with the knowledge to streamline your data management tasks.
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. Mastering the art of restricting and sorting data enables you to customize your queries, displaying only what’s essential and presenting it in a meaningful order. Understanding single-row functions and conversion functions helps optimize data handling by transforming and converting data types as needed. Lastly, by unraveling the mysteries of SQL joins, group functions, and subqueries, you’ll acquire the ability to aggregate data, combine data from multiple tables, and conduct complex queries with ease.
You’ll leave this course equipped with a firm understanding of SQL and its real-world application, helping you better organize, analyze, and manipulate data in your professional role. You’ll be ready to contribute to improving data management processes, enhancing efficiency, and providing actionable insights within your organization. You will be able to streamline data-related tasks, improve data quality, and contribute to data-driven decision-making processes.
NOTE: This course uses SQL Developer. SQL Developer is not the focus of the course, but rather the platform for learning the SQL querying skills. Students are NOT required to have prior experience with SQL Developer. All tools are provided in our easy-access, no-install required, cloud-based environment.
Audience Profile
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.
At Course Completion
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
Learn the art of data conversion and manipulation:
Delve into SQL joins, group functions, and subqueries: 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.
Outline
Please note that this list of topics is based on our standard course offering, evolved from typical industry uses and trends. We will work with you to tune this course and level of coverage to target the skills you need most. Course agenda, topics and labs are subject to adjust during live delivery in response to student skill level, interests and participation.
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
OPTIONAL / TIME-PERMITTING
The following chapters are included in your course materials, and will be covered time-permitting, depending on the skill level and interests of the attendees.
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
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.