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:

INSERT
HAVING
GROUP BY
WHERE
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

Oracle SQL Commands: Part 6

Reactions

Comments