Transcript Lecture slides

COIT 13143
Database Programming and Administration
Lecture 1


Welcome to Database Programming and Administration
Course
Aim is to provide students with introduction to
contemporary database development and server
technologies being used to support Internet
Database Application.
Text Book : Kroenke, David M. (2002)
Database Processing – Fundamentals,
Design and Implementation (8th Edition)
Prentice Hall International, Inc.
Prepared by: Mohasin Ali Unia and
Michael Gregory
About Course
More emphasis is placed on Assignments
 Less theoretical contents
 Open book Exam
 Goal is to provide platform for undertaking
database projects.

Course Contents
There are 4 modules:
 More SQL
 Development Technologies
 Database Server Technologies
 Internet Database Application
Technologies
Prepared by: Mohasin Ali Unia and
Michael Gregory
Subject Evaluation Criteria
Assignment 1 (End of 7th Week)
Assignment 2 (End of 10th Week)
Final Exam (Open book)
- 40%
- 20%
- 40%
------100%
-------
Assignment Questions are available in the Course Profile
Prepared by: Mohasin Ali Unia and
Michael Gregory
Assignment 1

Covers:
– Professional Form Development using Access
(In preparing for Assignment 1 we will cover
Module 2 and an introduction to Access)
Notes on Assignment 1





Assignment 1 is due in Week –7 (Friday)
Assignment 1 is big.
You will be able to make start this week.
For Assignment 1, you will be asked to submit a
log of all of your work on the Assignment (Read
Course Profile)
For each session of your work on Assignment 1 ,
record –
–
–
–
–
–
What you achieved
Problems encountered
Date and number of hours worked
Keep it brief
A log between 2 or 4 A4 pages is expected.
Study Schedule





In the first few weeks we will introduce
Application Development in Access.
Module 1 covers mostly SQL
Module 2 covers Database Development
Technologies like Embedded SQL, 4GLs and
RAD’s.
We will also cover Database Application
Architecture – Centralized, Client/Server, FileServer and Distributed.
Module 3 and 4 will be covered later in the term
What is SQL




Structured Query Language (SQL), is the most
important relational data manipulation language in use
today.
It is been endorsed by the American National Standard
Institute (ANSI) as the language of choice for
manipulating relational databases, and it is the data
access language used by many commercial DBMS
products, including Oracle, Sybase, SQL Server, dBase,
Microsoft Access etc.
SQL commands can be used interactively as a query
language or they can be embedded in application
programs.
SQL is not a programming language, it is a data sublanguage or data access language.
Database Tables



Databases contain objects called Tables.
Records of data are stored in these tables. Tables are
identified by names (like "Persons", "Orders",
"Suppliers").
Tables contain Columns and Rows with data. Rows
contain records (like one record for each person).
Columns contain data (like First Name, Last Name,
Address, and City).
LastName
FirstName
Address
City
Hansen
Ola
Timoteivn 10
Sandnes
Svendson
Tove
Borgvn 23
Sandnes
Pettersen
Kari
Storgt 20
Stavanger
LastName, FirstName, Address, and City are table Columns. The
Rows contain 3 records about 3 persons.
SQL Queries
With SQL, we can Query a database and
have a Result returned in a tabular form.
 A Query like this:

SELECT LastName FROM Persons
give a Result like this:
LastName
Hansen
Svendson
Pettersen
SQL Data Manipulation
As the name suggests, SQL is a syntax for
executing queries. But the SQL language also
includes a syntax to update records, insert new
records and delete existing records.
 These query and update commands together form
the Data Manipulation Language (DML) part of
SQL:

–
–
–
–
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT - inserts new data into a database
SQL Data Definition


The Data Definition Language (DDL) part of SQL
permits database tables to be created or deleted. We can
also define indexes (keys), specify links between tables,
and impose constraints between database tables.
The most important DDL statements in SQL are:
–
–
–
–
–
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
Result Table

The result from a SQL query is stored in a
result set. The result set can be thought of as
a result table. Most database software allow
navigation of the result set with
programming functions like: Move-ToFirst-Record, Get-Record-Content, MoveTo-Next-Record......
Where Clause

The WHERE clause is used to specify a selection
criterion.
SELECT column FROM table WHERE column condition value

With the WHERE clause, these conditions can be
used: Operator
Condition
=
Equal
<>
Not equal
>
Greater than
<
Less than
>=
Greater than or equal
<=
Less than or equal
BETWEEN
Between an inclusive range
LIKE
Explained next page

This SQL statement will return persons with a first name
that start with an 'O'.
SELECT * FROM Persons WHERE FirstName LIKE 'O%'

This SQL statement will return persons with a first name
that end with an 'a'.
SELECT * FROM Persons WHERE FirstName LIKE '%a'

This SQL statement will return persons with a first name
that contains the pattern 'la'.
SELECT * FROM Persons WHERE FirstName LIKE '%la%'

All the examples above will return the following result:
LastName
FirstName
Address
City
Year
Hansen
Ola
Timoteivn 10
Sandnes
1951
BETWEEN ... AND

The BETWEEN ... AND operator selects an inclusive
range of data between two values. These values can be
numbers, text, or dates.
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2


Example 1
To display the persons alphabetically between (and
including) "Hansen" and "Pettersen", use the following
SQL:
SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'


Example 2
To display the persons outside the range used in the
previous example, use the NOT operator:
SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Hansen' AND 'Pettersen'
Group By and Having





The GROUP BY keywords have been added to SQL
because aggregate functions (like SUM) return the
aggregate of all column values every time they are
called.
Without the GROUP BY functionality, finding the sum
for each individual group of column values was not
possible. SELECT column,SUM(column) FROM table GROUP BY column
The HAVING keyword has been added to SQL because
a WHERE keyword cannot be used against aggregate
functions (like SUM).
Without the HAVING keyword it would not be possible
to test for function result conditions.
The syntax for the HAVING function is:
SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column)
condition value


GROUP BY Example
This "Sales" Table:
Company
W3Schools
IBM
W3Schools


SELECT Company, SUM(Amount) FROM Sales
And This SQL:
Returns this result:
Company
W3Schools
IBM
W3Schools


Amount
5500
4500
7100
SUM(Amount)
17100
17100
17100
The above code is invalid because the
column returned is not part of an aggregate.
A GROUP BY clause will correct, as in this
SQL:
SELECT Company,SUM(Amount) FROM Sales GROUP BY Company
Returns this result:
Company
W3Schools
IBM
SUM(Amount)
12600
4500
Having Example

This SQL:
SELECT Company,SUM(Amount) FROM Sales GROUP BY Company
HAVING SUM(Amount)>10000

Will give the result:
Company
SUM(Amount)
W3Schools
12600
SQL Join


Sometimes we have to select data from two tables to make
our result complete. We have to perform a join.
Tables in a database can be related to each other with keys.
– A primary key is a column with a unique value for each row. The
purpose is to bind data together, across tables, without repeating all
of the data in every table.




In the "Employees" table next, the "ID" column is the
primary key, meaning that no two rows can have the same
ID. The ID distinguishes two persons even if they have the
same name.
When you look at the example tables next, notice that:
The "ID" column is the primary key of the "Employees"
table
The "ID" column in the "Orders" table is used to refer to the
persons in the "Employees" table without using their names


Employee Table :
ID
Name
01
Hansen, Ola
02
Svendson, Tove
03
Svendson, Stephen
04
Pettersen, Kari
Order Table :
ID
Product
01
Printer
03
Table
03
Chair
Referring to Two Tables
We can select data from two tables by referring to
two tables, like this:
 Example
 Who has ordered a product, and what did they
order?

SELECT Employees.Name, Orders.Product FROM Employees,
Orders WHERE Employees.ID = Orders.ID
Name
Product
Hansen, Ola
Printer
Svendson, Stephen
Table
Svendson, Stephen
Chair
Using Joins

OR we can select data from two tables with the JOIN
keyword, like this (this is the preferred way to do it):
Example INNER JOIN
Syntax SELECT field1, field2, field3 FROM first_table INNER JOIN second_table

Who has ordered a product, and what did they order?





ON first_table.keyfield = second_table.foreign_keyfield
SELECT Employees.Name, Orders.Product FROM Employees
INNER
JOIN Orders ON Employees.ID = Orders.ID
LEFT JOIN
Syntax
SELECT field1, field2, field3 FROM first_table LEFT JOIN second_table ON
first_table.keyfield = second_table.foreign_keyfield
The LEFT JOIN returns all the rows from the first table, even if there are
no matches in the second table. If there are rows in First Table that do not
have matches in Second Table, those rows also will be listed


Right Join
Syntax:
SELECT field1, field2, field3 FROM first_table RIGHT JOIN
second_table ON first_table.keyfield = second_table.foreign_keyfield

The RIGHT JOIN returns all the rows
from the second table, even if there are
no matches in the first table. If there
had been any rows in the Second Table
that did not have matches in First Table
, those rows also would have been
listed.
Week Activities

Subscribe to Majordomo
– To subscribe to the mailing list for this course, send an
email message to [email protected]
– and in the body of the message put
subscribe coit13143-t3
end
– To post to the list, send your message to
[email protected]


Complete exercises given in the Study guide.
Start working on Assignment 1
– Assignment 1 is big, worth 40% of the Assessment
– Aim at:
» read and understand assignment 1
» download and explore the database file for assignment 1
» Set up your journal