Beginning SQL - Retrieving Data from a Database (
Page 2 of 4 )
For starters, build a database with a table called Employees. Give the table four columns: FirstName, LastName, Position, Salary. Now enter the following data into the table.
|
First Name |
Last Name |
Position |
Salary |
|
Larry |
Smith |
President |
$100,000 |
|
Homer |
Sampson |
CEO |
$75,000 |
|
Your |
Name |
Whipping Boy |
$15,000 |
In the above table you can see that we have three text fields and a currency field (and also, that you make way less than everyone else; but that's okay...once you learn SQL, you'll be sleeping on a quilt made of dollar bills).
Say you want to see a list of the last names of every employee within the company. For that you would need the most useful Statement in SQL, the Select statement.
Select LastName from Employees;
This will return the following result:
|
Last Name |
|
Smith |
|
Sampson |
|
Your Last Name |
The statement Select literally tells the database to choose data. Typing LastName tells the database which column the data is in, and from Employees tells it which table the data is in. The semi-colon (;) at the end tells the database to separate your statement from other statements.
Note that unlike many languages, SQL statements are not case-sensitive.
To select more than one column from the table, you would use the following code:
Select FirstName, LastName from Employees;
This returns the results:
|
First Name |
Last Name |
|
Larry |
Smith |
|
Homer |
Sampson |
|
Your |
Name |
As you can see, we now have a list of all of the employees first names and corresponding last names.
Now say that you want to select all fields within the Employee table. To do this, you simply use an * symbol.
Select * from Employees;
Your result would be a table showing all of the data in your Employee database.
There may come a time when you wish to select a specific piece of data from your database. Like maybe you want the phone number of that cute girl in the mail room. You know the one I'm talking about. With the WHERE CLAUSE you could get it (you could...but you probably shouldn't). Likewise, when the person in charge of payroll needs to know which address to send your last paycheck to (since you got fired for harassing the cute girl in the mail room), the WHERE CLAUSE is what they would use.
For now let's say that you wanted to see all employees that are CEOs within the company.
Select * from Employees
Where Position='CEO';
This will return the following table:
|
First Name |
Last Name |
Position |
Salary |
|
Homer |
Sampson |
CEO |
$75,000 |
Note that when you are searching for text, you use a single quotation ('). However when you search for a numerical value, you would not use a quotation.
There are a number of operators you can use alongside a WHERE Clause. I bet they would look pretty in a table. Let's see.
|
Operator |
Function |
|
= |
Equals |
|
> |
Greater than |
|
< |
Less than |
|
<> |
Not equal |
|
<= |
Less than or equal to |
|
>= |
Greater than or equal to |
|
Like |
For similar data |
|
In |
The exact value you wish to return |
|
Between |
For data with a certain range |
The Between Operator works well with numbers. For instance, you would use it if you wanted to see a list of employees that made between $75,000 and $100,000. The Like Operator will show you data that is similar. If you had a list of names and one was Sampson and the other was Simpson (DOH!), then it would return both results, as the two are similar.
Select * from Employees
where LastName Like '%son%';
Technically, the above code would show us any employee who last name contained “son”
/ 12 
