Welcome to our free SQL tutorial. This tutorial is based on Webucator's Introduction to SQL Training course.
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.
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 is updated every few years. 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.
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.
A table in a database is a collection of rows and columns. Tables are also known as entities or relations.
A row contains data pertaining to a single item or record in a table. Rows are also known as records or tuples.
A column contains data representing a specific characteristic of the records in the table. Columns are also known as fields or attributes.
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.
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.
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 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
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.
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 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.
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.
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.
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.
DML statements are used to work with data in an existing database. The most common DML statements are:
DDL statements are used to structure objects in a database. The most common DDL statements are:
DCL statements are used for database administration. The most common DCL statements are:
DENY (SQL Server Only)