Techumber
Home Blog Work

Basic SQL SELECT Queries You Must Know

Published on April 18, 2013

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.