1 / 31

CS 370 Database Systems

CS 370 Database Systems. Lecture 13 Introduction to SQL. SQL Aggregate Functions. Aggregate Functions. Operates on a column of a relation, and return a value avg : average value min : minimum value max : maximum value sum : sum of values count : number of values. balance. account.

gay-calhoun
Télécharger la présentation

CS 370 Database Systems

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. CS 370 Database Systems Lecture 13 Introduction to SQL

  2. SQLAggregate Functions

  3. Aggregate Functions • Operates on a column of a relation, and return a valueavg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of values

  4. balance account select balance from account where branch-name =“Perryridge” Avg() 120,000 Aggregate Functions(cont.) • Find the average account balance at the Perryridge branch. select avg(balance)from accountwhere branch-name=“Perryridge”

  5. Aggregate Functions(cont.) • Find the numbers of tuples in the customer relation.select count(*)from customer • remember * stands for all attributes • compare to: select count(customer-city) from customer • Find the number of depositors in the bankselectcount (distinct customer-name)from depositor • distinct is redundant if you know customer-name is a key

  6. Modes of Interaction between Users and DBMS

  7. user Ad hoc query result query Stored procedure result SQL result C/Java program Embedded SQL DBMS

  8. Back to SQL … Set and Nested Queries

  9. Null values • It is possible for tuples to have a null value, denoted by null, for some of their attributes; null signifies an unknown value or that a value does not exist. • The result of any arithmetic expression involving null is null. • Roughly speaking, all comparisons involving null return false.More precisely, • Any comparison with null returns unknown • (true or unknown) = true, (false or unknown) = unknown (unknown or unknown) = unknown,(true and unknown) = unknown, (false and unknown) = false (unknown and unknown) = unknown • Result of where clause predicate is treated as false if it evaluates to unknown

  10. Null Values (cont.) • Find all loan numbers which appear in the loan relation with null values for amount.select loan-numberfrom loanwhere amount is null • Total of all loan amountsselect sum(amount)from loan • All aggregate operations except count(*) ignore tuples with null values on the aggregated attributes.

  11. SQL - Nested Subqueries • Every SQL statement returns a relation/set in the result; remember a relation could be null or merely contain a single atomic value • You can replace a value or set of values with a SQL statement (ie., a subquery)select * select *from loan from loanwhere amount > 1200 where amount > select avg(amount) from loan • Illegal if the subquery returns the wrong type for the comparison

  12. Check for each borrower if he/she is also a depositor Returns the set of depositors Example Nested Query • Find all customers who have both an account and a loan in the bank.selectdistinctcustomer-namefromborrowerwherecustomer-namein (selectcustomer-namefromdepositor)

  13. Example Query • Find all customers who have a loan at the bank but do not have an account at the bank.select distinctcustomer-namefromborrowerwhere customer-namenot in (selectcustomer-namefromdepositor)

  14. Returns borrowers with loan at Perryridge Important link: (Perryridge, D Lee) Returns depositor information Set Membership Example • Find all customers who have both an account and a loan at the Perryridge branch.select distinctcustomer-namefromborrower, loanwhereborrower.loan-number = loan.loan-numberandbranch-name=“Perryridge” and (branch-name, customer-name) in (selectbranch-name, customer-namefromdepositor, accountwheredepositor.account-number = account.account-number)

  15. Views • Provide a mechanism to hide certain data from the view of certain users. To create a view we use the command:create view view-name as <query expression>where: • <query expression> is any legal SQL query • the name of the view is represented by view-name

  16. Example Queries • A view consisting of branches and their customerscreate viewall-customeras (select branch-name, customer-namefrom depositor, accountwhere depositor.account-number = account.name-number)union (select branch-name, customer-namefrom borrower, loanwhere borrower.loan-number = loan.loan-number) • Find all customers of the Perryridge branchselect customer-namefromall-customerwhere branch-name = “Perryridge”

  17. Modification of the Database - Deletion • Delete all account records at the Perryridge branchdelete from accountwhere branch-name = “Perryridge” • Conceptually, delete is done in two steps: • find the tuples you want to delete:select * from accountwhere branch-name = “Perryridge” • delete the tuples you found.

  18. Modification of the Database - Deletion • Delete all accounts at every branch located in Needham.delete from depositorwhere account-number in (select account-numberfrom branch, accountwhere branch-city = “Needham”and branch.branch-name = account.branch-name)deletefrom accountwhere branch-name in (select branch-name from branchwhere branch-city = “Needham”) • The first delete is needed to enforce “every depositor must have at least one account”. Note that the two deletes can’t be reordered.

  19. Example Query • Delete the records of all accounts with balances below the average at the bankdeletefrom accountwhere balance < (selectavg (balance)from account) • Problem: as we delete tuples from deposit, the average balance changes • solution used in SQL: • First, compute and save the average balance of the bank in a variable • Next, delete all accounts with balance less than the average

  20. Modification of the database - Insertion • Add a new tuple to accountinsertinto account values (“Perryridge”, A-9732, 1200)To reorder attributes, specify attribute names explicitly:insertinto account (branch-name, balance, account-number)values (“Perryridge”, 1200, A-9732) • Add a new tuple to account with balance set to nullinsertinto account values ( “Perryridge”, “A-777”, null)

  21. Modification of the Database - Insertion • Create a $200 savings account for all loan customers of the Perryridge branch. Let the loan number serve as the account number for the new savings account.insertinto accountselect branch-name, loan-number, 200from loanwhere branch-name=“Perryridge”insertinto depositorselect customer-name, loan-numberfrom loan, borrowerwhere branch-name=“Perryridge”and loan.account-number = borrower.account-number

  22. Modification of the database - Updates • Increase all accounts with balance over $10,000 by 6%, all other accounts receive 5%. • Write two update statements:update accountset balance = balance *1.06where balance >10000update account set balance = balance *1.05where balance  10000 • the order is important • can be done better using the case statement

  23. Case Statement for Conditional Updates • Same query as before: Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%. update accountset balance = casewhen balance <= 10000 then balance *1.05else balance * 1.06end

  24. Update of a View • Create a view of all loan data in the loan relation, hiding the amount attributecreate view branch-loan asselect branch-name, loan-numberfrom loan • Add a new tuple to branch-loaninsert into branch-loanvalues(“Perryridge”, “L-307”)This insertion must be represented by the insertion of the tuple (“Perryridge”, “L-307”,null)into the loan relation. • Updates on more complex views are difficult or impossible to translate, and hence are disallowed.

  25. AI AI incorrect! 789 correct View Update Problems Why can’t views be updated? create view works-on-view as select employee-name, project-name, hours from employee, project, works-on where employee.id=works-on.eid and project.id=works-on.pid works-on-view employee works-on project

  26. Replace old pid with the pid of “AI” Find the tuple relating “Dik Lee” to the “database” project Correct Update Procedure update works-on set pid = ( select id from project where name=`AI’ ) where eid = ( select id from employee where name = `Dik Lee’ ) and pid = ( select id from project where name = ‘database’ ) • The correct update procedure is application dependent and must be written by the database application programmer • A trigger can invoke the procedure automatically upon updates

  27. Rules for Updatable Views Rules for legal view updates: • A view built on a single defining table is updatable if the view contains the primary key of the defining table • Views defined on multiple tables are in general not updatable • Views involving aggregate functions on the defining table are not updatable

  28. Review: Data Definition Language • The SQL syntax for CREATE TABLE is CREATE TABLE "table_name"("column 1" "data_type_for_column_1","column 2" "data_type_for_column_2",... ) • So, if we are to create the customer table specified as above, we would type in CREATE TABLE customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date date)

  29. Sailors Review: Data Definition Language CREATE TABLE Sailors (sid INTEGER, sname CHAR(20), rating INTEGER, age REAL, PRIMARY KEY sid) CREATE TABLE Boats (bid INTEGER, bname CHAR (20), color CHAR(10) PRIMARY KEY bid) CREATE TABLE Reserves (sid INTEGER, bid INTEGER, day DATE, PRIMARY KEY (sid, bid, day), FOREIGN KEY sid REFERENCES Sailors, FOREIGN KEY bid REFERENCES Boats) NOT NULL, Boats NOT NULL, NOT NULL, Reserves NOT NULL, NOT NULL,

  30. Integrity Constraints (ICs) • A foreign key constraint is anIntegrity Constraint: • a condition that must be true for anyinstance of the database; • Specified when schema is defined. • Checked when relations are modified. • Primary/foreign key constraints; but databases support more general constraints as well. • e.g. domain constraints like: • Rating must be between 1 and 10 ALTER TABLE SAILORS ADD CONSTRAINT RATING CHECK (RATING >= 1 AND RATING < 10) • Or even more complex (and potentially nonsensical): ALTER TABLE SAILORS ADD CONSTRAINT RATING CHECK (RATING*AGE/4 <= SID) CS370 Spring 2007

  31. Integrity Constraints can help prevent data consistency errors • …but they have drawbacks: • Expensive • Can’t always return a meaningful error back to the application. e.g: What if you saw this error when you enrolled in a course online? “A violation of the constraint imposed by a unique index or a unique constraint occurred”. • Can be inconvenient e.g. What if the ‘Sailing Class’ application wants to register new (unrated) sailors with rating 0? • So they aren’t widely used • Software developers often prefer to keep the integrity logic in applications instead CS370 Spring 2007

More Related