1 / 29

The University of Akron Dept of Business Technology Computer Information Systems

The University of Akron Dept of Business Technology Computer Information Systems. The Relational Model: Query-By-Example (QBE). 2440: 180 Database Concepts Instructor: Enoch E. Damson. Query-by-Example (QBE). Query Questions represented in a way the DBMS can recognize and process QBE

verlee
Download Presentation

The University of Akron Dept of Business Technology Computer Information Systems

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. The University of AkronDept of Business TechnologyComputer Information Systems The Relational Model: Query-By-Example (QBE) 2440: 180Database Concepts Instructor: Enoch E. Damson

  2. Query-by-Example (QBE) • Query • Questions represented in a way the DBMS can recognize and process • QBE • Visual approach to writing queries • Used in MS-Access The Relational Model: QBE

  3. Figure 2.3: An Example of Simple Queries The Relational Model: QBE

  4. Figure 2.4: Simple Queries… The Relational Model: QBE

  5. Simple Criteria • Criteria – conditions that data must satisfy • Criterion – a single condition • To display specific query results, enter the condition in the appropriate column in the design grid The Relational Model: QBE

  6. Query with Simple Criteria The Relational Model: QBE

  7. Comparison (Relational) Operators • Finds something other than an exact match • Comparison operators are: • = (equal to) • > (greater than) • < (less than) • >= (greater than or equal to) • <= (less than or equal to) • NOT (not equal to) The Relational Model: QBE

  8. Compound Criteria • Combines comparison operators • Many languages use AND or OR between the separate criteria • In an AND criterion, both criteria must be true • In an OR criterion, the overall criterion is true if either of the individual criteria is true The Relational Model: QBE

  9. Query Using AND Criteria The Relational Model: QBE

  10. Query Using OR Criteria The Relational Model: QBE

  11. Computed Fields • You can include calculated fields that are not in the database in queries • Computed field (calculated field) – a field that is the result of a calculation using one or more existing fields • In a query that uses computed fields, if a field name contains spaces you must enclose it in square brackets The Relational Model: QBE

  12. Query Using Computed Field The Relational Model: QBE

  13. Count Sum Avg (average) Max (largest value) Min (smallest value) StDev (standard deviation) Var (variance) First Last Calculating Statistics • Built-in statistics (called aggregate functions in Access) include: The Relational Model: QBE

  14. Query to Calculate an Average The Relational Model: QBE

  15. Grouping • Grouping – creating groups of records that share some common characteristic • Functions can be used in combination with grouping where statistics are calculated for groups of records The Relational Model: QBE

  16. Sorting • Sorting – listing records in a query’s results in a particular way • It is possible to sort using more than one field • Sort Key – the field on which records are sorted • Major sort key (primary sort key) – the more important field • Minor sort key (secondary sort key) – the less important field • Major sort key is on the left of the grid and the minor sort key is on the right The Relational Model: QBE

  17. Query to Sort Records The Relational Model: QBE

  18. Query to Sort on Multiple Keys The Relational Model: QBE

  19. Query to Sort on Multiple Keys… The Relational Model: QBE

  20. Joining Tables • Queries to select data from more than one table • Join the tables based on matching fields in corresponding columns • In an Access query, a join line between matching fields in the two tables will be created indicating how the tables are related • When joining multiple tables • Add all the tables involved to the upper pane • Add the query results grid in the desired order The Relational Model: QBE

  21. Query to Join Table The Relational Model: QBE

  22. Query to Join Tables… The Relational Model: QBE

  23. Update Query • Update query – a query that changes data • Makes a specified change to all records satisfying the criteria in the query • In Access, a new row is created that is used to indicate how to update the data selected by the query The Relational Model: QBE

  24. Update Query The Relational Model: QBE

  25. Delete Query • Queries can be used to delete one or more records at a time • Delete query – deletes all the records satisfying the criteria entered into the query • When you change the Query type to Delete Query, an extra row, called a Delete row, is added to the design grid The Relational Model: QBE

  26. Delete Query The Relational Model: QBE

  27. Make-Table Query • Queries can be used to create a new table in either the current database or in a separate database • Make-table query – creates a new table using the query results • The data added to the new table is separate from the original table in which it appears The Relational Model: QBE

  28. Make-Table Query The Relational Model: QBE

  29. Make-Table Query… The Relational Model: QBE

More Related