Selecting Data

The select statement retrieves the data from the database that meets the criteria mentioned in the SQL select query. The result of the select query is stored in a result table, called the result-set.
Syntax:

SELECT column_name1, column_name2, ... 
FROM table_name;

Example:
Below is a table named Emp_Add.

ssn	fname	lname	address	city	state
512687458	Joe	Smith	83 First Street	Howard	Ohio
758420012	Mary	Scott	842 Vine Ave.	Losantiville	Ohio
102254896	Sam	Jones	33 Elm St.	Paris	New York
876512563	Sarah	Ackerman	440 U.S. 110	Upton	Michigan

Now, let's say you want to see the state from where each employee is. Use the SELECT statement.

SELECT fname, lname, state
FROM Emp_Add; 

Output:

fname	lname	state
Joe	Smith	Ohio
Mary	Scott	Ohio
Sam	Jones	New York
Sarah	Ackerman	Michigan

To explain what you just did, you asked for the all of the data in Emp_Add table, stored in the columns, fname, lname and state.

---

To get all columns of a table without typing all column names, there's another simple method:

SELECT *FROM TableName; 

Each database management system (DBMS) and database software has different methods for logging in to the database and entering SQL commands; see the local computer "guru" to help you get onto the system, so that you can use SQL.

---

Conditional Selection

Sometimes you might want to filter the rows also along with the columns. This is known as conditional selection. For this purpose, WHERE clause is used. This clause is optional and if used, it will display the result-set depending upon the criteria mentioned after it.

Operators

The operators used with the WHERE clause are shown below:

=	Equal
<> or !=	Not Equal
<	Less Than
>	Greater Than
<=	Less Than or Equal To
>=	Greater Than or Equal To
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns
AND & OR	Filter records based on more than one condition

Example:
Consider the table named Emp_Stats given below:

emp_id	salary	benefits	position
010	75000	15000	Manager
105	65000	15000	Manager
152	60000	15000	Manager
215	60000	12500	Manager
244	50000	12000	Staff
300	45000	10000	Staff
335	40000	10000	Staff
400	32000	7500	Entry-Level
441	28000	7500	Entry-Level

Logical operators
If you wanted to see the employee ID of those making at or over $50,000, use the following:

SELECT emp_id
FROM Emp_Stats
WHERE salary >= 50000;

Notice that the >= (greater than or equal to) sign is used, as we wanted to see those who made greater than $50,000, or equal to $50,000, listed together.
Output:

emp_id
------------
010
105
152
215
244

The WHERE description, "salary >= 50000", is known as a condition. The same can be done for text columns:

SELECT emp_id
FROM Emp_Stats
WHERE position = 'Manager';

This displays the ID Numbers of all "Managers". Generally, with text columns, stick to equal to or not equal to conditions.
Now what is the technical difference that you notice in both the above queries i.e. when you select using numeric values and when you select using text values??
The numeric values are not surrounded in quotes while the text values are surrounded by single quotes. Some database systems also accept double quotes. That is why 50000 is not surrounded by quotes while Manager is surrounded by single quotes.

LIKE operator
The following symbols, known as the wildcards are used with the LIKE operator.

Wildcard	Description
%	Represents any possible character (number, letter, or punctuation) or set of characters that might appear at its place.
_	Represents any single character
[list]	Represents any single character from the list.
[^list] or [!list]	Represents any single character but from the list.

Look at the Emp_Add table, and say you wanted to see all people whose last names started with "S"; try:

SELECT fname, lname
FROM Emp_Add
WHERE lname LIKE 'S%';

Output:

fname	lname
Joe	Smith
Mary	Scott

To find those people with LastName ending in "L", use '%L', or if you wanted the "L" in the middle of the word, try '%L%'. The '%' can be used for any characters, in that relative position to the given characters.
NOT LIKE displays rows not fitting the given description.

IN & BETWEEN operators
IN and BETWEEN operators are an easier method of using compound conditions. For example, if you wanted to list all managers and staff:

SELECT emp_id
FROM Emp_Stats
WHERE position IN ('Manager', 'Staff');

or to list those making greater than or equal to $30,000, but less than or equal to $50,000, use:

SELECT emp_id
FROM Emp_Stats
WHERE salary BETWEEN 30000 AND 50000;

To list everyone not in this range, try:

SELECT emp_id
FROM Emp_Stats
WHERE salary NOT BETWEEN 30000 AND 50000;

Similarly, NOT IN lists all rows excluded from the IN list.

And & OR operators
The AND operator joins two or more conditions, and displays the data of a row only if it satisfies all conditions listed (i.e. all conditions hold true). For example, to display all staff whose salary is over $40,000, use:

SELECT emp_id
FROM Emp_Stats
WHERE salary > 40000 AND position = 'Staff';

The result-set will look like:

emp_id
------------
244
300

The OR operator joins two or more conditions, but returns a row if any of the conditions listed hold true. To see all those employees whose salary is less than $40,000 or benefits are greater than $10,000 listed together, use the following query:

SELECT emp_id
FROM Emp_Stats
WHERE salary < 40000 OR benefits > 10000;

The result set will be:

emp_id
------------
010
105
152
215
244
400
441

AND & OR can be combined, for example:

SELECT emp_id
FROM Emp_Stats
WHERE position = 'Manager' AND salary > 60000 OR benefits > 12000;

First, SQL finds the rows where the salary is greater than $60,000 or the benefits is greater than $12,000, then taking this new list of rows, SQL then sees if any of these rows satisfies the condition that the Position column if equal to 'Manager'. Subsequently, SQL only displays this second new list of rows, as the AND operator forces SQL to only display such rows satisfying the Position column condition. Also note that the OR operation is done first.

To generalize this process, SQL performs the OR operation(s) to determine the rows where the OR operation(s) hold true (remember: any one of the conditions is true), then these results are used to compare with the AND conditions, and only display those remaining rows where the conditions joined by the AND operator hold true.

To perform AND before OR, like if you wanted to see a list of managers or anyone making a large salary (>$50,000) and a large benefit package (>$10,000), whether he or she is or is not a manager, use parentheses. Have a look at the following query:

SELECT emp_id
FROM Emp_Stats
WHERE position = 'Manager' OR (salary > 50000 AND benefit > 10000);

While executing this query the SQL first performs the conditions mentioned inside the parentheses. After this it proceeds to check the conditions mentioned outside the parentheses. Inside the parentheses, it performs the conditions following the normal pattern.