Introduction

A query is not a programming language such as python,c,c++, java etc although it is a type of language used in Oracle databases to retrieve data from tables, manipulate data (insert records into database tables, update the values of column/columns of a particular table, and delete records from database tables), and is also known as SQL (structured query language) first it was called as Structured English Query Language (SEQUEL) to fulfil Codd’s concept. SEQUEL was later renamed SQL (still pronounced “sequel”). SQL is now widely acknowledged as the industry-standard RDBMS language.

How to write Oracle Queries

Writing an oracle query is easy once you get to know when and where to use reserved keywords. Oracle has a list of reserved keywords that are used to perform basic operations such as insert, manipulate, update and display data from the table. there are so many reserved keywords such as SELECT, INSERT, UPDATE, DELETE, TRUNCATE, DROP, CASCADE, ASC, DESC, DISTINCT, etc.

You can make valid SQL statements that are both easy to understand and amend by following the simple rules and guidelines below:

• There is no case sensitivity in SQL statements (unless indicated).
• SQL statements can be written on a single line or several lines.
• Keywords cannot be truncated or separated between lines.
• Clauses are frequently placed on distinct lines to make them easier to read and modify.
• Keywords are usually typed in capital letters; all other words, such as table and column names, are typed in lowercase.

Oracle Select

A SELECT statement retrieves information from the database. You can use the following capabilities with a SELECT statement:

• Projection: Select the columns in a table that you want to be returned by a query. Choose as few or as many columns as you need.
• Selection: Select the rows in a table that you want to display by a query. Various criteria can be used to limit the number of rows returned.
• Joining: By establishing a link between tables, you can bring data from separate tables together.

Syntax

SELECT expressions
FROM tables
[Where conditions]

A SELECT statement must contain the following elements in its most basic form:

• The SELECT clause indicates which columns should be displayed.
• A FROM clause identifies the table containing the columns that are listed in the SELECT clause
• If the * symbol is specified, then it means all the rows of that table.

Syntax Explanation

SELECT: This is a list of one or more columns

FROM: Specifies the table containing the columns

Where: Used to filter out more records from the table

Example

SELECT * FROM employees WHERE employee_id=1001;

Oracle Insert

The INSERT statement in the Oracle database is used to write DML (Data Manipulation Language) statements/ queries to add one or more rows of data to an already created table in the database in two ways: conventional insert (the Oracle database reuses free space in the table without compromising referential integrity constraints) and direct-path insert (the Oracle database skips the buffer cache and does not reuse the table’s spare space).

Syntax

INSERT INTO table_name
(column1, column2, …… , column_n)
VALUES
(expression1, expression2, expression3, expression_n);

Example:

INSERT INTO employee(employee_id, name,department_name) VALUES(‘0107′,’Sharmishtha’, ‘Sales’);

Oracle Insert All

With the Oracle INSERT ALL statement, you can insert several rows with just one INSERT statement. With only one SQL command, you can insert the rows into one or several tables.

Syntax:

INSERT ALL

INTO table_name (col1, col2, col_n) VALUES (expr_1, expr_2, expr_n)

INTO table_name(col1, col2, col_n) VALUES (expr_1, expr_2, expr_n)

INTO table_name (col1, col2, col_n) VALUES (expr_1, expr_2, expr_n)

SELECT * FROM dual;

Example:

INSERT INTO suppliers

(supplier_id, supplier_name)

VALUES

(50, ‘mindvalley’);

Oracle Update

So, to change existing records in a table in Oracle, we use the UPDATE statement.

Syntax

UPDATE table

SET column1 = expression1,

column2 = expression2,

…

column_n = expression_n

WHERE conditions;

Example:

UPDATE suppliers

SET supplier_name = ‘Rachel’

WHERE supplier_id = 2;

Oracle Delete

To delete or remove one or more records from a table in Oracle uses the DELETE command.

Syntax

DELETE FROM table_name

WHERE conditions;

Example:

DELETE FROM customers

WHERE name = ‘Monica’;

Truncate Table

To remove all records from a table in Oracle, use the TRUNCATE TABLE statement. It functions in the same way as a DELETE command, but without the WHERE clause. it’s a good option when you don’t have to worry about rolling back,

A table can’t be rolled back once it’s been truncated. None of the table’s indexes, triggers, or dependencies are affected by the TRUNCATE TABLE statement.

Syntax

TRUNCATE TABLE customers;

Example:

DELETE TABLE customers;