SQL

1
SQL
Lecture 8

• PROF. Sin-Min LEE
• Department of Computer Science

2
(No Transcript)
3
(No Transcript)
4
(No Transcript)
5
(No Transcript)
6
(No Transcript)
7
Data Definition

• The SQL Data Definition Language (DDL) allows us
  to create and destroy database objects such as
  schemas, domains, tables, views, and indexes.
  The ISO standard also allows the creation of
  assertions,
• character sets, collations
• and translations.

8
(No Transcript)
9

• The main SQL data definition
• language statements are
• CREATE SCHEMA
• DROP SCHEMA
• CREATE DOMAIN
• ALTER DOMAIN
• DROP DOMAIN

10

• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• CREATE VIEW
• DROP VIEW
• While CREATE INDEX and
• DROP INDEX are provided
• by DBMS

11
(No Transcript)
12
(No Transcript)
13
How is it used ?

• As a Data Definition Language

• Create the database and its table structure

• Create the tables - CREATE TABLE command

• ? Entity integrity
• ? Referential integrity

• automatically enforced

14
(No Transcript)
15
(No Transcript)
16
(No Transcript)
17
(No Transcript)
18
(No Transcript)
19
SELECT AcctNo, Amount FROM ATMWithdrawals WHERE
Amount lt 50
20
SELECT AcctNo, Amount FROM ATMWithdrawals WHERE
Amount lt 50
This is the WHERE clause. The WHERE clause will
be evaluated for each record in the table.
21
Is the amount field of this record less than 50?
YES!
Amount lt 50
22
Is the amount field of this record less than 50?
NO!
Amount lt 50
Ignore this record!
23
Is the amount field of this record less than 50?
YES!
Amount lt 50
24
Is the amount field of this record less than 50?
YES!
Amount lt 50
25
Amount lt 50
Is the amount field of this record less than 50?
NO!
Ignore this record!
26
SELECT AcctNo, Amount FROM ATMWithdrawals WHERE
Amount lt 50
Next we consider the attributes listed in the
SELECT clause. We throw away all attributes that
are not listed in the SELECT clause. Thus the
final query answer is
27
Given this table
SELECT AcctNo, Amount FROM ATMWithdrawals WHERE
Amount lt 50
This query
Produces this query answer
28
Another SQL Query (using one table)

• SELECT FROM ATMWithdrawal
• WHERE TransactionId 3
• The five rows are considered, one by one, to see
  if TransactionId 3 (to see if the WHERE
  clause evaluates to true).

29

• SELECT
• FROM ATMWithdrawal
• WHERE TransactionId 3
• Query Answer is

30
How an SQL query is evaluated
SELECT AcctNo, Amount FROM ATMWithdrawal WHERE Am
ount lt 50
31
SQL query using two tables
SELECT C.Name, A.Balance FROM Customer As C,
CheckingAccount As A WHERE C.Id A.Owner and
A.Balance gt 750
How does this work? Which rows, from which
tables, are evaluated in the WHERE clause?
32
SELECT C.Name, A.Balance FROM Customer C,
CheckingAccount A WHERE C.Id A.Owner and
A.Balance gt 750
We must check every combination of one row
from Customer with one row from CheckingAccount!
33
Customer 1 Smith 503 555 1111 xxx Beaverton
1111 2 Jones 503 555 2222 yyy Beaverton 2222 3
Wei 503 555 3333 zzz Portland 3333
CheckingAccount 100 2 10,000.00 1/19/00 101
2 500.00 7/10/00 102 1 1,000.00 8/1/00
1 Smith 503 555 1111 xxx Beaverton 1111 100
2 10,000.00 1/19/00
WHERE C.Id A.Owner and A.Balance gt 750

34
Customer 1 Smith 503 555 1111 xxx Beaverton
1111 2 Jones 503 555 2222 yyy Beaverton 2222 3
Wei 503 555 3333 zzz Portland 3333
CheckingAccount 100 2 10,000.00 1/19/00 101
2 500.00 7/10/00 102 1 1,000.00 8/1/00
1 Smith 503 555 1111 xxx Beaverton 1111 101
2 500.00 7/10/00
WHERE C.Id A.Owner and A.Balance gt 750

35
Customer 1 Smith 503 555 1111 xxx Beaverton
1111 2 Jones 503 555 2222 yyy Beaverton 2222 3
Wei 503 555 3333 zzz Portland 3333
CheckingAccount 100 2 10,000.00 1/19/00 101
2 500.00 7/10/00 102 1 1,000.00 8/1/00
1 Smith 503 555 1111 xxx Beaverton 1111 102
1 1,000.00 8/1/00
WHERE C.Id A.Owner and A.Balance gt 750

1 Smith 503 555 1111 xxx Beaverton 1111 102
1 1,000.00 8/1/00
36
Customer 1 Smith 503 555 1111 xxx Beaverton
1111 2 Jones 503 555 2222 yyy Beaverton 2222 3
Wei 503 555 3333 zzz Portland 3333
CheckingAccount 100 2 10,000.00 1/19/00 101
2 500.00 7/10/00 102 1 1,000.00 8/1/00
1 Jones 503 555 2222 yyy Beaverton 2222 100
2 10,000.00 1/19/00
WHERE C.Id A.Owner and A.Balance gt 750

1 Smith 503 555 1111 xxx Beaverton 1111 102 1
1,000.00 8/1/00 2 Jones 503 555 2222 yyy
Beaverton 2222 100 2 10,000.00 1/19/00
37
Customer 1 Smith 503 555 1111 xxx Beaverton
1111 2 Jones 503 555 2222 yyy Beaverton 2222 3
Wei 503 555 3333 zzz Portland 3333
CheckingAccount 100 2 10,000.00 1/19/00 101
2 500.00 7/10/00 102 1 1,000.00 8/1/00
2 Jones 503 555 2222 yyy Beaverton 2222 101 2
500.0 7/10/00
WHERE C.Id A.Owner and A.Balance gt 750

1 Smith 503 555 1111 xxx Beaverton 1111 102 1
1,000.00 8/1/00 2 Jones 503 555 2222 yyy
Beaverton 2222 100 2 10,000.00 1/19/00
38
Customer 1 Smith 503 555 1111 xxx Beaverton
1111 2 Jones 503 555 2222 yyy Beaverton 2222 3
Wei 503 555 3333 zzz Portland 3333
CheckingAccount 100 2 10,000.00 1/19/00 101
2 500.00 7/10/00 102 1 1,000.00 8/1/00
2 Jones 503 555 2222 yyy Beaverton 2222 102 1
1,000.00 8/1/00
WHERE C.Id A.Owner and A.Balance gt 750

1 Smith 503 555 1111 xxx Beaverton 1111 102 1
1,000.00 8/1/00 2 Jones 503 555 2222 yyy
Beaverton 2222 100 2 10,000.00 1/19/00
39
Customer 1 Smith 503 555 1111 xxx Beaverton
1111 2 Jones 503 555 2222 yyy Beaverton 2222 3
Wei 503 555 3333 zzz Portland 3333
CheckingAccount 100 2 10,000.00 1/19/00 101
2 500.00 7/10/00 102 1 1,000.00 8/1/00
3 Wei 503 555 3333 zzz Portland 3333 100 2
10,000.00 1/19/00
WHERE C.Id A.Owner and A.Balance gt 750

1 Smith 503 555 1111 xxx Beaverton 1111 102 1
1,000.00 8/1/00 2 Jones 503 555 2222 yyy
Beaverton 2222 100 2 10,000.00 1/19/00
40
Customer 1 Smith 503 555 1111 xxx Beaverton
1111 2 Jones 503 555 2222 yyy Beaverton 2222 3
Wei 503 555 3333 zzz Portland 3333
CheckingAccount 100 2 10,000.00 1/19/00 101
2 500.00 7/10/00 102 1 1,000.00 8/1/00
3 Wei 503 555 3333 zzz Portland 3333 101 2
500.00 7/10/00
WHERE C.Id A.Owner and A.Balance gt 750

1 Smith 503 555 1111 xxx Beaverton 1111 102 1
1,000.00 8/1/00 2 Jones 503 555 2222 yyy
Beaverton 2222 100 2 10,000.00 1/19/00
41
Customer 1 Smith 503 555 1111 xxx Beaverton
1111 2 Jones 503 555 2222 yyy Beaverton 2222 3
Wei 503 555 3333 zzz Portland 3333
CheckingAccount 100 2 10,000.00 1/19/00 101
2 500.00 7/10/00 102 1 1,000.00 8/1/00
3 Wei 503 555 3333 zzz Portland 3333 102 1
1,000.00 8/1/00
WHERE C.Id A.Owner and A.Balance gt 750

1 Smith 503 555 1111 xxx Beaverton 1111 102 1
1,000.00 8/1/00 2 Jones 503 555 2222 yyy
Beaverton 2222 100 2 10,000.00 1/19/00
42
Customer 1 Smith 503 555 1111 xxx Beaverton
1111 2 Jones 503 555 2222 yyy Beaverton 2222 3
Wei 503 555 3333 zzz Portland 3333
CheckingAccount 100 2 10,000.00 1/19/00 101
2 500.00 7/10/00 102 1 1,000.00 8/1/00
SELECT C.Name, A.Balance FROM Customer C,
CheckingAccount A WHERE C.Id A.Owner and
A.Balance gt 750
Input rows that evaluate to true in the WHERE
clause

1 Smith 503 555 1111 xxx Beaverton 1111 102 1
1,000.00 8/1/00 2 Jones 503 555 2222 yyy
Beaverton 2222 100 2 10,000.00 1/19/00
43
Input rows that evaluate to true in the WHERE
clause

1 Smith 503 555 1111 xxx Beaverton 1111 102 1
1,000.00 8/1/00 2 Jones 503 555 2222 yyy
Beaverton 2222 100 2 10,000.00 1/19/00
SELECT C.Name, A.Balance FROM Customer C,
CheckingAccount A WHERE C.Id A.Owner and
A.Balance gt 750
Final query answer

Smith 1,000.00 Jones 10,000.00