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