How to retrieve data from a database using SQL Queries

In this tutorial, we will see how to retrieve data from an Oracle Table using a basic SELECT command, Displaying the data using alternate column aliases, selecting the data by eliminating duplicate rows.

SQL Select

SELECT is used to query the data from a table , It can be specified with * or particular column names that we need to extract from the table.

Example:

SELECT * from employee_details;
Retrieving Data using the Oracle SQL SELECT Statement

Now , if we need to specify only particular column names , then we need to retrieve the data as below

SELECT employee_id,first_name,last_name FROM employee_details;
Retrieving Data using the Oracle SQL SELECT Statement

Note that the city column name is not displayed since we excluded it in the query above.

Column Aliases

Aliases of Columns are alternate names given while displaying the data from a table.

Example:

SELECT city from employee_details;

The above query results in the output as below

Retrieving Data using the Oracle SQL SELECT Statement: Column Aliases

If you want to query the data from table ‘EMPLOYEE_DETAILS‘ with column name as ‘CITY OF RESIDENCE‘ instead of just CITY , then we need to use column alias as below

SELECT city  as "CITY OF RESIDENCE" from employee_details;
Retrieving Data using the Oracle SQL SELECT Statement: Column Aliases

DISTINCT

If you need to query about different data that is present in the table then we will use the DISTINCT keyword. This will remove the duplicates from the table and return the result based on the columns specified in the DISTINCT keyword.

The table contains 11 rows as mentioned below.

Retrieving Data using the Oracle SQL SELECT Statement: Distinct

Now, we will know from what different cities the employees come from. For this we need to write the query as below

SELECT DISTINCT(city)  FROM employee_details;

This will give the output as below, as we can see that there are 10 different cities that all the 11 employees come from.

Retrieving Data using the Oracle SQL SELECT Statement: Distinct

Concatenation Operator

The operator || is called the concatenation operator which can be used to concatenate strings as mentioned below.

Example

Given below the first_name and last_name of the employees available in employee_details table.

SELECT first_name,last_name  FROM employee_details
Retrieving Data using the Oracle SQL SELECT Statement: Concatenation Operator

Now , if we need to query the full name of the employee in the table by concatenating the “first_name” and “last_name” columns , then we need to use the concatenation operator as mentioned below.

SELECT  first_name|| ' ' || last_name  AS "Employee Name" FROM employee_details;
Retrieving Data using the Oracle SQL SELECT Statement: Concatenation Operator

Literal Character Strings

A literal can be a string, number or date that can be used in a select statement.

Example:

Let us see how the employee names and city information is present in the table.

SELECT  first_name,last_name ,City  FROM employee_details;

This statement will give the output as below

Retrieving Data using the Oracle SQL SELECT Statement: Literal Character Strings

However, if we need to query this in a meaningful way by adding the literal ‘ is living in ‘ for every employee, then we need to specify the query as below.

SELECT  first_name|| ' ' || last_name  || ' is living in ' || City  as "Employee Residence Details" FROM employee_details;
Retrieving Data using the Oracle SQL SELECT Statement: Literal Character Strings

Note that , here we have included spaces before and after the string ‘is living in’

Alternative Quote Character

For example if we need to input ‘s in the literal and if we try as below , then error will be returned.Since Oracle thinks ‘ as starting of the literal .

SELECT  first_name|| ' ' || last_name  || ' live's in  ' || City  as "Employee Residence Details" FROM employee_details;

So in order to overcome this Alternative Quote character. This can be started with q at the beginning followed by ‘ and delimiter. Once it is finished then it is ended with delimiter followed by ‘.

SELECT  first_name|| ' ' || last_name  || q'[ live's in  ]' || City  as "Employee Residence Details" FROM employee_details;

Retrieving Data using the Oracle SQL SELECT Statement: Alternative Quote Character

Delimiters can also be replaced as {, <, (, [, !

Using NULL values in the SELECT statement

A null value means nothing, it is not equal to value with space or zero. We cant use comparison operators with null values, If we need to query null values then we need to use ISNULL or IS NOT NULL. If you do not insert anything in the data , then it becomes null.

Example

Let us see the data available in the table as below

Retrieving Data using the Oracle SQL SELECT Statement: Null values
SELECT  * FROM employee_details WHERE city IS NULL;
Retrieving Data using the Oracle SQL SELECT Statement: Null values
SELECT  * FROM employee_details WHERE city IS NOT NULL;
Retrieving Data using the Oracle SQL SELECT Statement: Null values

I hope you got a basic idea.

Happy Learning.

Leave a Comment