SQL, Chapter 1: Overview

Structured Query Language, or SQL, is the standard language for interacting with Relational Database Management Systems (RDBMSs). Microsoft SQL Server, Oracle, IBM DB2, MySQL and PostgreSQL are a few popular RDBMSs that use SQL.

Each SQL-based RDBMS has characteristics of its own. Dialects of SQL began to proliferate in the first 15 years of its existence. In response, the American National Standards Institute (ANSI) created a SQL standard specification in 1986. The specification underwent a major revision in 1992. While there have been numerous revisions since then, most of what people use is in the 1992 specification.

None of the popular RDBMSs are fully compatible with one another. In this series, I’m doing my best to stick to ANSI SQL. Because of this, one of the features I’m not covering is the FETCH FIRST/LIMIT/TOP feature. This feature selects a subset of the resultset, for example the first 10 rows. The way that RDBMSs implement this feature is inconsistent. Wikepedia’s article on SQL SELECT has more information under the Limiting Result Rows header.

Declarative vs. Imperative Languages

SQL is for the most part a declarative language, as opposed to an imperative language. The basic difference between declarative and imperative languages is that declarative languages tell the interpreter what to do, whereas imperative languages also tell it how to do it.

Ruby can do both, so a small Ruby example can illustrate the difference. Suppose I have a method (a function) that adds two to every element in a given array: print add_two([1, 2, 3]) #=> [3, 4, 5]. I could implement the add_two like this:

def add_two(ary)
  counter = 0
  while counter < ary.size
     ary[counter] += 2
     counter += 1
  end
  ary
end        

This is an example of an imperative approach to the problem; saying “take each element of the given array one at a time and add two to it.”

If I did it this way:

def add_two(ary)
   ary.map { |elt| elt + 2 }
end        

This would be declarative, as I am simply saying “add two to every element in the given array.”

SQL is primarily a declarative language. With very few exceptions, SQL statements say what to do without saying how to do it. One imperative element in SQL is the ability to give hints about the execution plan of a query. Another is the oft-maligned CURSOR, which allows specification of how to iterate through the rows of a table.

The Three SQL Sub-Languages

The three SQL sub-languages are:

1. DDL or Data Definition Language. This is the language which defines or alters the structure of the database: creating, altering or dropping databases, tables, views, constraints, sequences and/or triggers, among others.
2. DML or Data Manipulation Language. This is the language which allows create, read, update and delete (CRUD) operations on the database.
3. DCL or Data Control Language. This is the language which controls access to the database, for example granting read rights to an entity for users in a specific role, defining and removing roles, adding users, adding users to roles, and so on. DCL examples include GRANT, CREATE USER and CREATE ROLE.

DML is the most commonly used of the three. Users of DCL and DDL usually use an IDE (Integrated Development Environment) such as the Management Studio for Microsoft SQL Server, MySQL Workbench for MySQL, pgAdmin for PostgreSQL, or Oracle Developer Studio for Oracle.

This series covers DML and DDL. DCL is more for administrators than developers, so covering it would go beyond the scope of the series as I’ve conceived it.

CRUD operations With DML

The create, read, update and delete (CRUD) operations are performed in SQL by INSERT, SELECT, UPDATE and DELETE, respectively.

By far the most often-used of these is the SELECT statement. The next few chapters will dive into it in some detail.

I've copied this chapter over from my blog site. If I haven't put the next chapter up here yet, the link to the next chapter will take you to the copy on robertrodes.com.

Chapter 2: the SELECT Statement