1 / 23

Computer Science & Engineering 2111

Computer Science & Engineering 2111. Lecture 11 Querying a Database. 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

dillon-arnold
Download Presentation

Computer Science & Engineering 2111

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. 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

More Related