数据库系统概念教材ch5 Other Relational Languages.pptVIP

Condition Box (Cont.) Find all branches that have assets greater than those of at least one branch located in Brooklyn The Result Relation Find the customer_name, account_number, and balance for all customers who have an account at the Perryridge branch. We need to: Join depositor and account. Project customer_name, account_number and balance. To accomplish this we: Create a skeleton table, called result, with attributes customer_name, account_number, and balance. Write the query. The Result Relation (Cont.) The resulting query is: Ordering the Display of Tuples AO = ascending order; DO = descending order. Example: list in ascending alphabetical order all customers who have an account at the bank When sorting on multiple attributes, the sorting order is specified by including with each sort operator (AO or DO) an integer surrounded by parentheses. Example: List all account numbers at the Perryridge branch in ascending alphabetic order with their respective account balances in descending order. Aggregate Operations The aggregate operators are AVG, MAX, MIN, SUM, and CNT The above operators must be postfixed with “ALL” (e.g., SUM.ALL. or AVG.ALL._x) to ensure that duplicates are not eliminated. Example: Find the total balance of all the accounts maintained at the Perryridge branch. Aggregate Operations (Cont.) UNQ is used to specify that we want to eliminate duplicates Find the total number of customers having an account at the bank. Query Examples Find the average balance at each branch. The “G” in “P.G” is analogous to SQL’s group by construct The “ALL” in the “P.AVG.ALL” entry in the balance column ensures that all balances are considered To find the average account balance at only those branches where the average account balance is more than $1,200, we simply add the condition box: Query Example Find all customers who have an account at all branches located in Brooklyn. Approach: for each customer, find the number of branches in Brooklyn at which they