Oracle SQL Training (ORA100)
This course is designed to provide a solid foundation in SQL and relational databases, essential for anyone interested in database management, data analysis, or software development. Whether you are a beginner or looking to reinforce your database skills, this course will guide you through the fundamental concepts and practical techniques required to work with relational databases effectively.
The course begins with an introduction to the basics of relational databases, including a brief history of SQL, an overview of relational database concepts, and a look at popular database systems. You will learn about schemas and users, setting the stage for creating and managing database objects.
As you progress, you will dive into creating tables, understanding different data types, and applying constraints. Practical exercises will help reinforce your learning by guiding you through creating and altering tables, adding unique constraints, and managing table columns.
The course then covers essential SQL querying skills, starting with basic SELECT statements, sorting records, and using the WHERE clause to filter data. You will practice writing SQL queries that involve multiple conditions and learn how to limit results using FETCH.
You will also explore Oracle SQL functions, including numeric, character, and datetime functions, and work with calculated fields and column aliases. Aggregate functions such as COUNT, SUM, AVG, and grouping data will also be covered in depth.
The course delves into more advanced topics, including joins, subqueries, and set operators like UNION, INTERSECT, and MINUS. You will learn how to use these tools to combine and manipulate data from multiple tables effectively.
Conditional processing with CASE statements, data manipulation language (DML) operations like INSERT, UPDATE, and DELETE, and creating views are also key components of this course. By the end, you will be well-prepared to write complex SQL queries, manage data efficiently, and utilize advanced features of SQL to meet various database requirements.
- Understand how Oracle works
- Learn how tables are structured and how data is stored.
- Learn to use Oracle to output reports.
- Learn to use SQL functions.
- Learn to group data to get aggregate values.
- Learn to write joins and subqueries to get data from multiple tables.
- Learn to use SET operators.
- Learn to do conditional processing with CASE.
- Learn to write INSERT, UPDATE, and DELETE statements.
- Learn to create views.
Public expert-led online training from the convenience of your home, office or anywhere with an internet connection. Guaranteed to run .
Private classes are delivered for groups at your offices or a location of your choice.
Learn at your own pace with 24/7 access to an On-Demand course.
- Relational Database Basics
- Brief History of SQL
- Relational Databases
- Tables
- Rows
- Columns
- Relationships
- Data Types
- Primary Keys
- Foreign Keys
- Relational Database Management System
- Popular Databases
- Commercial Databases
- Popular Open Source Databases
- Schemas and Users
- Connection Lines
- Tables
- Creating Tables
- Data Types
- Creating Tables
- NULL Values
- Primary Keys
- Foreign Keys
- Adding Constraints
- Dropping Constraints
- UNIQUE Constraints
- Adding and Dropping Columns
- Dropping Tables
- Basic Selects
- Comments
- Whitespace and Semi-colons
- Case Sensitivity
- SELECTing All Columns in All Rows
- SELECTing Specific Columns
- Sorting Records
- Sorting by a Single Column
- Sorting By Multiple Columns
- Ascending and Descending Sorts
- The WHERE Clause and Logical Operator Symbols
- Checking for Equality
- Checking for Inequality
- Checking for Greater or Less Than
- Checking for NULL
- WHERE and ORDER BY
- Checking Multiple Conditions with Boolean Operators
- AND
- OR
- Order of Evaluation
- The WHERE Clause and Logical Operator Keywords
- The BETWEEN Operator
- The IN Operator
- The LIKE Operator
- The NOT Operator
- Limiting Rows
- Fetching a Percent of Records
- Oracle SQL Functions
- The DUAL Table and Column Aliases
- Column Aliases
- Calculated Fields '
- Concatenation
- Mathematical Calculations
- ROW_NUMBER()
- Numeric Functions
- ABS(), POWER(), and SQRT()
- CEIL(), FLOOR(), and ROUND()
- ROUND(num1, num2) and TRUNC(num1, num2)
- MOD()
- Character Functions Returning Character Values
- TO_CHAR(number, format_model)
- CONCAT()
- LOWER(), UPPER(), and INITCAP()
- LPAD() and RPAD()
- TRIM(), LTRIM(), and RTRIM()
- REPLACE() and SUBSTR()
- Character Functions Returning Number Values
- INSTR() and LENGTH()
- Datetime Functions
- CURRENT_DATE, CURRENT_TIMESTAMP, SYSDATE, and
- SYSTIMESTAMP
- TO_DATE()
- TO_CHAR(datetime, format_model)
- ROUND() and TRUNC()
- NULL-Related Functions
- COALESCE()
- NVL()
- NVL2()
- Other Functions
- DECODE()
- GREATEST() and LEAST()
- The DUAL Table and Column Aliases
- Aggregate Functions
- Introduction to Aggregate Functions
- Grouping Data
- GROUP BY
- HAVING
- Order of Clauses
- Grouping Rules
- Selecting Distinct Records
- ROLLUP() and CUBE()
- ROLLUP()
- CUBE()
- Joins
- Inner Joins
- Outer Joins
- Left Joins
- Right Joins
- Full Outer Joins
- Subqueries
- Subquery Basics
- Subqueries in the SELECT Clause
- Combining SELECT and WHERE Subqueries
- Set Operators
- Set Operators
- Rules for Set Operations
- UNION
- UNION ALL
- INTERSECT
- MINUS
- Conditional Processing with CASE
- Using CASE
- Selected Case
- Searched Case
- Data Manipulation Language
- INSERT
- UPDATE
- DELETE
- Updating and Deleting Multiple Records
- Creating Views
- Creating Views
- Dropping Views
- Benefits of Views
- Inline Views
- Creating Views
Each student will receive a comprehensive set of materials, including course notes and all the class examples.
Courses that can help you meet these prerequisites:
Live Public Class
$2,300.00 / student
Live Private Class
- Private Class for your Team
- Live training
- Online or On-location
- Customizable
- Expert Instructors
Self-Paced Course
- On Demand 24/7
- Readings
- Presentations
- Exercises
- Quizzes
- Full Year of Access
- Learn more