Transcript No Slide Title

Structured Query Language
(SQL)
BUAD/American University
SQL
1
Structured Query Language (SQL)
• SQL (pronounced "ess-que-el") stands for Structured
Query Language.
• SQL is used to communicate with a database.
According to ANSI (American National Standards
Institute),
• it is the standard language for relational database
management systems.
• SQL statements are used to perform tasks such as
update data on a database, or retrieve data from a
database
BUAD/American University
SQL
2
Structured Query Language (SQL)
• Common relational database management systems that
use SQL are: Oracle, Sybase, Microsoft SQL Server,
Access, Ingres, etc.
• most database systems use SQL, most of them also have
their own additional proprietary extensions that are
usually only used on their system.
• Standard SQL commands such as "Select", "Insert",
"Update", "Delete", "Create", and "Drop" can be
used to accomplish almost everything that one needs to
do with a database
BUAD/American University
SQL
3
History of SQL
• “Sequel” project was initiated by IBM Research
Lab in San Jose : 1974-79
• First RDBMS to implement SQL: SQL/DS
• Other products: INGRES, Oracle, Sybase,
Informix
• ISO and ANSI ratified SQL92 standard -> SQL3
• Implemented in both mainframe and client/server
BUAD/American University
SQL
4
Benefits of a Standardized
Relational Language
•
•
•
•
•
•
Reduced training costs.
Productivity.
Application portability.
Application longevity.
Reduced dependence on a single vendor.
Cross-system communication.
BUAD/American University
SQL
5
Terminology
• Data Definition Language (DDL):
– Commands that define a database, including creating,
altering, and dropping tables and establishing
constraints.
• Data Manipulation Language (DML)
– Commands that maintain and query a database.
• Data Control Language (DCL)
– Commands that control a database, including
administering privileges and committing data.
BUAD/American University
SQL
6
Simple SELECT statements
• query the database and retrieve selected data that
match the criteria that you specify.
• format of a simple select statement:
select "column1"[,"column2",etc] from "tablename"
•
[where "condition"];
[] = optional
BUAD/American University
SQL
7
Select Command Description
• The column names that follow the select keyword
determine which columns will be returned in the results.
You can select as many column names that you'd like, or
you can use a "*" to select all columns.
• *The table name that follows the keyword from specifies
the table that will be queried to retrieve the desired results.
• *The where clause (optional) specifies which data values
or rows will be returned or displayed, based on the criteria
described after the keyword where.
BUAD/American University
SQL
8
Conditional selections used in where clause:
• = Equal
• > Greater than
• < Less than
• >= Greater than or equal to
• <= Less than or equal to
• <> Not equal to
• LIKE
BUAD/American University
SQL
9
Like
• Pattern matching operator can also be used in the
conditional selection of the where clause.
• Selects only rows that are "like" what you specify.
• Percent sign "%" can be used as a wild card to match any
possible character that might appear before or after the
characters specified. For example:
• Select first, last, city from empinfo
• where first LIKE 'Er%';
• This SQL statement will match any first names that start
with 'Er'. Strings must be in single quotes.
BUAD/American University
SQL
10
Simple Select
Retrieve the record for Team 20.
SELECT *
FROM TEAMS
WHERE TEAMNUM=20;
20 Yankees New York Simpson
BUAD/American University
SQL
11
Defining a Database
• CREATE DATABASE databasename
• CREATE TABLE tablename (column_name
datatype [NULL|NOT
NULL][column_contraint_clause]...
column_name datatype [NULL|NOT
NULL][column_contraint_clause]);
BUAD/American University
SQL
12
Constraint Clause
• CONSTRAINT constraint_name
[PRIMARY KEY primary_key|FOREIGN
KEY foreign_key REFERENCES
primary_key]
• Establishes referential integrity between
two tables
BUAD/American University
SQL
13
Modifying Table Structure
• ALTER TABLE table_name ADD
(column_name data_type) …
(column_name data_type) )
• Removing Table: DROP TABLE
table_name
BUAD/American University
SQL
14
Inserting, Updating, and
Deleting Data
• INSERT INTO table_name VALUES
(column1_value, column2_value … )
• To delete all rows: DELETE FROM
table_name
• UPDATE table_name SET column_name =
new_column_value
BUAD/American University
SQL
15
Processing operations
• Selection: selection of a subset of rows in
table(s)
• Projection: selection of a subset of columns
from table(s)
• Join: joining of columns from 2 or more
tables based on a certain condition
• Result of the above operations is a
“relation”
BUAD/American University
SQL
16
Processing Single Tables
• SELECT: list of columns from base tables
to be projected in to the result table
• FROM: identifies the table from which
columns will be chosen to appear in the
result table
• WHERE: includes the conditions for row
selection within a singe table
– conditions can be simple or a combination
BUAD/American University
SQL
17
Retrieve Table
Retrieve the entire Teams table.
SELECT *
FROM TEAMS;
12
15
20
24
BUAD/American University
Dodgers
Giants
Yankees
Tigers
Los Angeles Wilson
San Francisco Johnson
New York
Simpson
Detroit
Corbin
SQL
18
Simple Project
Find the numbers and names of all of the
teams.
SELECT TEAMNUM, TEAMNAME
FROM TEAMS;
12
15
20
24
BUAD/American University
Dodgers
Giants
Yankees
Tigers
SQL
19
Combination of Conditions
Which players, over 27 years old, have player
numbers of at least 1000?
SELECT PLAYNUM, PLAYNAME
FROM PLAYERS
WHERE AGE>27
AND PLAYNUM>=1000;
1131 Johnson
5410 Smith
8366 Gomez
BUAD/American University
SQL
20
ANDs and ORs
Which players are over 30 years old or are less than
22 years old and have a player number less than
2000?
SELECT *
FROM PLAYERS
WHERE AGE>30
OR (AGE<22 AND PLAYNUM<2000);
358 Stevens 21
523 Doe
32
8366 Gomez 33
BUAD/American University
SQL
21
Between
Which players are between 25 and 27 years
old?
SELECT PLAYNUM, PLAYNAME
FROM PLAYERS
WHERE AGE BETWEEN 25 AND 27;
1779
2007
4280
5410
BUAD/American University
Jones
Dobbs
Cohen
Smith
SQL
22
Using Expressions
• In the selection conditions, expressions
(muliplication/addition) or stored functions
(sum, average, min or max) can be used
• What is the total values of product in
inventory?
– SELECT product_name, unit_price, on_hand,
unit_price*on_hand AS value FROM product_t
BUAD/American University
SQL
23
Using Function
• How many different items were ordered on
order number 1004?
– SELECT COUNT(*) FROM product_order_t
WHERE order_id = 1004
BUAD/American University
SQL
24
String Comparison
Which teams are based in Detroit?
SELECT TEAMNUM, TEAMNAME
FROM TEAMS
WHERE TEAMNAME=‘Detroit’;
24 Tigers
BUAD/American University
SQL
25
Like
Find all of the players whose last names begin
with “S”. (use of wildcard)
SELECT PLAYNUM, PLAYNAME
FROM PLAYERS
WHERE PLAYNAME LIKE ‘S%’;
358 Stevens
5410 Smith
8093 Smith
BUAD/American University
SQL
26
Distinct
To list unique values
List the names of the companies that
manufacture bats for the league.
SELECT DISTINCT MANUF
FROM BATS;
Acme
General
United
Modern
BUAD/American University
SQL
27
IN and NOT IN Lists
• List all customers who live in Eastern States
– SELECT customer_name, city, state FROM
customer_t WHERE state IN (‘NY’, ‘NJ’,
‘MD’, ‘DC’, ‘CT’)
BUAD/American University
SQL
28
Processing Multiple Tables
• Multiple tables are related by a JOIN
operation using foreign key referencing of
primary key
• FROM: list multiple table
• WHERE: foreign_key = primary_key and
other conditions for join
BUAD/American University
SQL
29
Simple Join
• What are the names of customers who
placed orders?
– SELECT customer_t.customer_name FROM
customer_t, order_t WHERE
customer_t.customerid = order_t.customerid
BUAD/American University
SQL
30