branch-name.pptVIP

CMSC424: Database Design Lecture 7 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#) More SQL Set comparison SOME ALL Set Comparison Find all branches that have greater assets than some branch located in Brooklyn. Set Comparison Find all branches that have greater assets than all branches located in Brooklyn. unique Find all customers who have at least two accounts at the Perryridge branch. Views Provide a mechanism to hide certain data from the view of certain users. To create a view we use the command: Example Queries A view consisting of branches and their customers 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 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 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) Example Query Delete the record of all accounts with balances below the average at the bank. Modification of the Database – Insertion Add a new tuple to account insert into account values (‘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 nu