Oracle select statements tutorial for Java Beginners

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

---

Dummy Table

We will use JBT_TABLE and populate the 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 columns from a table. Specify the column name in the query which you want to fetch

SELECT firstname, lastname FROM jbt_table

Change Default Title of ResultSet

An alias can be used to display the 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 employees in a 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 employees 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 departments. Where Departement name should be shown as “Information Technology” if the 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 departments. 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 salary.

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’