CMSC424 Database Design Lecture 7 CMSC424 Spring 2005 1 SQL Query Examples Movie title year length inColor studioName producerC StarsIn movieTitle movieYear starName MovieStar name address gender birthdate MovieExec name address cert netWorth Studio name address presC CMSC424 Spring 2005 2 More SQL Set comparison SOME ALL CMSC424 Spring 2005 3 Set Comparison Find all branches that have greater assets than some branch located in Brooklyn select branch name from branch where assets some select assets from branch where branch city Brooklyn CMSC424 Spring 2005 4 Set Comparison Find all branches that have greater assets than all branches located in Brooklyn select branch name from branch where assets all select assets from branch where branch city Brooklyn CMSC424 Spring 2005 5 unique Find all customers who have at least two accounts at the Perryridge branch select distinct T customer name from depositor T where not unique select R customer name from account depositor as R where T customer name R customer name and R account number account account number and account branch name Perryridge CMSC424 Spring 2005 6 Views Provide a mechanism to hide certain data from the view of certain users To create a view we use the command create view v as query expression where query expression is any legal expression The view name is represented by v CMSC424 Spring 2005 7 Example Queries A view consisting of branches and their customers create view all customer as select branch name customer name from depositor account where depositor account number account account number union select branch name customer name from borrower loan where borrower loan number loan loan number Find all customers of the Perryridge branch select customer name from all customer where branch name Perryridge CMSC424 Spring 2005 8 Derived Relations Find the average account balance of those branches where the average account balance is greater than 1200 select branch name avg balance from select branch name avg balance from account group by branch name as result branch name avg balance where avg balance 1200 CMSC424 Spring 2005 9 Modification of the Database Deletion Delete all account records at the Perryridge branch delete from account where branch name Perryridge Delete all accounts at every branch located in Needham city delete from account where branch name in select branch name from branch where branch city Needham delete from depositor where account number in select account number from branch account where branch city Needham and branch branch name account branch name CMSC424 Spring 2005 10 Example Query Delete the record of all accounts with balances below the average at the bank delete from account where balance select avg balance from account Problem as we delete tuples from deposit the average balance changes Solution used in SQL 1 First compute avg balance and find all tuples to delete 2 Next delete all tuples found above without recomputing avg or retesting the tuples CMSC424 Spring 2005 11 Modification of the Database Insertion Add a new tuple to account insert into account values A 9732 Perryridge 1200 or equivalently insert into account branch name balance account number values Perryridge 1200 A 9732 Add a new tuple to account with balance set to null insert into account values A 777 Perryridge null CMSC424 Spring 2005 12 Modification of the Database Updates Increase all accounts with balances over 10 000 by 6 all other accounts receive 5 Write two update statements update account set balance balance 1 06 where balance 10000 update account set balance balance 1 05 where balance 10000 The order is important Can be done better using the case statement CMSC424 Spring 2005 13 Update of a View Create a view of all loan data in loan relation hiding the amount attribute create view branch loan as select branch name loan number from loan Add a new tuple to branch loan insert into branch loan values Perryridge L 307 This insertion must be represented by the insertion of the tuple L 307 Perryridge null into the loan relation Updates on more complex views are difficult or impossible to translate and hence are disallowed Most SQL implementations allow updates only on simple views without aggregates defined on a single relation CMSC424 Spring 2005 14 Transactions A transaction is a sequence of queries and update statements executed as a single unit Transactions are started implicitly and terminated by one of commit work makes all updates of the transaction permanent in the database rollback work undoes all updates performed by the transaction Motivating example Transfer of money from one account to another involves two steps deduct from one account and credit to another If one steps succeeds and the other fails database is in an inconsistent state Therefore either both steps should succeed or neither should If any step of a transaction fails all work done by the transaction can be undone by rollback work Rollback of incomplete transactions is done automatically in case of system failures CMSC424 Spring 2005 15 Transactions Cont In most database systems each SQL statement that executes successfully is automatically committed Each transaction would then consist of only a single statement Automatic commit can usually be turned off allowing multistatement transactions but how to do so depends on the database system Another option in SQL 1999 enclose statements within begin atomic end CMSC424 Spring 2005 16 join CMSC424 Spring 2005 17 Data Definition Language DDL Allows the specification of not only a set of relations but also information about each relation including The schema for each relation The domain of values associated with each attribute Integrity constraints The set of indices to be maintained for each relations Security and authorization information for each relation The physical storage structure of each relation on disk CMSC424 Spring 2005 18 Domain Types in SQL char n Fixed length character string with user specified length n varchar n Variable length character strings with user specified maximum length n int Integer a finite subset of the integers that is machine dependent smallint Small integer a machine dependent subset of the integer domain type numeric p d Fixed point number with user specified precision of p digits with n digits to the right of decimal point real double precision Floating point and double precision floating point numbers with machine dependent precision float n Floating point number with user
View Full Document
Unlocking...