Basic SQL

A quick overview of SQL: History, Concepts, and Syntax

What is SQL

SQL == Structured Query Language

• First invented in the early 1970s at IBM
• Based on Relational Algebra and Tuple Relational Calculus
• Used to get at data stored in their System-R database management system
• Picked up by Relational Software (now Oracle) in the late 1970s
• Oracle V2, the first commercial Relational Database released in 1979
• IBM followed with System/38, SQL/DS and DB2 between 1979 and 1983

source: http://en.wikipedia.org/wiki/SQL

What is SQL

SQL, and Relational Database Management Systems (RDBMS) have been the de-facto standard for data persistence for 30+ years

Currently, there are more than 100 RDBMS available, both proprietary and open-source.

Most, if not all, include some implementation of SQL as their query language.

source: http://en.wikipedia.org/wiki/List_of_relational_database_management_systems

Big Players in SQL

There are a number of RDBMS that you will run into regularly

Commercial / Proprietary

• MS SQL Server
• Oracle
• MySQL Enterprise (Oracle)

Open Source

• PostgreSQL
• MariaDB (MySQL community)
• SQLite

SQL/RDBMS Concepts - Tables

A table consists of rows (also called records) and columns

Each row/record represents a single item

Each column represents a data point

Most tables will have one column which is considered the primary key

This value will uniquely identify a single row out of all the rows in the table

An Example

Here is an example table which represents people in a system:

SQL/RDBMS Concepts - Relations

You can model things using tables like this. Adding columns for all sorts of different data points

But what happens when not all of the items in a table share the same data points?

Or what if some of the items need to have more than one of a particular data point?

Leaving columns empty in a row wastes memory and slows down querying. Use relations to solve these types of problems

Types of Relations

There are three basic types of relationships:

One-to-one relationships

Best used to represent aspects of an item which are not core to it. Like user (id, password) -> user_profile (preferences, name, address)

Many-to-one relationships

Used to represent relationships of ownership or belonging. Like product -> manufacturer or book -> author

Many-to-many relationships

Used to represent associations or membership. Like users -> groups or items -> orders

SQL Relations - ∞ -> 1

Many-to-one relationships are modelled using Foreign Keys

The many table has a column which holds the primary key of the row from the one table:

Consider the relationship of books to author:

Books -> Author

People:

Books:

SQL Relations - 1 -> 1

One-to-one relationships are really just a special case of Many-to-one, and are also modelled with Foreign Keys

In this case, the column on the related table which holds the primary key of the target table has an additional unique constraint, so that only one related record can exist

The classic purpose is for data that doesn't need to be accessed often, and is unique per record

Consider the example of birth records:

Birth Record -> Person

People:

Birth Records:

SQL Relations - ∞ -> ∞

Many-to-many relations are a bit trickier.

You can't have a multi-valued field, so there's no way to define a foreign key-like construct that would work

Instead, this relationship is modelled using a join table, which has two foreign key fields, one for each side of the relation.

Beyond these two, other columns can add data points describing the qualities of the relation itself

Group Memberships

SQL Syntax

The syntax of SQL can be broken into constructs:

• Statements are discreet units that perform some action, like inserting records or querying
• Clauses are sub-units of statements which indicate some action or condition
• Expressions are elements that produce values, either unitary or as tables themselves
• Predicates are conditionals which produce some boolean or three-valued truth value

image: CC-BY-SA by Ferdna http://en.wikipedia.org/wiki/File:SQL_ANATOMY_wiki.svg

SQL Syntax - Subsets

SQL statements can be thought of as belonging to one of several subsets

Data Definition

Statements in this subset concern the structure of the database itself:

CREATE TABLE "jos_groups" (
  "group_id" character varying(32) NOT NULL,
  "name" character varying(255) NOT NULL,
  "description" text NOT NULL
)

SQL Syntax - Subsets

SQL statements can be thought of as belonging to one of several subsets

Data Manipulation

Statements in this subset concern the altering of data within the database:

UPDATE people
    SET first_name='Bill'
    WHERE id=4;

SQL Syntax - Subsets

SQL statements can be thought of as belonging to one of several subsets

Data Query

Statements in this subset concern the retrieval of data from within the database:

SELECT user_id, COUNT(*) c
  FROM (SELECT setting_value AS interests, user_id
          FROM user_settings
          WHERE setting_name = 'interests') raw_uid
  GROUP BY user_id HAVING c > 1;