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