Oracle select statements tutorial for Java Beginners

Select statement comes under DQL(Data Query Language) statement. It is used to fetch data from Database. Data fetched from table stored in result set. Now we will explore the different option available with select statement.

 


 

Dummy Table

We will use JBT_TABLE and populate table with dummy data.

 

Create Dummy Table Script

CREATE TABLE jbt_table ( firstname VARCHAR2(20), phone VARCHAR2(20), address VARCHAR2(50), dept VARCHAR2(20), sallary INTEGER, lastname VARCHAR2(20) )

 Dummy Table Data Population Script

INSERT INTO jbt_view
            (firstname, phone, address, dept, sallary, lastname
            )
     VALUES ('Simon', '12-456-256', 'My Address1', 'HR', 25363, 'Clinton'
            );
INSERT INTO jbt_view
            (firstname, phone, address, dept, sallary, lastname
            )
     VALUES ('Dunja', '12-456-256', 'Address2', 'Payroll', 98657, 'Pacaric'
            );
INSERT INTO jbt_view
            (firstname, phone, address, dept, sallary, lastname
            )
     VALUES ('Jayesh', '12-456-256', 'address 3', 'IT', 19687, 'Patel'
            );
INSERT INTO jbt_view
            (firstname, phone, address, dept, sallary, lastname
            )
     VALUES ('Vivekanand', '12-456-256', 'My Address1', 'HR', 56329, 'Rai'
            );
INSERT INTO jbt_view
            (firstname, phone, address, dept, sallary, lastname
            )
     VALUES ('Vivekanand', '12-456-256', 'My Address1', 'IT', 75623, 'Gautam'
            );
INSERT INTO jbt_view
            (firstname, phone, dept, sallary, lastname
            )
     VALUES ('Alisha', '12-456-256', 'HR', 85672, 'Chinoy'
            );
INSERT INTO jbt_view
            (firstname, phone, address, dept, sallary, lastname
            )
     VALUES ('Equbal', '12-456-256', 'address 3', 'it', 91257, 'Nayyar'
            );

 


 

Syntax of Query:

Show All Record

SELECT *
  FROM jbt_table;

Fetch Specific Column Data

Query to fetch specific column from table. Specify the column name in query which you want to fetch

SELECT firstname, lastname FROM jbt_table

Change Default Title of ResultSet

Alias can be used to display desired title of the result set.

SELECT firstname "First Name", lastname "Last Name" FROM jbt_table

Query to fetch record from two different tables

select c.name, a.name,a.pincode  from contact c, address a

Condition Bases Query Syntax

Query to show the number of employee in particular department. (e.g employee in IT department)

SELECT *
  FROM jbt_table
 WHERE dept = 'IT'

Note this search is case sensitive. So if there is any record in table where dept name is ‘it‘(In small case) then it will not be picked up in above query to pick those record use lower / upper function in query

SELECT *
  FROM jbt_table
 WHERE UPPER (dept) = 'IT';

Or

SELECT *
  FROM jbt_table
 WHERE LOWER (dept) = 'it'

 


 

Query to show the number of employee in each department.

SELECT   dept, COUNT (firstname) "No Of Employee"
    FROM jbt_table
GROUP BY dept

This query will work fine in normal condition(When department names are either in lower or upper case)but will fail in case, records have both lower and upper case entry for department(IT & it). To overcome this problem use below query.

SELECT   LOWER (dept) department, COUNT (firstname) "No Of Employee"
    FROM jbt_table
GROUP BY LOWER (dept)

Above query will work fine for both kind of dept name(Lower / Upper)

 


 

Show the details of employees who have duplicate names. Like in this  case show the detail of employee having name ‘Vivekanand

SELECT *
  FROM jbt_table
 WHERE firstname IN (SELECT   firstname
                         FROM jbt_table
                     GROUP BY firstname
                       HAVING COUNT (firstname) > 1)

 


 

Query to identify the duplicate name

SELECT   firstname
    FROM jbt_table
GROUP BY firstname
  HAVING COUNT (firstname) > 1

or

SELECT firstname
  FROM (SELECT ROWID, firstname,
               ROW_NUMBER () OVER (PARTITION BY firstname ORDER BY firstname)
                                                                           rn
          FROM jbt_table)
 WHERE rn > 1

 


 

Query to show the detail of those employees whose address is empty or null. Like in this case Employee Ramesh record should be displayed

SELECT *
  FROM jbt_table
 WHERE address IS NULL

 


 

Query to show the information of all employees in IT department. Where Departement name should be shown as “Information Technology” instead of ‘IT’

SELECT firstname, phone, address,
       DECODE (UPPER (dept), 'IT', 'Information Technology') department,
       sallary
  FROM jbt_table
 WHERE LOWER (dept) = 'it'

 


 

Query to show the Information of all employees from all department. Where Departement name should be shown as “Information Technology” if dept is ‘IT’ else “Other”.

SELECT firstname,
       DECODE (UPPER (dept),
               'IT', 'Information Technology',
               'Other'
              ) department,
       phone, address, sallary
  FROM jbt_table

 


 

Query to show the information of all employees from all department. Where Departement name should be shown as “Information Technology” for’IT‘ , “Human Resource” for ‘‘HR ‘  & ‘Payroll Department
for ‘payroll‘.

SELECT firstname,
       DECODE (UPPER (dept),
               'IT', 'Information Technology',
               'HR', 'Human Resoource',
               'PAYROLL', 'Payroll Department'
              ) department,
       phone, address, sallary
  FROM jbt_table

 


 

Query to show employee details in order of increasing sallary.

SELECT   *
    FROM jbt_table
ORDER BY sallary ASC

 

Other Example of Select Statement

select t.* from JBT_TABLE t

select T.name “USER NAME” FROM JBT_TABLE T

SELECT * FROM JBT_TABLE WHERE upper(NAME)  like ‘%VIVE%’

SELECT * FROM JBT_TABLE WHERE LOWER(NAME)  like ‘%vive%’

SELECT * FROM JBT_TABLE WHERE name is null(not null)

SELECT * FROM JBT_TABLE WHERE name ^= ‘Viveka’

SELECT * FROM JBT_TABLE WHERE name != ‘Viveka’

SELECT * FROM JBT_TABLE where name not like ‘vivek%’

SELECT T.name FROM JBT_TABLE T where name like ‘punit/_gupta’ escape ‘/’
(Here _ will not be treated as regular part but will be ignore by escape)

SELECT * FROM JBT_TABLE WHERE phone >=123522;

SELECT * FROM JBT_TABLE where lower(name) like ‘%vive%’ and phone >= 1992;

SELECT * FROM JBT_TABLE where lower(name) like ‘%vive%’ or phone >= 1992;

SELECT * FROM JBT_TABLE where name not in (‘Viveka’, ‘Vivek’, ‘punit_gupta’)

SELECT * FROM JBT_TABLE WHERE phone between 100 and 256965896

SELECT name FROM JBT_TABLE group by name having name = ‘Viveka’

By | 2017-07-21T21:45:29+00:00 June 16th, 2012|Oracle|2 Comments

About the Author:

2 Comments

  1. jagan April 17, 2013 at 1:48 pm - Reply

    neat and simple article. . . keep up the gud work. . .

  2. Mathan September 26, 2015 at 12:34 am - Reply

    Hello Sir,
    I started reading your tutorials..its very nice and I can able to understand the concepts much easily..It il be very helpful for beginners as like me..Thank you very much sir..

Leave A Comment