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: