Today I will share how to retrieve data by using SQL SELECT statement. Basically SELECT statement are used to identifie the columns to be displayed. When we want to retrieve or extract data from database table then we use SELECT statement. Now I am showing some example of SQL SELECT statement below:
Syntax of SELECT statement:
====================
SELECT * | {[DISTINCT] column|expression [alias]…..}
FROM table;
–>Example-1: Retrieve all data from employees table by using SELECT statement.
SELECT *
FROM employees;
EMPLOYEE_ID LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
----------- -------------- --------------- -------------- --------- -------
198 OConnell DOCONNEL 650.507.9833 21-JUN-07 SH_CLERK
199 Grant DGRANT 650.507.9844 13-JAN-08 SH_CLERK
200 Whalen JWHALEN 515.123.4444 17-SEP-03 AD_ASST
201 Hartstein MHARTSTE 515.123.5555 17-FEB-04 MK_MAN
202 Fay PFAY 603.123.6666 17-AUG-05 MK_REP
203 Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP
204 Baer HBAER 515.123.8888 07-JUN-02 PR_REP
205 Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR
206 Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT
100 King SKING 515.123.4567 17-JUN-03 AD_PRES
–>Example-2: Retrieve last name from employees table by using SELECT statement.
SELECT last_name FROM employees; LAST_NAME ----------- OConnell Grant Whalen Hartstein Fay Mavris Baer Higgins Gietz King
–>Example-3: Retrieve first name, employee id, salary from employees table by using SELECT statement.
SELECT first_name, employee_id, salary FROM employees; FIRST_NAME EMPLOYEE_ID SALARY ---------- ----------- ------- Donald 198 2600 Douglas 199 2600 Jennifer 200 4400 Michael 201 13000 Pat 202 6000 Susan 203 6500 Hermann 204 10000 Shelley 205 12008 William 206 8300 Steven 100 24000
–>Example-4: Retrieve employee id, first name, salary, (salary + 500) *12 from employees table by using SQL SELECT statement.
SELECT employee_id, first_name, salary, (salary+500)*12
FROM employees;
EMPLOYEE_ID FIRST_NAME SALARY (SALARY+500)*12
----------- ------------- --------- ---------------
198 Donald 2600 37200
199 Douglas 2600 37200
200 Jennifer 4400 58800
201 Michael 13000 162000
202 Pat 6000 78000
203 Susan 6500 84000
204 Hermann 10000 126000
205 Shelley 12008 150096
206 William 8300 105600
100 Steven 24000 294000
–>Example-5: Retrieve department id, department name as deptname from departments table by using SQL SELECT statement.
SELECT department_id, department_name AS deptname
FROM departments;
DEPARTMENT_ID DEPTNAME
------------- -----------------
10 Administration
20 Marketing
30 Purchasing
40 Human Resources
50 Shipping
60 IT
70 Public Relations
80 Sales
90 Executive
100 Finance
–>Example-6: Retrieve first name with add last name from employees table by using SELECT statement
SELECT first_name ||' '||last_name AS "Name" FROM employees; Name ------------------ Donald OConnell Douglas Grant Jennifer Whalen Michael Hartstein Pat Fay Susan Mavris Hermann Baer Shelley Higgins William Gietz Steven King Neena Kochhar Lex De Haan Alexander Hunold Bruce Ernst David Austin Valli Pataballa Diana Lorentz Nancy Greenberg Daniel Faviet John Chen
–>Example-7: Retrieve unique department id from employees table by using SQL SELECT statement
SELECT DISTINCT department_id
FROM employees;
DEPARTMENT_ID
-------------
100
30
20
70
90
110
50
40
80
10
60