Post
Ask Question

Oracle SQL Commands: Part 6

3. SELECT Command
 
While using Oracle when we need to extract information regarding data from the database, we use the SELECT statement. Using the SELECT statement, we can retrieve data from one column, multiple columns and all columns from a table. The asterisk can be used to SELECT all columns of a table. Before writing a SELECT statement, we must determine which table or tables contain the information that we need from the database.
 
Syntax

Select Column_name

FROM table_name;
 
Example

1. The following is a sample of a Select statement retrieving information from a single column.
 
Syntax (same as Select)

Select Column_name

FROM table_name;
 
Example

Assume the following STUDENT table.

Stud_ID Stud_Name Age Subject
1001 Mira 17 Oracle
1002 Ipshita 19 Oracle
1003 Aayushi 18 Unix
1004 Anjali 19 Database
1005 Swati 20 Unix

Query

Select Stud_name

FROM Students;
 
Result

Stud_Name

Mira
Ipshita
Aayushi
Anjali
Swati


2. The following is a sample of a Select statement retrieving information from Multiple columns.
 
Syntax
 

Select Column1, column2, column3

FROM table_name;


Example

Assume the following CUSTOMERS table.

Cust_ID Cust_Name ContactNo Location
111 Mira 475617 Kanpur
222 Ipshita 845648 Lucknow
333 Aayushi 874875 Amritsar
444 Anjali 896575 Mumbai
555 Swati 939575 Delhi

Query

SELECT Cust_Name, Location

FROM Customers;
 
Result
 

Cust_Name Location
Mira Kanpur
Ipshita Lucknow
Aayushi Amritsar
Anjali Mumbai
Swati Delhi


3. The following is a sample of a Select statement retrieving information from all columns.
 
Syntax
 

Select *

FROM table_name;


Example

Assume the following EMPLOYEES table.

Emp_Id Emp _Firstname Emp _Lastname Age Designation Hire_Date
00011234 Ankit Singh 52 Project Manager 20th Oct' 1999
00011247 Mohit Chauhan 43 Projeact Lead 15th Apr' 2003
00011239 Karan Sagu 51 Sr. Software Engg. 1st Jan' 2008
00011252 Seema Lambha 32 Software Engg. 25th Jan' 2012
00011261 Ritu Bohra 37 Tech Lead 10th Aug' 2005

Query

SELECT *

FROM Employees;
 
Result
 

Emp_Id Emp _Firstname Emp _Lastname Age Designation Hire_Date
00011234 Ankit Singh 52 Project Manager 20th Oct' 1999
00011247 Mohit Chauhan 43 Projeact Lead 15th Apr' 2003
00011239 Karan Sagu 51 Sr. Software Engg. 1st Jan' 2008
00011252 Seema Lambha 32 Software Engg. 25th Jan' 2012
00011261 Ritu Bohra 37 Tech Lead 10th Aug' 2005


We can use the SELECT with various other clauses like:

  1. INSERT
  2. HAVING
  3. GROUP BY
  4. WHERE
  5. SAMPLE
Sub_ID Sub_Name Mark_Obt Tot_Marks
10 Hindi 150 200
20 English 140 200
30 Science 125 200
40 Maths 138 200
50 S.Study 110 200

1. SELECT with INSERT Clause
 
The SELECT Clause in Oracle can be used within an INSERT statement to insert one or more rows into a table from the result table generated from a SELECT statement.
 
Syntax

INSERT INTO <table_name>

<SELECT Statement>;

Example

Student Table
 

Stud_ID Stud_Name Age Email Sub_id Sub_name
1 Sapna 15 [email protected] 10 Hindi
2 Shweta 14 [email protected] 20 English
3 Swati 19 [email protected] 30 Science
4 Preeti 17 [email protected] 40 Maths
5 Divya 20 [email protected] 50 S.Study


Suppose we want to create a table named New_Student from the preceding table STUDENT having a detail of Student-Identity, name, Email and Subject name. For this the query will be:
 
Query
 

CREATE TABLE New_Student

(

Stud_id       Number NOT NULL,

Stud_name     VARCHAR2(20) NOT NULL,

Email         VARCHAR2(30) NOT NULL,

Sub_name      VARCHAR2(20)

);

 

INSERT INTO New_Student

SELECT Stud_id, Stud_name, Email, Sub_name     

FROM Student;

 

SELECT * FROM New_Student;


Result

NEW_STUDENT
 

Stud_ID Stud_Name Email Sub_name
1 Sapna [email protected] Hindi
2 Shweta [email protected] English
3 Swati [email protected] Science
4 Preeti [email protected] Maths
5 Divya [email protected] S.Study


2. SELECT with HAVING Clause
 
The HAVING clause is used in place of a WHERE clause with the GROUP BY statement in the SELECT clause because group functions cannot be used in a WHERE Clause but can be used in a HAVING clause.

Syntax
 

SELECT column1, column2, ... column_n, aggregate_function (expression)

FROM tables

WHERE predicates

GROUP BY column1, column2, ... column_n

HAVING condition1 ... condition_n;
 
Example

Customer Table
 

Cust_ID State Item Price
44332 Maharashtra Soap 300
44338 Uttar Pradesh Talc 480
44336 Himachal Pradesh Blanket 226
44337 Himachal Pradesh Kitchen ware 5200
44339 Maharashtra Medicines 1200


The preceding table displays the Customers in each unique state that have more than one Customer in the same state. Use the following query:

Query
 

SELECT COUNT(*)STATE

FROM CUSTOMER

GROUPBY STATE

HAVING COUNT(*)>1;


Result
 

Count(*) State
2 Maharashtra
2 Himachal Pradesh


3. SELECT with GROUP BY Clause
 
The GROUP BY clause is used to group the results occuring from the collection of data from multiple records. It will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on one or more columns.

Syntax
 

SELECT COUNT(*) COLUMN NAME

FROM TABLE NAME

GROUP BY COLUMN;
 
Example

Employee Table
 

Emp_Id Emp _name Salary Designation City
111 ABC 42K Manager Ahemdabad
222 XYZ 24K Clerk Noida
333 ASD 50K Manager Noida
444 QWE 20K Clerk Itawa
555 PQR 38K P.Officer Kutuk
666 RST 51K Manager Bareilly
777 JKL 47K F.Officer Madurai
888 GHI 50K Manager Bareilly


From the preceding table the Employees in each unique designation is selected and then the number of employees on each are displayed. Use the following query:

Query
 

SELECT COUNT (*) Designation

FROM CUSTOMER

GROUP BY Designation;


Result
 

Designation City
Manager 4
Clerk 2
P.Officer 1
F.Officer 1

Previous article: Oracle SQL Commands: Part 5
Next article:  Oracle SQL Commands: Part 7


Similar Articles