Basic SQL SELECT Statements in Oracle

Basic SQL SELECT Statements in Oracle:

SQL Statement has the following features:

• Projection
• Selection
• Join

Projection:

Projection simply means that whether we want to query a few columns or all columns from a table.

Selection:

Selection means selecting rows from a table. We can use multiple filters to restrict number of rows from a table.

Join:

Join means that related data are stored in multiple tables and we want to fetch data from all those tables through a common link. This link is called the join.

Example of Projection:

Selecting all columns from a table:

We have an example table called EMP_SELECT.

SELECT * FROM EMP_SELECT
ORDER BY EMPLOYEE_ID;

Selecting specific columns from a table:

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY
FROM EMP_SELECT
ORDER BY EMPLOYEE_ID;

Example of Selection:

Restricting table rows by using single filter criteria:

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

Restricting table rows by using multiple filter criteria:

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
AND SALARY > 3000
ORDER BY EMPLOYEE_ID;

Example of Join:

We have employee data:

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, HIRE_DATE, SALARY, DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

We have department data:

SELECT * FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 30;

Now we will show the combined data by using join:

SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.JOB_ID, A.HIRE_DATE, A.SALARY, A.DEPARTMENT_ID, B.DEPARTMENT_NAME, B.MANAGER_ID, B.LOCATION_ID
FROM EMP_SELECT A,
     DEPARTMENTS B
WHERE A.DEPARTMENT_ID = 30
AND A.DEPARTMENT_ID = B.DEPARTMENT_ID
ORDER BY A.EMPLOYEE_ID;

Now we can see that the data from both employees and departments are visible in the output.

Arithmetic Expressions:

Arithmetic expressions are applicable for Number and Date data types only.

Operators	Description
+	Add
–	Subtract
*	Multiply
/	Divide

Add:

Example of Add with Number data type:

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY, (SALARY + 300) NEW_SALARY, DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

Example of Add with Date data type:

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY, HIRE_DATE, (HIRE_DATE + 30) NEXT_MONTH_AFTER_HIRE_DATE, DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

Subtract:

Example of Subtract with Number data type:

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY, (SALARY - 300) OLD_SALARY, DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

Example of Subtract with Date data type:

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY, HIRE_DATE, ROUND((SYSDATE - HIRE_DATE)/365, 0) NO_OF_YEARS, DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

Multiply:

Example of Multiply with Number data type:

SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY, (SALARY *.05) COMMISSION, DEPARTMENT_ID
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

Example of Multiply with Date data type:

Date data type doesn't support multiplication.

Divide:

Example of Divide with Number data type:

SELECT DEPARTMENT_ID, SUM(SALARY) DEPT_WISE_TOTAL_SALARY, COUNT(EMPLOYEE_ID) DEPT_WISE_EMP_COUNT, ROUND(SUM(SALARY)/COUNT(EMPLOYEE_ID), 0) DEPT_WISE_AVERAGE_SALARY
FROM EMP_SELECT
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;

Example of Divide with Date data type:

Date data type doesn't support division.

Column Aliases:

By default oracle using the table's column name as heading of the column in output data. However we can rename the default name of the column by using aliases.

SELECT EMPLOYEE_ID EMPID, FIRST_NAME FNAME, JOB_ID JOB, SALARY SAL, (SALARY *.05) COMMISSION, DEPARTMENT_ID DEPT
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

We can use spaces in alias as well by using double quotes.

SELECT EMPLOYEE_ID "EMPPLOYEE ID", FIRST_NAME "FIRST NAME", JOB_ID JOB, SALARY SAL, (SALARY *.05) "COMMISSION PCT", DEPARTMENT_ID "DEPT ID"
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

Concatenation Operator:

Concatenation operator is used to join two columns or two strings.

Example of Concatenation operator to join two columns:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, FIRST_NAME||' '||LAST_NAME FULL_NAME, JOB_ID, SALARY, HIRE_DATE, DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

Example of Concatenation operator to join two strings:

SELECT 'Employee ID -> '||EMPLOYEE_ID EMPLOYEE, FIRST_NAME, LAST_NAME, FIRST_NAME||' '||LAST_NAME FULL_NAME, JOB_ID, SALARY, HIRE_DATE, DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

Literal Character Strings:

Literal value is a character, number or date included in the select list. Character or date will be enclosed by single quotes. Literal will be returned against each row.

SELECT 'Employee ID -> '||EMPLOYEE_ID||', Name -> '||FIRST_NAME||' '||LAST_NAME||' works in department '|| DEPARTMENT_ID EMP_DESCRIPTION
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30
ORDER BY EMPLOYEE_ID;

Removing Duplicate Rows:

If we select a column which is repeated with same value in each row then we can call that column value as duplicate.

SELECT DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30;

To remove the duplicate rows we can use the DISTINCT keyword.

SELECT DISTINCT DEPARTMENT_ID 
FROM EMP_SELECT
WHERE DEPARTMENT_ID = 30;

RELATED TOPICS:

• Index Scanning Methods In Oracle