Course Description
In this course students learn the basics of writing Oracle SQL statements. This course provides the SQL skills that allow developers, database administrators and end-users to write queries against single and multiple tables, manipulate data in tables and take advantage of Oracle supplied row functions.
In This Course You Will Learn:
- Access database data with a basic SELECT statement
- Access data from more than one table
- Manipulate database data
- Decide where best to use Oracle-supplied functions in place of user-written code
Prerequisites
You need to have equivalent experience or have taken the following GogoTraining course:
Outline
Module 00: Oracle 11g/12c: Introduction to SQL Online Training - Course Introduction
Module 01: Writing SQL SELECT Statements
- Review the basic SQL SELECT statement syntax
- Select all columns using a wildcard notation from a table
- Valid SELECT statement expressions
- Write a query containing the arithmetic operators
- Use aliases to customize column headings
- Create a character expression with the concatenation operator
- Use the CASE Expression
- Lab Exercise
Module 02: Writing SQL SELECT Statements Demo
Module 03: Restricting Data
- Use the WHERE Clause
- WHERE clause operators
- =, <>, >, < operators
- BETWEEN, IN, LIKE, IS NULL operators
- Adding ANY or ALL to an operator
- List the Logical Conditions AND, OR, NOT
- Use Multiple Conditions in the WHERE clause
- Describe the Rules of Precedence
- Lab Exercise
Module 04: Restricting Data Demo
Module 05: Sorting Data
- Discuss default order of result sets
- Sort Rows with the ORDER BY Clause
- ORDER BY valid expressions
- DISTINCT and UNIQUE clauses
- Lab Exercise
Module 06: Sorting Data Demo
Module 07: Displaying Data from Multiple Tables
- Identify Types of Joins
- ANSI standard join operators vs. non-standard join methods
- Using the non-standard join methods
- Inner join vs. Outer join
- Generating a Cartesian Product
- ANSI standard join operators
- NATURAL JOIN, JOIN USING, JOIN ON, LEFT/RIGHT/FULL OUTER joins
- Lab Exercise
Module 08: Displaying Data from Multiple Tables Demo
Module 09: Character Row Functions
- Categories of row functions
- Statement behavior of row functions
- Useful / common character row functions
- Nest Functions to perform multiple tasks in one statement
- Lab Exercise
Module 10: Character Row Functions Demo
Module 11: Numeric Row Functions
- Where numeric row functions can and cannot be used
- Helpful numeric row functions in SQL statements
- Functionalities of common numeric row functions
- Explain Implicit and Explicit conversion
- Lab Exercise
Module 12: Numeric Row Functions Demos
Module 13: Date/Time Row Functions
- Where date/time row functions can and cannot be used
- Helpful date/time row functions in SQL statements
- Functionalities of common date/time row functions
- Lab Exercise
Module 14: Date/Time Row Functions Demo
Module 15: Miscellaneous Row Functions
- Using conversion functions
- Using conversion functions to format date/time data
- Environment and Data specific row functions
- The Decode function versus the Case Option
- Lab Exercise
Module 16: Miscellaneous Row Functions Demo
Module 17: Aggregating Data Using Group Functions
- Categorize the Types of Group Functions
- Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query
- Utilize the DISTINCT Keyword with the Group Functions
- Describe how Nulls are handled with the Group Functions
- Create Groups of Data with the GROUP BY Clause
- Group Data by more than one column
- Exclude Groups of Data with the HAVING Clause
- Differences between the HAVING clause and the WHERE clause
- Lab Exercise
Module 18: Aggregating Data Using Group Functions Demo
Module 19: Extensions to the GROUP BY Clause
- Using the CUBE option
- Using the ROLLUP option
- CUBE vs. ROLLUP output
- The GROUPING BY option
- Lab Exercise
Module 20: Extensions to the GROUP BY Clause Demo
Module 21: Manipulating Data I
- Write INSERT statements to add rows to a table
- Insert Special Values
- Copy Rows from Another Table
- Update Rows in a Table
- Use DELETE statements to remove rows from a table
- Delete Rows Based on Another Table
- The COMMIT and ROLLBACK statements
- Using the SAVEPOINT statement
- Lab Exercise
Module 22: Manipulating Data I Demo
Module 23: Manipulating Data II
- Describe the TRUNCATE Statement
- TRUNCATE vs. DELETE
- Using the Multi-table INSERT statement
- Using the MERGE statement
- Show how Read Consistency works
- Lab Exercise
Module 24: Manipulating Data II Demo
Module 25: 12c New Features
- Top-n Query
- Cascading Truncate Statement
- Native Left Outer Join
Certification
Oracle 11g/12c Introduction to SQL combined with Oracle 11g/12c Advanced SQL helps you to prepare for any of the following exams: 1Z0-047 Oracle Database SQL Expert or 1Z0-051 Oracle Database 11g: SQL Fundamentals I or 1Z0-061 Oracle Database 12c: SQL Fundamentals. When you pass one of the 1Z0-051 or 1Z0-061 exams, you have completed a portion of the requirements towards one of several Oracle’s Certified Associate (OCA) certifications. Passing the 1Z0-047 exam gives you the Oracle Database SQL Certified Expert certification.
For more information about certification, requirements and fees, click on the link found in Related Links.
The following certification prep books provide further help including valuable practice exams. Combining a hands-on course with practice exams is highly recommended to prepare for Oracle’s various certifications.
OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)
by Steve O'Hearn ISBN-13: 978-0071614214 ISBN-10: 9780071614214
OCA Oracle Database 11g SQL Fundamentals I Exam Guide Exam (1Z0-051)
by John Watson ISBN-13: 978-0071597869 ISBN-10: 9780071597869
OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061)
by Roopesh Ramklass ISBN-13: 978-0071820288 ISBN-10: 0071820280
