EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Select in Oracle
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Select in Oracle

By Priya PedamkarPriya Pedamkar

Select in Oracle

Introduction to Select in Oracle

Oracle is one of the most extensively used databases in the industry. The most commonly used query is a SELECT query. This query is used to retrieve data from one or more tables in the database. A SELECT query is not just used alone but with it, many conditions, clauses and inner queries are used to get data from databases in the industry. SELECT query does not manipulate any data in the table on which it is executed. Select keyword in oracle is applied for fetching a set of data, which can be used singly or by combining other conditional statements as filters. When a select statement is as ‘SELECT * from <Table_Name>’, the whole table is displayed as the result-set, whereas select statement as ‘SELECT Column_1, Column_2 from <Table_Name>’ displays the contents of only the column_1 & column_2 of ‘<Table_Name>’. ‘Where’, ‘Group By’, ‘Order By’ conditions can also be applied at the end of the Select statement.

Syntax:

SELECT expressions
FROM tables
[WHERE conditions];

The first two lines in the syntax is a compulsory part of syntax but [WHERE conditions] is optional as it is used if we want to extract data from one or more tables based on some conditions. The ‘expressions’ in the syntax represents columns of the table. If we want to extract all fields from the table we put ‘*’ in place of ‘expressions’ in the syntax otherwise we put the column names.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Examples to Implement Select in Oracle

Below are the different examples of select in oracle:

1. SELECT all fields without WHERE condition

To select all fields from tables: We will use ‘*’ character to signify that we want to extract all fields from a particular table.

Query:

SELECT * from employee;

This query will fetch all the records from the table as there is no condition clause attached to it.

Output:

Select in Oracle eg1

2. SELECT all fields with WHERE condition

We will now use where condition on the same earlier used query. In this query, we only want the details of a particular employee. To achieve this we will use the where condition. let us look at the query

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,408 ratings)

Query:

SELECT * from employee WHERE EMPLOYEE_ID= 'AD001';

We are using EMPLOYEE_ID in this query as it is the primary key in this table and so it is unique.

Output:

Select in Oracle eg2

3. SELECT a single field from a table

In this example, we are going to use a SELECT statement to select a few fields but not all fields from a table. Let us look at the query.

Query:

SELECT employee_id, name from employee;

This query will display only two columns because we have not used ‘*’ after SELECT in this query.

Output:

Select eg3

4. SELECT fields from multiple tables

In this example, we are going to use a SELECT statement to retrieve data from more than one table using JOIN. Let us look at the query.

Query:

SELECT employee.name, vehicle.vehicle_name from employee INNER JOIN vehicle ON employee.vehicle_id = vehicle.vehicle_id;

Here in this query, we are using the SELECT statement which joins two tables based on the condition that vehicle id from employees table should match vehicle id from the vehicle table. The query displays the employee name from the employee table and vehicle name from the vehicle table.

Output:

Select eg4

5. Display records in order using SELECT

We can also display particular records in ORDER which can be ascending or descending by using the ORDER BY clause with the query. We will look at both ascending and descending order queries.

a. Query for ascending order

SELECT * from employee ORDER BY name ASC;

In this query we are displaying all records and the result set is sorted by name in ascending order.
The below screenshot shows the output when query is executed in SQL Developer.

Output:

Select in Oracle eg5 ascending

b. Query for descending order

SELECT * from employee ORDER BY name DESC;

In this query, we are displaying all records from the table and the result set is sorted by name in descending order
The below screenshot shows the output when query is executed in SQL Developer.

Output:

eg5 decending

6. SELECT query with GROUP BY clause

We use the GROUP BY clause with SELECT statement when we want to get records based on groups. So basically it groups rows that have the same values. It is used generally in conjugation with aggregate functions. It is useful in producing summary reports.

We will now look at an example to see how we can use GROUP BY with SELECT statement in oracle database.

Query:

SELECT COUNT(EMPLOYEE_ID),VEHICLE_NAME FROM employee GROUP BY VEHICLE_NAME;

So in the above example basically we are finding the number of employees using a particular brand of car. GROUP BY groups the records based on the type of car and then we use aggregate function COUNT to calculate the number of employees in each group by using the column employee_id as it is the primary key of the table.

Output:

eg6

7. SELECT query with the HAVING clause

The having clause is used with a select statement where we want to have some conditions as where keyword cannot be used directly with aggregate functions. That is the reason the having clause was added in SQL. We are going to see an example of how we can use the having clause with a select statement.

Query:

SELECT COUNT(EMPLOYEE_ID),VEHICLE_NAME FROM employee GROUP BY VEHICLE_NAME HAVING COUNT(EMPLOYEE_ID)> 2;

As we can see that we have added the HAVING clause with the GROUP BY due to which we are able to put a condition that only vehicles with employees more than two are to be displayed. This is where the HAVING clause becomes useful.

Output:

 eg7

If we see the output we get only two records instead of five which we got when we used GROUP BY function.

Conclusion

In this article, we learned the syntax and the reason why we use SELECT and also the various ways in which we can write a SELECT query in SQL and also where and for what reason we should use them.

Recommended Articles

This is a guide to Select in Oracle. Here we discuss the introduction and different examples of select query in SQL with syntax. You may also look at the following articles to learn more –

  1. Joins in Oracle
  2. Oracle Data Warehousing
  3. Agile Oracle
  4. What Is Oracle Database
  5. Oracle Operators | Examples
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more