SQL Syntax II

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 4                                              SQL Syntax II

Table of Contents
1.0              Introduction
2.0              Objectives
3.0              Main Content
3.1              The ORDER BY Keyword
3.1.1        SQL ORDER BY Syntax
3.1.2        ORDER BY Example
3.2              SQL INSERT INTO Statement
3.2.1   SQL INSERT INTO Syntax
3.2.2   SQL INSERT INTO Example
3.3              SQL UPDATE Statement
3.3.1        SQL UPDATE Syntax
3.3.2   SQL UPDATE Example
3.4              SQL DELETE Statement
3.4.1        SQL DELETE Syntax
3.4.2   SQL DELETE Example
3.4.3   Delete All Rows
3.4      JOINING Tables
4.0              CONCLUSION
5.0              SUMMARY
6.0              Tutor Marked Assignment
7.0              Further Reading and Other Resources

1.0      INTRODUCTION

This unit introduces you to how to 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 the create, select, where and the logical
operator.

3.0        MAIN CONTENT

3.1      THE ORDER BY Keyword
3.1.1   ORDER BY Syntax
The ORDER BY keyword is used to sort the result-set. The ORDER BY keyword is used to sort the
result-set by a specified column. The ORDER BY keyword sorts the records in ascending order by  

default. If you want to sort the records in a descending order, you can use the DESC keyword. The
order by clause comes last in a select statement.
The syntax of the order by clause given below;
SELECT  expr
FROM  table
[WHERE  condition(s)]
[ORDER BY{column, expr} [ASC|DESC]          
In the syntax,
ORDER BY     specifies the order in which the retrieved rows are displayed
ASC                  specifies rows in ascending order (this is the default value)
DESC                 order the rows in descending order
With the ORDER BY clause;
•        Numeric values are displayed with the lowest value first e.g 1-999
•        Date values are displayed with the earliest value first e.g 01-JAN-92 before 01-JAN-95
•        Character values are displayed in alphabetical order 
•        Null values are displayed last for ascending sequences and first for descending sequences
3.1.2   ORDER BY EXAMPLE
Sorting in Descending order
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY last_name;
LAST_NAME  JOB_ID  DEPARTMENT_ID  HIRE_DATE 
Akinbode SA_REP  80  21-APR-00 

Amodu SA_REP  20  21-APR-00 
Buba SA_REP  80  24-MAR-00 
Ngozi ST_CLERK  50  08-MAR-00 
Okafor SA_REP  80  23-FEB-00 
Sowale ST_CLERK  50  06-FEB-00 
We also sort using multiple columns.
For example,
SELECT last_name, department_id, salary FROM employees
ORDER BY department_id, salary DESC;
3.2         INSERT STATEMENT
3.2.1       INSERT INTO Syntax
The INSERT INTOstatement is used to insert new records statement is used to insert a new row in a
table.
The syntax of the insert statement is;
INSERT IBTO table [{column, [,column…..])]
VALUES (value [, value….]);

In the syntax,
table  is the name of the table
column  is the name of the column
value       is the corresponding value for the column
3.2.2   INSERT STATEMENT EXAMPLE
INSERTING NEW ROWS
E.G
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES  (170, ‘Public Relations’,100,1700); 
INSERTING ROWS WITH NULL VALUES
Implicit method example
INSERT INTO departments (department_id, department_name) 
Values (30,’Purchasing’);
Explicit Method example
INSERT INTO departments 
Values (100, ‘Finance’, NULL, NULL);
INSERTING SPECIAL VALUES
Example
INSERT  INTO  employees  (employee_id,  first_name,  last_name,  email,  phone_number,  hire_date,
job_id,salary, commission_pct, manager_id, department_id )
Values (7, ‘Adeola’, ‘Chalse’, ‘ade_char’, ‘2348039990985’, SYSDATE, ‘AC_ACCOUNT’, 6900,
NULL, 205, 100);
3.3         UPDATE STATEMENT
3.3.1        UPDATE STATEMENT Syntax

The UPDATE statement is used to update existing records in a table.
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
Note:Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record
or records that should be updated. If you omit the WHERE clause, all records will be updated!
3.3.2      SQL UPDATE EXAMPLES
updating rows in a table
UPDATE employees
SET department_id=70
WHERE employee_id = 113;
3.4      SQL DELETE Statement
3.4.1       SQL DELETE SYNTAX
The DELETE statement is used to delete records and rows in a table
DELETE FROM table_name
WHERE some_column=some_value
3.4.2        SQL DELETE EXAMPLES
DELETE * 
FROM employees;
DELETE FROM employees
Where department_id =60;
3.4.3   Delete All Rows

It is possible to delete all rows in a table without deleting the table. This means that the table structure,
attributes, and indexes will be intact:
DELETE FROM table_name
or
DELETE * FROM table_name
Note: Be very careful when deleting records. You cannot undo this statement!
3.5      JOINING TABLES
The Select statement can be used to join two tables together. It can be used to extract part of
Table A and part of Table B to form Table C. For example, assuming studentand studentclassare two
different tables. Let us look at this instruction:-Select student.SID, student.name, studentclass.classname
From sudent, studentclass
Where student.SID = studentclass.SID
This statement shows that SID, name are columns or fields from student table and classname and SID
are also columns from studentclass table.
The fields in the new table to form by this instruction are:-SID                name  classname

Next UNIT 5:                                  More SQL Statements

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 statement syntax 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 syntax of different SQL statement
•        What SQL statement can be used for
6.0        EXERCISES
Exercise 2.1    Write the SQL statement to delete two rows from student, Name and grade = 56?
Ans:         DELETE FROM student
WHERE Name='Tjessem' AND grade= 56
`                      
Exercise 2.2    What is the syntax to arrange the element of table in Ascending and Descending order.
Ans:         SELECT * FROM Tablename

ORDER BY LastName DESC

Next UNIT 5:                                  More SQL Statements