ORACLE LIVE SQL EDITOR AND SQL QUICK GUIDE

ORACLE LIVE SQL EDITOR AND SQL QUICK GUIDE

ORACLE LIVE SQL EDITOR AND SQL QUICK GUIDE

In this article, you are going to learn how to use Oracle Live SQL and how to work with Oracle Database without installing the database. Get instant access to the Oracle Database and learn from a collection of community scripts and tutorials. Write your own SQL scripts and easily share them with others. All you need to get started is your Oracle.com account.

Following steps helps you to build your knowledge in SQL.

Step 1: Start with a basic structure as given in the following below diagram.

table
Fig: Table

Step 2: Learn some basic terminologies i.e. Field, Row, And Column (s). So, it helps you to better understanding.

Step 3: Break it into some simple steps, i.e. Create a Table, Make some conditions on attributes (integrity constraints), Inserting values, and Make sure all values are inputted in their proper place.

Step 4: There are some queries and you should answer those.

Step 5: Commit; After Creating and inserting values because sometimes data is not saved automatically. Commit is used for saving purposes.

There are some steps between Sql statement and query results. Parsing is for the parse tree structure and binding is for bind sub results and query optimization is for execute over queries and finally we get the output.
Fig: SQL STATEMENT TO RESULT INSIDE PROCESS
There are some steps between Sql statement and query results. Parsing is for the parse tree structure and binding is for bind sub results and query optimization is for execute over queries and finally we get the output.
Fig: There are some steps between SQL statement and Query results. Parsing is for the parse tree structure and binding is for bind sub results and query optimization is for execute over queries and finally we get the output.

PRACTICE SQL IN LIVESQL

Creating Tables:

Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; after that a datatype, such as VARCHAR2, DATE, or NUMBER; after that mention a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.

You can specify rules for each column of a table. These rules are called integrity constraints. One example is a NOT NULL (integrity constraint). This constraint forces the column to contain a value in every row, NOT NULL is not a value.

For example:

DeptNO generated using Trigger, Tables can declaratively specify relationships between tables, typically referred to as referential integrity(Parent-Child relation). To see how this works we can create a “child” table of the DEPARTMENTS table by including a foreign key in the EMPLOYEES table that references the DEPARTMENTS table.

For example:

Foreign keys must reference primary keys, so to create a “child” table the “parent” table must have a primary key for the foreign key to reference. It also helps to reduce problem-solving complexity.

Creating Triggers:

Triggers are procedures that are stored in the database and are implicitly run, or fired, when something happens. Traditionally, triggers supported the execution of a procedural code, in Oracle procedural SQL is called a PL/SQL block. PL stands for procedural language. When an INSERT, UPDATE, or DELETE occurred on a table or view. Triggers support system and other data events on DATABASE and SCHEMA.

Triggers are frequently used to automatically populate table primary keys, the trigger examples below show an example trigger to do just this. We will use a built-in function to obtain a globally unique identifier or GUID.

Inserting Data:

We created tables properly, and we have triggers to automatically populate our primary keys, we can add data to our tables. Because we have a parent-child relationship, with the DEPARTMENTS table as the parent table, and the EMPLOYEES table as the child we will first INSERT a row into the DEPARTMENTS table. So, the Insertion of data values can easily be done.

Let’s verify that the insert was successful by running a SQL SELECT statement to query all columns and all rows of our table.

DEPTNONAMELOCATION
241871540121193736077918737151514289109FinanceNew York
241871540121196153929557966409863701461DevelopmentSan Jose
Fig: Department table

You can see that an ID will have been automatically generated. You can now insert into the EMPLOYEES table a new row but you will need to put the generated DEPTNO value into your SQL INSERT statement. So, joining related queries can be performed. The examples below show how we can do this using a SQL query, but you could simply enter the department number directly.

EMPNONAMEJOBMANAGERHIREDATESALARYCOMMISSIONDEPTNO
241871133287053808049605250860362424530Sam SmithProgrammer5000241871540121196153929557966409863701461
241871133287056225901244480118711836882Mara MartinAnalyst6000241871540121193736077918737151514289109
241871133287058643752883709377061249234Yun YatesAnalyst5500241871540121196153929557966409863701461
FIG: EMPLOYEES TABLE

Indexing Columns :

Typically developers index columns for three major reasons:

  1. To enforce unique values within a column
  2. To improve data access performance
  3. To prevent lock escalation when updating rows of tables that use declarative referential integrity

When a table is created and a PRIMARY KEY is specified an index is automatically created to enforce the primary key constraint. If you specific UNIQUE for a column when creating a column a unique index is also created. To see the indexes that already exist for a given table you can run the following dictionary query.

TableIndexColumnPosition
DEPARTMENTSPK_DEPARTMENTSDEPTNO1
EMPLOYEESPK_EMPLOYEESEMPNO1
Fig: Indexing Columns

It is typically good form to index foreign keys, foreign keys are columns in a table that reference another table. So, You can use some join operations. In our EMPLOYEES and DEPARTMENTS table example, the DEPTNO column in the EMPLOYEE table references the primary key of the DEPARTMENTS table.

We may also determine that the EMPLOYEE table will be frequently searched by the NAME column. To improve the performance searches and to ensure uniqueness we can create a unique index on the EMPLOYEE table NAME column.

Oracle provides many other indexing technologies including function based indexes which can index expressions, such as an upper function, text indexes which can index free form text, bitmapped indexes useful in data warehousing. You can also create indexed organized tables, you can use partition indexes and more. Sometimes it is best to have fewer indexes and take advantage of in memory capabilities. All of these topics are beyond the scope of this basic introduction.

Querying Data :

To select data from a single table it is reasonably easy, simply use the SELECT … FROM … WHERE … ORDER BY … syntax.

EMPNONAMEJOBMANAGERHIREDATESALARYCOMMISSIONDEPTNO
241871133287053808049605250860362424530Sam SmithProgrammer5000241871540121196153929557966409863701461
241871133287056225901244480118711836882Mara MartinAnalyst6000241871540121193736077918737151514289109
241871133287058643752883709377061249234Yun YatesAnalyst5500241871540121196153929557966409863701461
Fig: Employees Table

To query data from two related tables you can join the data

EMPLOYEEDEPARTMENTJOBLOCATION
Mara MartinFinanceAnalystNew York
Sam SmithDevelopmentProgrammerSan Jose
Yun YatesDevelopmentAnalystSan Jose
Fig: Join result

As an alternative to a join you can use an inline select to query data.

EMPLOYEEDEPARTMENTJOB
Mara MartinFinanceAnalyst
Sam SmithDevelopmentProgrammer
Yun YatesDevelopmentAnalyst
Fig: Join result 2

Adding Columns :

You can add additional columns after you have created your table using the ALTER TABLE … ADD … syntax. For example:

Querying the Oracle Data Dictionary:

Table metadata is accessible from the Oracle data dictionary. The following queries show how you can query the data dictionary tables. In LIVE SQL, desc table_name is not working, in those cases, we can consider the following query.

STATUS
EMPLOYEESLIVESQL_USERSVALID
Fig: Employees table status
COLUMN_IDCOLUMN_NAMEDATA_TYPE
1EMPNONUMBER
2NAMEVARCHAR2
3JOBVARCHAR2
4MANAGERNUMBER
5HIREDATEDATE
6SALARYNUMBER
7COMMISSIONNUMBER
8DEPTNONUMBER
9COUNTRY_CODEVARCHAR2
Fig: Employees table column values

Updating Data :

You can use SQL to update values in your table, to do this we will use the update clause

The query above will update all rows of the employee table and set the value of country code to US. You can also selectively update just a specific row.

Lets run a Query to see what our data looks like

NAMECOUNTRY_CODESALARYCOMMISSION
Mara MartinUS6000
Sam SmithUS50002000
Yun YatesUS5500
Fig: Employees table

Aggregate Queries :

Except for COUNT(*) , aggregate functions ignore null values. and COUNT never returns null, but returns either a number or zero.

You can sum numeric data in tables using aggregate functions.

We will use column aliases to rename columns for readability.

We will also use the null value function (NVL) to allow us to properly sum columns with null values. Such that, Return date in a Library Management System is Null until it was returned.

Suppose, Primary key present in an attribute then null values are restricted because the Primary key does not contain any null values.

EMPLOYEE_COUNTTOTAL_SALARYTOTAL_COMMISSIONMIN_COMPENSATIONMAX_COMPENSATION
316500200055007000
Fig: Employees table result

Compressing Data :

As your database grows in size to gigabytes or terabytes and beyond, consider using table compression. So, Table compression saves disk space and reduces memory use in the buffer cache. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for data loading and DML. Table compression is completely transparent to applications. It is especially useful in online analytical processing (OLAP) systems, where there are lengthy read-only operations, but can also be used in online transaction processing (OLTP) systems.

You specify table compression with the COMPRESS clause of the CREATE TABLE statement. You can enable compression for an existing table by using this clause in an ALTER TABLE statement. In this case, the only data that is compressed is the data inserted or updated after compression is enabled. Similarly, you can disable table compression for an existing compressed table with the ALTER TABLE…NOCOMPRESS statement. In this case, all data the was already compressed remains compressed, and new data is inserted uncompressed.

To enable compression for future data use the following syntax.

Deleting Data :

You can delete one or more rows from a table using the DELETE syntax. In those cases where a wrong value is inserted or want to remove some rows from the table. For example to delete a specific row:

Dropping Tables :

You can drop tables using the SQL DROP command. Thus, Dropping a table will remove all of the rows and drop sub-objects including indexes and triggers. The following DROP statements will drop the departments’ and employees’ tables. The optional cascade constraints clause will drop remove constraints thus allowing you to drop database tables in any order.

Un-dropping Tables :

If the RECYCLEBIN initialization parameter is set to ON (the default in 10g), then dropping this table will place it in the recycle bin. To see if you can undrop a table run the following data dictionary query:

To undrop tables we use the flashback command, for example:

DEPARTMENTS
2
Fig: Department table
EMPLOYEES
2
Fig: Employees Table
Oracle Live SQL
Fig: Oracle Live SQL

In the above figure, An instance is shown. So that, Understanding is clearer to you.

YOU MIGHT LIKE:

FAQ:

1> How to describe my table in Live SQL?

Leave a Reply

Your email address will not be published. Required fields are marked *

Close Bitnami banner
Bitnami