Wednesday , November 13 2019
Home / SQL / Query and Retrieve Data by SELECT statement

Query and Retrieve Data by SELECT statement

The SELECT statement is used to retrieve one or more rows from single or multiple tables or views. The SELECT statement is also known as Query statement. You can retrieve rows from table belonging to not only your own schema but also another user schema.

Prerequisites:

  • If you want to query from your own schema table, you must have SELECT privilege on the table.
  • If you want to query from another user schema table, you must have SELECT ANY TABLE privilege on the table.

Syntax:

SELECT    column_list   FROM   schema_name.table_name    WHERE   conditions;

column_list     is the name of columns that will be displayed.

table_name    is the name of the table from which rows will be retrieved.

schema_name  is the name of the schema that belongs to the table.

WHERE  clause is used to set conditions in SELECT statement.

conditions    is the list of condition that should be met to retrieve data.

Practice:

To complete the practice of SELECT statement, the following steps should be executed.

Step-1: First create a table named CUSTOMERS and insert 6 rows and commit transactions.

CREATE TABLE Customers

(

Customer_Id          number(5),

Customer_Name   varchar2(50),

City                           varchar2(50),

Phone_No              varchar2(20),

Email                       varchar2(50)

);

INSERT   INTO   Customers   VALUES  (101,’Dr. Jessy’,’Dhaka’,’01711000215′,’jessy_dhk@gmail.com’);

INSERT   INTO   Customers   VALUES  (102,’Jibon Chowdhury’,’Dhaka’,’01715850245′,’jibon_dhk@gmail.com’);

INSERT   INTO   Customers   VALUES  (103,’Milon Sarker’,’Sylhet’,’01721087465′,’milon_srk@yahoo.com’);

INSERT   INTO   Customers   VALUES  (104,’Joynal Mia’,’Chittagong’,’01611012125′,’joy_cht@gmail.com’);

INSERT   INTO   Customers   VALUES  (105,’Likhon Khondoker’,’Dhaka’,’01911041217′,’likhon_bd@hotmail.com’);

INSERT   INTO   Customers   VALUES  (106,’Andrew Enderson’,’Gazipur’,’01774110265′,’enderson_dhk@gmail.com’);

COMMIT;

Step-2: Now run the following query statement.

SELECT * FROM Customers;

The results of the SELECT statement.

ID    NAME                     CITY         PHONE_NO            EMAIL

101  Dr. Jessy                  Dhaka      01711000215         jessy_dhk@gmail.com

102 Jibon Chowdhury  Dhaka      01715850245               jibon_dhk@gmail.com

103 Milon Sarker           Sylhet      01721087465             milon_srk@yahoo.com

104 Joynal Mia               Chittagong  01611012125           joy_cht@gmail.com

105 LikhonKhondoker  Dhaka        01911041217         likhon_bd@hotmail.com

106 Andrew Enderson  Gazipur      01774110265         enderson_dhk@gmail.com

From the above result, we see that after executing the SELECT statement, all rows of all columns have been displayed from the table CUTOMERS. In the statement (SELECT * FROM Customers;), the asterisk sign(*) means all rows and all columns. So, if you use the asterisk sign  (*) as the column list of a SELECT statement , the all rows of  all columns will be retrieved and displayed.

Step-3: If you want to retrieve selective columns instead of all columns from a table, you have to specify column names in SELECT statement instead of asterisk sign (*).  Suppose in the CUSTOMERS table already created, there are five columns. We want to display only three columns named CUSTOMER_ID, CUSTOMER_NAME, and PHONE_NO. Execute the statement below.

SELECT Customer_Id,Customer_Name,Phone_No FROM Customers;

Here, three columns have been specified in the column list. The columns have been separated by comma (,) from one to another and after last column, no comma (,) is needed.

The result of the above statement is below:

CUSTOMER_ID  CUSTOMER_NAME               PHONE_NO

101                           Dr. Jessy                                    01711000215

102                          Jibon Chowdhury                    01715850245

103                          Milon Sarker                             01721087465

104                          Joynal Mia                                 01611012125

105                          Likhon Khondoker                   01911041217

106                          Andrew Enderson                    01774110265

From the above result, we see that only three columns named CUSTOMER_ID, CUSTOMER_NAME, and PHONE_NO have been displayed instead of all columns.

Step-4:

Executing the above two SELECT statements, all records or rows of all customers have been displayed. But if you want to retrieve and display for specific customer records, then you have to execute SELECT statement with WHERE clause.

So, the WHERE clause is used to set one or more conditions. If you use WHERE clause with SELECT statement, the rows will be retrieved and displayed that have been met the conditions specified in WHERE clause.

Example:

SELECT Customer_Id,Customer_Name,Phone_No FROM Customers WHERE Customer_Id=105;

In the statement, condition has been set in WHERE clause. So, the customer details will be displayed only for customer id 105.

The result of the above statement is:

CUSTOMER_ID         CUSTOMER_NAME       PHONE_NO

105                                 Likhon Khondoker           01911041217

In the result, the record of customer id 105 is displayed.

This is how; we can use SELECT statement in oracle database to retrieve the records.

Check Also

Unique Constraint

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint: The UNIQUE constraint is used to ensure unique or distinct values …

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

One comment

  1. But a smiling visitant here to share the love (:, btw great pattern. “Better by far you should forget and smile than that you should remember and be sad.” by Christina Georgina Rossetti.

Leave a Reply

Your email address will not be published. Required fields are marked *