UNIT 2:Introduction to Basic SQL

Working Through This Course

In order to have a thorough understanding of the course units, you will need to read and understand the contents, practice the steps by designing an Information system of your own, and be committed to learning and implementing your knowledge.
This course is designed to cover approximately seventeen weeks, and it will require your devoted attention.

MODULE ONE

UNIT 1:                                  Introduction to Information Systems
UNIT 2:                                  Introduction to Basic SQL
UNIT 3:                                  SQL Syntax I
UNIT 4:                                  SQL Syntax II
UNIT 5:                                  More SQL Statements
UNIT 6:                                  Database Programming and JBDC

UNIT 2                                  Introduction to Basic SQL

Table of Contents
1.0              Introduction
2.0              Objectives
3.0              Main Content
3.1              Introduction to Database and Structured Query Language (SQL)
3.2              History of SQL
3.3              Basic Categories of SQL Statements
3.4              Viewing The Structure of a Table
3.5              Writing Basic SQL Select Statement
3.6              Summary of Functions of SQL
3.7              Using SQL in Your Web Site
3.8              Relational Database Management System
3.9              Introduction to SQL Syntax
3.9.1        Database Tables
3.9.2        SQL Statements 
3.9.3        SQL DML and DDL
4.0              CONCLUSION
5.0              SUMMARY
6.0              Tutor Marked Assignment
7.0              Further Reading and Other Resources

1.0              INTRODUCTION

An aspect of information Systems is data processing, with the prior knowledge of information systems
and computer technology as a whole you will it will be easy to introduce the basic concept of
Structured Query Language which is a useful tool in accessing and manipulating databases. You will be
introduced to the basic statement of the SQL programs that will enable you to write simple database
programs.
2.0              OBJECTIVES
By the end of this unit, you should be able to:
•        Understand the basics of the SQL programs.
•        Familiarize yourself with standard keywords of the SQL programs
•        Work on any SQL platform/server such as mysql, Ms Access etc.

•        Manage and access databases of any size easily.
3.0              MAIN CONTENT
3.1      Introduction to Database and SQL
A databaseis an organized collection of data. There are many different strategies for organizing data to
facilitate easy access and manipulation. A database management system (DBMS) provides mechanisms
for storing, organizing, retrieving and modifying data for many users. Database management systems
allow for the access and storage of data without concern for the internal representation of data.
Today's most popular database systems are relational databases. A language called  SQLpronounced
"sequel," or as its individual lettersis the international standard language used almost universally with
relational databases to perform queries (i.e., to request information that satisfies given criteria) and to
manipulate data. 
•         SQLis an acronym for Structured Query Languagestands for Structured Query Language 
•         SQL is used to access and manipulate databases 
•         SQL is an ANSI (American National Standards Institute) standard 
It is a database language that is used for querying and modifying relational databases. SQL is a
programming language for querying and modifying data and managing databases. Using SQL, you can
communicate with the database server. SQL has the following advantages:
•        Efficient
•        Easy to learn and use
•        Functionally complete(With SQL, you can define, retrieve, and manipulate data in the tables)
Although SQL is an ANSI (American National Standards Institute) standard, there are many different
versions of the SQL language.
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the
SQL standard!
3.2      HISTORY OF SQL
SQL was developed by IBM Research in the mid 70’s and standardized by the ANSI and later by the
ISO. Most database management systems implement a majority of one of these standards and add their
proprietary extensions. SQL allows the retrieval, insertion, updating, and deletion of data. A database 

management  system  also  includes  management  and  administrative  functions.  Most  –  if  not  all  –
implementations  also  include  a  command-line  interface  (SQL/CLI)  that  allows  for  the  entry  and
execution  of  the  language  commands,  as  opposed  to  only  providing  an  application  programming
interface (API) intended for access from a graphical user interface (GUI).
The first version of SQL was developed at IBM by Andrew Richardson, Donald C. Messerly and
Raymond  F.  Boyce  in  the  early  1970s. This  version,  initially  called  SEQUEL,  was  designed  to
manipulate and retrieve data stored in IBM's original relational database product; System R. IBM
patented their version of SQL in 1985, while the SQL language was not formally standardized until
1986 by the American National Standards Institute (ANSI) as SQL-86. Subsequent versions of the SQL
standard have been released by ANSI and as International Organization for Standardization (ISO)
standards.
Originally designed as a declarative query and data manipulation language, variations of SQL have
been created by SQL database management system (DBMS) vendors that add procedural constructs,
flow-of-control statements, user-defined data types, and various other language extensions. With the
release of the SQL: 1999 standard, many such extensions were formally adopted as part of the SQL
language via the SQL Persistent Stored Modules (SQL/PSM) portion of the standard.
SQL was adopted as a standard by ANSI in 1986 and ISO in 1987. In the original SQL standard, ANSI
declared that the official pronunciation for SQL is "es queue el". However, many English-speaking
database professionals still use the nonstandard pronunciation /ˈsiːkwəl/  (like the word "sequel"). As
mentioned above, SEQUEL was an earlier IBM database language, a predecessor to the SQL language.
SQL is designed for a specific purpose: to query data contained in a relational database. SQL is a setbased, declarative query language, not an imperative language such as C or BASIC. However, there are
extensions  to  Standard  SQL  which  add  procedural  programming  language  functionality,  such  as
control-of-flow constructs. An example is the Procedural Language of SQL (PL/SQL).
3.3      THE BASIC CATEGORIES OF SQL STATEMENTS
SQL statements are basically divided into four; viz;
•        Data Manipulation Language (DML)
•        Data Definition Language (DDL)
•        Data Control Language (DCL)
•        Transaction Control

DATA MANIPULATION LANGUAGE (DDL)
•         DML retrieves data from the database, enters new rows, changes existing rows, and removes
unwanted  rows from tables in  the database, respectively. The basic  Data Manipulation  Language
(DDL) includes the following;
-          Select statement
-          Insert statement
-          Update statement
-          Delete statement
-          Merge statement
•         DDL  sets up, changes and removes data structures from tables. The basic Data Definition
Language includes the following;
-          Create statement
-           Alter statement
-          Drop statement
-          Rename statement
-          Truncate statement
-          Comment statement
•         DCL gives or removes access rights to both a database and the structures within it. The basic
Data Control Languages are;
-          Grant Statement
-          Revoke Statement
•         Transaction Control manages the changes made by the DML statements. Changes to the data
can be grouped together into logical transactions. The basic Transaction control languages are;
-          Commit

-          Rollback
-          Savepoint
Using the following simple rules and guidelines, you can construct valid statements that are both easy
to read and easy to edit
•                    SQL statements are not case sensitive, unless indicated
•                    SQL statements can be entered on one or many lines
•                    Keywords cannot be split across lines or abbreviated
•                    Clauses are usually placed on separate lines for readability
•                    Indents should be used to make code readable
•                    Keywords typically are entered in uppercase; all other words, such as table names and
columns are entered in lowercase
3.4      VIEWING THE STRUCTURE OF A TABLE
The structure of any database table can be view by using the describe clause of the SQL statement. The
general syntax of the describe statement is given below;
DESCRIBE table;
For the purpose of this course two tables called Departments and Employees in the Oracle database will
be used. Thus, we need to see the structure of this tables so that we will be able to familiarize ourselves
with the column used in the table. To do this, we write the query;
DESCRIBE departments;
Name NULL? Type
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID   NUMBER(6)
LOCATION_ID   NUMBER(4)
From the table above, we can infer that departments table has 4 columns and that 2 of these columns
are not allowed to be null.

DESCRIBE employees;
Name  Null?  Type 
EMPLOYEE_ID  NOT NULL  NUMBER(6) 
FIRST_NAME     VARCHAR2(20) 
LAST_NAME  NOT NULL  VARCHAR2(25) 
EMAIL  NOT NULL  VARCHAR2(25) 
PHONE_NUMBER     VARCHAR2(20) 
HIRE_DATE  NOT NULL  DATE 
JOB_ID  NOT NULL  VARCHAR2(10) 
SALARY     NUMBER(8,2) 
COMMISSION_PCT     NUMBER(2,2) 
MANAGER_ID     NUMBER(6) 
DEPARTMENT_ID     NUMBER(4) 
From the table above, we can infer that employees table has 11 columns and that 5 of these columns are not allowed to be null.

3.5      WRITING BASIC SQL SELECT STATEMENTS
To extract data from the database, you need to use the SQL SELECT statement. You may need to
restrict the columns that are displayed. Using a SELECT statement, you can do the following;
Projection: You can use the projection capability to choose the columns in a table that you want to
return by your query. You can choose as few or as many columns of the table as you require.
Selection: You can use the selection capability in SQL to choose the rows in a table that you want to
return by a query. You can use various criteria to restrict the rows that you use.
Joining: You can use the join capability to bring together data that is stored in different tables by
creating a link between them.

3.6      Summary of the functions of SQL
•         SQL can execute queries against a database 
•         SQL can retrieve data from a database 
•         SQL can insert records in a database 
•         SQL can update records in a database 
•         SQL can delete records from a database 
•         SQL can create new databases 
•         SQL can create new tables in a database 
•         SQL can create stored procedures in a database 
•         SQL can create views in a database 
•         SQL can set permissions on tables, procedures, and views 
•         SQL can allow the construction codes manipulating database
3.7      Using SQL for Web Site
To build a web site that shows some data from a database, you will need the following:
•         An RDBMS database program (i.e. MS Access, SQL Server, MySQL) 
•         A server-side scripting language, like PHP or ASP 
•         SQL 
•         HTML / CSS 
3.8      RDBMS
RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2,
Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables.
A table is a collection of related data entries and it consists of columns and rows. Relational database
will further be described in Module 2. 

3.9      SQL SYNTAX
3.9.1    Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers"
or "Orders"). Tables contain records (rows) with data.
Below is an example of a table called "Persons":
P_I
d
LastName FirstName Address City
1 Akinbode Ola 10, Odeku Str.  Lagos
2 Okafor Chris 23, Princewill Drive Porthacourt
3 Amodu Ali 20, Dauda lane Kaduna
The table above contains three records (one for each person) and five columns (P_Id, LastName,
FirstName, Address, and City).
3.9.2    Format of SQL Statements 
Most of the actions you need to perform on a database are done with SQL statements. 
The following SQL statement will select all the records in the "Persons" table: 
SELECT * FROM Persons
Some database systems require a semicolon at the end of each SQL statement.
Semicolon is the standard way to separate each SQL statement in database systems that allow more
than one SQL statement to be executed in the same call to the server.
3.9.3    SQL, DML and DDL
SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition
Language (DDL).
The query and update commands form the DML part of SQL:
 SELECT- extracts data from a database 
 UPDATE- updates data in a database 
 DELETE- deletes data from a database 

 INSERT INTO- inserts new data into a database 
The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys),
specify links between tables, and impose constraints between tables. The most important DDL
statements in SQL are:
 CREATE DATABASE - creates a new database 
 ALTER DATABASE- modifies a database 
 CREATE TABLE- creates a new table 
 ALTER TABLE - modifies a table 
 DROP TABLE- deletes a table 
 CREATE INDEX- creates an index (search key) 
 DROP INDEX - deletes an index 

Next UNIT 3:                                  SQL Syntax I

4.0              CONCLUSION
In this unit you have been introduced to the fundamental concepts of a typical database computing
environment. You also learnt the specific requirement of an environment for the development of SQL
statements. You were also introduced to various SQL statements necessary in writing simple SQL
codes.
5.0              SUMMARY
What you have learned in this unit concerns
•        Structured Query Language (SQL) which is a standard language for accessing and
manipulating databases.
•        The different SQL statement
•        What SQL statement can be used for
•        Not all SQL statement accepts semicolon at the end of it depending on the
platform or server that is used to execute the statement.
6.0              EXERCISES
Exercise 3.1    What does SQL means?
Exercise 3.2    List and state the functions of the component of the DDL parts of
SQL program.
7.0              TUTOR MARKED ASSIGNMENT

Write a short note on structured query language program and explain the basic components of SQL

Next UNIT 3:                                  SQL Syntax I