SQL (thing)

SQL is a language for accessing relational databases in a standard, database independant manner. A SQL statement can be entered into a database command shell, embedded directly into code (as with Pro*C and SQLJ), or invoked through a programmatical interface such as ODBC or JDBC.

SQL databases store data in tables. Each table has one or more named columns. The column names represent the fields of a record, and each column has a specific type. Each row of the table stores a record.

SQL Types

Some of the allowable types for table columns are:

• CHAR(n) a fixed length string of n characters
• VARCHAR(n) a variable length string of n characters
• INTEGER a whole number, positive or negative
• DECIMAL(x, y) a decimal number, where x is the maximum number of digits in total, and y is the maximum number of digits after the decimal point -- the maximum (5,2) number would be 999.99
• DATE a date

Modifying Tables

Tables are added to a database with the CREATE TABLE statement. This example adds a table called Users with a numerical id, plus fields for the first and last names:
create table Users (
    userId integer not null,
    firstName varchar(20),
    lastName varchar(20)
);

Tables can be removed, too. Note that dropping a table also erases all the records in that table:
drop table Users;

Because data is lost when a table is dropped, it is usually preferable to alter the table, rather than dropping and recreating. The ALTER statement can be used to add a column without affecting the existing data:
alter table Users add (sex char(1));

Modifying Data

A record can be added into a table with the insert statement. If the column names aren't specified explicitly, the first n columns, in the order they appear in the table, are used:
insert into Users values (1, 'John', 'Doe', 'M');
insert into Users (firstName, lastName, userId) values ('John', 'Doe', 1);

Similarly, data can be deleted or updated:
delete from Users where userId = 1;
update Users set sex = 'F' where firstName = 'John' and lastName = 'Doe';

Querying with SELECT

The real power of SQL is in the queries, and queries in SQL are done with the SELECT statement. SELECT is the most complicated part of SQL, and probably the most used part, too.

The basic idea of SELECT is it grabs data from one or more tables based on some conditions and returns it.

For the select examples, lets pretend we have a small database of users and accounts. Each user has a name and each account has a balance. A user can have multiple accounts, but each account has only one user. A diagram of the tables, with some example data is shown below:

Users

userId | firstName | lastName | sex
-------+-----------+----------+----
1      | John      | Doe      | M
2      | Jane      | Roe      | F

accountId | accountBalance | userId
----------+----------------+--------
1         | 100.00         | 1
2         | 200.00         | 1
3         | 300.00         | 2

So we have two users, Jane and John. John has two accounts and Jane has one.

The simplest select dumps the entire Users table as shown above:
select * from Users;

You can also request which columns you want to select:
select firstName, lastName from Users;

Of course, most of the time, you don't want all the data; to specify conditions, you add a WHERE clause. This query will only show the first and last names of John Doe:
select firstName, lastName from Users where userId = 1;

And finally, you can select data from multiple tables. Usually, the tables will have a column in common that relates them (this is where the term relational database comes from). This select shows the account owners name and the account balance for each account:
select u.firstName, u.lastName, a.accountBalance from Users u, Accounts a where u.userId = a.userId;

This last query is sometimes called a JOIN, because it joins related data from several tables.

Operators

Sometimes you want to further process or summarize the data returned by a select. This can be done with SQL operators. Here are some common examples.

To return just the number of rows that match a query, rather than the rows themselves, the COUNT operator can be used:
select count(*) from Users;

Or to get the sum of a value across all rows:
select sum(accountBalance) from Accounts;

There are many, many more variations on the SELECT statement, such as sorting, grouping, unioning, nested selects and more, but that covers the basics. Try installing a SQL database such as MySQL and play around with it!