Data Query Language Commands in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Data Query Language or Data Retrieve Language Commands in Oracle

In this article, I am going to discuss with Data Query Language (DQL) or Data Retrieve Language (DRL) command i.e. SELECT Command in Oracle with Examples. Please read our previous article, where we discussed Data Manipulation Language (DDL) Commands in Oracle with Examples.

DQL (Data Query Language) in Oracle:

The DQL statements in Oracle or you can say the SELECT statements. The Select Statement in Oracle is basically used to return records in the form of a result set from one or more tables or views.

The SQL Select Query does not store any data itself. It simply displays the data that is stored in database tables. The Select Statement in Oracle can retrieve and shows the data from one or more database tables or views, from other queries, or from a combination of the above two.

The SELECT clause is the first clause in any SELECT statement. The SELECT statement’s main purpose is to retrieve the data from the database table and return it in a tabular structure. The SELECT clause defines the columns that will be returned in the final tabular result set.

The SELECT clause is executed after the FROM clause and any optional WHERE, GROUP BY, and HAVING clauses if present. The FROM clause builds an intermediate tabular result set from which the SELECT clause ultimately selects the data to be returned. However, the presence of a GROUP BY clause changes the structure of this intermediate table, thus changing the data available to the SELECT clause when it’s finally executed.

The SELECT is used to retrieve all rows from a table at a time or can be used to retrieve a specific row from a table by using the “WHERE” condition.

Note: A SELECT statement with a FOR UPDATE clause is a transaction and DML statement, not a DQL query.

Select Statement Syntax in SQL Server:

Syntax1: SELECT All Columns

If you want to select all the columns of a table or view then you can use “*” as shown in the below syntax.
SELECT * FROM Table_Name;

Note: In the SQL SELECT statement (*) star or asterisk symbol is a special type of keyword that means ALL. Whenever you want to retrieve all columns from a table or view in a SQL SELECT Statement, then you can use *.

Syntax2: SELECT Specific Columns

If you want to select specific columns, then you need to specify such column names before the FROM Clause as shown in the below syntax.
SELECT Column_List FROM Table_Name;

Note: If you want to select all the columns of a table or view then you can also use “*”, but for better performance use the column list instead of using “*”.

Syntax3: SELECT All Columns with Conditions

If you want to select All Columns with WHERE (or any other) conditions then you need to use the select statement as shown in the below syntax.
SELECT * FROM tables [WHERE conditions];

Syntax4: SELECT Specified Columns with Conditions

If you want to select specific Columns with WHERE (or any other) conditions then you need to use the select statement as shown in the below syntax. Here, you need to specify the column list that you want to retrieve.
SELECT Column_List FROM tables [WHERE conditions];

Examples of SELECT Statement in Oracle:

We are going to use the following Employee table to understand the need and use of Oracle SELECT Statement with Examples.

Please execute the below SQL query to drop the existing Employee table and Create a new Employee table with the required sample data. We also set the linesize to get the output in the above format.

SET linesize 300;

DROP Table Employee;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  Department VARCHAR(10),
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Age INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1002, 'Smith', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 28, 'Delhi');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina', 'HR', 75000, 'Female', 26, 'Mumbai');

Example: Retrieve all fields from the Employee table

Let’s see how to write a SELECT statement to retrieve all the columns from the Employee table. As we are going to retrieve all columns so we have two options.

Option1: Use * as follows
SELECT * FROM Employee;
Output:

Option2: Specify all the Columns in the SELECT Class as follows:

SELECT Id, Name, Department, Salary, Gender, Age, City FROM Employee;

Output:

Note: In both cases, when we execute the SELECT query, we will get the same result set.

Example: Retrieve Specific Columns from Employee table

Let’s see how to write a SELECT statement in Oracle to retrieve ID, Name, Department, and Salary columns from the Employee table. As we are going to retrieve specific columns, so we can’t use *, instead, here we need to specify the required columns that we want to retrieve in the SELECT clause as shown in the below example.

SELECT Id, Name, Department, Salary FROM Employee;

When we execute the above SELECT query, we will get the following result set which includes only the Id, Name, Department, and Salary columns.

Example: Retrieve all Columns with Condition in Oracle

Let us see how to write a SELECT statement to retrieve all the columns from the Employee table with some conditions. The condition is we need to retrieve those employees whose Gender is Male. Here, we are using the WHERE Clause to provide the condition. In our upcoming article, we are will discuss the need and use of the WHERE clause in Oracle. Again, as we are going to retrieve all the columns from the Employee table so we have two options.

Option1: Use * as follows

SELECT * FROM Employee WHERE Gender = ‘Male’;

Option2: Specify all the Columns in SELECT Class as follows:

SELECT Id, Name, Department, Salary, Gender, Age, City FROM Employee WHERE Gender = ‘Male’;

In both cases, when you execute the above SELECT queries, you will get the same result set as shown in the below image.

FROM Clause in Oracle

As of now we are using the FROM Clause in the select statement and specifying the table name from where we want to retrieve the data. Let us learn something about the FROM Clause.

The FROM clause can be simple, and it can also be quite complex. The FROM clause forms a tabular structure. This tabular structure is referred to as the “RESULT SET” or an “INTERMEDIATE RESULT SET” or an “INTERMEDIATE TABLE” of the FROM clause.

If we get the FROM clause wrong, then the SQL statement will always return the wrong results. It’s the FROM clause that produces the tabular structure, the starting set of data on which all other operations in a SELECT statement are performed.

The FROM clause is the first clause that the database system looks at when it parses the SQL statement. The FROM statements can return the result sets from one table or more than one table using joins, views, and subqueries. We will learn more about joins, views, and subqueries in our upcoming articles. Let’s learn about FROM clause with some examples. Please have a look at the following SQL Statement.

SELECT * FROM Employee;

When you execute the above SELECT statement, it returns all the columns and all the rows from the Employee table as shown in the below image. What basically the FROM clause does is, it will try to get all the columns from the Employee table which is specified by the FROM clause.

Now, let see what happens when we try to get a result set from a table that does not exist in the database as shown below. Here, the table Employee1 does not exist in the database.

SELECT * FROM Employee1;

When we try to execute the above SQL Query, we will get the following error saying that the table or view Employee1 does not exist and that makes sense.

Here in this SQL statement, the system first processes the “FROM clause” and checks if the table name Employee1 exists or not. This SQL statement proves that FROM clause is the first clause that the database system looks at when it parses the SQL statement.

SELECT DISTINCT Statement in Oracle

The SELECT DISTINCT SQL statement in Oracle is used to return only the distinct or different values from a table column. In a database table, a column may contain duplicate or similar values. For example: In our employee database table, in Department and Gender column we have duplicate values.

That means multiple employees can be in the same Department or have the same salary or have the same gender as well. In such a case, if we want to get only the distinct values from the columns, the SELECT DISTINCT Statement is very useful. The SELECT DISTINCT Statement in Oracle will return all the distinct values in the given column.

Let’s see the SELECT DISTINCT SQL statement with some examples. Our requirement is to fetch all the distinct departments from the employee table. The following SQL query exactly does the same.

SELECT Distinct Department FROM Employee;

When we execute the above SELECT Distinct statement, then we will get the following result set.

We can also combine 2 or more columns to get distinct values. In the below query, we are combining Department and Salary columns.

SELECT Distinct Department, Salary FROM Employee;

When we execute the above SELECT query, we will get the following output.

Note: The Distinct keyword is used to eliminate duplicate values and display Unique values in the query results.

Alias Names in Oracle:

It is nothing but an alternate (or) temporary name. Users can create alias names on two levels in a database.

I) Column Level:

At this level, we are creating alias names on columns. The syntax is given below.
<column name> <column alias name>
Example: Department Dept

II) Table Level:

In this level, we create alias names on the table. The syntax is given below.
<table name> <table alias name>
Example: Employee Emp

Syntax to combined column + table level alias names by using “select” query:

Select <column name1> <column name1 alias name>, <column name2> <column name2 alias name> From <Table Name> <Table Alias Name>;

Example:

In the following SQL query, we have given alias name as Department column as Dept, Salary column as Sal, and Employee table as Emp;

SELECT Id, Name, Department Dept, Salary Sal FROM Employee Emp;

When you execute the above query, you will get the following output.

Concatenation Operator (||) in Oracle:

This operator is used to join two string values (or) two expressions in a select query.
Example: Select ‘Welcome’||’ ‘||’to Oracle’ from dual;
When you execute the above query, you will get the following output.

Example: Select ‘Mr.’||Name||’ ‘||’Salary is’||’ ‘||Salary from Employee;
When you execute the above query, you will get the following output.

How to copy data from one table to another table in Oracle?

Now, let us understand how to copy data from one table to another table in oracle. To do so, we have to use the following syntax.
INSERT INTO <DESTINATION TABLE NAME> SELECT * FROM <SOURCE TABLE NAME>;

Example1:

Suppose we have created the new table and then we want to insert data from the Existing table to the new table. Let us first create the TempEmployee table by executing the below Create Table statement.

CREATE TABLE TempEmployee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  Department VARCHAR(10),
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Age INT,
  City VARCHAR(10)
);

Now, if we want to copy the Employee table data into the TempEmployee table then we need to execute the following SQL query.
INSERT INTO TempEmployee SELECT * FROM Employee;
Once you execute the above query, you will get the following output.

Here in the above output, you can see that we are getting the message as 10 rows created. Let us see whether the TempEmployee is filled with the data from the Employee table by executing the following SELECT statement.

SELECT * FROM TempEmployee;

Once you execute the above SELECT query, you should get the following output.

Example2:

Suppose you want to create a new table with the same structure and same data as the existing table. For example, we want to create a new table with the name Employee1 with the same structure and same data as the Employee table, then we need to execute the following SQL query.

CREATE TABLE Employee1 AS SELECT * FROM Employee;

When you execute the above query, you will get the following output.

Let us see whether Employee1 is created with the same structure and data as the Employee table or not by executing the following SELECT statement.

SELECT * FROM Employee1;

Once you execute the above SELECT query, you should get the following output.

Example3:

If you want to create a new table with the same structure as an existing table but without data. For example, we want to create a new table with the name Employee2 with the same structure as the existing Employee table but with output data. To do so, we need to execute the following SQL query.

CREATE TABLE Employee2 AS SELECT * FROM Employee WHERE 1 = 0;

When you execute the above SQL query, you will get the following output. Here, it will create the Employee2 table without data.

Merge Command in Oracle:

It is a DML command (oracle 9i). It is used to transfer data from the source table to the destination table. If data is matching in both tables, then those matching data /rows are overridden on the destination table by using the “UPDATE” command whereas if data is not matching then those unmatching data/rows are transferred from the source table to the destination table by using the “INSERT” command.

Syntax:

Example: Using MERGE Command in Oracle

Step1:

SELECT * FROM Employee;

Step2: Create a new table from the existing Employee table with data

CREATE TABLE Employee3 AS SELECT * FROM Employee;

Step3: Insert two more records into the new Employee3 table

INSERT INTO Employee3 (Id, Name, Department, Salary, Gender, Age, City) VALUES (1011, ‘Kumar’, ‘IT’, 55000, ‘Male’, 27, ‘London’);

INSERT INTO Employee3 (Id, Name, Department, Salary, Gender, Age, City) VALUES (1012, ‘Santosh’, ‘HR’, 70000, ‘Female’, 29, ‘Mumbai’);

Step4: Checking the data of both source and destination tables

SELECT * FROM Employee3; Source Table
SELECT * FROM Employee; Old Table

Step5: Using Merge statement

MERGE INTO Employee d using Employee3 s on(d.Id = s.Id)
when matched then 
update set d.Name=s.Name, d.City=s.City,d.Department=s.Department, d.Salary=s.Salary, d.Age=s.Age, d.Gender=s.Gender
when not matched then
Insert(d.ID, d.Name, d.Department, d.City, d.Salary, d.Age, d.Gender)values(s.Id, s.Name, s.Department, s.City, s.Salary, s.Age, s.Gender);

When you execute the above MERGE statement, then you will get the following output.

In the next article, I am going to discuss Operators in Oracle with Examples. Here, in this article, I try to explain Data Query Language (DQL) or Data Retrieve Language (DRL) Command in Oracle with Examples and I hope you enjoy this Data Query Language (DQL) or Data Retrieve Language (DRL) Command in Oracle with Examples article.