1 / 116

CST221: Database Systems (II)

CST221: Database Systems (II). Dr. Zhen Jiang Computer Science Department West Chester University West Chester, PA 19383. Querying. Creation (Query wizzard ) Save Use. Wildcard (*, ?), page AC87 Use criteria for a field not included in the result list, page AC88

meira
Download Presentation

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