How to retrieve data in SQL? | OCA Article 2

I have already given multiple examples of SQL select statement in detail with real world industry examples .As we are starting the preparation of OCA I would like you to give you the syntax and examples of simple SQL select statement in detail and will answer you the question named – How to retrieve data in SQL? And I would also like to give you examples of simple SQL select statements for preparation of SQL. I would like to give you one file also where you can get the direct queries.

What you will find in this article?

1. How to retrieve data in SQL? – With multiple examples

2.How to concatenate columns in SQL?

3.Multiple important examples to retrieve the data in SQL.

How to retrieve data in SQL?

The SQL uses select statement to retrieve the specified data from the table. I have already given the information about the execution of SQL in my previous article so you can check that information. In this section I would like to give you multiple examples of select statement using HR schema. You can directly execute that in live SQL.

Case 1 : Retrieve the full data from table :

We need to use asterisk(*) operator to select all data from the table.

Syntax :

Select * from table_name;

Example :

Select * from Employees;

The above statement will retrieve the data from Employees table.

If you are using HR schema in mail schema or container database you can use HR.Employees.

Select * from HR.Employees;

Case 2 : Retrieve the specific column data from table :

The second case is to retrieve only specified columns from the table. These statements are important for reporting purpose where we require the specific data from the table.

Syntax :

Select Column_1,Column_2…Column_n from table_name;

Example :

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM employees;

The above query will give you specified results from employee table.

Case 3 : Retrieve the data with using arithmetic operators

There are four arithmetic operators in SQL + – * / . We can use the arithmetic operators in select statement to retrieve the specified data.

Lets say you want to select data of Employee and check the salary of employee if it is increased by 10% and increased by 1000 rupees.

Query :

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY  , SALARY+1000 , Salary+(Salary*0.10)  FROM HR.employees;

The above query will give you information about the Employee with their specified salary.

Case 4 : Retrieve the data with using aliases

The aliases are nothing but the system to temporary rename the column at the time of data retrieval.

There are multiple ways to use aliases :

Way 1 : use As statement

Way 2 : Use Space

Way 3 : Use Double Quote

Example :

SELECT FIRST_NAME, FIRST_NAME AS FirstName,FIRST_NAME  FirstNameSpace, FIRST_NAME ” First nAME” FROM   HR.EMPLOYEES;

The above statement will give  the First name with using different aliases.

Case 5 :Using concatenation operator

We can use concatenation operator to connect multiple columns together. (||)  I would like to give multiple examples of using concatenation operator.

Example 1 : Connect First_Name and Last name with space.

SELECT FIRST_NAME, LAST_NAME, FIRST_NAME||LAST_NAME “Full_Name_withoutspace”,FIRST_NAME||’ ‘||LAST_NAME ” Full_Name_withspace ”  from HR.EMPLOYEES;

Example 2 : Connect Full_Name with string

SELECT FIRST_NAME||LAST_NAME|| ‘ work in department ‘|| DEPARTMENT_ID FROM HR.EMPLOYEES;

Example 3:  Using Quote operator

SELECT FIRST_NAME||LAST_NAME || q'[ Department Name]’|| DEPARTMENT_ID FROM HR.EMPLOYEES;

SELECT FIRST_NAME|| FIRST_NAME||LAST_NAME ||  q'( Department Name)’|| DEPARTMENT_ID FROM HR.EMPLOYEES;

Case 6 : Retrieve the Distinct values

There are so many time when you require to select distinct values. If you want to find out Department_name as distinct values.

Department_Name with distinct :

SELECT distinct DEPARTMENT_NAME FROM   HR.Department;

The above statement will give you information about the distinct department names form Department table. It will not show the duplicate department name if any.

Hope you like this article on how to retrieve data in SQL? With multiple examples. If you like this article or if you have any concerns with the same kindly comment in comments section.