Banner Image About Visual Basic Lesson Information User Information guide/help on the site Return to Visual Basic Home Page Go to Previous Page Go to Next Page This course includes lectures on Microsoft Access



Lecture 1 - Database Theory & Basic SQL

Database theory
PLSQL data types
Decision Making
Loops
Case Statement
Global and Local variables
Routines
Arrays
Records
File Handling
String manipulation


Back To TOP

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.

Databases

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:

tblStudent Table
Forename Surname Course Duration Fee
Joe Bloggs Artificial Intelligence 2 years 30.00
Jane Bloggs Food Technology 3 years 5.00
Pamela Anderson Physical Education 2 years 25.00
Smith Mandy I.T 3 years 5.00
Hayek Salma Computing 2 years 25.00

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 this area:

    1. Creating a table schema and 'empty' table
    2. Changing the structure of the table
    3. Adding data to the table
    4. Deleting data from a table
    5. 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 summarised below:

    • 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:

      column-name    type   [Additional clauses]

      The common data types used are char, Integer, smallint, decimal, date etc.

      NOT NULL

      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

      Question

      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.

      Primary Key

      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:

      STUDENT Table
      Student ID Forename Surname Course Duration Fee
      0001 Anderson Pamela Artificial Intelligence 2 years 30.00
      0002 Smith Mandy I.T 3 years 5.00
      0003 Air Donna Computing 2 years 25.00
      0004 Smith Mandy I.T 3 years 5.00
      0005 Hayek Salma Computing 2 years 25.00

      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:

      1. The key must be unique
      2. 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, 
               STUNAME CHAR(30), 
               ROAD CHAR(30), 
               TOWN CHAR(30) 
               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);
      
      Inserting values into the table

      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:

      Inserting values into the table

      The statement will produce the following query illustrated below (Note : I have inserted three records):

      Inserting values into the table

    • 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 below:

      Inserting values into the table
      UPDATE TABLE
      SET field = value
      WHERE condition;
      

    • 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:

      Inserting values into the table

      The general syntax for the DELETE statement is:

      DELETE FROM tablename
       [WHERE clause]
      

      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 table is:

        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 are defined.

      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:

      Inserting values into the table

      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

      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:

      Inserting values into the 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:

      1. New columns can be added to a table after it has been created and put into use
      2. The data types of existing columns can be changed under specific conditions
      3. New integrity rules can be added
      4. Existing integrity rules can be deleted

      Inserting values into the table

      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:

      SELECT *
      FROM STUDENT;
    

    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
      FROM STUDENT;
    

    The query returns all records with only the playerno, name, sex and Birth_Year fields viewed. Some queries, can produce duplicate records, for example:

      SELECT playerno
      FROM penalties;
    

    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
      FROM penalties;
    

    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)
      FROM table(s)
      [WHERE predicate]
      [GROUP BY field(s)]
      [HAVING predicate]
      [ORDER BY field(s)]
     
    NOTE

    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:

      SELECT playerno
      FROM penalties
      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:

    • From

      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.

    • WHERE

      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. 
      

    • HAVING

      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)
        Example:
        SELECT * from STUDENT
        ORDER BY name DESC
      
      NOTE

      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
        FROM STUDENT
        ORDER BY 1 DESC;
      

    • SELECT

      The SELECT statement specifies the columns that are to be presented in the final results table.

    NOTE

    When the query is executed the only table seen will be the final table after the SELECT clause.

Conditions

These are constraints on the query which is placed in the WHERE clause. The following statements exist in SQL:

Function

Description

COUNT Counts the number of values inside a column or number of rows in a table
MIN Determines the smallest value in the column
MAX Determines the largest value in the column
SUM Determines the sum of the values in the column
AVG Determines the weighted arithmetic mean of the values in the column

Example 1 How many STUDENT are registered in the STUDENT table

  SELECT COUNT(*)
  FROM STUDENT;
Example 2 How many STUDENT live in Stratford

  SELECT COUNT(*)
  FROM STUDENT
  WHERE TOWN = 'STRATFORD';
Example 3 How many leaugue numbers are there

  SELECT COUNT(LEAGUENO)
  FROM STUDENT;
Example 4 How many different place names are there in the TOWN column

  SELECT COUNT(DISTINCT TOWN)
  FROM STUDENT;

Example 5 What is the highest penalty?

  SELECT MAX(AMOUNT)
  FROM PENALTIES;

Example 6 What is the difference between the highest and the lowest penalty?

  SELECT (MAX(AMOUNT) - MIN(AMOUNT))
  FROM PENALTIES;

Example 7 Give the average amount of penalties incurred by player 44?

  SELECT AVG(AMOUNT)
  FROM PENALTIES
  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:

  SELECT T.playerno
  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.

You are not authorised to use this material without the written permission of the author. The contents of this material must not be distributed or used for any teaching purposes. Failure to comply may result in criminal prosecution. Contact Vas Rabani for any comments on the website or queries