Select Statement

is the powerful SQL Command we use the most in the database activity. Select statement is used to retrieve the data from the tables.

Employee Table with data (Following examples and selects based on the following table (EMP))

Syntax

SELECT col_name, col_name.................
FROM table_name
WHERE condition

Selecting all columns

We can select all the columns from a table using * operator in SELECT statement.

SELECT * FROM EMP;

Displays all the rows from the emp table. Usually we can write this sort of select in the development environment, we should not write this sort of select in the production environment.

Selecting particular columns.

We can select particular columns from a table. Suppose if we want to select empno, ename and sal column values from the EMP table then we can write the SELECT as follows.

SELECT empno, ename, sal FROM Emp;

Column Aliases

Usually if we select a column from a table then the column heading is same as the column name, if we want to change the column header for display purpose then we have to use Aliases for the column names. If the alias includes the space in it then we should include with in the double quotes.

SELECT empno "Employee Number",
ename "Employee Name",
sal Salary
FROM Emp

Specific Rows

If we want to display all employee numbers and names who works in deptno 10 then how we should write the select. Here we need to display empno, ename so the columns in SELECT clause is empno, ename. In the FROM clause we need to specify the table name ie EMP. What is the condition? needs to display the employees works in deptno 10. So we need to write the WHERE clause in the SELECT. Here we are selecting specific rows with in the table. So our Select statement will be

SELECT empno, ename
FROM Emp
WHERE deptno = 10;

Ordering Rows

If we want to display the result set in an order then we include the ORDER BY CLAUSE in the Select statement. Display the employee names, salary information and sort the employee names alphabetically.

SELECT ename, sal
FROM Emp
ORDER BY ename;

Suppose we want to display the result set by salary in descending order then

SELECT ename, sal
FROM Emp
ORDER BY sal DESC;

By default the order by is ASC ie asending.

Expressions in Select statement

In order to get the sum of salary and commission we need to add two columns ie sal and comm. So you can manipulate in the Select statement itself.

SELECT ename, sal, comm, sal + comm "Total"
FROM emp;

If comm column is null then if we add sal to it, it ends up with a null value. So we can use NVL function.

SELECT ename, sal, comm, sal + nvl(comm,0) "Total"
FROM emp;

If you want to display all the employees who has their employee numbers as even number.

SELECT empno
FROM emp
WHERE mod(empno,2) = 0

Concatinating Strings

Suppose if we want to display the employee name and salary information as int the follwing format
JONES works in deptno 10

then in the above shown format JONES is ename column and 10 is deptno column from emp table. In the JONES works in deptno 10, the highlighted text should get repeated for all the rows then we should concatenate the ename information with the deptno value. To concate the two values in SQL you can use || or CONCAT function.

SELECT ename || ' works in deptno ' || deptno
FROM emp
or

SELECT ename CONCAT 'works in deptno' CONCAT deptno
FROM emp