1 / 25

QBE

QBE. A query is a question represented in a way that the database management system can recognize and process. Query-by-Example offers a very visual way to construct queries. Simple and Compound Criteria. Criteria are restrictions that the records to be retrieved must satisfy.

jnoble
Download Presentation

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. QBE • A query is a question represented in a way that the database management system can recognize and process. • Query-by-Example offers a very visual way to construct queries.

  2. Simple and Compound Criteria • Criteria are restrictions that the records to be retrieved must satisfy. • Compound criteria combines multiple criteria by using the commonly used operators AND and OR.

  3. QBE • Simple retrieval • AND / OR conditions • Two conditions in a single field • Computed fields • Calculating statistics e.g count, average • Grouping • Joins with or without restrictions

  4. Advanced QBE • Pattern match - LIKE • List of Values Match – IN • Non-Matching Value- NOT IN • Parameter query • Crosstabs query • Action queries (Update, Insert, & Delete)

  5. Assignment 3 • MS Access 2000 • Page AC 3.38 • #1-12

  6. The Relational Algebra • Relational algebra is a theoretical way of manipulating a relational database. • Retrieving data from a relational database involves issuing relational algebra commands to obtain results.

  7. Relational Operators • Projection • Selection (restriction) • Union • Difference • Product (Cartesian) • Join • Intersection • Division (hard to do in SQL)

  8. Normal Set Operations • The union of two tables is a table containing all rows that are either the first table, the second table, or both. • The intersection of two tables is a table containing all rows that are common to both. • The difference of tables A & B is the set of all rows that are in A, but not in B.

  9. Project Restrict Intersection Difference Union

  10. Cartesian Product • The PRODUCT of two tables is a table obtained by concatenating every row in the first with every row in the second

  11. Product of Two Tables

  12. JOINS • Cross join • Cartesian Product • Simple or natural join or inner join • No dangling tuples • Outer join (full, left or right) • Includes dangling tuples by padding out with NULLs

  13. Codd’s Relational System • Users perceive database as collection of tables only • The RESTRICT, PROJECT and JOIN relations are supported

  14. Classification Scheme • Tabular structure • does not support RESTRICT, PROJECT & JOIN • Minimally relational • does not support all relational algebra operations • Relationally complete • supports a full implementation of SQL • Fully relational • supports integrity rules as well

  15. SQL • Creates the components of a database • Manipulates components into various views • DDL (to define and create database components) • DML (to manipulate database components) • DCL (to provide internal security for the database)

  16. Why use SQL? • More powerful than QBE (performs unions and sub-queries) • Cannot use the DDL component of SQL in QBE • Can be used from within other applications (Excel, Word, Visual Basic) • Industry standard language (useful outside Access)

  17. SQL • DDL • CREATE • ALTER • DROP • DML • Required keywords • SELECT, FROM • Optional clauses • WHERE, ORDER BY • GROUP BY, HAVING

  18. General form of SELECT • SELECT [DISTINCT] field(s) • FROM table(s) • [WHERE condition] • [GROUP BY field(s) [HAVING condition]] • [ORDER BY field(s)];

  19. Searching with WHERE • Range search • BETWEEN / NOT BETWEEN • Set membership search • IN / NOT IN • Pattern search • LIKE / NOT LIKE • Null search • IS NULL / IS NOT NULL

  20. Aggregate Functions • COUNT • SUM • AVG • MAX • MIN

  21. SubQueries / Nested Queries • A subselect statement is used following a relational operator in the WHERE or HAVING clauses of the outer SELECT statement

  22. Form/Report Controls • Bound • data source is a field in the underlying table • Unbound • to display titles, labels and graphics • Calculated • data source is an expression, created from one or more fields

  23. Custom Forms/Reports • Use design view • Add header/footer (for form) • Sort/Group Data in a Report • Add Report header/footer (for group totals)

  24. Toolbox • List boxes • Combo boxes • Check boxes • Command buttons

  25. Assignment 4 • MS Access 2000 • Page AC 5.55-5.56 • #1-12 (Use SQL for 3-6)

More Related