SELECT

Introduction

SELECT is one command that you will use the most while working with Oracle Database. It is used to fetch data from table and display what we want. We use SQL queries to show only relevant information.

For example, let us consider the Employees Table from the HR Schema. This table consists of following columns: Employee_ID, First_Name, Last_Name, Salary, Commission_Pct etc.

To fetch information from this table we can use the following syntax:

SELECT column_name FROM table_name;

Using this statement, we can retrieve all the rows within that column in the table. We can also retrieve data from multiple columns and also the entire table, if we want. In the upcoming articles, we will see how SELECT statement is used in combination with other statements to manipulate data in a certain way.

We can arrange data in ascending or descending order, group them based on a property and do a lot more using SELECT statement. But in this article, we will just see SELECT with FROM statement.

SELECT Examples

1. Retrieve Data from only one Column

If we want to retrieve data from only one column in Employees table, then we will use this command.

SELECT first_name FROM employees;

Result:

This query gives us the first name of all the employees working in that organization

2. Retrieve Data from Multiple Columns

If we want to retrieve data from multiple columns in Employees Table, then we use this query:

SELECT First_Name, Last_Name, Salary FROM Employees;

Result:

This query gives us the first name, last name and Salary of Employees.

3. Retrieve Data from all the Columns in Table

We use the following query to give us data from all the columns in a table.

SELECT * FROM Employees;

Result:

4. Using Expressions in SELECT statement

If we directly want to perform some arithmetic on columns with numerical data type, then we can also do that using SELECT statement. Let’s see how to get annual salary of employees from Employee table.

SELECT first_name, last_name, salary * 12 from employees;

Result:

Arithmetic Expressions involving addition, subtraction, division and multiplication can be used in SELECT statement.

For Ex.

SELECT first_name, last_name, salary + 1000 FROM employees;

SELECT first_name, last_name, salary - 1000 FROM employees;

SELECT first_name, last_name, salary * commission_pct FROM employees;

SELECT first_name, last_name, 
salary + salary * commission_pct)/100 FROM employees;

Result:

We request you to practice these SQL queries in SQL developer and get used to it. We will be using more complex complex queries as we go along.

Points to Remember:

1. The queries in SQL are NOT case sensitive. You can use both SELECT * FROM EMPLOYEES and select * from employees to retrieve the same data.
2. Queries in SQL end with a semicolon ; at the end.
3. We can write same SQL query in multiple lines for better readability and understanding.