This course provides the basic concepts of Oracle Database and SQL. The participant can acquire a good understanding of how to use SQL to query and manipulate the data in Oracle Database. This course facilitates in writing queries, subqueries, combining multiple queries to retrieve and sort the required data.
This course helps in accomplishing the creation of tables to store data, views to display data and to manage the other schema objects. It discusses the practice of data dictionary views and ensures secure access to the database by restricting the user access control. It offers an overview of conditions and usage of regular expression to support SQL. The development tools used are SQL * Plus and SQL Developer.
Objectives
- Writing SQL to fetch data from multiple tables
- Execute Data Manipulation statements
- Knowing about the Integrity Constraints
- Learn to create tables and manage schema objects
- Displaying data with the help of views
- Gain knowledge of operators and functions
- Become familiar with the SQL * Plus utility
- Overview of SQL Developer
- Provide security to the database with user access control
- Hear about the overview of conditions
- Usage of regular expression in SQL
Course Content
Introduction to SQL
- Understand the System Development Life Cycle
- Know the SQL Standards
- Learn how SQL Works
- RDBMS concepts
- Introduction to Oracle Database
- Discover ER Model
- Learn about Oracle Internet Platform
Basic Elements of Oracle SQL
- Datatypes
- Capabilities of SELECT statements
- Arithmetic Expressions
- Operators
- Editing Commands
- File Commands
- Comments in SQL
DML, DDL, DCL and Transaction Control (TCL) Statements
- INSERT/UPDATE
- DELETE
- CREATE/ALTER
- GRANT/REVOKE
- COMMIT
- SAVEPOINT
- ROLLBACK
- SET TRANSACTION
Sorting and Restricting Data
- Using WHERE clause
- Comparison and Logical operators
- Rules of Precedence
SQL * Plus Commands
- Creating basic reports
- Using SET commands
- Column wrapping
- Spooling report output
Referential Integrity
- Overview of Integrity Constraints
- Types of Integrity Constraints
- Mechanisms of Constraint Checking
Joins and Sub queries
- Join Conditions
- Self-Join
- Inner Join
- Outer Join
- Using Subqueries
- Types of Subqueries
- Multiple column subqueries
Operators, Single Row and Group Functions
- Character and Number Functions
- Date Function
- NVL Function
- Decode and Nesting Function
- GROUP BY
- HAVING
Database Access using SQL * Plus and SQL Developer
- Entering SQL * Plus
- Schema Navigation with user Tables
- Interactive Reports
- Create and execute script files
- User Variables
- Customize the SQL* Plus Environment
Creating and Managing Schema Objects
- Tables
- Sequence
- Index
- Synonyms
- Views
User Access Control
- Creating users to access the database
- Learning about the Schema
- Knowing about the System Privileges
- Granting Object Privileges
- Roles
Regular Expressions Functions and Conditions
- Using Regular Expression in SQL
- Perform a Basic Search using the REGEXP_LIKE function
- Find patterns using the REGEXP_INSTR function
- Types of Conditions
- Conditions Precedence
Course Duration
