Computer Science & Engineering 2111

1. Computer Science & Engineering 2111 Lecture 11 Querying a Database CSE 2111 11-Querying a Database

2. What is a Database Query? • A request for information from a database • To extract information from the Database you must use a Query which is a “question” or “request” • Criterion • An expression that tells the DBMS which records to retrieve • Make up of conditions • Can be one conditions or many • When you run the query a dynaset, or subset of the database is displayed. • You can make changes to this dynaset and the changes will be reflected in your database, because the dynaset is just a view of your database. CSE 2111 11-Querying a Database

3. Structured Query Language (SQL) • SQL is the language you use to talk to the database • MS Access 2010 supplies a graphical user interface (GUI) called the Query By Example Grid or (QBE) grid • MS Access 2010 creates the SQL for you. CSE 2111 11-Querying a Database

4. Query By Example (QBE) Grid Dynaset created when Query is Run SQL Created from QBE Grid CSE 2111 11-Querying a Database

5. Create a list of clients by first and and last name CSE 2111 11-Querying a Database

6. The resulting dynaset: What happens if we laterchange the last name of John Smith to Jones on the Client Table and re-run the query? CSE 2111 11-Querying a Database

7. Write a query to list the First Name and Last Name and state for all clients who live in Ohio. When typing in any non-numeric criteria, always surround the criteria with quotes. CSE 2111 11-Querying a Database

8. The data table The resulting dynaset CSE 2111 11-Querying a Database

9. Wild cards in Criteria An asterisk * replaces any number of characters • Used with the keyword, Like • Like “C*” – in Product Name field will select c, Cookie, cake. • Like “*cookie*” - in Product Name field will select all records that include the word cookie in the Product Name field A ? replaces a single character • Like “B?” – in the Category field will select BE & BA CSE 2111 11-Querying a Database

10. Write a query to list the First Name and Last Name for all clients who live within the 614 area code. CSE 2111 11-Querying a Database

11. The data table The resulting dynaset CSE 2111 11-Querying a Database

12. Write a query to list the First Name and Last Name of all clients who DO NOT live within the 614 area code. CSE 2111 11-Querying a Database

13. The data table The resulting dynaset CSE 2111 11-Querying a Database

14. Relational operators in a query = , <= , >= , <> , < , > • With Numbers <= 10 values of less than or equal to 10 • With Text < “G” text beginning with letters A through F > “Jones” text from Jones through end The appropriate expression is placed in the field where this data if found. CSE 2111 11-Querying a Database

15. Write a query to list the First Name, Last Name, amount and payment date for all clients who paid $200 or more. CSE 2111 11-Querying a Database

16. The data tables The resulting dynaset Dynaset only includes records that have matching keys on both tables. This is called an inner join which is the default join type in Access CSE 2111 11-Querying a Database

17. For multiple conditions in the same field use Boolean Operators - AND, OR, NOT • “BE or “BA” • >5 AND <10 • NOT “BE” • For conditions in multiple fields the placement of your arguments determines the Boolean relationship between those arguments • If a criteria is on the same line it is automatically considered an AND • If a criteria is on a separate line it is automatically considered an OR CSE 2111 11-Querying a Database

18. Write a query to list the First Name, Last Name, and amount for all clients who paid $250 or more or made payments of less than $75. CSE 2111 11-Querying a Database

19. The data tables The resulting dynaset Dynaset only includes records that have matching keys on both tables. This is called an inner join which is the default join type in Access CSE 2111 11-Querying a Database

20. Write a query to list the First Name, Last Name, amount and payment date for all clients who paid more than $100 on or after 3/3/2008. CSE 2111 11-Querying a Database

21. The data tables (partial view) The resulting dynaset CSE 2111 11-Querying a Database

22. Write a query to list the First Name and Last Name of all clients who made payments between 1/1/2008 and 3/8/2008. CSE 2111 11-Querying a Database

23. The data tables The resulting dynaset CSE 2111 11-Querying a Database