SQL Tutorial

Relational Database Basics

SQL stands for Structured Query Language and is pronounced either ess-que-el or sequel. It is the language used by relational database management systems (RDBMS) to access and manipulate data and to create, structure and destroy databases and database objects.

Lesson Goals

• About the history of SQL and relational databases.
• How relational databases are structured.
• About some of the most popular relational databases.
• About the major SQL statements.

Lesson Activities

1. Brief History of SQL
2. Relational Databases
3. Popular Databases
4. SQL Statements

---

Brief History of SQL

In 1970, Dr. E.F. Codd published "A Relational Model of Data for Large Shared Data Banks," an article that outlined a model for storing and manipulating data using tables. Shortly after Codd's article was published, IBM began working on creating a relational database. Between 1979 and 1982, Oracle (then Relational Software, Inc.), Relational Technology, Inc. (later acquired by Computer Associates), and IBM all put out commercial relational databases, and by 1986 they all were using SQL as the data query language.

In 1986, the American National Standards Institute (ANSI) standardized SQL. This standard was updated in 1989, in 1992 (called SQL2), in 1999 (called SQL3), in 2003 (called SQL 2003), in 2006 (called SQL 2006) and in 2008 (called SQL 2008). Standard SQL is sometimes called ANSI SQL. All major relational databases support this standard but each has its own proprietary extensions. Unless otherwise noted, the SQL taught in this course is the standard ANSI SQL.

Like this Microsoft SQL tutorial? Try our self-paced online Microsoft SQL course, which includes videos and exercises in addition to the content in this Microsoft SQL tutorial. Not sure if you want to pay for that? Register for a free demo of the course.

Relational Databases

A relational database at its simplest is a set of tables used for storing data. Each table has a unique name and may relate to one or more other tables in the database through common values.

Tables

A table in a database is a collection of rows and columns. Tables are also known as entities or relations.

Rows

A row contains data pertaining to a single item or record in a table. Rows are also known as records or tuples.

Columns

A column contains data representing a specific characteristic of the records in the table. Columns are also known as fields or attributes.

Relationships

A relationship is a link between two tables (i.e, relations). Relationships make it possible to find data in one table that pertains to a specific record in another table.

Datatypes

Each of a table's columns has a defined datatype that specifies the type of data that can exist in that column. For example, the FirstName column might be defined as varchar(20), indicating that it can contain a string of up to 20 characters. Unfortunately, datatypes vary widely between databases.

Primary Keys

Most tables have a column or group of columns that can be used to identify records. For example, an Employees table might have a column called EmployeeID that is unique for every row. This makes it easy to keep track of a record over time and to associate a record with records in other tables.

Foreign Keys

Foreign key columns are columns that link to primary key columns in other tables, thereby creating a relationship. For example, the Customers table might have a foreign key column called SalesRep that links to EmployeeID, the primary key in the Employees table.

Relational Database Management System

A Relational Database Management System (RDBMS), commonly (but incorrectly) called a database, is software for creating, manipulating, and administering a database. For simplicity, we will often refer to RDBMSs as databases.

Like this Microsoft SQL tutorial? Try our self-paced online Microsoft SQL course, which includes videos and exercises in addition to the content in this Microsoft SQL tutorial. Not sure if you want to pay for that? Register for a free demo of the course.

Popular Databases

Commercial Databases

Oracle

Oracle is the most popular relational database. It runs on both Unix and Windows. It used to be many times more expensive than SQL Server and DB2, but it has come down a lot in price.

SQL Server

SQL Server is Microsoft's database and, not surprisingly, only runs on Windows. It has only a slightly higher market share than Oracle on Windows machines. Many people find it easier to use than Oracle.

DB2

IBM's DB2 was one of the earliest players in the database market. It is still very commonly used on mainframes and runs on both Windows and Unix.

Popular Open Source Databases

MySQL

Because of its small size, its speediness, and its very good documentation, MySQL has quickly become the most popular open source database. MySQL is available on both Windows and Unix, but it lacks some key features such as support for stored procedures.

PostgreSQL

Until recently, PostgreSQL was the most popular open source database until that spot was taken over by MySQL. PostgreSQL now calls itself "the world's most advanced Open Source database software." It is certainly a featureful and robust database management system and a good choice for people who want some of the advanced features that MySQL doesn't yet have.

Like this Microsoft SQL tutorial? Try our self-paced online Microsoft SQL course, which includes videos and exercises in addition to the content in this Microsoft SQL tutorial. Not sure if you want to pay for that? Register for a free demo of the course.

SQL Statements

Database Manipulation Language (DML)

DML statements are used to work with data in an existing database. The most common DML statements are:

• SELECT
• INSERT
• UPDATE
• DELETE

Database Definition Language (DDL)

DDL statements are used to structure objects in a database. The most common DDL statements are:

• CREATE
• ALTER
• DROP

Database Control Language (DCL)

DCL statements are used for database administration. The most common DCL statements are:

• GRANT
• DENY (SQL Server Only)
• REVOKE

Like this Microsoft SQL tutorial? Try our self-paced online Microsoft SQL courses, which includes videos and exercises in addition to the content in this Microsoft SQL tutorial. Not sure if you want to pay for that? Register for a free demo of the course.

Continue to the next lesson on Simple SELECTs »