1 / 116

CST221: Database Systems (II)

This resource covers essential querying techniques for databases, focusing on Microsoft Access functionalities. Key topics include creating queries using the Query Wizard, applying wildcard searches, constructing parameter queries, and utilizing comparison operators. You'll learn about compound criteria using AND/OR, sorting options, and how to omit duplicates in results. Additionally, discover how to calculate fields and statistics, handle multiple keys, and perform top-value queries. Practical exercises provide opportunities to apply these concepts, making it ideal for students and professionals alike.

meira
Télécharger la présentation

CST221: Database Systems (II)

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. CST221: Database Systems (II) Dr. Zhen Jiang Computer Science Department West Chester University West Chester, PA 19383

  2. Querying • Creation (Query wizzard) • Save • Use

  3. Wildcard (*, ?), page AC87 • Use criteria for a field not included in the result list, page AC88 • Parameter Query, page AC89 • Join table, page AC103 • Comparison Operator, page AC94 • Compound criterion (AND, OR), page AC 95 • Sorting, page AC97 • Omitting duplicates, page AC 100 • Multiple keys (high priority from left to right), page AC101 • Top-value query, page AC 102 • Calculated field, page AC 113 • Calculating Statistics (built-in statistics, group), page AC 117

  4. Exercise 2

  5. Show the name and age and salary of all employees who are younger than 25.

  6. Double click the items to include in the list

  7. Add criteria to meet the certain requirement

  8. Datasheet view • Design view

  9. Hidden column for criteria (not displayed in the list)

  10. Wildcard (*, ?), such as “Ber*” in City field • Use criteria for a field not included in the result list, disabled show property • One table, decide it early and add all fields at the beginning • Comparison Operator, such as >10000 in amount paid field • Compound criterion (AND => 1 row, OR => another row) • Sorting • Omitting duplicates, “unique record” in property sheet • Multiple keys (high priority from left to right) • Top-value query, “return” in top design view (very early stage) • Calculated field, “zoom” in shortcut menu (e.g., type “Total Amount:[Amount Paid]+[Current Due]”) • Calculating Statistics (built-in statistics, “group by”)

  11. Show the Enum, name, salary of all employees whose names have 5 letters.

  12. Show the name, Enum ,salary, and age of all employees with a salary > $40,000ANDyounger than 25

  13. Show the Enumof all employees sorted by salary in the ascending order

  14. Hidden column for criteria (not displayed in the list)

  15. List all different Dnum, without duplicates.

  16. List top 5 high paid employees.

  17. List the LName of all employees in the department (Dnum) entered by the user.

  18. List the LName of all employees with a new column labeled “hiring age”, which has a value by subtracting the years of working (wYears) from the current age (age).

More Related