Oracle SELECT command

SELECT command is used fetch data from table. Using SELECT command we can retrieve the data in two ways.
1. SELECTION: Retrieve the data based on the condition.
2. JOINS: Retrieve the data from more than one table.

Syntax:

SELECT column1, column2, …. FROM table_name
WHERE condition
GROUP BY column_name
HAVING condition
ORDER BY column_name[asc/desc];

Create table using existing table with SELECT statement:
Syntax: CREATE NEW_TABLE AS SELECT* FROM old_table_name;
Example;
CREATE emp1 as SELECT * FROM emp;
SELECT*FROM emp1;

Create table using existing table without copying data(only structure will be copied):
Syntax:
CREATE NEW_TABLE AS SELECT* FROM old_table_name
where False condition;

Example:
CREATE emp2 as SELECT * FROM emp
WHERE 1=2;

Arithmetic operators can be used in number, date datatype columns
Examples:
Write a query to display ename, sal, Annual Salary from employee table
SELECT ename, sal, sal*12 as “Annual Salary” from emp;

Write a query to display the employees except job as clerk.
SELECT * FROM emp where job<>‘CLERK’;

Write a query to display employees who are getting salary more than 2000.
SELECT* FROM emp WHERE sal>2000;

DISTINCT Clause: Using distinct command, we can remove the duplicates from the result set. The distinct clause can be used with select statements only.

Syntax:
SELECT DISTINCT columns
FROM table_name
WHERE condition;

Example:

SELECT DISTINCT job from emp;

Powered by k2schools