1 / 34

Query-by-Example (QBE)

Query-by-Example (QBE). Presented by: Wong, Lok Man CS 157A Dr. Sin Min Lee. History of QBE. History: QBE is the name of both a data-manipulation language and an early database system that included this language QBE was developed at IBM’s T.J. Watson Research Center (1970’s). Content.

kenaz
Download Presentation

Query-by-Example (QBE)

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. Query-by-Example (QBE) Presented by: Wong, Lok Man CS 157A Dr. Sin Min Lee

  2. History of QBE History: • QBE is the name of both a data-manipulation language and an early database system that included this language • QBE was developed at IBM’s T.J. Watson Research Center (1970’s)

  3. Content • QBE Basic Structure • Queries on One Relation • Queries on Several Relations • Condition Box • Result Relation • Ordering of the Display of Tuples • Aggregate Operations • Modification of Database • QBE in Microsoft Access

  4. Basic Structure • QBE has two dimensional syntax: 1. Queries look like tables 2. QBE queries are expressed “by example” • Non-procedural • Queries are expressed using skeleton tables

  5. Basic Structure • User selects skeletons needed • User fills in the skeletons with example row • Example row consists of constants & example elements, which are domain variables • Constants appear without any qualification • QBE uses an underscore character “_” before domain variables, as in “_x”

  6. Queries on One Relation Ex: Find all loan numbers at the Perryridge branch Bring up the skeleton of the loan relation P. – before the variable to display the value X – a variable Perryridge – branch-name

  7. Queries on One Relation (cont.) Ex: Suppress duplicate elimination - insert “ALL” Ex: display entire loan relation – place single P.

  8. Queries on One Relation (cont.) Ex: Find loan numbers of all loans with amount more than $700. Use arithmetic operations (=, <, >, ¬) With no variable, displays all attributes Ex: Find names of all branches not in Brooklyn

  9. Queries on One Relation (cont.) Ex: Find the loan numbers of all loans made jointly to Smith and Jones. Only one P. is needed. Ex: Find all customers who live in the same city as Jones

  10. Queries on Several Relations • QBE allows queries to span several different relations • The connection among the various relations are achieved through variables that force certain tuples to have the same value on certain attributes

  11. Queries on Several Relation (cont.) Ex: Find the names of all the customers that have a loan from the Perryridge branch

  12. Queries on Several Relation (Cont.) Ex: Find the names of all customer with both and an account and a loan at the bank The “¬” symbol represents “there does not exist”. A “¬” under an attribute name stands for “not equal”.

  13. Queries on Several Relation (Cont.) Ex: Display all customer-name values that appear in at least two tuples, with the second tuple having an account-number different from the first.

  14. Condition Box • QBE includes a condition boxfeature that allow the expression of general constraints over any of the domain variables. • Logical operators in words and and or, or as symbols “&” and “|”, appear in a condition box Ex: Find the loan numbers of all loans made to Smith, to Jones, or both

  15. Condition Box (Cont.) Ex: Find all branches that have assets greater than those of at least one branch located in Brooklyn

  16. Condition Box (Cont.) Ex: Find all account numbers of account with a balance between $1000 and $1500, but not exactly $1255

  17. Result Relation • If the result of a query includes attributes from several relation schemas, we can declare a temporary result relation that includes all the attributes of the result. Ex: Create a skeleton table, called result, with attributes customer-name, account-number, and balance, at the Perryridge branch. (Two Steps) 1.

  18. Result Relation (Cont.) Once the new skeleton table is created, write the query. 2.

  19. Ordering of the Display Tuples • To gain control over the order in which tuples in a relation are displayed, indicate AO (ascending order) or Do (descending order). • To specify the order in which the sorting should be carried, add an integer surrounded by parentheses after sort operator (AO(1) or DO(2)).

  20. Ordering of the Display Tuples (Cont.) Ex: List all account numbers at the Downtown branch in ascending alphabetic order with their respective account balances in descending order.

  21. Aggregate Operations • Aggregate Operators include AVG, MIN, MAX, SUM, and CNT • We must postfix these operators with “ALL.” to create a multiset on which the aggregate operation is evaluated • Operator “UNQ.” is to specify that duplicates are to be eliminated • To compute functions on groups of tuples, then use “G.”.

  22. Aggregate Operations (Cont.) Ex: Find the total balance of all the accounts maintained at the Downtown branch Ex: Find the total number of customers who have an account at the bank

  23. Aggregate Operations (Cont.) Ex: Find the average balance at each branch

  24. Ex: Find all customers who have accounts at each of the branches located in Brooklyn

  25. Modification of the Database • Deletion, uses “D.” replacing “P.” • Insertion, uses “I.” replacing “P.” • Updates, uses “U.” replacing “P.”

  26. Deletion Ex: Delete customer Roy Ex: Delete the branch-city value of the branch whose name is “San Jose”

  27. Deletion (Cont.) Ex: Delete all accounts at all branches located in Brooklyn

  28. Insertion Ex: Insert the fact that account AM-8976 at the Downtown branch has a balance of $900. Ex: Insert information into the branch relation about a new branch with name “Capital”, city “Queens,” and asset “200,000”

  29. Insertion (Cont.) Ex: Insert as a gift, for all loan customers of the Downtown branch, a new $300 savings account for every loan account that they have, with the loan number serving as the account number for the savings account.

  30. Insertion (Cont.)

  31. Updates Ex: Update the asset value of the San Jose branch to $100,000,000. Ex: Update all balances with a 5% increase. Use “_x” to hold the old balances

  32. QBE in Microsoft Access • Original QBE was designed for a text-based display environment • Access QBE is a graphical display environment, called graphical query-by-example (GQBE) • GQBE version attributes are written one below the other, instead of horizontally • Instead of shared variable in QBE, GQBE uses lines to link attributes of two tables

  33. QBE in Microsoft Access (Cont.) • In GQBE links are automatically created, on the basis of the attribute name • In GQBE attributes printed in a separate box is specified, called the design grid, instead of using “P.” for QBE.

  34. THE END!!! THANKS FOR STAYING AWAKE! QUIZ TIME!!!

More Related