The Structured Query Language (SQL) comprises one of the fundamental building blocks of modern database architecture. SQL defines the methods used to create and manipulate relational databases on all major platforms. At first glance, the language may seem intimidating and complex but it’s really not all that bad.
This article in the SQL series provides an introduction to the basic concepts behind SQL and we’ll take a brief look at some of the main commands used to create and modify databases.
By the way, the correct pronunciation of SQL is a contentious issue within the database community. In their SQL standard, the American National Standards Institute declared that the official pronunciation is “es queue el.” However, many database professionals have taken to the slang pronunciation “sequel.” The choice is yours.
SQL comes in many flavors. Oracle databases utilize their proprietary PL/SQL. Microsoft SQL Server makes use of Transact-SQL. However, all of these variations are based upon the industry standard ANSI SQL
The Data Definition Language (DDL) contains the commands used to create and destroy databases and database objects. After the database structure is defined with DDL, database administrators and users can utilize the Data Manipulation Language to insert, retrieve and modify the data contained within it.
The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project.
Let’s take a look at the structure and usage of four basic DDL commands:
- CREATE
Installing a database management system (DBMS) on a computer allows you to create and manage many independent databases. For example, you may want to maintain a database of customer contacts for your sales department and a personnel database for your HR department. The CREATE command can be used to establish each of these databases on your platform. For example, the command:
CREATE DATABASE employees
Create an empty database named “employees”. After creating the database, your next step is to create tables that will contain data. The command:
CREATE TABLE personal_info (first_name char(20) not null, last_name char(20) not null, employee_id int not null)
Establish a table titled “personal_info” in the current database. In our example, the table contains three attributes: first_name, last_name and employee_id.
- USE
The USE command allows you to specify the database you wish to work with within your DBMS. For example, if we’re currently working in the sales database and want to issue some commands that will affect the employees database, we would preface them with the following SQL command:
USE employees
It’s important to always be conscious of the database you are working in before issuing SQL commands that manipulate data.
- ALTER
Once you’ve created a table within a database, you may wish to modify the definition of it. The ALTER command allows you to make changes to the structure of a table without deleting and recreating it. Take a look at the following command:
ALTER TABLE personal_info ADD salary money null
This example adds a new attribute to the personal_info table — an employee’s salary. The “money” argument specifies that an employee’s salary will be stored using a dollars and cents format. Finally, the “null” keyword tells the database that it’s OK for this field to contain no value for any given employee.
- DROP
The final command of the Data Definition Language, DROP, allows us to remove entire database objects from our DBMS. For example, if we want to permanently remove the personal_info table that we created, we’d use the following command:
DROP TABLE personal_info
Similarly, the command below would be used to remove the entire employees database:
DROP DATABASE employees
Use this command with care! Remember that the DROP command removes entire data structures from your database. If you want to remove individual records, use the DELETE command of the Data Manipulation Language.
The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let’s take a brief look at the basic DML commands:
- SELECT
The SELECT command is the most commonly used command in SQL. It allows database users to retrieve the specific information they desire from an operational database.
- INSERT
The INSERT command in SQL is used to add records to an existing table.
- UPDATE
The UPDATE command can be used to modify information contained within a table, either in bulk or individually.
- DELETE
Finally, let’s take a look at the DELETE command. You’ll find that the syntax of this command is similar to that of the other DML commands.
