Oracle SQL Tutorial for Beginners: Ace Your Data Tasks Easily

Numerous big businesses have made significant investments in Oracle databases and apps, which power their vital systems (such as CRMs and ERPs). Skilled Oracle specialists are needed for continuous maintenance, support, and optimization of these systems. This Oracle SQL for beginners will help you learn the fundamentals. Get Started with our Oracle SQL Course Syllabus.

What is SQL?

A common language for database access and manipulation is called SQL (Structured Query Language). It’s the main method of interacting with the Oracle Database in the context of Oracle. Because it’s a declarative language, you can declare your goals without necessarily mentioning how to get there. The Oracle database determines the most effective method to complete the task. You will learn the Oracle SQL Basics here.

Core Building Blocks of Oracle SQL

Let’s examine the fundamental components in this Oracle SQL tutorial for beginners. The essential components you’ll need to work with and modify data in an Oracle database are as follows:

Clauses: SQL statements can include clauses that define conditions, grouping, ordering, and other requirements for data retrieval and processing. Typical clauses include the following:

• WHERE: Uses predefined criteria to filter rows.
• ORDER BY: Sorts the result set either descendingly or ascendingly according to one or more columns.
• GROUP BY: Creates a summary row by combining rows with the same values in one or more columns. Aggregate functions are frequently utilized.
• HAVING: Uses predefined criteria to filter a GROUP BY clause’s output.
• JOIN: Using a relevant column, joins rows from two or more tables. There are various kinds of joins, including FULL OUTER JOIN, LEFT JOIN, RIGHT JOIN, and INNER JOIN.
• DISTINCT: Only distinct rows from the result set are returned.
• **LIMIT“ /FETCH FIRST (Oracle Specific):** Limits how many rows a query can return. Oracle utilizes ROWNUM~ or FETCH FIRST n ROWS ONLY.

Expressions (The Calculations): Columns, literals (constant values), operators, and functions that evaluate to a single value are all combined to form expressions.

• Arithmetic Operators: +, -, *, /
• Comparison Operators: =, >, <, >=, <=, != or <>, LIKE, BETWEEN, IN, IS NULL
• Logical Operators: AND, OR, NOT
• Functions: Oracle offers numerous built-in functions for a variety of uses:
  • Scalar Functions: Such as UPPER(), LOWER(), SUBSTR(), LENGTH(), ROUND(), TRUNC(), and SYSDATE, operate on a single row and return a single result.
  • Aggregate Functions: Such as COUNT(), SUM(), AVG(), MIN(), and MAX(), operate on a collection of rows and return a single summary result.
  • Analytical Functions: Use functions like ROW_NUMBER(), RANK(), LAG(), LEAD(), and SUM() OVER() to do computations across a group of table rows that are connected to the current row.

Database Objects: These are the named structures used to store and arrange data in the Oracle database.

• Tables: They are the basic building blocks for data storage; they are arranged in rows and columns, much like a spreadsheet.
  • Columns: Show properties or features of the data in a table, such as department, age, and employee name. A distinct data type (such as NUMBER, VARCHAR2, or DATE) is assigned to each column.
  • Rows: Individual records or instances of the data in a table are represented by rows.
• Views: Virtual tables created using a SQL statement’s output. They offer a streamlined or personalized view of the underlying information.
• Indexes: Structures known as indexes let tables retrieve data more quickly.
• Sequences: Things that produce consecutive numbers are called sequences.
• Synonyms: Alternative names for database objects are called synonyms.
• Procedures and Functions (PL/SQL): Blocks of stored procedural code that can be run inside the database. Even though PL/SQL is an extension of SQL, it’s crucial to comprehend how the two languages interact.
• Packages (PL/SQL): Groups of related variables, functions, procedures, and other PL/SQL constructs.

Data Types: Specify the types of information that can be kept in a column. Typical Oracle data types consist of:

• Character Data Types:
  • VARCHAR2(size): Character strings of variable length are denoted by it.
  • NUMBER(precision, scale): Numerical data that can have scale (number of digits to the right of the decimal point) and precision (total number of digits) applied.
  • CLOB (Character Large Objects): They are used to store big character data blocks.
• Numeric Data Types:

• DATE: Keep track of the time and date.
• NUMBER(precision, scale): Holds both fixed and floating-point numbers with the provided scale (number of digits to the right of the decimal point) and precision (total number of digits).
• INTEGER or INT: Whole numbers are stored in an INTEGER or INT.
• FLOAT: Approximate floating-point numbers are stored in it.
• TIMESTAMP: Uses fractional seconds to store the date and time.
• TIMESTAMP WITH TIME ZONE: Date, time, and time zone data are stored.

• Binary Data Types:
  • BLOB (Binary huge Objects): They are used to store huge binary data, such multimedia or pictures.
  • RAW(size): It stores binary data of a fixed length.
• Other Data Types: ROWID, XMLType, JSON, and more data types are supported by Oracle.

Schema Objects: Database elements such as tables, views, indexes, procedures, and more are referred to as schema objects.

Writing successful and efficient Oracle SQL queries and meaningfully engaging with Oracle databases require an understanding of these fundamental building blocks.

Recommended: Oracle SQL Online Course Program.

Core SQL Operations

SQL Statements (The Actions): You can interact with the Oracle database using these instructions. They instruct the database on your desired action. The following are the main types of SQL statements:

• Data Manipulation Language (DML): The statements in the DML are used to control and modify the data in tables.
  • Select: SELECT column1, column2 FROM table_name WHERE condition;
  • Insert: INSERT INTO table_name(column1, column2) VALUES (value1, value2);
  • Update: UPDATE table_name SET column1 = new_value WHERE condition;
  • Delete: DELETE FROM table_name WHERE condition;
  • Merge: MERGE INTO target_table t 
• Data Definition Language (DDL): The structure of the database objects (tables, indexes, views, etc.) is defined and managed using statements in the DDL.
  • Create: 

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50)

);

• Alter: ALTER TABLE employees ADD (email VARCHAR2(100));
• Drop: DROP TABLE employees;
• Truncate: TRUNCATE TABLE employees;
• Rename: RENAME employees TO staff;

• Data Control Language (DCL): Statements in the DCL regulate who has access to the database and its contents.
  • GRANT: GRANT SELECT ON employees TO user1;
  • Revoke: REVOKE SELECT ON employees FROM user1;
• Transaction Control Language (TCL): These statements control transactions, which are logical work units.
  • Commit: 

INSERT INTO departments (department_id, department_name) VALUES (10, ‘Finance’);

COMMIT;

• Rollback:

UPDATE employees SET salary = salary * 1.10 WHERE department_id = 10;

ROLLBACK; — Undoes the salary update

• SavePoint:

SAVEPOINT update1;

UPDATE employees SET salary = salary * 1.05;

— … more operations …

ROLLBACK TO SAVEPOINT update1; — Undoes changes after update1

Key Concepts in Data Retrieval (SELECT Statements)

Here are some important Oracle SQL concepts in data retrieval:

SELECT Clause: The columns you wish to get are specified by the SELECT clause. You can list particular column names or use * to pick all columns.

FROM Clause: It specifies which table or tables the data should be retrieved from.

WHERE Clause: It filters the data according to predetermined criteria. You employ logical operators (AND, OR, NOT), comparison operators (=, >, <, >=, <=,!=, <>), and other operators (BETWEEN, IN, LIKE, IS NULL).

ORDER BY Clause: Sorting the result set according to one or more columns (descending DESC or ascending ASC) is done.

GROUP BY Clause: It creates a summary row by combining rows with the same values in one or more columns. Aggregate functions are frequently utilized.

Aggregate Functions: Such as COUNT, SUM, AVG, MIN, and MAX, compute a single value from a collection of rows.

HAVING Clause: It filters a GROUP BY clause’s output according to predetermined criteria.

Joins: Using a relevant column, combine rows from two or more tables. Various kinds of joins consist include:

• INNER JOIN: Only when there is a match in both tables does an INNER JOIN return rows.
• LEFT JOIN (LEFT OUTER JOIN): All rows from the left table and matching rows from the right table are returned by an LEFT JOIN. It returns NULL for the columns in the right table if there is no match.
• RIGHT JOIN (or RIGHT OUTER JOIN): All rows from the right table and matching rows from the left table are returned by a right join, also known as a right outer join. It returns NULL for the columns in the left table if there is no match.
• FULL OUTER JOIN: When there is a match in either the left or right table, it returns every row. It returns NULL for the non-matching table’s columns if there isn’t a match. 

Recommended: Oracle DBA Course in Chennai.

Constraints in Oracle SQL

To save data integrity, rules are applied to data columns. Typical limitations consist of:

• NOT NULL: A column cannot have a null value if it is marked as NOT NULL.
• UNIQUE: It ensures that each value in a column is unique.
• PRIMARY KEY: Each row in a table is uniquely identified by its primary key. 
• NOT NULL and UNIQUE: It combines the terms NOT NULL and UNIQUE. 
• FOREIGN KEY: It creates a connection between two tables. It makes reference to another table’s main key.
• CHECK: Enforces a particular condition to a column’s values.

Views in Oracle SQL

In Oracle SQL, views are similar to having a virtual table based on a SQL statement’s outcome. A view simply saves the query, as opposed to literally storing the data as a table would. Oracle executes the underlying query when you query a view and shows you the results. 

Why Use Views?

• Simplifying Complicated Queries: Let’s say you often execute a complex join that involves multiple tables and filtering constraints.
• Data Security: By using a view, you can allow users to access particular rows or columns without granting them access to the foundation tables that underlie them. 
• Data Independence: You can frequently update the view to keep your applications’ interfaces the same even if the structure of your underlying tables changes (for example, by renaming a column), which lessens the impact of the changes.
• Data Consistency: Despite the structural differences between the underlying tables, views can display data in a consistent manner. 

How to Create a View:

To define a view, use the CREATE VIEW statement. The basic syntax is as follows:

CREATE [OR REPLACE] VIEW view_name

AS

SELECT column1, column2, …

FROM table1

WHERE condition;

CREATE VIEW view_name: The fundamental command to build a new view with the given name.

[OR REPLACE]: You can change an existing view with this optional clause. It will be replaced if there is already a view with the same name.

AS SELECT…: The query that the view is based on is defined here. Any legitimate SELECT statement, including subqueries, aggregations, and joins, may be used.

Example:

CREATE VIEW employee_department_view

AS

SELECT e.first_name || ‘ ‘ || e.last_name AS full_name,

       d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id;

Types of Views:

Here are the two types of views:

• Simple Views: They don’t have functions, group by clauses, or link by clauses; instead, they are built on a single base table. With a few limitations, they are usually updatable (you can use DML operations like INSERT, UPDATE, and DELETE on them).
• Complex Views: These have functions, group by clauses, or connect by clauses, and are based on several tables. In most cases, they cannot be directly updated.

Suggested: Oracle PL/SQL Course in Chennai.

Indexes in Oracle SQL

An optional structure connected to a table (and occasionally a cluster) is called an index. Each value in the indexed column or columns has an entry in it, along with a pointer to the row or rows that contain that value. Oracle can save disk I/O and increase query speed by employing an index to directly access the rows that meet a query’s criterion.

Why Use Indexes?

• Better Query Performance: This is the main justification for index use. Indexes can significantly speed up the execution of queries that regularly filter or sort data according to specific columns.
• Enforcing Uniqueness: To guarantee that the values in a certain column or group of columns are distinct across all table rows, you can construct a unique index. Although an index is used in its implementation, this is comparable to a unique constraint.

Types of Indexes in Oracle SQL

Oracle SQL offers various types of indexes to implement in different needs:

• B-tree Indexes: The most used index type in Oracle is the B-tree index, which is also the default type when you build an index without choosing a type.
• Bitmap indexes: These work well for columns with little cardiacity, like marital status or gender.
• Function-Based Indexes: The result of a function or expression involving one or more columns is the basis for function-based indexing.
• Reverse Key Indexes: When heavy insert operations are performed in a typical B-tree index, contention may arise in index blocks if the indexed column contains sequential data.
• Partitioned Indexes: For highly big tables, partitioning indexes can enhance query performance and manageability.
• Index-Organized Tables (IOTs): Although theoretically a form of table storage, they store the data rows according to the primary key in a B-tree index structure. 

How to Create an Index:

To create an index, use the CREATE INDEX statement. The basic syntax is as follows:

CREATE [UNIQUE] INDEX index_name

ON table_name (column1 [, column2, …])

[TABLESPACE tablespace_name]

[BITMAP]

[REVERSE]

[FUNCTION BASED INDEX expression];

• CREATE INDEX index_name: With the given name, a new index is created. Selecting a name (such as idx_employees_last_name) that accurately represents the table and column(s) being indexed is best practice.
• [UNIQUE]: By creating a unique index, this optional term ensures that the values in the indexed column or columns are distinct.
• ON table_name (column1 [, column2, …]): The table and the column or columns to be included in the index are specified by it. 
• [TABLESPACE tablespace_name]: It indicates which tablespace will house the index. Keeping indexes in a different tablespace from the table data is frequently a smart idea.
• [BITMAP]: Indicates the creation of a bitmap index.
• [REVERSE]: Indicates the creation of a reverse key index.
• [FUNCTION BASED INDEX expression]: It identifies the expression or function that the index should be created on.

Examples:

Creating a B-tree index on the employees table’s last_name column:

CREATE INDEX idx_employees_last_name

ON employees (last_name);

Creating a special index for the employees table’s email column:

CREATE UNIQUE INDEX idx_employees_email

ON employees (email);

Creating a composite index using the salary and department_id columns:

CREATE INDEX idx_employees_dept_salary

ON employees (department_id, salary);

Creating a function-based index on the last_name in uppercase:

CREATE INDEX idx_employees_upper_lname

ON employees (UPPER(last_name));

An essential component of Oracle SQL query performance optimization is the use of indexes. Effective database architecture and application development depend on knowing the various index types and when to utilize them. 

Explore all software training courses available at SLA.

Conclusion

Oracle sql basics for beginners are covered in this Oracle SQL tutorial. Subqueries, functions (both built-in and user-defined), stored procedures, triggers, and other more complex subjects will come up as you go further. We hope this Oracle SQL Tutorial for Beginners is helpful for you. Learn everything with practical experiences in our Oracle SQL training in Chennai.