Transcript Structured Query Language (SQL)

Structured Query Language
(SQL)
CIS*2450
Advanced Programming Concepts
1
Structured Query Language
• SQL consists of a set of commands for defining,
accessing, and managing relational databases.
• In 1970, E.F. Codd at IBM Research Labs in San
Jose, California published “A Relational Model of
Data for Large Shared Data Banks”
(Communications of the ACM, Vol. 13, No. 6,
June 1970) in which he described a set of abstract
principles for database management - the
relational model. The field of relational database
technology has its origins in that paper.
2
Structured Query Language
• This research described several relational
languages which implement some/all of the
features of the abstract relational model.
One of these languages which was created
in 1974-75 was the “Structured English
Query Language” (SEQUEL), defined by
Donald Chamberlin and others at IBM
Labs. The first IBM prototype was called
SEQUEL-XRM (1974-75).
3
Structured Query Language
• A revised version of SEQUEL-XRM was defined
in 1976-77 and named SEQUEL/Z. The name
was changed to SQL for legal reasons.
• A prototype of this became operational in 1977
called System R.
• Due to the success of System R, vendors rushed to
create their own SQL products. The Oracle
database was released before IBM's own product.
4
Structured Query Language
• In 1982, the American National Standards Institute
(ANSI) chartered its Database Committee (X3H2)
to develop a standard for a relational language. In
1986, the X3H2 proposal was ratified by ANSI
which consisted essentially of the IBM dialect of
SQL.
• In 1987, the ANSI standard was accepted as an
international standard by the International
Organization for Standards (ISO).
5
Structured Query Language
• The original standard is also known as SQL/86.
• Enhancements were made over time
– SQL/89 - included an Integrity Enhancement Feature
– SQL/92 - ISO and ANSI developed a revised standard
also known as SQL2
– SQL/99 - “SQL3” incorporates object-oriented access
– A consortium of vendors known as the SQL Access
Group has been working to enhance interoperability
across different systems
6
SQL as a Standard
• Since SQL is such a pervasive standard, let
us review some information on standards their good points and their not so good
points.
7
Are Standards Good? -- Good
Points
• Standards reduce training costs.
• They promote application portability.
• Standards promote application longevity.
– Standards have a reasonably long life, so
applications using them should as well.
8
Are Standards Good? -- Good
Points
• Intersystem Communications
– are more easily achieved.
– Different database management systems can function
equally well on a single database if they support the
same standard interface.
• Customer Choice
– If products support the same interface then customers
can focus on the implementation that best meets their
own needs without having to choose among different
interfaces.
9
Are Standards Good? -- Bad
Points
• Standards can stifle creativity - system
implementers may be prevented from
providing “the best” solution because the
standard prescribes some alternative.
• SQL has some flaws in its design, some of
which are considered severe.
10
SQL Overview
• SQL is used to define, manipulate, and
control data in relational databases.
• A relational database is one which is
perceived as a collection of tables by the
user.
• A table is an unordered collection of rows.
11
A Suppliers-and-Part Database
Example
• Tables
– S : suppliers
– P : parts (and where to ship an order)
– SP : shipment of parts by suppliers
12
Table S
SNO
SNAME
STATUS
CITY
S1
Smith
20
London
S2
Jones
10
Paris
S3
Blake
30
Paris
S4
Clark
20
London
Primary Key = SNO
13
Table P
PNO
PNAME
P1
Nut
Red
12
London
P2
Bolt
Green
17
Paris
P3
Screw
Blue
17
Rome
P4
Screw
Red
14
London
Primary Key = PNO
COLOUR WEIGHT CITY
14
Table SP
SNO
PNO
QTY
S1
P1
300
S1
P2
200
S2
P1
300
S3
P2
200
S4
P4
400
Primary Key = SNO and PNO
15
Tables
• Tables can be thought of as files, with rows
representing records and the columns as fields.
• The SQL standard always uses the terms table,
row and column.
• SQL statements can be invoked either interactively
or from within an application.
– Interactive SQL generally displays the results on the
screen.
– Invocation from a program means the results are made
available as input to the program.
16
The Select Statement
• SQL Query:
SELECT S.CITY
FROM S
WHERE S.SNO = 'S4'
• Result:
CITY
------London
17
SELECT
• SELECT is used for data retrieval.
• FROM indicates the table from which to retrieve
the data.
• WHERE is used to describe column features that
are desired in the retrieved data.
– No WHERE statement will cause all rows to be
returned.
– You can use all of the standard comparisons
(<,<=,>,>=,=).
– Literal strings must appear in single quotes.
18
Creating Tables
• Empty tables are constructed using the
CREATE TABLE statement.
• Data must be entered later using INSERT.
CREATE TABLE S ( SNO CHAR(5),
SNAME CHAR(20),
STATUS DECIMAL(3),
CITY CHAR(15),
PRIMARY KEY (SNO) )
19
Creating Tables
• A table name and unique column names
must be specified.
• Columns which are defined as primary keys
will never have two rows with the same key
value.
• Primary key may consist of more than one
column (values unique in combination)
called composite key.
20
Creating Tables
CREATE TABLE SP ( SNO CHAR(5),
PNO CHAR(5),
QTY DECIMAL(5),
PRIMARY KEY (SNO,PNO),
FOREIGN KEY (SNO) REFERENCES S,
FOREIGN KEY (PNO) REFERENCES P )
• Foreign keys refer to other tables.
– Any key in SP.SNO must also appear in S.SNO and any
key in SP.PNO must appear in P.PNO - a shipment
cannot exist unless the supplier and part also exist.
21
Data Manipulation
• There are four basic SQL data manipulation
operations.
–
–
–
–
SELECT - retrieves data
INSERT - add a new row
UPDATE - change values in existing records
DELETE - remove row(s)
22
INSERT
INSERT
INTO SP ( SNO, PNO, QTY )
VALUES ( 'S4', 'P1', 1000 )
• Row added to Table SP.
23
UPDATE
UPDATE S
SET STATUS = 2 * S.STATUS
WHERE S.CITY = 'London'
• Status doubled for suppliers in London (S1
and S4)
24
DELETE
DELETE
FROM P
WHERE P.WEIGHT > 15
• Rows deleted from P where WEIGHT > 15
(P2 and P3)
25
SELECT
• SELECT has the general form
SELECT-FROM-WHERE.
• The result is another (new) table.
26
SELECT
SELECT DISTINCT
P.COLOUR, P.CITY
FROM P
WHERE P.WEIGHT > 10
AND P.CITY <> 'Paris'
results in the table,
COLOUR CITY
---------------Red London
Blue Rome
[ Red London ] - eliminated
because of
DISTINCT
statement which
removes multiple copies of rows
27
SELECT
• DISTINCT - no duplicate rows.
• No WHERE - all rows of FROM table are
returned.
• SELECT * is short for select the entire row
(all columns).
28