Transcript Structured Query Language (SQL)

LIS 384K.11
Database-Management
Principles and Applications
Introduction to SQL:
Structured Query Language
R. E. Wyllys
Copyright © 2002 by R. E. Wyllys
Last revised 2002 Feb 12
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
What Is SQL?
• Structured Query Language
– A widely used standard set of commands and
syntax for doing things with RDBMSs
– Used especially for query and retrieval
– Includes commands for defining RDBs, conducting
transactions, storing data, etc.
– Implemented in all major RDBMSs
– SQL does not handle all the practical details
involved in using a RDB. Hence, every RDBMS
has additional features (some of which may form
part of an extension of SQL for that particular
RDBMS).
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
History of SQL
• A prototype DB query and retrieval language
was developed by IBM in the early 1970s as
"Sequel"
• Other RDB developers and users recognized
the advantages of having a standard
language for manipulating RDBs
• This led to ANSI adoption of SQL as an
enhanced RDB language based on Sequel
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
History of SQL
• Standardized Versions of SQL
– Work toward an ANSI standard version started in
1983
– Further drafts in 1986 and 1988 led to SQL-1 in
1989
– SQL-2, adopted as an ANSI and ISO standard in
1992, is the version generally used at present
– The latest ANSI-ISO standard, 1998-1999, SQL-3,
has not yet been widely implemented.
• SQL-3 contains new features that help in the construction
of "object-relational" databases, i.e., RDBs that can
handle objects in the technical sense: viz., sets of data
together with program code that operates on the data.
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Syntax Conventions:
Backus-Naur Form
• SQL (like other computer languages) is formally
defined in a notation called "Backus-Naur Form"
• History of Backus-Naur Form (BNF)
– In 1959 John Backus of IBM devised Backus Normal Form
as a concise notation for describing components of a
programming language (Algol 58, the first high-level
programming language)
– In 1960 Peter Naur, a Danish programmer, refined Backus's
notation
– Result is Backus-Naur Form (the preferred name for for the
notation in current use, though this is still usually called
Backus Normal Form)
• Use of BNF
– Backus-Naur Form makes it possible to define SQL (and
other programming languages) concisely
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Backus-Naur Form
• Ingredients of BNF
– When parentheses "()" are provided in the
definition of a command, this tells you that you
must use the parentheses, as shown, when you
write out the command.
– | (vertical bar) means an "exclusive or"; that is,
"either but not both" or "any one but not all"
– "," (comma) means a "nonexclusive or"; that is,
"none, or one, or more than one"
– "..." (ellipses) mean that you may repeat the
previous item as many times as you wish
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Backus-Naur Form
• Ingredients of BNF (cont'd)
– "<>" (angle brackets) are used to contain a
generic name for a type of item; they mean that
you are to replace them and their contents by the
actual name of the item named generically
between the "<>"
• Example: "SELECT <tablename>" means that if, say,
your RDB contains a table named "students" and you
want to perform a select operation on this table, you
should write
SELECT students
– Note: When confusion is unlikely to occur, angle
brackets are often omitted in definitions of SQL
commands; i.e., you will often see statements like
"SELECT tablename"
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Backus-Naur Form
• Ingredients of BNF (cont'd)
– {} (braces) mean you must choose at least
one of the enclosed choices, which may be
separated by "|" or "," (i.e., by an "exclusive
or" or a "non-exclusive or")
– Examples
• {a|b|c} means you must choose exactly one of
the choices
• {a,b,c} means you must choose one or more of
the choices
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Backus-Naur Form
• Ingredients of BNF (cont'd)
– [] (brackets) mean that you may choose
one or more of the enclosed choices,
which may be separated by "|" or ","
– Examples
• [a|b|c] means that you may choose at most one
of the choices
• [a,b,c] means that you may choose none, one,
or several of the choices
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Examples of SQL Commands
• CREATE DATABASE database_name
– Example
• CREATE DATABASE library_catalog
• CREATE DATABASE employees
• CREATE INDEX index_name ON table_name
(column_name [, column_name]...)
– Examples
• CREATE INDEX publisher_ndx ON monographs
(pub_name)
• CREATE INDEX name_ndx ON employee_names
(emp_lname, emp_fname)
– Note: This second example shows the creation of a
composite index.
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Examples of SQL Commands
• CREATE TABLE table_name (column_name
datatype [NULL | NOT NULL] [, column_name
datatype [NULL | NOT NULL] ]...)
– Examples
• CREATE TABLE employee_names (SSN CHAR(11)
NOT NULL, emp_lname CHAR(20), emp_fname
CHAR(15), emp_bdate DATE(mm"/"dd"/"yy))
• CREATE TABLE cataloging_staff (catlgr_fname
CHAR(15), catlgr_lname CHAR(20), workstation_num
CHAR(3))
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Examples of SQL Commands
• DELETE FROM table_name WHERE
select_statement
– Examples
• DELETE FROM employee_names WHERE
SSN = "123-45-6789"
• DELETE FROM monographs WHERE
copyright_date LESS THAN 1910 AND
WHERE language = "Dutch"
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Examples of SQL Commands
• INSERT INTO table_name [(column_list)]
VALUES (value1, value2, ...)
– Examples
• INSERT INTO employee_names VALUES ("987-654321", "Lee", "Jerry", "07/04/68")
• INSERT INTO employee_names (SSN, emp_fname)
VALUES ("001-23-4456, "Socrates")
• INSERT INTO cataloging_staff VALUES ("Jerry", "Lee",
"3")
– Note: The first example results in the addition to the
employee_names table of a new row with the entries
specified. The second example will result in the addition to
the employee_names table of a row with "001-23-4456" in
the first column, "Socrates" in the third column, and nulls in
the second and fourth columns.
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Examples of SQL Commands
• SELECT [ALL | DISTINCT] select_col_list FROM
{table_name | view_name} [, {table_name |
view_name}] ... [WHERE
selection_condition_statement] [GROUP BY
column_name [, column_name]...] [HAVING
search_conditions] [ORDER BY {column_name |
select_list-number} [ASC | DESC] [, {column_name |
select_list_number} [ASC | DESC]...}
– Examples
• SELECT emp_fname, emp_lname FROM employees
WHERE SSN="123-45-6789"
• SELECT ALL FROM cataloging_staff ORDER BY
workstation_num
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Examples of SQL Commands
• SELECT [ALL | DISTINCT] select_column_list FROM
{table_name | view_name} [, {table_name |
view_name}] ... [WHERE
selection_condition_statement] [GROUP BY
column_name [, column_name]...] [HAVING
search_conditions] [ORDER BY {column_name |
select_list-number} [ASC | DESC] [, {column_name |
select_list_number} [ASC | DESC]...}
– Example
• SELECT SSN, emp_fname, emp_lname,
workstation_num FROM employees, cataloging_staff
WHERE SSN="987-65-4321" AND WHERE
catlgr_lname = emp_lname AND WHERE
catlgr_fname=emp_fname
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
Examples of SQL Commands
• SELECT [ALL | DISTINCT] select_column_list FROM
{table_name | view_name} [, {table_name |
view_name}] ... [WHERE
selection_condition_statement] [GROUP BY
column_name [, column_name]...] [HAVING
search_conditions] [ORDER BY {column_name |
select_list-number} [ASC | DESC] [, {column_name |
select_list_number} [ASC | DESC]...}
– Example
• SELECT mono_title FROM monographs WHERE
au_lname="Asimov" AND au_fname="Isaac" GROUP BY
fict_non_fict_marker ORDER BY mono_title asc
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications
The World is Full of Files
SQL Helps Us Handle Them in RDBMSs
GSLIS - The University of Texas at Austin
LIS 384K.11, Database-Management Principles and Applications