Write a query to produce a list of employee names and jobs for
every employee in Department 84.
After you have successfully run the query in step 1, use the
Query function key to bring it back to your display panel. Change
it to produce a list of employee numbers, employee names, years
of service, and salary for every employee in Department 51.
Change the preceding query to show all of the columns for employees
in Department 51.
Produce a report that contains the employee identification number,
name, department, and years of service for each person who has no
data in the YEARS column.
Write a query to produce a list that shows employee identification
number, name, job, and years of service for everyone with 10 or
more years of service. Would someone with exactly 10 years of service
appear on your list?
Produce a report that contains the name and commission for any
manager whose row contains a commission amount.
Exercise 2
Produce a report that contains each clerk's name, department,
and years of service. Arrange the report in ascending alphabetic
order by employee name.
Produce a report that contains the name, department, and years
of service for every clerk. Put the department numbers in ascending
order and, within each department, put the years of service in ascending
order.
Write a query to produce a list that shows employee number,
employee name, and years of service for all the clerks. Arrange
the report by years of service with the most senior clerk first.
Change step 3 to again arrange the report in descending order
by years of service but, within each year, in ascending order by
department number. Include the department numbers in your report.
Produce a report that contains each employee whose name contains
the letter Z.
Produce a report that contains each employee whose name begins
with S.
Produce a report that contains each employee whose name has
an A as its third character.
Exercise 3
Produce a report that contains the name, salary, and commission
of all persons whose salary is greater than $18,000 or whose commission
exceeds $1,000.
Produce a report that lists all employees who have no data in
their years of service column or no data in their commission column.
Show the employee's name, years of service, and commission. (Hint:
Remember that you must use the equal (=) or not equal (¬=)
symbols when comparing for NULL values in QBE.)
Write a query to produce a list that shows employee number,
name, and salary for everyone with a salary between $20,000 and
$21,000. Did people with a salary of exactly $20,000 or $21,000
appear on your list?
If they did appear, how could you exclude them?
If they did not appear, how could you include them?
Produce a report that lists all managers who have been with
the company less than 10 years, but whose salary is at least $20,000.
Show the name, job title, years of service, and salary.
Show the name, years of service, salary, and commission of those
employees with less than 10 years of service and either a salary
over $20,000 or a commission over $1,000.
Exercise 4
Write a query to produce the name, employee number, salary,
commission, and total earnings (salary plus commission) of everyone
with a job of sales.
Write a query to produce the name, number, salary, commission,
and total earnings of everyone with a job of sales whose total earnings
are less than $17,500.
Produce a report that lists each sales person's name and commission
as a percentage of salary. (For example, if a person's salary is
$20,000 and commission is $2,000, the commission percentage is 10.)
Arrange the report in descending order by the commission percentage.
Change step 3 so that commission percentage is based on total
earnings (salary plus commission = 100%).
Exercise 5
Write a query that will access both the Q.STAFF and Q.ORG tables
(DRAW Q.STAFF and DRAW Q.ORG). Produce a report that contains each department
name, location, and manager's name.
Change step 1 to list only those departments in the Eastern
Division.
Change step 2 to list any managers in the Eastern Division who
have 10 or more years of service. For each manager, list the department
name, location, and manager's name.
Exercise 6
Make a copy of the Q.STAFF table and call it MYTABLE.
Write a query to update MYTABLE. Change the name of the manager
for Department 66 to RAMOTH, years of service to 7, and salary to
$18,238.50. Write a query to retrieve the row after you have updated
it.
Write a query that increases the salaries of MYTABLE by 10%.
Retrieve all the rows for clerks. Because MYTABLE began with data
identical to Q.STAFF, you can randomly check YEARS and SALARY against
the Q.STAFF table in QMF sample
tables, to ensure that the correct
persons received the salary increases.
Insert a new row into MYTABLE. The new employee's information
is as follows:
ID = 275
NAME = ROGERS
DEPT = 66
JOB = SALES
YEARS = NULL
SALARY = $14,000.00
COMM = NULL
After you have inserted the row, write and run a query that will
display it.
Delete from MYTABLE the rows for salespersons in Department
66.