CST221: Database Systems (II)

CST221: Database Systems (II)Dr. Zhen JiangComputer Science DepartmentWest Chester UniversityWest Chester, PA 19383QueryingCreation (Query wizzard) SaveUseWildcard (*, ?), page AC87Use criteria for a field not included in the result list, page AC88Parameter Query, page AC89Join table, page AC103Comparison Operator, page AC94Compound criterion (AND, OR), page AC 95Sorting, page AC97Omitting duplicates, page AC 100Multiple keys (high priority from left to right), page AC101Top-value query, page AC 102Calculated field, page AC 113Calculating Statistics (built-in statistics, group), page AC 117

3Exercise 2

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

Double click the items to include in the list

Add criteria to meet the certain requirement

Datasheet viewDesign view

Hidden column for criteria (not displayed in the list) Wildcard (*, ?), such as Ber* in City field Use criteria for a field not included in the result list, disabled show propertyOne table, decide it early and add all fields at the beginning Comparison Operator, such as >10000 in amount paid fieldCompound criterion (AND => 1 row, OR => another row)SortingOmitting duplicates, unique record in property sheetMultiple 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)

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

Show the name, Enum ,salary, and age of all employees with a salary > $40,000 AND younger than 25

Show the Enum of all employees sorted by salary in the ascending order

Hidden column for criteria (not displayed in the list)

List all different Dnum, without duplicates.

List top 5 high paid employees.

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

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

List the max, min, and average of salary for all employees.

Joint table (AC 105-109)DeptDnumDnameCityD22Web WCD25Databases NYD28Software LAEmpEnumLNameagesalaryDnumwYearsE101Jones45$56,000.00D2512E202Anders66$46,000.00D2225E303Smith34$25,000.00D229E404Rivera22$30,000.00D251E505Brown45$80,000.00D2517E606Caldwell52$70,000.00D2820E707Stiles44$65,000.00D2811E808Walker48$90,000.00D2221E909Hartman20$25,000.00D281E222Carter29$35,000.00D253For each employee, print out the department name.

Assignment 2Statistics of an groupExercise 3 Show the average age (label as average age) and average salary (label as average salary) for all employees

Show the max, min and average age for all employees in Dept Software

Still incorrect!

Join Table (AC 105)For all employees under 30, show a count of these employees and their average salary.

For all employees in department D22 or D28, show a count of these employees, their average age, and maximum salary

What will be the result?

For each department list avg, max and min age, along with a count of employees in each department.

For each department find a count and average salary for all employees younger than 40.

For each department with an average salary of >40000 list the dnums, employee count for these departments.

For each department with less than 4 employees, show the average age and number of employees.

For each department with more than 2 people over 40 list deptNum, and a count of these people.

Assignment 3MaintenanceRecord addition, page AC 144Search, page AC 145Record update, page AC 147Record deletion, page AC 148Filter, page AC 149