How do I view SQL History in Oracle?

SQL History in Oracle

In June 1970, Dr. E. F. Codd published the initial version of the relational model concept. Codd’s paradigm was then used by IBM Corporation, Inc. to create Structured English Query Language (SEQUEL), which eventually became known as SQL. When Relational Software, Inc. (now Oracle Corporation) released the first commercially available SQL implementation in 1979, it was a significant milestone. As the industry standard language for Relational Database Management Systems (RDBMS), SQL is now widely used.

With its adherence to the ANSI/ISO standard SQL syntax, Oracle has shown a strong commitment to SQL standards over the past ten years. Usually, the only exceptions to the worldwide standard are for practical SQL extensions that are unique to Oracle. The majority of SQL examples found in Oracle documentation are therefore probably applicable to other SQL-supporting database management system (DBMS) implementations.

How SQL Works

By allowing you to work with data logically, SQL lets you specify what data you want to work with rather than how to store or get it physically. It offers automatic navigation to the data and processes it as sets or groups rather than as individual units. Similar to Oracle’s PL/SQL extension, flow-control statements are a more recent addition to “persistent stored modules” (PSM), however SQL statements are strong on their own and stand alone. Every SQL statement makes use of the optimiser, an Oracle Database feature that chooses the most effective method of accessing the given data.

Types of SQL Statements

Generally speaking, SQL statements fall into one of two primary categories: Data Definition Language (DDL) or Data Manipulation Language (DML).

Data Definition Language (DDL)

Tables, views, indexes, triggers, and other database objects can be defined, altered, or deleted using DDL statements. There is no implied commitment to the current transaction made by these remarks.

DDL commands that are frequently used include:

1. CREATE:  To create new objects in the database.
2. ALTER:  To change database objects that are already there.
3. DROP:  To delete database objects, use DROP.

Example of a CREATE TABLE statement:

CREATE TABLE MY_TABLE (
   what VARCHAR2(10),
   who  VARCHAR2(10),
   mark VARCHAR2(1)
);

Output:

Table created.

The three columns in the new table created by this statement what, who, and mark each have their own data types and sizes. The table is called MY_TABLE.

Data Manipulation Language (DML)

To access and modify data inside pre-existing schema objects, DML statements are utilised. DML statements do not implicitly commit the current transaction, just like DDL statements do.

The main commands in DML are:

• SELECT:  Data can be retrieved from an Oracle database using the SELECT command. The SQL statement that is utilised the most is this one.
• INSERT:  An Oracle table can have new rows added using the INSERT command.
• UPDATE: One or more records in a table can be changed using the UPDATE function.
• DELETE: This function eliminates one or more entries from a table.
• MERGE: Originally introduced in Oracle 9i and improved in Oracle Database 10g, this command uses a single statement to perform inserts and updates.

Example of an INSERT statement:

INSERT INTO my_table (what, who, mark)
VALUES ('Hello', 'world', '!');

Output:

1 row created.

This command adds a new row of data into the MY_TABLE.

Example of a SELECT statement with WHERE and ORDER BY:

SELECT what, who
FROM my_table
WHERE what = 'Hello'
ORDER BY who;

Output:

WHAT       WHO
---------- ----------
Hello      world

This query retrieves the what and who columns from MY_TABLE for rows where the what column equals ‘Hello’, and then sorts the results by the who column. The SELECT keyword specifies the columns to retrieve, FROM indicates the table, WHERE applies conditions to filter rows, and ORDER BY sorts the result set.

Oracle’s SQL Environment and Tools

Oracle offers tools for interacting with the database, but you can also write SQL statements directly. SQLPlus is an interactive command-line utility that comes with every installation of an Oracle database server or client. It lets users send SQL statements and get results. A large number of database managers frequently utilise SQLPlus. Another well-liked application that provides a graphical user interface (GUI) for SQL testing and execution is Oracle SQL Developer.

Integration with PL/SQL

The procedural extension for SQL and the Oracle relational database, PL/SQL (Procedural Language/Structured Query Language), developed by Oracle Corporation, further improves SQL. Developers can easily integrate programming logic into SQL instructions using PL/SQL, including variables, conditional expressions (IF, CASE), and looping structures. Strong applications and stored program objects, such as procedures, functions, packages, and triggers, can be created because to this close connection. These can live and run inside the database server itself.

In summary, SQL is the worldwide language for working with relational databases, and Oracle has consistently improved its implementation by offering strong tools and extensions that facilitate thorough and effective data administration and application development. Consider SQL to be a complex data vault’s instruction manual. It explicitly instructs the vault on what information to keep (INSERT), what to retrieve (SELECT), what to modify (UPDATE), what to remove (DELETE), and how to arrange the compartments (CREATE, ALTER, DROP). Data operations rely on Oracle because it offers a reliable way to comprehend and carry out these instructions.