QBE

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)