Basic SQL SELECT Queries You Must Know

When we work with DATABASE, most of the times we need to write queries to  retrieve data than any other queries. So today i will list out different types of SQL Select statements with example which we use regularly in our programs.
Types of SQL SELECT Queries You Must Know

1)Literal Select

This is very simple select statement in this we will return a simple leteral.
Syntex:

SELECT '<literal>'; 

Example:

SELECT 'Hellow SQL'; 

2)Table Select

This statement use to retrieve data form table.
Syntex:

SELECT <column1>, <column2>  
FROM <table>; 

Example:

SELECT FirstName,LastName  
FROM Person; 

3)Table Select With Alias

This statement use to retrieve data as alias column name.
Syntex:

SELECT <column> AS <aliascolumn>  
FROM <table>; 

Example:

SELECT FirstName AS Name  
FROM Person; 

4)With WHERE Clause

This is used to retrieve based on condition.
Syntex:

SELECT <column1>,<column2>  
FROM <table>  
WHERE <column> = <value>; 

Example:

SELECT FirstName,LastName  
FROM Person  
WHERE FirstName="aravind"; 

5)Using BETWEEN Operator

This is used to retrieve form a range.
Syntex:

SELECT <column1>,<column2>  
FROM <table>  
WHERE <column> BETWEEN <value1> AND <value2>; 

Example:

SELECT FirstName,LastName  
FROM Person  
WHERE DOB BETWEEN '1989-06-08' AND '2013-06-08'; 

6)Using NOT BETWEEN Operator

This is used to retrieve other data but not form range.
Syntex:

SELECT <column1>,<column2>  
FROM <table>  
WHERE <column> NOT BETWEEN <value1> AND <value2>; 

Example:

SELECT FirstName,LastName  
FROM Person  
WHERE DOB NOT BETWEEN '1989-06-08' AND '2013-06-08'; 

7)Using LIKE Operator

This used to retrieve pattern matching data with help of wildcards(%,)
_Syntex:

SELECT <column1>,<column2>  
FROM <table>  
WHERE <column> LIKE "%val%"; 

Example:

SELECT FirstName,LastName  
FROM Person  
WHERE FirstName LIKE '%ra%'; 

8)Using NOT LIKE Operator

This is complement to previous one.
Syntex:

SELECT <column1>,<column2>  
FROM <table>  
WHERE <column> NOT LIKE "%val%"; 

Example:

SELECT FirstName,LastName  
FROM Person  
WHERE FirstName NOT LIKE '%ra%'; 

9)Using Pattern Matches

This is used to retrieve data which matches the regular expression.
Syntex:

SELECT <column1>,<column2>  
FROM <table>  
WHERE <column> LIKE "[pattern]"; 

Example:

SELECT FirstName,LastName  
FROM Person  
WHERE FirstName LIKE '[a-gA-G]'; 

10)Using Predicates

To check two or more column conditions.
Syntex:

SELECT <column1>,<column2>  
FROM <table>  
WHERE <column1>=<value> AND <column1>=<value>; 

SELECT <column1>,<column2>  
FROM <table>  
WHERE <column1>=<value> OR <column1>=<value>; 

Example:

SELECT FirstName,LastName  
FROM Person  
WHERE FirstName="aravind" AND LastName="buddha" ; 

SELECT FirstName,LastName  
FROM Person  
WHERE FirstName="aravind" OR LastName="buddha" ; 

11)Using IN Operator

This operator is very useful to compare multiple values with single column.
Syntex:

SELECT <column1>,<column2>  
FROM <table>  
WHERE <column> IN (<value1>,<value2>); 

Example:

SELECT FirstName,LastName  
FROM Person  
WHERE FirstName IN ("aravind","dimpu","krish"); 

12)Using CONTAINS Operator

In order to use This operator you must enable full-text search feature in SQL server. This is used to search for particular word or phrase in a column.
Syntex:

SELECT <column1>,<column2>  
FROM <tablename>  
WHERE CONTAINS(<indexed column="">,<searchterm>); 

Example:

SELECT Address  
FROM Person  
WHERE CONTAINS(person,("vizag")) ; 

13)Using FREETEXT Operator

This operator is very similar to CONTAINS Operator, But it will return the row in which the search term have similar meaning.
Syntex:

SELECT <column1>,<column2>  
FROM <tablename>  
WHERE FREETEXT(<indexed column="">,<searchterm>); 

Example:

SELECT Address  
FROM Person  
WHERE FREETEXT(person,("vizag")) ; 

14)Using ORDER BY Operator(For Sorting)

This Operator is use to retrieve Sorted data. By default it will be in ascending order. If you want descending order you need to use DESC at the end of statement.
Syntex:

SELECT <column1>,<column2>  
FROM <tablename>  
ORDER BY <column1>[<sort direction="">],<column2> [<sort direction="">] 

Example:

SELECT FirstName,LastName  
FROM Person  
ORDER BY FirstName ; 

Conclusion

These are the very basic select statement we use very often in our projects. You can combine one or more above statement to form new statement. These statement are enough for beginner and intermediate level programmers. Of-course there are still existed more advanced select statement if you want.

Get posts by email

Like what you're reading? Get these posts delivered to your inbox.