Oracle Database For Beginners
- Descrição
- Currículo
- FAQ
- Revisões
Learn how to use Oracle Database to store, manipulate and query data. It takes you from fundamentals of databases to creating program units to implement business logic and rules using PL/SQL.
The following are major topics covered:
-
What is DBMS and RDBMS
-
How to get started with Oracle Database 18c XE and Oracle SQL Developer
-
How to use SQL to retrieve, manipulate and create data
-
How to group data using GROUP BY and HAVING clauses
-
How to use different types of joins to get data from multiple tables
-
How to use subquery and correlated subquery to write complex queries
-
What is a view, advantages of view and how to manipulate base table through view
-
What is index and how index improves performance
-
Implementing object-level security using object privileges
-
How to create and use regular expressions
-
Writing PL/SQL blocks to process data
-
Various control structures in PL/SQL
-
Handling exceptions
-
Explicit and Implicit cursors
-
Creating procedures and functions to implement business logic
-
Creating database triggers to implement business rules
-
Dynamic SQL and collections
-
1
Introduction To CourseTopics covered:
What is covered in this course
Prerequisites for this course
Software used
Structure of the course
-
2
Introduction To DBMS and Oracle Database
Topics covered:
What is DBMS?
Different database models
Relational model terminology
Oracle Database architecture
-
3
Installing Oracle Database 18c Express Edition (XE) on Windows
Topics covered:
How to download and install Oracle Database 18c XE
How to unlock HR account in Pluggable Database (PDB)
-
4
Installing SQL Developer and connecting to Oracle Database
Topics covered:
How to download and install Oracle SQL Developer
How to create a connection to HR account in PDB
How to use Worksheet to write commands
-
5
SELECT Command
Topics covered:
How to use SELECT command to retrieve data from tables
How to uses ORDER BY and WHERE clauses
-
6
Test Your Knowledge about SELECT Command -
7
Manipulating Data using DML commands
Topics covered:
How to use DML commands like UPDATE, DELETE and INSERT.
Understand what is a transaction and how to manage it.
-
8
Test Your Knowledge about DML Commands
-
9
Number Functions and Date Functions
Topics covered:
Number functions
Date data type
Date arithmetic
Date functions
-
10
Test Your Knowledge about Number Functions and Date Functions
-
11
String Functions
Topics covered:
String functions
-
12
Test Your Knowledge about String Functions
-
13
Conversion Functions and Miscellaneous Functions
Topics covered:
Automatic type conversion
Conversion functions
Miscellaneous functions
-
14
Test Your Knowledge about Conversion and Miscellaneous Functions
-
15
Creating Tables
Topics covered:
How to create a table using create table command
Column and table constraints
Getting information about user tables
-
16
Test Your Knowledge about Creating Tables
-
17
Altering structure of table and dropping table
Topics covered:
Adding a new column to existing table
Modifying existing column attributes
Dropping a constraint or a column
Dropping table using drop table command
-
18
Test Your Knowledge about Altering and dropping table
-
19
Grouping Data using Group By and Group Functions
Topics covered:
Group functions
Group by clause to group rows
Having clause to filter groups
-
20
Test Your Knowledge about Group By and Group Functions
-
21
Joining Tables
Topics covered:
How to join tables using NATURAL JOIN
Joining USING clause
Joining using ON clause
Joining more than two tables
Self join
Outer join
-
22
Test Your Knowledge about Joining Tables
-
23
Subqueries
Topics covered:
What is subquery and how to use it
Multiple and nested subqueries
Using subqueries in DML commands
Correlated subqueries
Correlated subqueries in DML commands
-
24
Test Your Knowledge about Subqueries
-
25
Views
Topics covered:
What is a view and its advantages
How to create a view and use it
Modifying base table through View
Using WITH CHECK OPTION
What is Materialized view and its advantages
-
26
Test Your Knowledge about Views
-
27
Indexes and Sequences
Topics covered:
What is Index and its importance
How to create an index
When Oracle does not use index
How to create a sequence and use it
-
28
Test Your Knowledge about Indexes and Sequences
-
29
Security - Granting and Revoking Privileges
Topics covered:
Difference between System privileges and Object privileges
Different types of object privileges
Using GRANT and REVOKE commands
What is a ROLE and its usage
-
30
Test Your Knowledge about Security
-
31
Regular Expressions
Topics covered:
What is a regular expression
Meta characters used in regular expressions
Functions related to regular expression like REGEXP_LIKE
-
32
Test Your Knowledge about Regular Expressions
-
33
Introduction to PL/SQL
Topics covered:
What is PL/SQL
Structure of PL/SQL blocks
How to use INTO options with SELECT command in PL/SQL
-
34
Test Your Knowledge about PL/SQL
-
35
Control Structures in PL/SQL
Topics covered:
How to use IF statements and its variants
Using CASE expression and CASE statement
-
36
Test Your Knowledge about Control Structures in PL/SQL
-
37
Looping Structures
Topics covered:
How to use LOOP and ENDLOOP
WHILE Loop
FOR loop
EXIT and Continue statements
-
38
Test Your Knowledge about Looping Structures
-
39
Exception Handling
Topics covered:
What is an exception and how to handle it
SQLCODE and SQLERRM functions
Nested blocks
User-defined exception
Exception propagation
Re-raising exceptions
-
40
Test Your Knowledge about Exception Handling
-
41
Cursor Handling
Topics covered:
What is explicit cursor and how to use it
Cursor for loop
Attributes of explicit cursor
Using input arguments with cursor
Use of implicit cursor
-
42
Test Your Knowledge about Cursor Handling
-
43
Program Units - Part 1
Topics covered:
What is a stored procedure and its advantages
Creating and calling stored procedures
Creating and calling stored functions
How to get errors related to program unit's creation
How to access source code of program units
EXECUTE privilege
-
44
Test Your Knowledge about Program Units - Part 1
-
45
Programs Units - Part 2
Topics covered:
Parameter modes - in, out, in out
Parameter notations - position, named and mixed
Invoker rights
What is a package and how to create its specification and body
Calling members of package
Oracle supplied packages
-
46
Test Your Knowledge about Program Units - Part 2
-
47
Database Triggers - Part 1
Topics covered:
What is a trigger and its usages
Execution flow of triggers
How to create row-level trigger to implement business rules
Using correlation names NEW and OLD
How to create statement-level triggers
-
48
Test Your Knowledge about Database Triggers - Part 1
-
49
Database Triggers - Part 2
Topics covered:
How to use instead-of triggers
Compound trigger and its usage
How to enable and disable triggers
-
50
Test Your Knowledge about Database Triggers - Part 2
-
51
Collections and Dynamic SQL
Topics covered:
What is index-by table and how to use it
What is dynamic sql and its usage
Executing non-queries using dynamic sql
Executing non-query using parameters with dynamic sql
Executing single row query with dynamic sql
Executing multi-row query with cursor in dynamic sql
-
52
Test Your Knowledge about Collections and Dynamic SQL
