|
|
|
SQL stands for Structured Query Language. It is a language oriented
specifically around relational databases.
SQL eliminates a lot of the work you would have to do if you were using
a general-purpose programming language, such as C. To build a relational
database in C, you would have to start from scratch. You would have to define
an object called a "table" that could grow to have any number of rows, and then
create step-by-step procedures for putting values in it and retrieving them.
If you wanted to find some particular rows, you would have to enumerate each step
of the process like this: 1. Look at a row of the table. 2. Perform a test to see if it is one of the rows you want. 3. If so, store it somewhere until the whole table is examined. 4. See if there are any more rows in a table. 5. If there are more rows, go back to step one. 6. If there are no more rows, output all rows stored in step 3. SQL, however, spares you all this. Commands in SQL (traditionally called "statements") can operate on entire tables as single objects and can treat any quantity of information extracted or derived from them as a single unit as well. [1] |
|
We define tables with the CREATE TABLE command. This command creates an initially
empty table - a table with no rows. We enter values with an INSERT statement,
which will be explained below. The CREATE TABLE command basically defines
a table name as describing a set of named columns in a specified order. It also
defines the data types and sizes of the columns. Here is the syntax: [2] CREATE TABLE tablename ({columnname datatype[(size)]}.,..); The following command, for example, would create a Salespeople table: CREATE TABLE Salespeople (snum INTEGER, sname CHAR(10), city CHAR(10), comm DECIMAL); |
|
You enter all rows in SQL using the update command INSERT. In its simplest
form, INSERT uses the following syntax: INSERT INTO tablename VALUES (value.,..); So, for example, to enter a row into the Salespeople table, you could use the following statement: INSERT INTO Salespeople VALUES (1001, 'Peel', 'London', .12); You can also specify the columns into which you wish to insert a value by name. This allows you to insert into them in any order: INSERT INTO Salespeople (city, sname, snum) VALUES ('London', 'Hoffman', 2001); You will notice that "comm" column is omitted. This means that it will be automatically set to default value for this row. The default will be either NULL or an explicitly defined default. Listing the column names is often a good idea even when it is not strictly necessary. It makes your code more understandable and maintainable. [3] |
|
The most frequently used aspect of SQL are queries. A query is a statement you give
your DBMS that tells it to produce certain specified information. Queries in SQL
are all constructed from a single SELECT statement. The structure of this statement is
deceptively simple, because you can extend it enough to allow some highly sophisticated
evaluating and processing of data. In its simplest form, SELECT instructs the database to retrieve the contents of a table. For example, you could produce the Salespeople table by typing the following: SELECT snum, sname, city, comm FROM Salespeople; If you want to see every column of a table, there is an optional abbreviation you can use. You can substitute an asterisk (*) for a complete list of the columns. Doing so will produce the same result as the previous statement. [4] |
|
SQL enables you to define criteria to determine which rows are selected for output, because
usually only certain rows interest you at a given time. The WHERE clause of the SELECT
statement enables you to define a predicate, a condition that can be TRUE, FALSE, or UNKNOWN
for any given row of a table. The statement extracts only those rows from a table for which the
predicate is TRUE. Suppose you want to see the names and commissions of all salespeople in London. You could enter this statement: SELECT sname, city FROM Salespeople WHERE city = 'London'; When a WHERE clause is present, the database program goes through the entire table one row at a time and examines each row to determine if the predicate is TRUE. [5] |
|
This webpage just touches the surface of SQL. I recommend Gruber's "Mastering SQL"
for more information. Also some more advanced tutorials are available online: http://www.sqlcourse.com/ http://www.w3schools.com/sql/ http://sqlzoo.net/ |
|
References: [1] Martin Gruber 2000, "Mastering SQL", Sybex Inc., p.20 [2] Martin Gruber 2000, "Mastering SQL", Sybex Inc., p.38 [3] Martin Gruber 2000, "Mastering SQL", Sybex Inc., p.80 [4] Martin Gruber 2000, "Mastering SQL", Sybex Inc., p.92 [5] Martin Gruber 2000, "Mastering SQL", Sybex Inc., p.98 Last modified on January 30, 2003 By Svetlana Minina svetlana@jhu.edu |