Information Technology

Introduction to Writing SQL Queries (TTSQL003)

Instructor-Led 18 Agenda

Description

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 explo

Course Overview

Course Objective

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

Who Should Attend

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.

Introduction: Quick Tools Review Lesson Id 1

Introduction: Quick Tools Review

Using the SQL SELECT Statement Lesson Id 2

Using the SQL SELECT Statement

Restricting and Sorting Data Lesson Id 3

Restricting and Sorting Data

Single-Row Functions Lesson Id 4

Single-Row Functions

Conversion Functions and Expressions Lesson Id 5

Conversion Functions and Expressions

Using the Group Functions and Aggregated Data Lesson Id 6

Using the Group Functions and Aggregated Data

SQL Joins and Join Types Lesson Id 7

SQL Joins and Join Types

Using Subqueries Lesson Id 8

Using Subqueries

Using the SET Operators Lesson Id 9

Using the SET Operators

Using Data Manipulation Language (DML) statements Lesson Id 10

Using Data Manipulation Language (DML) statements

Using Data Definition Language (DDL) Lesson Id 11

Using Data Definition Language (DDL)

Data Dictionary Views Lesson Id 12

Data Dictionary Views

Creating Sequences, Synonyms, Indexes Lesson Id 13

Creating Sequences, Synonyms, Indexes

Creating Views Lesson Id 14

Creating Views

Managing Schema Objects Lesson Id 15

Managing Schema Objects

Retrieving Data Using Subqueries Lesson Id 16

Retrieving Data Using Subqueries

Data Control Language (DCL) Lesson Id 17

Data Control Language (DCL)

Manipulating Data Lesson Id 18

Manipulating Data

Lesson Id	Title	Description
1	Introduction: Quick Tools Review	Introduction to SQL and its development environments<br />Using SQL*PLUS<br />Using SQL Developer
2	Using the SQL SELECT Statement	Capabilities of the SELECT statement<br />Arithmetic expressions and NULL values in the SELECT statement<br />Column aliases<br />Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword<br />Use of the DESCRIBE command
3	Restricting and Sorting Data	Limiting the Rows<br />Rules of precedence for operators in an expression<br />Substitution Variables<br />Using the DEFINE and VERIFY command
4	Single-Row Functions	Describe the differences between single row and multiple row functions<br />Manipulate strings with character function in the SELECT and WHERE clauses<br />Manipulate numbers with the ROUND, TRUNC and MOD functions<br />Perform arithmetic with date data<br />Manipulate dates with the date functions
5	Conversion Functions and Expressions	Describe implicit and explicit data type conversion<br />Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions<br />Nest multiple functions<br />Apply the NVL, NULLIF, and COALESCE functions to data<br />Decode/Case Statements
6	Using the Group Functions and Aggregated Data	Group Functions<br />Creating Groups of Data<br />Having Clause<br />Cube/Rollup Clause
7	SQL Joins and Join Types	Introduction to JOINS<br />Types of Joins<br />Natural join<br />Self-join<br />Non equijoins<br />OUTER join
8	Using Subqueries	Introduction to Subqueries<br />Single Row Subqueries<br />Multiple Row Subqueries
9	Using the SET Operators	Set Operators<br />UNION and UNION ALL operator<br />INTERSECT operator<br />MINUS operator<br />Matching the SELECT statements
10	Using Data Manipulation Language (DML) statements	Data Manipulation Language<br />Database Transactions<br />Insert<br />Update<br />Delete<br />Merge
11	Using Data Definition Language (DDL)	Data Definition Language<br />Create<br />Alter<br />Drop
12	Data Dictionary Views	Introduction to Data Dictionary<br />Describe the Data Dictionary Structure<br />Using the Data Dictionary views<br />Querying the Data Dictionary Views<br />Dynamic Performance Views
13	Creating Sequences, Synonyms, Indexes	Creating sequences<br />Creating synonyms<br />Creating indexes<br />Index Types
14	Creating Views	Creating Views<br />Altering Views<br />Replacing Views
15	Managing Schema Objects	Managing constraints<br />Creating and using temporary tables<br />Creating and using external tables
16	Retrieving Data Using Subqueries	Retrieving Data by Using a Subquery as Source<br />Working with Multiple-Column subqueries<br />Correlated Subqueries<br />Non-Correlated Subqueries<br />Using Subqueries to Manipulate Data<br />Using the Check Option<br />Subqueries in Updates and Deletes<br />In-line Views
17	Data Control Language (DCL)	System privileges<br />Creating a role<br />Object privileges<br />Revoking object privileges
18	Manipulating Data	Overview of the Explicit Default Feature<br />Using multitable INSERTs<br />Using the MERGE statement<br />Tracking Changes in Data