Database Design   «Prev  Next»

Lesson 6 Structured Query Language
ObjectiveWhy is SQL the common language of relational databases?

Why is SQL the Common Language of Relational Databases?

(SQL, pronounced “sequel”) Structured Query Language has been adopted internationally as the standard language for creating relational databases. It is considered the common language of relational databases because it serves as both a (DDL) data definition language[1] and (DML)data manipulation language[2] .

Data Definition Language

As a data definition language (DDL), SQL contains statements that:
  1. Create (and delete) tables
  2. Modify tables (add, delete, or change fields)
  3. Create (and delete) user views

Let us take another look at the Employees table.

Employees table containing 1) primary key EmpID 2) fields a) LastName, b) FirstName, c)HireDate
SQL Table: Employees table containing 1) primary key EmpID 2) fields a) LastName, b) FirstName, c)HireDate

The section of the Employees table outlined in red contains the actual structures (one table with four fields) created by SQL statements. To specify EmpID as the primary key field for the table requires an additional statement.
The Diagram below illustrates the six SQL statements used to create the Employees table. The explanations are listed below for each SQL statement.

The following diagram describes the fields of the table.
SQL Statements
  1. Create Table Employees
  2. EmpID numeric
  3. LastName varchar (20)
  4. FirstName varchar (15)
  5. HireDate date
  6. Primary Key (EmID)

Create Table with SQL
The following diagram below contains the 1) SQL create statement and resulting 2) column fields of the table.
Create Table using SQL
  1. Creates the Employees table
  2. Creates the EmpID field and specifies that data values entered in the field may contain up to 15 characters.
  3. Creates the LastName field and specifies that the data values entered in the field may contain up to 20 characters.
  4. Creates the FirstName field and specifies that the data values entered in the field may contain up to 15 characters.
  5. Creates the HireDate field.
  6. Creates a primary key field and specifies that “EmpID” is the primary key of the table.

Different Dialects of SQL

SQL comes in several dialects, so the syntax of statements is not uniform. For example, one dialect may require parentheses to enclose certain elements in SQL statements, another may use colons between elements, and so on.

Data Manipulation Language

As a data manipulation language (DML), SQL contains statements that manipulate data, enabling you to:
  1. Create queries
  2. Modify records (insert, delete, or update them)
  3. Invoke user views
In addition, the DML capabilities built into SQL usually include mathematical and statistical calculations that assist in generating reports. In the next lesson, the functions of a relational database management system (RDBMS), whose major component is SQL, will be discussed.

[1](DDL) data definition language: A collection of programming statements that describe and define data and data relationships in a database. The data definition language is made of the commands used to change metadata in a database, such as creating tables, changing tables, and dropping tables.
[2](DML) data manipulation language: A collection of programming statements used to manipulate a database.