DBMS
- 20 students
- 100 lessons
- 60 day duration
COURSE DESCRIPTION
This course introduces students to the fundamentals of SQL using Oracle Database 11g database technology. In this course, students learn the concepts of relational databases and the powerful SQL programming language. This course provides the essential SQL skills that allow developers to write queries against single and multiple tables, manipulate data in tables, and create database objects.
The students also learn to use single-row functions to customize the output, use conversion functions and conditional expressions, and use group functions to report aggregated data. Demonstrations and hands-on practice reinforce the fundamental concepts.
In this course, students use Oracle SQL Developer as the main tool, and SQL *Plus is available as an optional tool.
LEARN TO:
- Retrieve row and column data from tables with the SELECT statement
- Create reports of sorted and restricted data
- Display data from multiple tables.
- Use DML statements to manage data.
- Use DDL statements to manage database objects
AUDIENCE:
- Developer
- End-User
- Implementer
OBJECTIVES:
- Employ SQL functions to generate and retrieve customized data
- Display data from multiple tables using the ANSI SQL 99 JOIN syntax
- Create reports of aggregated data
- Use the SET operators to create subsets of data
- Run data manipulation statements (DML) to update data in the Oracle Database 11g
- Run data definition language (DDL) statements to create and manage schema objects
- Identify the major structural components of the Oracle Database 11g
- Retrieve row and column data from tables with the SELECT statement
- Create reports of sorted and restricted data
-
Introduction
- Oracle Application Express
- Relational Database Technology
- Anatomy of a SQL Statement
-
SELECT and WHERE
- Columns, Characters, and Rows
- Limit Rows Selected
- Comparison Operators
-
WHERE, ORDER BY, and Intro to Functions
- Logical Comparisons and Precedence Rules
- Sorting Rows
- Introduction to Functions
-
Single Row Functions Part I
- Case and Character Manipulation
- Number Functions
- Date Functions
-
Single Row Functions Part II
- Conversion Functions
- NULL Functions
- Conditional Expressions
-
JOINs
- Cross Joins and Natural Joins
- Join Clauses
- Inner versus Outer Joins
- Self-Joins and Hierarchical Queries
- Oracle Equijoin and Cartesian Product
- Oracle Nonequijoins and Outer Joins
-
Group Functions
- Group Functions
- Oracle Nonequijoins and Outer Joins
- Using Group By and Having Clauses
- Using Rollup and Cube Operations, and Grouping Sets
- Using Set Operators
-
Subqueries
- Fundamentals of Subqueries
- Single-Row Subqueries
- Multiple-Row Subqueries
- Correlated Subqueries
-
DML
- INSERT Statements
- Updating Column Values and Deleting Rows
- DEFAULT Values, MERGE, and Multi-Table Inserts
-
DDL
- Creating Tables
- Using Data Types
- Modifying a Table
-
Constraints
- Intro to Constraints; NOT NULL and UNIQUE Constraints
- PRIMARY KEY, FOREIGN KEY, and CHECK Constraints
- Managing Constraints
-
Views
- Creating Views
- DML Operations and Views
- Managing Views
-
Sequences and Synonyms
- Working With Sequences
- Indexes and Synonyms
-
Privileges and Regular Expressions
- Controlling User Access
- Creating and Revoking Object Privileges
- Regular Expressions
-
TCL
- Database Transactions
-
Fundamentals of PL/SQL
- Introduction to PL/SQL
- Benefits of PL/SQL
- Creating PL/SQL Blocks
-
Defining Variables and Datatypes
- Using Variables in PL/SQL
- Recognizing PL/SQL Lexical Units
- Recognizing Data Types
- Using Scalar Data Types
- Writing PL/SQL Executable Statements
- Nested Blocks and Variable Scope
- Good Programming Practices
-
Using SQL in PL/SQL
- Review of SQL DML
- Retrieving Data in PL/SQL
- Manipulating Data in PL/SQL
- Using Transaction Control Statements
-
Program Structures to Control Execution Flow
- Conditional Control: IF Statements
- Conditional Control: CASE Statements
- Iterative Control: Basic Loops
- Iterative Control: WHILE and FOR Loops
- Iterative Control: Nested Loops
-
Using Cursors and Parameters
- Introduction to Explicit Cursors
- Using Explicit Cursor Attributes
- Cursor FOR Loops
- Cursors with Parameters
- Using Cursors for UPDATE
- Using Multiple Cursors
-
Using Composite Datatypes
- User-Defined Records
- Indexing Tables of Records
-
Exception Handling
- Handling Exceptions
- Trapping Oracle Server Exceptions
- Trapping User-Defined Exceptions
- Recognizing the Scope of Exceptions
-
Using and Managing Procedures
- Creating Procedures
- Using Parameters in Procedures
- Passing Parameters
-
Using and Managing Functions
- Creating Functions
- Using Functions in SQL Statements
- Review of the Data Dictionary
- Managing Procedures and Functions
- Review of Object Privileges
- Using Invoker’s Rights and Autonomous Transactions
-
Using and Managing Packages
- Creating Packages
- Managing Package Concepts
- Advanced Package Concepts
-
Getting the Best out of Packages
- Persistent State of Package Variables
- Using Oracle-Supplied Packages
-
Improving PL/SQL Performance
- Using Dynamic SQL
- Improving PL/SQL Performance
-
Using and Managing Triggers
- Creating DML Triggers, Part I
- Introduction To Triggers
- Creating DML Triggers, Part II
- Creating DDL and Database Event Triggers
- Managing Triggers
OUR METHODOLOGY
-
- 70% practical based and 30% theory-based.
- Version Control System like GIT (Git Hub) right from the beginnings, assignments/hands-on projects in-between the course to enhance the practical knowledge.
- Testing Framework like Unittest is a part of course to write TestCases for the application.
- Complete description contents in the form of PDF/PPT for every topic.
- Homework, assignments, and projects to teach the practical implementation of every theoretical topic.
- Practical sessions for every single topic so that students can understand how to manage applications using the version control systems and how to test and develop applications in real life for IT Industries.
