SQL Syntax I

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 3                                  SQL Syntax I

Table of Contents
1.0              Introduction
2.0              Objectives
3.0              Main Content
3.1              SQL Create Table Statement
3.1.1        SQL CREATE TABLE Syntax
3.1.2        CREATE TABLE Example
3.2              SQL SELECT Statement
3.2.1        SQL SELECT Syntax
3.2.2         An SQL SELECT Example
3.2.3        Navigation in a Result-set
3.3              The SQL SELECT DISTINCT Statement
3.3.1        SQL SELECT DISTINCT Syntax
3.3.2        SELECT DISTINCT Example
3.4              SQL WHERE Clause
3.4.1        SQL WHERE Syntax
3.4.2        WHERE Clause Example
3.4.3        Quotes Around Text Fields
3.4.4        Operators Allowed in the WHERE Clause 
3.5              SQL AND & OR Operators
3.5.1         AND Operator Example          
3.5.2        OR Operator Example
3.6              Combining AND & OR
4.0              CONCLUSION
5.0              SUMMARY
6.0              Tutor Marked Assignment
7.0              Further Reading and Other Resources
1.0              INTRODUCTION
This unit will introduce you to how write basic SQL programs such as creating tables, selecting a view
from a table and familiarize you with basic SQL operators.
2.0              OBJECTIVES
By the end of this unit, you should be able to:
•        Write simple SQL programs.
•        Familiarize yourself with standard keywords of the SQL programs

•        Understand how to construct a good SQL statement. 
•        Manage and access databases using thecreate, select, where and the logical
operators.
3.0              MAIN CONTENT
3.1      The CREATE TABLE Statement
The CREATE TABLE statement is used to create a table in a database.
3.1.1   SQL CREATE TABLE Syntax
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)
The data type specifies what type of data the column can hold. For a complete reference of all the data
types available in MS Access, MySQL, and SQL Server visit www.datatyperef.com
3.1.2   CREATE TABLE Example
Now we want to create a table called "Persons" that contains five columns: P_Id, LastName,
FirstName, Address, and City.
We use the following CREATE TABLE statement:
CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),

City varchar(255)
)
The P_Id column is of type int and will hold a number. The LastName, FirstName, Address, and City
columns are of type varchar with a maximum length of 255 characters.
The empty "Persons" table will now look like this:
P
_
I
d
LastName FirstName Address City
The empty table can be filled with data with the INSERT INTO statement.
3.2      The SQL SELECT Statement
The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.
3.2.1   SQL SELECT Syntax
SELECT column_name(s)
FROM table_name
and
SELECT * FROM table_name
Note: SQL is not case sensitive. SELECT is the same as select.
3.2.2   An SQL SELECT Example
The "Persons" table:
P_Id 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
Now we want to select the content of the columns named "LastName" and "FirstName" from the table
above.
We use the following SELECT statement:
SELECT LastName,FirstName FROM Persons
The result-set will look like this:
LastName FirstName
Akinbode Ola
Okafor Chris
Amodu Ali
SELECT * Example
Now we want to select all the columns from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
Tip: The asterisk (*) is a quick way of selecting all columns!
The result-set will look like this:
P_Id 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

3.2.3   Navigation in a Result-set
Most database software systems allow navigation in the result-set with programming functions, like:
Move-To-First-Record, Get-Record-Content, Move-To-Next-Record, etc.
3.3      The SQL SELECT DISTINCT Statement
In a table, some of the columns may contain duplicate values. This is not a   problem; however,
sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.
3.3.1   SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s)
FROM table_name
3.3.2   SELECT DISTINCT Example
The "PersonsOne" table:
P_Id LastName FirstName Address City
1 Akinbode Ola 10, Odeku Str.  Lagos
2 Okafor Chris
23,  Princewill
Drive
Lagos
3 Amodu Ali 20, Dauda lane Kaduna
Now we want to select only the distinct values from the column named "City" from the table above.
We use the following SELECT statement:
SELECT DISTINCT City FROM Persons
The result-set will look like this:
City
Lagos

Kaduna
3.4      SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified criterion.
3.4.1        SQL WHERE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator value
3.4.2   WHERE Clause Example
The "Persons" table:
P_Id 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
Now we want to select only the persons living in the city "Sandnes" from the table above.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City='Kaduna'

The result-set will look like this:
P_Id LastName FirstName Address City
1 Amodu Ali 20, Dauda lane Kaduna
3.4.3   Quotes Around Text Fields
SQL uses single quotes around text values (most database systems will also accept double quotes).
Although, numeric values should not be enclosed in quotes.
For text values:
This is correct:
SELECT * FROM Persons WHERE FirstName='Chris'
This is wrong:
SELECT * FROM Persons WHERE FirstName=Chris
For numeric values:
This is correct:
SELECT * FROM Persons WHERE Year=1965
This is wrong:
SELECT * FROM Persons WHERE Year='1965'
3.4.4   Operators Allowed in the WHERE Clause
With the WHERE clause, the following operators can be used:
Operator Description
= Equal
<>  Not equal

>  Greater than
<  Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want
to  return  for  at  least  one  of  the
columns
Note: In some versions of SQL the <> operator may be written as !=
3.5.     SQL AND & OR Operators
The AND & OR operators are used to filter records based on more than one condition.
The AND operator displays a record if both the first condition and the second condition is true while
the OR operator displays a record if either the first condition or the second condition is true.
3.5.1   AND Operator Example
The "Persons" table:
P_Id 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
Now we want to select only the persons with the first name equal to "Tove" AND the last name equal to 

"Svendson":
We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName='Ola'
AND LastName='Akinbode'
The result-set will look like this:
P_I
d
LastName FirstName Address City
1 Akinbode Ola 10, Odeku Str.  Lagos
3.5.2   OR Operator Example
Now we want to select only the persons with the first name equal to "Tove" OR the first name equal to
"Ola":
We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName='Chris'
OR FirstName='Ali'
The result-set will look like this:
P_I
d
LastName FirstName Address City
2 Okafor Chris 23, Princewill Drive Porthacourt
3 Amodu Ali 20, Dauda lane Kaduna
3.5.3   Combining AND & OR
You can also combine AND and OR (use parenthesis to form complex expressions).
Now we want to select only the persons with the last name equal to "Svendson" AND the first name
equal to "Tove" OR to "Ola":
We use the following SELECT statement:

SELECT * FROM Persons WHERE
LastName='Akinbode' OR LastName=’Okafor’
The result-set will look like this:
P_Id LastName FirstName Address City
1 Akinbode Ola 10, Odeku Str.  Lagos
2 Okafor Chris 23, Princewill Drive Porthacourt

Next UNIT 4:                                  SQL Syntax II

4.0              CONCLUSION
In this unit you have been introduced to the fundamental Queries of a typical database computing
environment e. g. SQL. You also learnt the specific operators that works for SQL statements. You were
also introduced to various SQL statements necessary in writing simple SQL codes.
5.0              SUMMARY
The summaries of what you have learnt are:-•        Structured Query Language (SQL) which is a standard language for accessing and
manipulating databases.
•        The syntax of 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 2.1    Create a table called student which will contain the following: student_id, studentname,
dept, level and grade?
Ans: Create Table Student
`                      (Student_id Varchar (25),
Student name Varchar (70),
Dept varchar2 (255),
Level char (12),
Grade number (3))
Exercise2.2     Write the syntax to insert into the table created in exercise 2.1
Ans:  INSERT INTO Student (Student_id, Student name, Dept,
Level, Grade)
VALUES (value1, value2, value3, value4, value5)

7.0              TUTOR MARKED ASSIGNMENT
Create a database performing all the discussed activities in this unit. i. e. create a table, insert into that
table etc.
8.0              FURTHER READING AND OTHER RESOURCES
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

Next UNIT 4:                                  SQL Syntax II