CMSC424: Database DesignSQL Query ExamplesMore SQLSet ComparisonSlide 5uniqueViewsExample QueriesDerived RelationsModification of the Database – DeletionExample QueryModification of the Database – InsertionModification of the Database – UpdatesUpdate of a ViewTransactionsTransactions (Cont.)joinData Definition Language (DDL)Domain Types in SQLDate/Time Types in SQL (Cont.)Create Table ConstructIntegrity Constraints in Create TableDrop and Alter Table ConstructsEmbedded SQLNext:IC’sCMSC424, Spring 2005 1CMSC424: Database DesignLecture 7CMSC424, Spring 2005 2SQL Query ExamplesMovie(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 3More SQLSet comparisonSOMEALLCMSC424, Spring 2005 4Set ComparisonFind all branches that have greater assets than some branch located in Brooklyn.select branch-namefrom branchwhere assets > some (select assets from branch where branch-city =‘Brooklyn’)CMSC424, Spring 2005 5Set ComparisonFind all branches that have greater assets than all branches located in Brooklyn.select branch-namefrom branchwhere assets > all (select assets from branch where branch-city =‘Brooklyn’)CMSC424, Spring 2005 6uniqueFind all customers who have at least two accounts at the Perryridge branch. select distinct T.customer-namefrom depositor Twhere not unique (select R.customer-namefrom account, depositor as Rwhere T.customer-name = R.customer-name andR.account-number = account.account-number andaccount.branch-name = ‘Perryridge’)CMSC424, Spring 2005 7ViewsProvide 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 expressionThe view name is represented by vCMSC424, Spring 2005 8Example QueriesA view consisting of branches and their customersFind all customers of the Perryridge branchcreate 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)select customer-namefrom all-customerwhere branch-name = ‘Perryridge’CMSC424, Spring 2005 9Derived RelationsFind the average account balance of those branches where the average account balance is greater than $1200.select branch-name, avg-balancefrom (select branch-name, avg (balance) from account group by branch-name) as result (branch-name, avg-balance)where avg-balance > 1200CMSC424, Spring 2005 10Modification of the Database – DeletionDelete all account records at the Perryridge branchdelete from accountwhere branch-name = ‘Perryridge’Delete all accounts at every branch located in Needham city.delete from accountwhere branch-name in (select branch-name from branch where branch-city = ‘Needham’)delete from depositorwhere account-number in (select account-number from branch, account where branch-city = ‘Needham’ and branch.branch-name = account.branch-name)CMSC424, Spring 2005 11Example QueryDelete 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 changesSolution used in SQL:1. First, compute avg balance and find all tuples to delete2. Next, delete all tuples found above (without recomputing avg or retesting the tuples)CMSC424, Spring 2005 12Modification of the Database – InsertionAdd a new tuple to accountinsert into accountvalues (‘A-9732’, ‘Perryridge’,1200)or equivalentlyinsert into account (branch-name, balance, account-number)values (‘Perryridge’, 1200, ‘A-9732’)Add a new tuple to account with balance set to nullinsert into accountvalues (‘A-777’,‘Perryridge’, null)CMSC424, Spring 2005 13Modification of the Database – UpdatesIncrease all accounts with balances over $10,000 by 6%, all other accounts receive 5%.Write two update statements:update accountset balance = balance 1.06where balance > 10000update accountset balance = balance 1.05where balance 10000The order is importantCan be done better using the case statementCMSC424, Spring 2005 14Update of a ViewCreate a view of all loan data in loan relation, hiding the amount attributecreate view branch-loan asselect branch-name, loan-numberfrom loanAdd a new tuple to branch-loaninsert into branch-loanvalues (‘Perryridge’, ‘L-307’)This insertion must be represented by the insertion of the tuple(‘L-307’, ‘Perryridge’, null)into the loan relationUpdates 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 relationCMSC424, Spring 2005 15TransactionsA transaction is a sequence of queries and update statements executed as a single unitTransactions 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 exampleTransfer of money from one account to another involves two steps:• deduct from one account and credit to anotherIf one steps succeeds and the other fails, database is in an inconsistent stateTherefore, either both steps should succeed or neither shouldIf 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 failuresCMSC424, Spring 2005 16Transactions (Cont.)In most database systems, each SQL statement that executes successfully is automatically committed. Each transaction would then consist of only a single statementAutomatic commit can usually be turned off, allowing multi-statement transactions, but how to do so depends on the database systemAnother option in SQL:1999: enclose statements within begin atomic … endCMSC424, Spring 2005 17joinCMSC424, Spring 2005 18Data Definition Language (DDL)The schema for each relation.The domain of values associated with each attribute.Integrity constraintsThe set of indices to be maintained for each
View Full Document
Unlocking...