CHAPTER FIVE

STRUCTURED QUERY LANGUAGE (SQL)

What is SQL?
Structured Query Language, commonly abbreviated to SQL and pronounced as “sequel”, is not a
conventional computer programming language in the normal sense of the phrase. It allows users
to access data in relational database management systems. SQL is about data and results, each SQL
statement returns a result, whether that result be a query, an update to a record or the creation of a
database table. SQL is most often used to address a relational database, which is what some people
refer to as a SQL database. So in brief we can describe SQL as follows:
• SQL stands for Structured Query Language
• SQL allows you to access a database
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert new records in a database
• SQL can delete records from a database
• SQL can update records in a database
• SQL is easy to learn
Creating a Database
Many database systems have graphical interfaces which allow developers (and users) to create,
modify and otherwise interact with the underlying database management system (DBMS).
However, for the purposes of this chapter all interactions with the DBMS will be via SQL
commands rather than via menus.
SQL Commands
There are three groups of commands in SQL:
1. Data Definition
2. Data Manipulation and
3. Transaction Control

Characteristics of SQL Commands

Here you can see that SQL commands follow a number of basic rules:
• SQL keywords are not normally case sensitive, though this in this tutorial all commands
 (SELECT, UPDATE etc) are upper-cased.
• Variable and parameter names are displayed here as lower-case.
• New-line characters are ignored in SQL, so a command may be all on one line or broken up
across a number of lines for the sake of clarity.
• Many DBMS systems expect to have SQL commands terminated with a semi-colon character.
Data Definition Language (DDL) in SQL
The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted.
We can also define indexes (keys), specify links between tables, and impose constraints between
database tables.
The most important DDL statements in SQL are:
• CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table

How to create table

Creating a database is remarkably straightforward. The SQL command which you have to give is
just:
CREATE DATABASE dbname;
In this example you will call the database TESTDB, so the command which you have to
give is:

CREATE DATABASE TESTDB;

Once the database is created it, is possible to start implementing the design sketched out
previously.
So you have created the database and now it's time to use some SQL to create the tables required
by the design. Note that all SQL keywords are shown in upper case, variable names in a mixture
of upper and lower case.
The SQL statement to create a table has the basic form:
CREATE TABLE name( col1 datatype, col2 datatype, …);
So, to create our User table we enter the following command:
CREATE TABLE User (FirstName TEXT, LastName TEXT, UserID TEXT, Dept TEXT,
EmpNo INTEGER, PCType TEXT );
The TEXT datatype, supported by many of the most common DBMS, specifies a string of
characters of any length. In practice there is often a default string length which varies by product.
In some DBMS TEXT is not supported, and instead a specific string length has to be declared.
Fixed length strings are often called CHAR(x), VCHAR(x) or VARCHAR(x), where x is the string
length. In the case of INTEGER there are often multiple flavors of integer available. Remembering
that larger integers require more bytes for data storage, the choice of int size is usually a design
decision that ought to be made up front.
How to Modify table
Once a table is created it's structure is not necessarily fixed in stone. In time requirements change
and the structure of the database is likely to evolve to match your wishes. SQL can be used to
change the structure of a table, so, for example, if we need to add a new field to our User table to
tell us if the user has Internet access, then we can execute an SQL ALTER TABLE command as
shown below:
ALTER TABLE User ADD COLUMN Internet BOOLEAN;
To delete a column the ADD keyword is replaced with DROP, so to delete the field we have just
added the SQL is:

ALTER TABLE User DROP COLUMN Internet;

How to delete table

If you have already executed the original CREATE TABLE command your database will already
contain a table called User, so let's get rid of that using the DROP command:
DROP TABLE User;
And now we'll recreate the User table we'll use throughout the rest of this tutorial:
CREATE TABLE User (FirstName VARCHAR (20), LastName VARCHAR (20),
UserID VARCHAR(12) UNIQUE, Dept VARCHAR(20), EmpNo INTEGER UNIQUE, PCType
VARCHAR(20);
Data Manipulation Language in SQL (DML)
SQL language also includes syntax to update, insert, and delete records. These query and update
commands together form the Data Manipulation Language (DML) part of SQL:
• INSERT INTO - inserts new data into a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• SELECT - extracts data from a database table

How to Insert Data

Having now built the structure of the database it is time to populate the tables with some data. In the
vast majority of desktop database applications data entry is performed via a user interface built around
some kind of GUI form. The form gives a representation of the information required for the application,
rather than providing a simple mapping onto the tables. So, in this sample application you would
imagine a form with text boxes for the user details, drop-down lists to select from the PC table, drop-
down selection of the software packages etc. In such a situation the database user is shielded both from
the underlying structure of the database and from the SQL which may be used to enter data into it.
However we are going to use the SQL directly to populate the tables so that we can move on to the
next stage of learning SQL.
The command to add new records to a table (usually referred to as an append query), is:
INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...]);
So, to add a User record for user Jim Jones, we would issue the following INSERT query:
INSERT INTO User (FirstName, LastName, UserID, Dept, EmpNo, PCType)
VALUES ("Jim", "Jones", "Jjones","Finance", 9, "DellDimR450");
Obviously populating a database by issuing such a series of SQL commands is both tedious and prone
to error, which is another reason why database applications have front-ends. Even without a
specifically designed front-end, many database systems - including MS Access - allow data entry direct
into tables via a spreadsheet-like interface.
The INSERT command can also be used to copy data from one table into another. For example, The
SQL query to perform this is:
INSERT INTO User ( FirstName, LastName, UserID, Dept, EmpNo, PCType, Internet )
SELECT FirstName, LastName, UserID, Dept, EmpNo, PCType, Internet
FROM NewUsers;
How to Update Data
The INSERT command is used to add records to a table, but what if you need to make an amendment
to a particular record? In this case the SQL command to perform updates is the UPDATE command,
with syntax:
UPDATE table
SET newvalue
WHERE criteria;
For example, let's assume that we want to move user Jim Jones from the Finance department to
Marketing. Our SQL statement would then be:
UPDATE User
SET Dept="Marketing"
WHERE EmpNo=9;
Notice that we used the EmpNo field to set the criteria because we know it is unique. If we'd used
another field, for example LastName, we might have accidentally updated the records for any other
user with the same surname.
The UPDATE command can be used for more than just changing a single field or record at a time. The
SET keyword can be used to set new values for a number of different fields, so we could have moved
Jim Jones from Finance to marketing and changed the PCType as well in the same statement (SET
Dept="Marketing", PCType="PrettyPC"). Or if all of the Finance department were suddenly granted
Internet access then we could have issued the following SQL query:
UPDATE User
SET Internet=TRUE
WHERE Dept="Finance";
You can also use the SET keyword to perform arithmetical or logical operations on the values. For
example if you have a table of salaries and you want to give everybody a 10% increase you can issue
the following command:
UPDATE PayRoll
SET Salary=Salary * 1.1;
How to Delete Data
Now that we know how to add new records and to update existing records it only remains to learn how
to delete records before we move on to look at how we search through and collate data. As you would
expect SQL provides a simple command to delete complete records. The syntax of the command is:
DELETE [table.*]
FROM table
WHERE criteria;
Let's assume we have a user record for John Doe, (with an employee number of 99), which we want to
remove from our User we could issue the following query:
DELETE *
FROM User
WHERE EmpNo=99;
In practice delete operations are not handled by manually keying in SQL queries, but are likely to be
generated from a front end system which will handle warnings and add safe-guards against accidental
deletion of records.
Note that the DELETE query will delete an entire record or group of records. If you want to delete a
single field or group of fields without destroying that record then use an UPDATE query and set the
fields to Null to over-write the data that needs deleting. It is also worth noting that the DELETE query
does not do anything to the structure of the table itself, it deletes data only. To delete a table, or part of
a table, then you have to use the DROP clause of an ALTER TABLE query.

Constraints in SQL
Data types are a way to limit the kind of data that can be stored in a table. For many applications,
however, the constraint they provide is too coarse. For example, a column containing a product price
should probably only accept positive values. But there is no data type that accepts only positive
numbers. Another issue is that you might want to constrain column data with respect to other columns
or rows. For example, in a table containing product information, there should only be one row for each
product number.
To that end, SQL allows you to define constraints on columns and tables. Constraints give you as much
control over the data in your tables as you wish. If a user attempts to store data in a column that would
violate a constraint, an error is raised. This applies even if the value came from the default value
definition.
Check Constraints
A check constraint is the most generic constraint type. It allows you to specify that the value in a certain
column must satisfy an arbitrary expression. For instance, to require positive product prices, you could
use:
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0)
);
As you see, the constraint definition comes after the data type, just like default value definitions.
Default values and constraints can be listed in any order. A check constraint consists of the key word
CHECK followed by an expression in parentheses. The check constraint expression should involve the
column thus constrained, otherwise the constraint would not make too much sense.
4.7.2 Not-Null Constraints
A not-null constraint simply specifies that a column must not assume the null value. A syntax
example:
CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price
numeric);
A not-null constraint is always written as a column constraint. A not-null constraint is functionally
equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL
creating an explicit not-null constraint is more efficient. The drawback is that you cannot give explicit
names to not-null constraints created that way.
4.7.3 Unique Constraints
Unique constraints ensure that the data contained in a column or a group of columns is unique with
respect to all the rows in the table. The syntax is
CREATE TABLE products ( product_no integer UNIQUE, name text, price numeric );
when written as a column constraint, and
CREATE TABLE products ( product_no integer, name text, price numeric,UNIQUE
(product_no));
when written as a table constraint.
4.7.4 Primary Key Constraints
Technically, a primary key constraint is simply a combination of a unique constraint and a not-null
constraint. So, the following two table definitions accept the same data:
CREATE TABLE products (product_no integer UNIQUE NOT NULL, name text,
price numeric);
CREATE TABLE products (product_no integer PRIMARY KEY, name text, price
numeric);
Primary keys can also constrain more than one column; the syntax is similar to unique
constraints:
CREATE TABLE example (a integer,b integer,c integer, PRIMARY KEY (a, c));
A primary key indicates that a column or group of columns can be used as a
unique identifier for rows in the table. (This is a direct consequence of the
definition of a primary key. Note that a unique constraint does not, in fact,
provide a unique identifier because it does not exclude null values.) This is
useful both for documentation purposes and for client applications. For
example, a GUI application that allows modifying row values probably needs to know
the primary key of a table to be able to identify rows uniquely.
Foreign Keys Constraints
A foreign key constraint specifies that the values in a column (or a group of columns)
must match the values appearing in some row of another table. We say this maintains
the referential integrity between two related tables.
Say you have the product table that we have used several times
already:
CREATE TABLE products (product_no integer
PRIMARY KEY, name text, price numeric);
Let's also assume you have a table storing orders of those products. We want to
ensure that the orders table only contains orders of products that actually exist. So
we define a foreign key constraint in the orders table that references the products
table:
CREATE TABLE orders ( order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no), quantity integer);
Now it is impossible to create orders with product_no entries that do not appear in
the products table. We say that in this situation the orders table is the referencing
table and the products table is the referenced table. Similarly, there are
referencing and referenced columns.