SQL by George Hernandez

The different variations of SQL have a lot in common. The details of SQL in this section are specific to the T-SQL of Microsoft SQL Server and the SQL of MySQL.

Intro
SQL Conventions

Intro

SQL: Structured Query Language. Pronounced "sequel". SQL is a cross platform language used to select, update, insert, or delete data in relational databases. SQL is also used to administer the RDBMS (relational database management system).

SQL was developed in the 1970s by IBM and is supported by most of the commercial RDBMS producers including Oracle, Sybase, and Microsoft SQL Server. Each DBMS has its own variation of SQL. Between dialects of SQL, the basic access syntax does not vary much from the "official" ANSI and ISO version (aka SQL-89/SQL1 and SQL-92/SQL2).

Here are some of the SQL variations:

IBM DB/2
Sybase SQL
Oracle SQL+ and PL/SQL
Microsoft Transact-SQL (aka T-SQL)

SQL statements are often divided into three categories:

DML (Data Manipulation Language). These SQL statements are used to retrieve and manipulate data. This category encompasses the most fundamental commands including DELETE, INSERT, SELECT, and UPDATE. DML SQL statements have only minor differences between SQL variations. DML SQL commands include the following:
- DELETE to remove rows.
- INSERT to add a row.
- SELECT to retrieve row.
- UPDATE to change data in specified columns.
DDL (Data Definition Language). These SQL statements define the structure of a database, including rows, columns, tables, indexes, and database specifics such as file locations. DDL SQL statements are more part of the DBMS and have large differences between the SQL variations. DML SQL commands include the following:
- CREATE to make a new database, table, index, or stored query.
- DROP to destroy an existing database, table, index, or view.
- DBCC (Database Console Commands) statements check the physical and logical consistency of a database.
DCL (Data Control Language). These SQL statements control the security and permissions of the objects or parts of the database(s). DCL SQL statements are also more part of the DBMS and have large differences between the SQL variations. DML SQL commands include the following:
- GRANT to allow specified users to perform specified tasks.
- DENY to disallow specified users from performing specified tasks.
- REVOKE to cancel previously granted or denied permissions.

SQL Conventions

In real SQL code the following conventions apply:

White space (tabs, spaces, carriage returns) are irrelevant but is useful for making the code legible to developers.
Case doesn't matter, but is useful for making the code legible. EG: tblCustomer is the same as tblcustomer but the former is easier to read
Database object names:
- The first character can usually only be a letter. Any characters that follow can usually only be letters or numbers. This precludes spaces. Object names that include spaces are usually delimited with square brackets. EG: tblCustomers.[last name].
- Name length is limited to 30-128 characters depending on the flavor of SQL. SQL Server object names are actually nvarchar(128).

In addition to my usual site conventions, the following syntax conventions are used in my examples of SQL code:

UPPERCASE indicates a keyword of the language itself. In real code writing, this can be tedious.
Ellipses (...) indicate that an item can be repeated one or more times.
For comments, I will use MS SQL Server conventions:
- Double hyphens (--) for single line comments. MySQL uses -- , /**/ and # (the latter is just like -- ).
- Forward slash and asterisk pairs (/* ... */) for blocks of comments.

You may also want to see my article on DB Naming Conventions.

Page Modified: (Hand noted: 2007-10-12 18:41:15Z) (Auto noted: 2010-12-24 22:47:34Z)