Introduction to Oracle: SQL

This is a online course is to gain fundamental understanding of Oracle SQL . The aim is to learn about Oracle SQL and its usage. The tutorials will help you learn about DBMS ER Model Normalization, Structured Query Language, Restricting Sorting Data, Single and Multiple Row Functions, Subqueries, Manipulating Data, Creating Managing Data, Oracle Date Time Functions, Pseudocolumn, Constraints, Sequences, Views, Indexes and Controlling User Access.

339

 14h 7m | 80 Videos | 88505 Views |   All Levels

What is Oracle SQL

SQL stands for Structured Query Language and it is a set of statements through which all programs and users access data in an oracle database. Users are often allowed to access the database without using SQL but these applications use SQL when processing the users request.

What SQL can do

SQL can perform the following functions

• Execute queries against a database
• Retrieve data from a database
• Insert records in a database
• Update and delete records in a database
• Create new databases
• Create new tables and stored procedures in a database
• Create views in a database
• Set permissions in tables, procedures and views

Course Objectives

At the end of this course you will be able to

• Understand the SQL queries
• Know more about the components of SQL
• Learn about the manipulation of data based on conditions
• Use Oracle supplied packages
• Use standard aggregate functions and SELECT statement in SQL
• Create sub programs, stored procedures and functions in SQL
• Use oracle system functions
• Know about tables and joins

Pre requisites for taking this course

Person who is taking this course should have some basic knowledge in relational databases and database concepts. They should also have a licensed copy of oracle to use it during the course. Other than this basic knowledge of computer and operating system is also necessary.

Target Audience for this course

The target audience for this course includes

• Programmer
• Database developer
• SQL developer
• DBA
• Web developer
• SQL report writer
• Data Analyst
• Data warehouse engineer
• And any other person who wanted to start a career in database in Oracle.

Course Description

Section 1: Introduction

SQL is used to create, transform and retrieve information from relational database software. It is a powerful language to interact with database. It was developed in the year 1970 by IBM. Most of the RDBMS use SQL as a database querying language. This chapter gives you an overall introduction to SQL, its history and why it is important. It also covers the goals and objective of this course.

Section 2: DBMS ER Model Normalization

File System DBMS

Before computers were invented filing system was too difficult to handle. It was done manually but now all these jobs became easy. Papers became files in computers. Files are grouped based on their categories and each file is given a related name. Programs like C, C++, COBOL all will help to search a file or open them and do other operations using file. This chapter contains the advantages and disadvantages using file processing system.

DBMS

A database management system is a collection of programs which is written to manage a database. It acts as a bridge between the user and database. A database management system which is based on relational data model is known as Relational Database Management System. This section contains the following topics

• Advantages of DBMS
• Disadvantages of DBMS

DBMS Object Oriented Model

Object based data model involves only entities, attributes and the mapping. There are two types of object based data model – Entity relationship model and Object oriented model. Object oriented model represents real world objects. This model is explained in detail in this chapter with example.

ER Model

ER model represents the conceptual view of a database. It deals with real world entities and the relationships among them. It is used for designing databases. This section covers the different type of attributes of ER model and Entity set and keys.

Normalization

Normalization is a set of rules that are used while designing a database. These rules helps in equal distribution of data among the different tables more efficiently and effectively. It is divided into six normal forms which are explained in this section

• First Normal Form
• Second Normal Form
• Third Normal Form
• Boyce-Codd Normal Form
• Forth Normal Form
• Fifth Normal Form

Section 3: Structured Query Language

Introduction and Components of SQL

This chapter gives a detailed introduction to SQL and a brief overview of the components of the SQL.

SQL SELECT Statement Arithmetic Operations Null Value

SQL is followed by a unique set of rules or guidelines which are called syntax. All the SQL statements starts with SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW.

The SELECT statement is used to select data from a database. This section explains the syntax and example of SELECT statement.

Null Value Components of SQL Statements

Null value represents missing unknown data. A table column by default holds a null value. This chapter explains the syntax, null value functions in SQL, IS NULL and IS NOT NULL operators, working with Null values and examples.

Components of SQL Statements DDL

DDL stands for Data Definition Language. CREATE, ALTER and DROP commands are used in this section.

Components of SQL Statements DML

DML stands for Data Manipulation Language. There are three SQL commands used under this – INSERT, UPDATE and DELETE.

Components of SQL Statements DCL

DCL stands for Data Control Language. Two SQL commands comes under this section – GRANT and REVOKE.

Section 4: Restricting Sorting Data

Where Clause Logical Operators

The Where clause is used to get those data which satisfies a given criteria. A logical operators are used to combine the result of two component conditions to give a single result or to invert the result of a single condition. The logical operators are AND, NOT and OR. The syntax and examples of where clause and logical operators are included in this section.

Case Insensitive Character Functions

The character functions like Char, Varchar, Varchar (max) and Text are explained in detail in this chapter.

Relational Operators

Relational operators is a comparison of two values and returns a boolean result. The different type of relational operators are given with its description and example under this chapter.

Column Alias

Column alias are used to rename the table’s columns for the purpose of a particular SQL query. This section contains the Syntax, usage and example of column alias.

Between in Sorting

ORDER BY clause in SELECT statement is used to sort the query result. Examples of sorting are given under this chapter.

Section 5: Single Row Functions

Single Row Functions SQL Functions

Single row functions return a single row result for every row of a queried table.

The single row miscellaneous functions explained in this chapter are COALESCE, DECODE, GREATEST, LEAST, NVL, NVL2, NULLIF, UID, USER, SYS_CONTEXT and VSIZE.

Character Functions Character Manipulation Function

Character function can be used on values of data type CHAR or VARCHAR. The character functions mentioned in this chapter are LOWER, UPPER, INITCAP, LENGTH, SUBSTR, INSTR, REPLACE, TRANSLATE, SOUNDEX, RPAD, LPAD, TRIM, LTRIM, RTRIM and CONCAT. All these functions are explained with an example.

Number Functions ROUND TRUNCATE CEIL POWER ABS DATE Functions

Number functions are also known as Math functions. Number functions accept numeric input and returns numeric values. The number functions available in oracle are explained in brief under this section. They are ABS, ACOS, CIEL, FLOOR, COS, SIN, TAN, MOD, POWER, EXP, LN, LOG, ROUND, TRUNC and SQRT.

Conversion Functions IMPLICIT EXPLICIT

Conversion functions are used to convert the unexpected data type to a compatible data type. It can be done either implicitly by oracle or explicitly by the programmer. The topics under this section are

• Implicit data type conversion
• Explicit data type conversion
• TO_CHAR function
• TO_NUMBER function
• TO_DATE function
• General functions
• COALESCE
• Conditional Functions

Section 6: Multiple Row Functions

Aggregate functions return a single value based on groups of rows. Aggregate functions are used with the GROUP BY clause in a SELECT statement. The important functions explained in this chapter are AVG, SUM, MIN, MAX, COUNT, STDDEV and VARIANCE

Section 7: SQL Joins

Join is a query which combines rows from two or more tables or views. It is used to combine fields from two or more tables using values that are common to each other. Most join queries uses WHERE clause condition which joins two columns each from a different table. This is called a join condition. This chapter contains the following sections

• Equi Joins
• Non Equi Joins
• Self Join
• Inner Join
• Outer Joins

Section 8: Subqueries

A query which is nested within a query is known as subquery. Subquery is used to return data that will be used in the main query to restrict the data to be retrieved. There are few guidelines for the Subqueries to be followed. Subqueries are mostly used with the SELECT statement. This chapter also contains the following topics

• Subqueries with the INSERT statement
• Subqueries with the UPDATE statement
• Subqueries with the DELETE statement

Section 9: Manipulating Data

Data Manipulation language statements helps to manipulate data in an existing schema objects. The DML statements in Oracle are INSERT, UPDATE, DELETE and MERGE. These statements are explained in detail with examples in this tutorial.

Transaction Control Language (TCL)

These statements manage the changes made by DML statements. Transaction is a set of SQL statements which are considered as a single unit. The TCL statements in oracle are

• COMMIT – Makes permanent changes in the transaction
• ROLLBACK – Restores the state of the database to the last commit point
• SAVEPOINT – This is a point in a transaction to which you can refer later

Section 10: Creating Managing Data

This section explains the creation of partition tables and the different type of partitioning

• Range partitioning
• Hash partitioning
• List partitioning
• Composite partitioning

The DEFAULT Option

This section explains how the DEFAULT value is specified for a column in a table

Advance Data type in Oracle Creating a Table using a subquery

A data type combines a fixed set of properties with the values in a column of a table. There are four types of data type in Oracle – Character data type, Numeric data type, Time and Date data type and Binary data type. These data types are explained in brief under this section.

Modify an existing Column SET UNUSED Option

This option helps to hide columns from a large table and it can be used later. The statement is explained with an example.

Dropping a Table Changing Name TRUNCATING

Truncating will delete all rows from the partition. Under this chapter you will learn how to use the Modify partition DROP VALUES clause of the ALTER TABLE statement with its syntax and example.

Section 11: Oracle Date Time Functions

To view the system date and time the following functions are used in SQL

• CURRENT_DATE
• SYSDATE
• SYSTIMESTAMP

The date format models are also given here

Section 12: Pseudocolumn

A Pseudocolumn is more like a table column but it is not stored in the table. You cannot make changes to the Pseudocolumn values. This is also similar to a function without arguments. There are different types of Pseudocolumn which are mentioned in this chapter.

Section 13: Constraints

Constraints are the rules which are enforced on data columns on table. This is used to limit the data type that can be inserted into a table. Through this way the reliability and accuracy of the data can be maintained. There are different types of constraints used in SQL. They are

• NOT NULL
• DEFAULT
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• INDEX

These constraints are explained with its syntax and example for each.

Section 14: Sequences

Sequence is used to create numbers in sequences. Sequences can be used to insert unique values in Primary key and Unique Key columns of tables. CREATE SEQUENCE statement is used to use the sequence in SQL.

NEXTVAL Pseudocolumn

NEXTVAL is used to give the next number in sequence. This is explained with its syntax and example.

Section 15: Views

Views are logical tables which represents the data of one or more tables. Views can be considered as any other table but it does not require much space like a table. The topics included in this chapter are

• Creating views
• Creating force views
• Altering views
• Referencing Invalid views
• Dropping views
• Modifying, inserting and deleting a Join view

Section 16: Indexes

Indexes are used to speed up queries in SQL. Indexes are useful to return a part of the data. There are few guidelines for creating indexes which are mentioned in this section. It also includes the following topics

• How to create indexes
• Bit map indexes
• Function based indexes
• Dropping indexes

Section 17: Controlling User Access

The Data Control Language statements in SQL are used to provide privileges to the users. The DCL statements of SQL are GRANT and REVOKE. The privileges are two types System and Object privileges. These are explained in this chapter with examples.

System Privileges

System privileges are granted to users and types of system privileges are CREATE SESSION, CREATE TABLE, CREATE USER and others.

Section 18: Synonym

A synonym is an alias of a table, view, snapshot or function. The two types of synonym are PUBLIC and PRIVATE. This chapter includes details about how to create and drop synonyms in SQL.

Section 19: Summary of SQL Course

This section is a conclusion to the course which gives a quick recap of all the important topics of SQL.

FAQ’s General Questions

• Why Oracle SQL training is important ?

Oracle is used by large enterprise application. This oracle SQL training gives you practical experience is writing SQL scripts and statements using Oracle. This course covers all the fundamentals of Oracle SQL. Oracle professionals have better job opportunity in database developer, DBA, web developer and oracle client server application development. After completing this course you will be able to learn any software easily.

Testimonials

Davidson

This is a great course on Oracle SQL. It was a great experience learning this course. It helped me to gain more knowledge about SQL and about the basic concepts of SQL. Even the complex topics were handled in a elegant way. The contents are well structured which are self explanatory. A recommended course to anyone who is looking out for SQL course. Thanks to educba. Looking forward to more courses.

Back to top ^