FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


SQL Basics





daemon
Knowing how to read and write SQL is a skill that is usually taken for granted. It's assumed SQL is a tool every IT professional has in his or her toolkit. Within the world of the intranet where the business and IT realms often blend together, that can be a detrimental assumption to make.

SQL is an important part of the intranet world, yet we hear so little about it. It's hidden under the covers of Web sites where it tends to go unnoticed, yet it's found just about everywhere because SQL is usually a component of every dynamic Web site. Regardless of the Web technologies used on the site (ColdFusion, ASP, Java/JSP, PHP, etc.), the bottom line is that some form of SQL is used for all database interactions.

Background

SQL stands for Structured Query Language and it is an American National Standards Institute (ANSI) language. It is used to access and manipulate data within relational databases such as MS Access, Oracle, DB2, and Sybase, to name a few. Almost all vendors have their own flavor of SQL, but in order to conform to the ANSI standard they must support the same major keywords (SELECT, UPDATE, DELETE, etc.) in a consistent manner.

Let's use a simple example to illustrate the most common usage of the SQL major keywords in most Web sites.

The Data

A database consists of one or more tables containing records (rows) of data. Each table has a set of predefined fields that define what types of information they can contain.

In this example, our table will be named "Customers." It has four pre-defined fields: LastName, FirstName, City, and State. It consists of four rows of data as shown below:

LastName FirstName City State
Smith John Philadelphia PA
Jones Laura Los Angeles CA
Casey Mike Salt Lake City UT
Doe Jane Bangor PA

The SELECT Statement

The SELECT statement is used more often than any other statement within the SQL language. It is used to select data read only from a database based on a set of criteria. The syntax for a select statement is:

SELECT [column1, column2, ... columnN] FROM [tablename] WHERE [condition1, condition2, ... conditionN];

At a minimum, you must select one column from one table; the rest is optional. As for the semicolon (wink.gif, it is used to terminate an SQL statement and separate it from other SQL statements included in the same call to the server. Whether the semicolon is required or is optional depends on the database with which you are working.

For example, if you wanted to select all of the records from our Customers table, the statement would be:

SELECT * from Customers;

Using a * denotes you you want to select "all." The result would be an exact replica of the Customers table as it is shown above.

If you wanted to choose only the first and last names of your customers, the statement would change to:

SELECT LastName, FirstName from Customers;

Result:

LastName FirstName
Smith John
Jones Laura
Casey Mike
Doe Jane

If you wanted to choose data based on a specified criteria, you would use the WHERE clause. The syntax looks as follows:

SELECT column FROM table WHERE column operator value;

The following operators can be used with the WHERE clause:

Operator Description
= Equal
< > (some versions of SQL use !=) Not equal
> Greater than
< Less than
> = Greater than or equal
< = Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern

So, for example, if you wanted to choose all information about only the customers that lived in the state of Pennsylvania, you would write the following:

SELECT * from Customers WHERE State='PA';

Result:
LastName FirstName City State
Smith John Philadelphia PA
Doe Jane Bangor PA

Notice that we used the single quote (') to enclose the value within the WHERE clause. Use single quotes to enclose textual values (most databases will accept double quotes as well); do not use any quotes for numeric values.

Use the LIKE operator to search for patterns within a data field. Combined with a wildcard "%", the LIKE operator is a powerful way to search.

To search for all cities within our Customers that begin with an "An":

SELECT City from Customers WHERE City LIKE 'An%';

Result:
City
Los Angeles

DISTINCT Keyword

With the SELECT statements used thus far, we've selected all values satisfying a particular criteria. If we wanted to select only the distinct values (no duplicates) from the table, we would have to use the keyword DISTINCT. For this example, let us use the following Orders Table:

CompanyName OrderNumber
ABC Toys 123456
Wonderful Widgets 456789
ABC Toys 158763
Fabulous Furry Friends 748596

If we simply wrote:

SELECT CompanyName from Orders;

The result would be:

CompanyName
ABC Toys
Wonderful Widgets
ABC Toys
Fabulous Furry Friends

That's all for this lesson! Shocked
Related topics
Help: SQL error after uploading new files(thread updated)
SLOW UPLOADING AN SQL FILE
have problem on my sql
Flash MX Basics
SQL Tutorial
Executing a .sql file through PHP
Quick Q :: Anyone stripped WinXP down to bare basics?
I can't upload my Database SQL script ! Helllppppp!!!!
SQL
SQL
SQL and PHP
Is there such a wsiwyg editor for sql
SQL Server 2000 and MYSQL, pls help me.
Can I create many SQL database by my self
Reply to topic    Frihost Forum Index -> Miscellaneous -> Tutorials

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.