Structured Query Language SQL

1
Chapter 9

• Structured Query Language (SQL)

2
Benefits of a Standardized Relational Language

• Reduced training costs
• Productivity
• Application portability
• Application longevity
• Reduced dependence on a single vendor
• Cross-system communication

3
Disadvantages of a standardized relational
language

• Stifle creativity and innovation
• never enough to meet all needs
• offspring of compromises
• difficult to change
• Using special features may result in loss of
  portability

4
A simplified schematic of a typical SQL
environment
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.

6
Data Definition Language (DDL)

• Creating tables
• Identify appropriate datatypes
• Identify columns that should accept null values
• Identify columns that need to be unique
• Identify all PK/FK mates
• Determine any default values to be inserted
• Identify columns which need a domain
  specification
• Create the table

7
DDL example in SQL

• CREATE TABLE ORDER
• (Order_Id char not null,
• Order_Date date default sysdate,
• Customer_Id char not null,
• Constraint Order_pk primary key (order_Id) ,
• Constraint Order_fk foreign key (Customer_Id
  references Customer(Customer_Id))

Order_ID
Cust_ID
Customer
Order
Order_Date
8
Other DDL commands

• Drop table
• Alter table
• create index
• drop index
• Create view
• Drop view
• Create schema

9
DML options

• Insert
• Delete
• Update
• Select/From/Where

10
Inserting data into a table

• Useful for populating a database
• Syntax is
• INSERT INTO Product(Product_ID, Product _Name,
  Unit_Price, On_Hand)
• VALUES (1, End Table, 175, 8)
• Or
• INSERT INTO Ca_Customer
• SELECT FROM Customer
• WHERE StateCA

11
Deleting

• DELETE FROM Customer
• WHERE StateHI
• Or
• DELETE FROM Customer

12
Updating

• UPDATE Product
• SET Unit_Price 775
• WHERE Product_ID 7

13
Simple Select

• Retrieve the entire Team table.
• SELECT
• FROM TEAM
• Would produce the following result
• 12 Dodgers Los Angeles Wilson
• 15 Giants San Francisco Johnson
• 20 Yankees New York Simpson
• 24 Tigers Detroit Corbin

TEAM
TeamNum Teamname City Coach
14
Project Select

• Find the numbers and names of all of the teams.
• SELECT TEAMNUM, TEAMNAME
• FROM TEAM
• 12 Dodgers
• 15 Giants
• 20 Yankees
• 24 Tigers

15
Conditional Select (Restrict)

• Retrieve the record for Team 20.
• SELECT
• FROM TEAM
• WHERE TEAMNUM20
• Would produce the following result
• 20 Yankees New York Simpson

TEAM
TeamNum Teamname City Coach
16
Operators for Conditional Statements

• AND, OR, NOT
• lt, gt,
• lt, gt, ltgt
• , / (numeric comparisons)
• , , _ (string comparisons)

17
Combination of Conditions

• Which players, over 27 years old, have player
  numbers of at least 1000?
• SELECT PLAYNUM, PLAYNAME FROM PLAYER
• WHERE AGEgt27
• AND PLAYNUMgt1000
• 1131 Johnson
• 5410 Smith
• 8366 Gomez

PLAYER
PlayNum Playname Age Position
18
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 PLAYER
• WHERE AGEgt30
• OR (AGElt22 AND PLAYNUMlt2000)
• 358 Stevens 21
• 523 Doe 32
• 8366 Gomez 33

19
Functions

• COUNT
• MIN
• MAX
• SUM
• AVG

20
COUNT

• Example
• SELECT COUNT() FROM Order_Line
• WHERE Order_Num1004
• SELECT COUNT(Prod_Desc) from Product

ORDER_LINE
Order_Num Prod_ID Quantity
PRODUCT
Prod_ID Prod_Desc Prod_Price
21
MIN and MAX

• SELECT MIN(Prod_Desc) FROM Product
• Or
• SELECT Prod_Id, Prod_Desc FROM Product
• WHERE Prod_Price
• (SELECT MAX(Prod_Price) FROM Product)

PRODUCT
Prod_ID Prod_Desc Prod_Price
22
String Comparison

• Which teams are based in Detroit?
• SELECT TEAMNUM, TEAMNAME
• FROM TEAM
• WHERE TEAMNAMEDetroit
• 24 Tigers

23
Between

• Which players are between 25 and 27 years old?
• SELECT PLAYNUM, PLAYNAME
• FROM PLAYER
• WHERE AGE BETWEEN 25 AND 27
• 1779 Jones
• 2007 Dobbs
• 4280 Cohen
• 5410 Smith

24
In

• Which teams are in New York or Detroit?
• SELECT TEAMNUM
• FROM TEAM
• WHERE CITY IN (New York, Detroit)
• 20
• 24

25
Like

• Find all of the players whose last names begin
  with S.
• SELECT PLAYNUM, PLAYNAME
• FROM PLAYER
• WHERE PLAYNAME LIKE S
• 358 Stevens
• 5410 Smith
• 8093 Smith

26
Distinct

• List the names of the companies that manufacture
  bats for the league.
• SELECT DISTINCT MANUF
• FROM BAT
• Acme
• General
• United
• Modern