Chapter 4. Basic ANSI-SQL

only for RuBoard

IN THIS CHAPTER

• Setting Up Your Workspace

• Getting Data from a Database

• The WHERE Clause

• The SubQuery

• SQL Joins

• Sorting Data

• Updating Existing Data

• Inserting New Data

• Deleting Existing Data

• Summary

This chapter will go over Structured Query Language, or SQL, which can be pronounced either "ess-cue-ell" or "sequel." Some people insist that it must be called one or the other. I prefer "sequel" because it's one less syllable to say.

SQL was invented by IBM in 1970 and became an ANSI standard in 1986. It's an English-like, nonprocedural language that aids in the definition, manipulation, and administration of data in a relational database management system (RDMS). I'll be going over today's most commonly used statements and clauses when working with SQL in a sub language environment, such as the writing of Web applications. The statements and clauses introduced in this chapter will be used throughout the rest of the book in code examples. This chapter will not make you an expert in SQL, but if you have not used SQL that much it will give you the foundation you need to start building data driven web applications.

All the example code used in this chapter assumes that you have the Northwind database, which is commonly distributed with either Microsoft's SQL Server or Microsoft Access. If you don't have either one on your computer, you can download the Access 2000 Northwind database at http://officeupdate.microsoft.com/2000/downloadDetails/Nwind2K.htm.

In this chapter, you'll learn about the following:

• Using the SELECT statement to retrieve data

• Using the FROM clause to specify which table to retrieve data

• Using the WHERE clause to filter and return specific rows

• Using a SubQuery to retrieve a value from a table nested in a different SQL statement

• Using SQL Joins to join two tables together into one result set

• Sorting data in descending or ascending order

• Using the ORDER BY clause to sort rows

• Using the GROUP BY clause to group rows together

• Using the UPDATE clause to update rows

• Using the INSERT statement to insert rows

• Using the DELETE statement to delete rows

only for RuBoard