Structured Query Language
The objective of this lesson is to understand the fundamentals of database design and SQL.
This knowledge is important when trying to write PLSQL scripts for the very first time. This is only intended to be a refresher course on SQL,
it is strongly recommended to purchase a good SQL book for further information.
A Database is a collection of data (or tables
which is shared in the manner it is logically meaningful to an organisation.
The objective of a database is to reduce data duplication,
storage capacity and maintain data integrity.
Data is often stored in the form of tables. A Table
consists of rows and columns. Where each row represents
a record of information. This is illustrated in the tblStudent
table shown below:
The table defined above consists of field identifiers, these are properties of a
table, for example, Surname, Forename etc. are all identifiers. Data is
stored in each of the fields, a whole row (or tuple) of data represents a
individual record. For example, the shaded row consisting of the record with the forename
Wajid is an example of a single record.
The table above consists of 5 separate records. However, there are two
records which are identical (Mandy Smith), if we try to retrieve a record on 'Smith' the result
will produce 2 records.
PLSQL stands for Programming Language/Structured
Query Language , this is an extension of SQL which had evolved during
the 70s. PLSQL encapsulates SQL
into a programmable unit allowing greater flexibility through the control
of the language. To understand PLSQL fully the basic SQL
commands need to be understood. This chapter starts with the very basics.
Various client tools are used, the scripts in this section should execute
on both SQL Plus for Oracle and Query Analyser
is a tool for entering SQL statments in SQL Server 6.5/7.0,
where you can create, and manipulate tables. Query Analyser,
is quite straightforward to use and it is recommended that you use the
online help system to create the statements.
SQL is a common manipulation language used for
database programming. SQL statements are like instructions which
are sent to the Database Server and then processed. Statements
range from creating, deleting or modifying data. The statements fit into
three main broad categories called Data Definition, Manipulation
and Control languages. These are discussed in detail below:
Data Definition Language (DDL)
This is part of the SQL language that provide facilities for
table creation and maintenance. There are a number of activities included in
- Creating a table schema and 'empty' table
- Changing the structure of the table
- Adding data to the table
- Deleting data from a table
- Ammending data from a table
Table names, column names and other items created within the
DDL must conform to the rules for the formation of SQL identifiers; these are
- A name must begin with a letter and must consist of letters, numbers or
the underscore character.
- A name may be upto (10 characters long (DQUERY), 128 charcaters
(SQL Server/ORACLE/MS Access))
- All table names must be unique
- You cannot use any SQL keyword as an identifier, e.g. you cannot name a
table 'SELECT' or 'WHERE'
- Can be case-sensitive based on the database management system
- Create statement
Creating a database table requires that you assign a name
to the table and that you define the names and data types
of each of the columns of the table. Additionally, each column description may include
other supplementary clauses concerned with validation and integrity. The
format is shown below:
CREATE TABLE tablename (column-definition list)
CREATE TABLE STUDENT (STUID int NOT NULL,
STUNAME varchar(30) NULL,
ROAD varchar(30) NULL,
TOWN varchar(30) NULL);
The table definition contains a field list which is
separated by commas each with the format:
type [Additional clauses]
The common data types used are char,
Integer, smallint, decimal, date etc.
Oracle and dbQuery support the NOT NULL value. This is specified
in the Create statement in defining integrity rules after the data types of the
columns. The NOT NULL option can point out which columns are
not allowed to contain null values. In other words, every NOT NULL
column must be filled with a value for every row.
Once the SQL statement has been written inside the
Input (Alt-I) screen it can be executed by pressing
F9. Once the table has been set up values can be inserted into it.
NOTE: To set a primary key
What if there were 10,000 records inside the database? How many 'Smiths'
do you expect to retrieve?.
This poses a problem of uniqueness and is overcome by the use of unique identifiers known as
Primary keys are used to identify unique records within a table. The basic
rule is that each row or tuple is uniquely identified by a number which cannot Null and cannot
be duplicated, this is illustrated in the revised table below:
The revised table shows that a new identifier has been created called Student ID,
this is the primary key. This has been underlined to distinguish it as a primary key. Each
record will now be uniquely identifiable through Student ID. To maintain
integrity, rules need to be applied to the primary key, these are as follows:
- The key must be unique
- Cannot contain a null value
These rules will enforce that no two rows will be the
same. We can now add as many records as we want without the fear
of duplicate records.
The reserved word Primary Key is inserted at the end of the
table field declarations. The actual primary key is enclosed in parenthesis.
This is illustrated in the statement below:
CREATE TABLE STUDENT (STUID INTEGER NOT NULL,
PRIMARY KEY (STUID));
THERE IS NO COMMA PLACED BEFORE THE PRIMARY KEY DECLARATION
- Insert Statement
To populate data into tables we can use the
INSERT statement. The format of the statement is given below:
INSERT INTO tablename (tablefield1, tablefield2 ...etc)
VALUES ( Value1, Value2, Value3 ...etc);
The diagram shows a single record inserted inside the
STUDENT table. More records can be inserted using this method.
We can view records inserted into the table using the
SELECT statement. Enter the following statement into the
Input window (ALT-I) and then press F9 to execute. The
statement is illustrated below:
The statement will produce the following query
illustrated below (Note : I have inserted three records):
- UPDATE Statement
We can edit a field by using the
UPDATE statement. The statement must reference the field
directly using the SELECT statement. This is illustrated
SET field = value
- DELETE Statement
We can delete a single row by using the
DELETE statement. The statement using a property of a
particlar row to delete it. This is illustrated in the diagram below:
The general syntax for the DELETE
DELETE FROM tablename
If you want to delete all the rows from a table, omit the
WHERE clause. This is different from the DROP table clause; in
addition to deleting the data, the DROP table command also
removes the table definition from the schema whereas the DELETE
FROM simply leaves the table empty. The general syntax to drop a
DROP TABLE tablename;
- Create View Statement
A table stores rows of actual data. This means that a table
occupies a particular amount of storage space; the more rows, the more
storage space is required. Views are tables that are visible to
users, but which do not occupy any storage space. A view can be referred to
as a derived or Virtual table. A view behaves as
though it contains actual rows of data, but in fact it contains none.
Instead the rows are derived from the base table or tables where the views
Views can be used to simplify the use of routine or
repetitive statements, restructure the way in which tables are seen and
protect data. An example of a view is illustrated below:
The contents of the AGE are not stored
in the database, but are derived the moment a SELECT statement (or
another statement is executed).
Synonyms are used to create alternative names
for a table. The CREATE synonym is used to define this, however this
statement will not create a new table:
Users can create synonyms in order to give a table a
view or an alternative name. For example, if a centrally defined table
name or view is not suitable for some reason.
We can use the SELECT statement to view
records with the new SYNONYM, for example:
SELECT * FROM MEMBERS;
We can delete the SYNONYM using the DROP
TABLE statement, this is illustrated below:
DROP SYNONYM MEMBERS;
- ALTER statement
The UPDATE, INSERT and DELETE statements are used to change
the contents of a table. Oracle offers the possibility of changing the
structure of a table. The way to do this is with the ALTER TABLE statement.
Different functions can be performed with this statement:
- New columns can be added to a table after it has been created and put
- The data types of existing columns can be changed under specific conditions
- New integrity rules can be added
- Existing integrity rules can be deleted
The definition statements allows you to use ADD to
insert a new row.
NOTE In Oracle, you can modify the data type by using the reserved
word MODIFY instead of ADD.
This is illustrated in the code below which changes the data type for field:
ALTER TABLE STUDENT MODIFY (AGE SMALLINT);
Data Manipulation Languages (DML)
Data Manipulation Languages are commands that allow the user to query the database to view selected records, or to insert, update and delete records. The most common of these statements is the Select statement.
In the simpliest case, the SELECT statement can be used to retrieve data from a single table. This is illustrated below:
The * (asterix) is a wild card which returns all fields in the table specified by the FROM clause. To view selected records we can specify selected fields, for example:
SELECT playerno, name, sex, birth_year
The query returns all records with only the playerno, name, sex and Birth_Year fields viewed. Some queries, can produce duplicate records, for example:
This query yields duplicate records for playerno field. We can modify the query to allow no duplicate entries. Here we use the DISTINCT statement. This is illustrated below:
SELECT DISTINCT playerno
The above statements are the most simpliest statements for querying the tables, However, DML statements can be modified further incorporating new clauses. This is illustrated in the example below:
SELECT [DISTINCT] item(s)
[GROUP BY field(s)]
[ORDER BY field(s)]
Each SELECT statement has a minimum of two clauses: the SELECT and FROM clause. The other clauses such as WHERE, GROUP BY and ORDER BY are not mandatory. They However should be used in the order specified. Study the following SELECT query:
WHERE amount > 25
GROUP BY playerno
HAVING count(*) > 1
ORDER BY playerno;
When the query is to be processed each clause will be executed resulting in intermediate tables. Each clause will produce its own intermediate table and these tables will be again filtered producing more tables. The order in which the above statements are to be executed are defined as follows:
FROM ...Intermediate table #1
WHERE ...Intermediate table #2
GROUP BY ... Intermediate table #3
HAVING ... Intermediate table #4
ORDER BY ... Intermediate table #5
SELECT ... Intermediate table #6
Therefore, it is the SELECT statement is executed last. The clauses are illustrated in detail below:
This states the table the query will work from. The from statement can interogate the data from one or more tables. The intermediate table produces an exact copy of the PENALTIES table.
This specifies the condition of all rows where the amount field is greater than 25.
- GROUP BY
Here the records are grouped together according to a particular field. In this case playerno. Thus all records with the related playerno will be grouped together in a single row. The format for the GROUP BY clause is follows:
GROUP BY column name
HAVING condition expression involving some
property of the group.
This statement is similar to the WHERE statement, except that it works on the intermediate result table of the GROUP BY clause.
- ORDER BY
This sorts the relevant fields in ascending order. The format for the statement is as follows:
ORDER BY field; (ascending order)
ORDER BY field DESC; (descending order)
SELECT * from STUDENT
ORDER BY name DESC
Oracle recognises NULL values as the highest values in a column. Therefore they are always placed at the bottom of the result if the order is ascending and at the top if the order is descending. For example:
SELECT DISTINCT leagueno
ORDER BY 1 DESC;
The SELECT statement specifies the columns that are to be presented in the final results table.
When the query is executed the only table seen will be the final table after the SELECT clause.
These are constraints on the query which is placed in the WHERE clause. The following statements exist in SQL:
||Counts the number of values inside a column or number of rows in a table
||Determines the smallest value in the column
||Determines the largest value in the column
||Determines the sum of the values in the column
||Determines the weighted arithmetic mean of the values in the column
Example 1 How many STUDENT are registered in the STUDENT table
Example 2 How many STUDENT live in Stratford
WHERE TOWN = 'STRATFORD';
Example 3 How many leaugue numbers are there
Example 4 How many different place names are there in the TOWN column
SELECT COUNT(DISTINCT TOWN)
Example 5 What is the highest penalty?
Example 6 What is the difference between the highest and the lowest penalty?
SELECT (MAX(AMOUNT) - MIN(AMOUNT))
Example 7 Give the average amount of penalties incurred by player 44?
WHERE PLAYERNO = 44;
Joining Multiple Tables
So far we have retrieved data from a single table. However, we may want to join more than one table together. Here,we use a concept where we attempt to match related records via the primary keys. Thus, logically combining several tables into one logical table. Study the following query:
SELECT teams.teamno, STUDENT.name, STUDENT.birth_year
FROM teams, STUDENT
WHERE teams.playerno = STUDENT.playerno;
The WHERE clause relates all records in one table with another by relating all matching records with the playerno which is common to both tables.
ALIAS or PSEUDONYM statements
To simplify statements using multiple tables we can use alias names. These are similiar to SYNONYMS, except they are used within the same statement to reference a table with an alternative (or user defined name). This is illustrated in the statement below:
FROM TEAMS T, PENALTIES PW
WHERE T.playerno = PN.playerno;
Here the Alias statement is defined in the FROM clause, which states the real name of the table and then the Alias name, i.e. TEAMS T, where TEAMS is the real name and T the alias. The SELECT and WHERE statement references the ALIAS name rather than the real name for each of the tables.