SQL stands for Structured Query Language. It's a language developed for communicating with databases, and is fairly standard across many different database platforms. The "language" is fairly simple (it was designed so that Pointy Haired Managers could use it.) Currently, SQL-compliant databases are being posted to the web (including Everything) using things like modPERL, PERL and HTML all together. The evil Microsoft also has a SQL server.

SQL exists in many different variations; the SQL base standard is simple and straightforward, but every vendor, including Informix, Oracle, Sybase, and Microsoft, have produced extensions for supporting everything from searching through images, hinting the query optimizer, adding object-orientation, and so on. Microsoft's began as a version of Sybase, but has since become a formidable contender, if a poorly-scalable one.

Pronounced: ess-queue-ell

An extremely powerful database query language. SQL is a widely ported and relatively simple programming language. Frequently used on servers and other large system that are serving database information.

Pronounced by people deep in the industry as "Squirrel". Upon hearing you pronounce it thusly, SQL gurus will begin to appreciate your database skills.
The ignorant will often laugh at this pronunciation, but this only emphasizes their incompetence.
The COBOL of the new century. It is a language that is easily learned for producing reports on data - the same role as COBOL.

There are several parts of SQL that preform different interactions with the database:

My books say "seequel"

SQL was introduced by IBM as the language to interface with its prototype relational database management system, System R. The first commercially available SQL relational database management system was introduced in 1979 by Oracle Corporation.
Today, SQL has become an industry standard, and Oracle is the leading company in relational database management systems.
Contrary to some previous writeups, SQL in its native (ANSI standard) form is not like COBOL or any other procedural language. SQL by itself makes no provision for ordinary procedural constructs, such as branching, selection, or iteration.

Standard SQL is designed to create and interact with result sets (rows of data, analagous to records in a file). SQL can interact with persistent result sets (database tables), or temporary result sets in memory (such as those generated from subqueries). SQL provides language elements that allow retrieval of rows from persistent result sets (SELECT), update of persistent result sets (UPDATE), creation or extension of result sets (INSERT), and reduction or destruction of result sets (DELETE).

Most database vendors have enhanced their SQL implementations with proprietary extensions that offer procedural language capabilities. Two examples are Oracle's PL/SQL, and Microsoft's Transact/SQL. Both combine familiar procedural constructs (IF-THEN-ELSE, LOOP, etc.) with basic SQL language operators and functions to enable the creation of stored procedures, triggers, and other types of SQL-based "programs". DBMS vendors also typically extend SQL's capabilities with proprietary functions and datatypes.

A Brief Introduction to SQL
by Andrew Taylor

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
Accounts
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!

The following is based on practical experience with disparate SQL formatting practices used by hundreds of different development shops within North America and elsewhere. These are intended as guidelines, for use as a starting point in developing a consistent personal style of SQL formatting. Discretion must of course be used for individual cases. Recommended deviations from the presented 'canonical' style have been mentioned where appropriate, but these are by no means exclusive. Adopting these or similar guidelines should greatly improve readability of code and clarity of thought, with a corresponding improvement in productivity. Some small effort has been made to use examples which could conceivably make 'practical sense', but this is by no means guaranteed, as contrived examples often do a better job of illustrating the formatting. This remains a work in progress so constructive criticism is welcomed.

How to Format SQL

SQL keywords should be entered in upper case to visually differentiate user-defined identifiers such as table and column names, which should be in lower case or mixed case.

Each clause starts a new line.

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

When there is only one 'argument' to a clause, it goes on the same line as the clause keyword.

SELECT <single_expression>
FROM single_table
WHERE <single_predicate>
GROUP BY single_column
HAVING <single_predicate>
ORDER BY single_column

when there are multiple comma-separated arguments, the arguments are listed one per line beneath the clause keyword, and indented. (two spaces is necessary and sufficient for fixed-width fonts; more spaces may be necessary for variable-width fonts. DO NOT use tabs.) When refering to multiple tables, it is always advisable to use a short form correlation name, usually derived from the initial or initials of the full table name.

SELECT
  <first_expression>,
  <second_expression>,
  <third_expression>
FROM
  first_table t1,
  second_table t2,
  third_table t3
GROUP BY
  c1,
  c2,
  c3
ORDER BY
  c1,
  c2,
  c3

Multiple elements within parentheses should also be indented.

SELECT 
  VALUES (
    10,
    100,
    1000
  )
FROM dummytable
GROUP BY
  c1,
  c2,
  GROUPING SETS (
    (
      c1,
      c2,
      c3
    )
  )

Some sets of elements lend themselves to conceptual grouping; you may choose to place the elements from such a group together on one line.

SELECT
  name_first, name_middle, name_last,
  address_street, address_city, address_state, address_pcode, address_country,
  phone_areacode, phone_number
FROM demographics_table
GROUP BY GROUPING SETS (
  (a, b, c),
  (a, c),
  (b)
)  

JOIN clauses should be laid out each on a new line, with the JOIN keyword beginning the line.

SELECT
  <columns>
FROM
  table_one t1
  LEFT JOIN table_two t2 ON t2.column_A = t1.column_B
  RIGHT JOIN table_three t3 ON t3.column_C = t1.column_D

Subselects should be further indented, with their enclosing parentheses on distinct lines.

SELECT
  (
    SELECT c1
    FROM t1
    WHERE c1='xyz'
  ),
  c2
FROM t2

Where the subselect is small, you may choose to reduce it to a single line.

SELECT
  (SELECT c1 FROM t1 WHERE c1='xyz'),
  c2
FROM t2

Multiple predicates in a WHERE clause should ideally be placed one per line where possible, with the logical conjunction (AND, OR) starting the line, and expressions within parentheses further indented.

SELECT column
FROM table
WHERE
  <predicate_1>
  AND <predicate_2>
  AND (
    <predicate_3>
    OR <predicate_4>
    OR <predicate_5>
  )
Sometimes you may also choose to group some predicates on a single line.
WHERE
  <predicate_1> AND <predicate_2>
  AND (<predicate_3> OR <predicate_4> OR <predicate_5>)

Set operations (UNION, EXCEPT, INTERSECT) between two subselects should be formatted with the set operator keyword on its own line, unindented, between the subselects, as follows:

SELECT c1 FROM t1
UNION
SELECT c2 FROM t2
EXCEPT
SELECT c3 FROM t3

Finally, these and similar ideas can be applied to other standard (and non-standard) SQL statements.

INSERT INTO my_table (
  <columns>
)
<some_table_expression>

UPDATE my_table
SET 
  <assignment_clauses>
WHERE
  <predicates>

DELETE FROM my_table
WHERE
  <predicates>

Log in or register to write something here or to contact authors.