1 / 43

SQL

Lecture 8. SQL. PROF. Sin-Min LEE Department of Computer Science. 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,

meredith-cole
Download Presentation

SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture 8 SQL PROF. Sin-Min LEE Department of Computer Science

  2. 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.

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

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

  5. 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

  6. ATMWithdrawals table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 2 1 102 $150.00 11/10/2000 13:15:00 3 2 101 $40.00 11/1/2000 10:05:00 4 2 100 $40.00 11/1/2000 10:07:00 5 2 100 $200.00 11/8/2000 14:14:00 SELECT AcctNo, Amount FROM ATMWithdrawals WHERE Amount < 50;

  7. ATMWithdrawals table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 2 1 102 $150.00 11/10/2000 13:15:00 3 2 101 $40.00 11/1/2000 10:05:00 4 2 100 $40.00 11/1/2000 10:07:00 5 2 100 $200.00 11/8/2000 14:14:00 SELECT AcctNo, Amount FROM ATMWithdrawals WHERE Amount < 50; This is the WHERE clause. The WHERE clause will be evaluated for each record in the table.

  8. ATMWithdrawals table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 2 1 102 $150.00 11/10/2000 13:15:00 3 2 101 $40.00 11/1/2000 10:05:00 4 2 100 $40.00 11/1/2000 10:07:00 5 2 100 $200.00 11/8/2000 14:14:00 Query Answer table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 Is the amount field of this record less than $50? YES! Amount < 50

  9. ATMWithdrawals table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 2 1 102 $150.00 11/10/2000 13:15:00 3 2 101 $40.00 11/1/2000 10:05:00 4 2 100 $40.00 11/1/2000 10:07:00 5 2 100 $200.00 11/8/2000 14:14:00 Query Answer table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 Is the amount field of this record less than $50? NO! Amount < 50 Ignore this record!

  10. ATMWithdrawals table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 2 1 102 $150.00 11/10/2000 13:15:00 3 2 101 $40.00 11/1/2000 10:05:00 4 2 100 $40.00 11/1/2000 10:07:00 5 2 100 $200.00 11/8/2000 14:14:00 Query Answer table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 3 2 101 $40.00 11/1/2000 10:05:00 Is the amount field of this record less than $50? YES! Amount < 50

  11. ATMWithdrawals table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 2 1 102 $150.00 11/10/2000 13:15:00 3 2 101 $40.00 11/1/2000 10:05:00 4 2 100 $40.00 11/1/2000 10:07:00 5 2 100 $200.00 11/8/2000 14:14:00 Query Answer table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 3 2 101 $40.00 11/1/2000 10:05:00 4 2 100 $40.00 11/1/2000 10:07:00 Is the amount field of this record less than $50? YES! Amount < 50

  12. ATMWithdrawals table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 2 1 102 $150.00 11/10/2000 13:15:00 3 2 101 $40.00 11/1/2000 10:05:00 4 2 100 $40.00 11/1/2000 10:07:00 5 2 100 $200.00 11/8/2000 14:14:00 Query Answer table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 3 2 101 $40.00 11/1/2000 10:05:00 4 2 100 $40.00 11/1/2000 10:07:00 Amount < 50 Is the amount field of this record less than $50? NO! Ignore this record!

  13. Query Answer table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 3 2 101 $40.00 11/1/2000 10:05:00 4 2 100 $40.00 11/1/2000 10:07:00 Final Query Answer table AcctNo Amount 102 $25.00 101 $40.00 100 $40.00 SELECT AcctNo, Amount FROM ATMWithdrawals WHERE Amount < 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:

  14. ATMWithdrawals table TransactionID CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/2000 9:45:00 2 1 102 $150.00 11/10/2000 13:15:00 3 2 101 $40.00 11/1/2000 10:05:00 4 2 100 $40.00 11/1/2000 10:07:00 5 2 100 $200.00 11/8/2000 14:14:00 • Final Query Answer table • AcctNo Amount • $25.00 • 101 $40.00 • 100 $40.00 Given this table: SELECT AcctNo, Amount FROM ATMWithdrawals WHERE Amount < 50; This query Produces this query answer:

  15. ATMWithdrawal TransactionId CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/00 9:45:00 AM 2 1 102 $150.00 11/10/00 1:15:00 PM 3 2 101 $40.00 11/1/00 10:05:00 AM 4 2 100 $40.00 11/1/00 10:07:00 AM 5 2 100 $200.00 11/8/00 2:14:00 PM 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).

  16. ATMWithdrawal TransactionId CustId AcctNo Amount WithdrawalDate 1 1 102 $25.00 11/1/00 9:45:00 AM 2 1 102 $150.00 11/10/00 1:15:00 PM 3 2 101 $40.00 11/1/00 10:05:00 AM 4 2 100 $40.00 11/1/00 10:07:00 AM 5 2 100 $200.00 11/8/00 2:14:00 PM TransactionId CustId AcctNo Amount WithdrawalDate 3 2 101 $40.00 11/1/00 10:05:00 AM SELECT * FROM ATMWithdrawal WHERE TransactionId = 3; Query Answer is:

  17. Third, the SELECT clause tells us which attributes to keep in the query answer. First, the FROM clause tells us the input tables. Second, the WHERE clause is evaluated for all possible combinations from the input tables. How an SQL query is evaluated SELECT AcctNo, Amount FROM ATMWithdrawal WHERE Amount < 50;

  18. 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 > 750; How does this work? Which rows, from which tables, are evaluated in the WHERE clause?

  19. Customer Id Name Phone Address PinNumber 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 AccountNo Owner Balance DateOpened 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 > 750; We must check every combination of one row from Customer with one row from CheckingAccount!

  20. 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 > 750;

  21. 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 > 750;

  22. 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 > 750; 1 Smith 503 555 1111 xxx Beaverton 1111 102 1 $1,000.00 8/1/00

  23. 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 > 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

  24. 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 > 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

  25. 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 > 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

  26. 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 > 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

  27. 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 > 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

  28. 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 > 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

  29. 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 > 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

  30. 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 > 750; Final query answer: Smith $1,000.00 Jones $10,000.00

More Related