More SQL Statements

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 5 MORE SQL STATEMENTS

Table of Contents
1.0 INTRODUCTION
2.0 OBJECTIVES
3.0 Main Content
3.1 ARITHMETIC OPERATIONS

3.1.1 USING ARITHMETIC OPERATORS
3.1.2 OPERATOR PRECEDENCE
3.1.3 DEFINING A NULL VALUE
4.0 CONCLUSION
5.0 SUMMARY
6.0 Tutor Marked Assignment
7.0 Further Reading and Other Resources
1.0 INTRODUCTION
Since SQL is a database language that is used for querying and modifying relational databases, this unit
introduces more SQL intructions manipulating database. It presents other statements apart from those
described in Module 2.
2.0 OBJECTIVES
By the end of this unit, you should be able to
 Write sql statements
 Use the ORDER BY statement
 Use the INSERT INTO statement
 Update a group of data
 Delete rows from a table
3.0 MAIN CONTENT
3.1 ARITHMETIC OPERATIONS
Create expressions with number ad date data by using arithmetic operators. You may need to modify
the way in which data is displayed, perform calculations, or look at what-if scenarios. These are all
possible using arithmetic expressions. An arithmetic expression can contain column names, constant
numeric values and arithmetic operators.
Operator Description

+ Add
- Subtract
* Multiply
/ Divide
3.1.1 USING ARITHMETIC OPERATORS
Let us first extend Table persons to Table employees by adding salary column to it and adding three
more records. For subsequent examples in this unit, the table is also assumed to have more than 5
columns.
P_I
d
LastName FirstName Address City
Salary
1 Akinbode Ola 10, Odeku Str. Lagos 4800
2 Okafor Chris 23, Princewill Drive Porthacourt 17000
3 Amodu Ali 20, Dauda lane Kaduna 12000
4 Buba Ibrahim 12, Dongorayaro Str. Kastina 9000
5 Ngozi Ebe 10, Felix Str. Imo 7700
6 Sowale Ayo 63, Atoba road Abeokuta 24000
The example below describes a scenario in which arithmetic operators can be used.
SELECT last_name, salary, salary+300
FROM employees;
This gives

LastName Salary Salary+300
Akinbode 4800 5100
Okafor 17000 17300
Amodu 12000 12300
Buba 9000 9300
Ngozi 7700 8000
Sowale 24000 24300
3.1.2 OPERATOR PRECEDENCE
If an arithmetic expression contains more than one operator, multiplication and division are evaluated
first. If operators within an expression are of the same priority, then evaluation is done from left to
right. Parenthesis can be used to force the expression within parentheses to be evaluated first.
Multiplication and division take priority over addition and subtraction
A query that shows how operator precedence works is shown below;
SELECT last_name, salary, 12*salary+100
FROM employees
LastName
Salary 12*Salary
+100
Akinbode 4800 57700
Okafor 17000 204100
Amodu 12000 144100
Buba 9000 108100

Ngozi 7700 92500
Sowale 24000 288100
A query that uses brackets to override the operator precedence is shown below;
SELECT last_name, salary, 12*(salary+100)
FROM employees
LastName
Salary 12*(Salary
+100)
Akinbode 4800 58800
Okafor 17000 205200
Amodu 12000 145200
Buba 9000 109200
Ngozi 7700 93600
Sowale 24000 289200
3.1.3 DEFINING A NULL VALUE
A null is a value that is unavailable, unassigned, unknown, or inapplicable. If a row lacks the data value
for a particular column, that value is said to be null, or to contain a null. Columns of any data type can
contain nulls. However, some constraints, NOT NULL and PRIMARY KEY, prevent nulls from being
used in the column.
A query that shows the null values is shown below;
SELECT last_name, job_id, salary, commission_pct
FROM employees;

LAST_NAME JOB_ID SALARY COMMISSION_PCT
Akinbode ST_MAN 4800
Okafor ST_CLERK 17000
Amodu ST_CLERK 12000
Buba ST_CLERK 9000
Ngozi ST_CLERK 7700
Sowale ST_CLERK 24000
In the COMMISSION_PCT column in the EMPLOYEES table, notice that only a sales manager ( form
the job_id column) can earn a commission.
Null Values in Arithmetic Expressions
Arithmetic expressions containing a null value evaluate to null.
E.g
SELECT last_name, 12*salary*commission_pct
FROM employees;
LAST_NAME 12*SALARY*COMMISSION_PCT
Akinbode
Okafor
Amodu
Buba
Ngozi
Sowale

Next UNIT 6: Database Programming and JBDC

4.0 CONCLUSION
In this unit, you have learnt how to write basic SQL statements, using operators in SQL, how to use the
SQL ORDER statement, to arrange a group of data. Also the SQL INSERT statement was explained,
including how to update and delete rows in a table.

Next UNIT 6: Database Programming and JBDC

5.0 SUMMARY
• What you have learnt in this unit concerns
• Writing basic SQL statements
• Ordering a group of data using the ORDER BY statement
• Updating, Inserting and Deleting rows in a table using SQl statements
6.0 FURTHER READING AND OTHER RESOURCES
Introduction to SQL 9i from Oracle University
www.wiki_SQL.com
Database System Concepts, 5th Ed.
SQL Tutorials by James Hoffman, 1997.
Teach yourself SQL in 21 days, second edition, Macmillian computer publishing
SQL – A practical introduction by Akeel I. Din
An Introduction to Database Systems, Eighth Edition, C. J. Date, Addison Wesley, 2004, ISBN:

0-321-19784-4.