CodesJava

Easy learning with example program codes

FROM clause in Oracle

ORACLE FROM
Oracle FROM clause is used with the Oracle SELECT statement and has a mandatory existence, as it is used to specify the tables from which the desired data needs to be retrieved.

Syntax: To select all fields from a table.

SELECT * 
FROM table_name;  

Parameters:
table_name: It is used to specify the name of the table from which you want to retrieve the records.
Example: Selecting all fields from a table.

Students table:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        5
2	        Smiley	        13
3	        Happy	        11

Query:

SELECT * FROM students;  

Output:

ID	NAME	AGE
1	Joy	5
2	Smiley	13
3	Happy	11

3 rows returned in 0.02 seconds

Explanation:
The ‘students’ is an already existing table. Here we are selecting all the fields of the ‘students’ table and thus the record will be retrieved as it is.

Syntax: To select specific fields from a table.

SELECT expressions  
FROM table_name
WHERE conditions;   

Parameters:
expressions: It is used to specify the columns or calculations to be retrieved.
table_name: It is used to specify the name of the table from which you want to retrieve the records.
conditions: It is used to specify the conditions to be strictly followed for selection.

Example: Selecting specific fields from a table.
Students table:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        5
2	        Smiley	        13
3	        Happy	        11

Query:

SELECT name, age  
FROM students  
WHERE  age > 10  
ORDER BY name; 

Output:

ID	NAME	AGE
3	Happy	11
2	Smiley	13

2 rows returned in 0.01 seconds

Explanation:
The ‘students’ is an already existing table. Here we are selecting the specific fields ‘name’ and ‘age’ from the ‘students’ table with a condition that the selected row must have an age greater than 10. The ORDER BY clause defines the order in which the data needs to be displayed after retrieval. Here we are asking to order it by name.

Syntax: To select specific fields from multiple tables.

SELECT expressions  
FROM table1
INNER JOIN table2 
ON join_predicate;

Parameters:
expressions: It is used to specify the columns or calculations to be retrieved.
table1: It is used to specify the name of the first table from which you want to retrieve the records.
table2: It is used to specify the name of the second table from which you want to retrieve the records.
join_predicate: It is used to specify the condition for joining tables.

Example: Selecting specific fields from multiple tables.
Students table:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        5
2	        Smiley	        13
3	        Happy	        11

Query:

SELECT students.name, teachers.subject
FROM students  
INNER JOIN teachers  
ON students.student_id = student_id  
ORDER BY name;     

Output:

NAME	SUBJECT
Happy	English
Happy	Hindi
Joy	English
Joy	Hindi
Smiley	English
Smiley	Hindi

6 rows returned in 0.03 seconds
Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status