>> Select statements in SQL

 Basic SQL statements with 'Select', 'Where', 'And', 'select desired columns only':

 

Let's take below table:

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
David	Marketing	2000	Chicago	Manager	IL	12-Dec-00
Richard	Sales	1000	Baltimore	Salesman	MD	1-Jan-08
Smith	Resources	1500	Dallas	HR	TX	5-Jan-06
Ram	Resources	1200	Dallas	Assistant	TX	5-Jun-06
Gopi	Sales	1100	Austin	Marketer	TX	9-Feb-08
Britni	Technical	1800	Baltimore	Assistant	MD	1-Feb-09
Adam	Technical	2200	Baltimore	Lead	MD	1-Mar-10
Mike	Executive	2500	Chicago	Director	IL	1-Aug-10
James	Marketing	1200	Reston	Accountant	VA	20-Aug-14
Juli	Executive	2200	Arlington	Manager	VA	21-Aug-12

 

1. To see all the contents of the table .

Syntax:

Select * from {table name};

Query:

Select * from EMP_INFO;  

The result will be:

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
David	Marketing	2000	Chicago	Manager	IL	12-Dec-00
Richard	Sales	1000	Baltimore	Salesman	MD	1-Jan-08
Smith	Resources	1500	Dallas	HR	TX	5-Jan-06
Ram	Resources	1200	Dallas	Assistant	TX	5-Jun-06
Gopi	Sales	1100	Austin	Marketer	TX	9-Feb-08
Britni	Technical	1800	Baltimore	Assistant	MD	1-Feb-09
Adam	Technical	2200	Baltimore	Lead	MD	1-Mar-10
Mike	Executive	2500	Chicago	Director	IL	1-Aug-10
James	Marketing	1200	Reston	Accountant	VA	20-Aug-14
Juli	Executive	2200	Arlington	Manager	VA	21-Aug-12

2. To see selected/desired columns only :

Syntax:

Select columnName1 , columnName2, columnName3,....from {table name}; -- desired columns only

Query:

Select NAME , SALARY , DEPARTMENT from EMP_INFO ;

The result will be:

NAEM	SALARY	DEPARTMENT
David	2000	Marketing
Richard	1000	Sales
Smith	1500	Resources
Ram	1200	Resources
Gopi	1100	Sales
Britni	1800	Technical
Adam	2200	Technical
Mike	2500	Executive
James	1200	Marketing
Juli	2200	Executive

3. Select with filter using 'Where'
We use  "where" to filter data and display in output :

Syntax:

Select columnName1 , columnName2, columnName3,....from {table name} -- desired columns only

where condition;

Suppose you want to see DATA for the state "MD" only:

Query:

Select * from EMP_INFO

where STATE= 'MD';

Result will be:

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
Richard	Sales	1000	Baltimore	Salesman	MD	1-Jan-08
Britni	Technical	1800	Baltimore	Assistant	MD	1-Feb-09
Adam	Technical	2200	Baltimore	Lead	MD	1-Mar-10

4. To add more than one filter conditions(using 'where' and 'and'): .

Syntax:

Select columnName1 , columnName2, columnName3,....from {table name} -- desired columns only

where condition1
AND condition2
AND condition3
.
.
so on until last condition ;

Example 1:
Below query will display the employees having salary above 1500 with position as Manager  :

Select * from EMP_INFO
where SALARY>1500
AND POSITION= 'Manager';  (Note: There are two conditions in this query.)

Result will be :

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
David	Marketing	2000	Chicago	Manager	IL	12-Dec-00
Juli	Executive	2200	Arlington	Manager	VA	21-Aug-12

Example 2:
Below query will display the employees having salary above 1500 and the position is not Manager  :

Select * from EMP_INFO
where SALARY>1500
AND POSITION  != 'Manager';  (Note: "!" before the "=" means : not equal to) 

Result will be :

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
Britni	Technical	1800	Baltimore	Assistant	MD	1-Feb-09
Adam	Technical	2200	Baltimore	Lead	MD	1-Mar-10
Mike	Executive	2500	Chicago	Director	IL	1-Aug-10

Example 3:
Below query will filter and display  Name , Salary, Position, Joindate of the employees having salary greater than 1500 and joindate after January 1st 2010.

Select NAME, SALARY, POSITION, JOINDATE from EMP_INFO
where SALARY>1500
AND JOINDATE> '01-JAN-2010';  (Note: This example shows that you can take your required columns only, if you do not need to select all the columns. and add filter as required.)

Result will be :

NAME	SALARY	POSITION	JOINDATE
Adam	2200	Lead	1-Mar-10
Mike	2500	Director	1-Aug-10
Juli	2200	Manager	21-Aug-12

Example 4:
Below query will filter and display  data where the salary is greater than 2000 , join date is greater than January 1st 2010 and the Department is Executive.

Select * from EMP_INFO
where SALARY>2000
AND JOINDATE > '01-JAN-2010'
AND DEPARTMENT = 'Executive';  (Note: This is how you can add more conditions with AND, in this query there are 3 conditions.)

Result will be :

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
Mike	Executive	2500	Chicago	Director	IL	1-Aug-10
Juli	Executive	2200	Arlington	Manager	VA	21-Aug-12

5. You can display the columns in the output/result in any order you want:

Suppose you want to see below information with the columns in this order:

STATE, DEPARTMENT, NAME,SALARY, JOINDATE, POSITION, LOCATION

Select STATE, DEPARTMENT, NAME,SALARY, JOINDATE, POSITION, LOCATION from EMP_INFO;

Result will be :

STATE	DEPARTMENT	NAME	SALARY	JOINDATE	POSITION	LOCATION
IL	Marketing	David	2000	12-Dec-00	Manager	Chicago
MD	Sales	Richard	1000	1-Jan-08	Salesman	Baltimore
TX	Resources	Smith	1500	5-Jan-06	HR	Dallas
TX	Resources	Ram	1200	5-Jun-06	Assistant	Dallas
TX	Sales	Gopi	1100	9-Feb-08	Marketer	Austin
MD	Technical	Britni	1800	1-Feb-09	Assistant	Baltimore
MD	Technical	Adam	2200	1-Mar-10	Lead	Baltimore
IL	Executive	Mike	2500	1-Aug-10	Director	Chicago
VA	Marketing	James	1200	20-Aug-14	Accountant	Reston
VA	Executive	Juli	2200	21-Aug-12	Manager	Arlington

NEXT

Go To Main SQL Page




.