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.
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.
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.
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%).
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.
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 Appendix B, 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.