Transcript SQL Origins

SQL

(CSCE 330)
Team Members



Bushira Kiyemba
La’Trice Johnson
Curtis Merriweather
1
SQL Origins


Relational Model formally defined by
Dr. E.F. Codd in 1970’s, reflects SQL’s
conceptual core
A Relational Model of Data for Large Shared Data Banks
2
SQL Origins



Designed by IBM research center in
1974-1975
System/R project, prototype of a
relational database
Originated as SEQUEL, structured
English Query Language, hence the
pronunciation of SQL
3
SQL Origins


1979, commercial relational database
management system – Oracle, used
SQL as its query language
ANSI approved SQL as official standard
in 1986, ISO standardized in 1987
4
Database Concepts

A database is a software program which
allows for storage and retrieval of
information on a computer hard drive’s
file system or other device. A relational
database is a database that allows for
queries which typically use Structured
Query Language (SQL) to store and
retrieve data.
5
Database Concepts

Relational databases allow for more
efficient queries which use less CPU
power and memory allocation, as they
are optimized for efficiency. However,
connecting to a database is significantly
slower than just reading a simple file off
of your computer’s hard drive. The
added features of a relational database
make this speed decrease worthwhile in
many situations.
6
Database Concepts

One of the most popular databases
used for dynamic web database
applications is called mySQL. It is
commonly installed on popular web
hosting provider’s Unix and Linux
servers.
7
Database Concepts

Data in relational databases is stored in
four primary areas: databases, tables,
rows, and columns. A database is an
area within the database software that
contains all the tables that make up this
particular set of data.
8
SQL Column Data Types






Auto Increment ID Fields
Varchar
Char
Integer
Blob
Bit
9
Additional SQL Commands

Show -

Describe -

Insert -
Show can be used to show all database
tables in a particular database, or all databases in a
particular server.
Describe lets you see the structure of
an existing database table
Insert is how you input information into
the database.

Update - Update lets us update values inside an
existing row inside a table.
10
Additional SQL Commands


Delete - Delete lets us delete rows inside a table.
Select - Does queries on the database to extract
data from it.
11
Table Creation
CREATE TABLE STATION
(ID INTEGER PRIMARY KEY,
CITY CHAR(20),
STATE CHAR(2),
LAT_N REAL,
LONG_W REAL);
12
Table Population

INSERT INTO STATION VALUES (13,
'Phoenix', 'AZ', 33, 112);
INSERT INTO STATION VALUES (44,
'Denver', 'CO', 40, 105);
INSERT INTO STATION VALUES (66,
'Caribou', 'ME', 47, 68);
13
Selection Statement
SELECT * FROM STATION;
ID
CITY
STATE
LAT_N
LONG_W
13
Phoenix
AZ
33
112
44
Denver
CO
40
105
66
Caribou
ME
47
68
14
SQL and other Languages
There are three main means for integrating SQL into a
language:

Direct

Embedded support like Powerbuilder

Indirect embedded support through a pre-processor

Database API support
Examples:

SQL and Python

SQL and Perl
15
Embedding SQL in Python
SELECT name INTO :my_name
FROM segments
WHERE segment = :my_segment;
16
Embedding SQL in Perl
<?PERL>
my @data;
<?SQL SQL="select name, address,
from people
where name like '%' || ? || '%'"
PARAMS="$search_name"
MY VAR="$n, $a, $p">
push @data, {
name => $n, address => $a, phone => $p, };
<?/SQL>
use Data::Dumper;
print Dumper (\@data);
<?/PERL>
17
Conclusion
The Structured Query Language (SQL) forms
the backbone of most modern database
systems. It operates on sets of data rather
than one data element at a time. With a
single statement, you can get just exactly the
answer you wanted from gigabytes of data in
a millisecond.
SQL is not particularly expressive and hence
the need for procedural languages and other
proprietary extensions
18
Sources
http://www.itworld.com/nl/db_mgr/05142001/
http://networking.webopedia.com/TERM/S/SQL.html
http://www.opengroup.org/public/tech/datam/sql.htm
http://databases.about.com/cs/sql/?once=true&
http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=6
http://www.geocrawler.com/archives/3/184/2000/12/0/4789047
http://www.python.org/search/hypermail/python-recent/0551.html
http://www.itl.nist.gov/div897/ctg/dm/sql_examples.html#create%20table
http://www.expertwebinstalls.com/cgi_tutorial/basic_relational_database_concept.html
http://developer.mimer.com/documentation/html_82/Mimer_SQL_Reference_Manual
l
19