HOMEWORK # 3: MDEPT/MEMP

1. MDEPT:

   Attribute	Data Type	Attribute Length
   Deptno	Integer
   Dname	Char	15
   Loc	Char	20

   with the following constraints:
   
   1. Department numbers (Deptno) must be unique
   2. Department names (Dname) cannot contain null values
2. MEMP:

   Attribute	Data Type	Attribute Length
   Empno	Integer
   Ename	Char	30
   Job	Char	15
   Manager	Integer
   Hiredate	Date
   Salary	Integer
   Commission	Integer
   Deptno	Integer

   with the following constraints:
   
   1. Employee name cannot have null values
   2. Employee number is the primary key
   3. Department number is the foreign key referring to the department number in the MDEPT table

1. Using SQL syntax, populate the MDEPT and MEMP tables as follows (input your data in all capital letters):
   1. MDEPT
      1. 10, finance, virginia
      2. 20, accounts, ohio
      3. 30, hrd, new york
      4. 40, head office, washington
      5. 50, marketing, california
   2. MEMP
      1. 7782, clark, manager, 7839, 09-jul-81,2450, null, 10
      2. 7839, king, president, null, 17-nov-81,5000, null, 20
      3. 7934, miller, clerk, 7782, 23-jan-82,1300, null, 30
      4. 7369, smith, clerk, 7782, 17-dec-80,1700, null, 40
      5. 7300, hyde, clerk, 7782, 17-aug-80,1600, null, 40
      6. 7765, blake, cleaner, 7765, 23-jun-89,7000, null, 50
2. List the employee name where the employee works as either a clerk or a manager.
3. List the employee name where the department number is neither 10 nor 20.
4. List the employee name and the employee number where the salary is greater than 1000.
5. List the employee name where the employee name begins with an `M'.
6. List the employee name where the employee name has `I' as the 2nd alphabet in the name.
7. List the employee name where the employee name has `AR' in the name.
8. List the employee name where the salary is between 2000 and 5000.
9. List the employee name where the commission is null.
10. List the employee name where the commission is not null.
11. What is the average salary of all managers?
12. What is the the sum of salaries of all clerks in department number 10?
13. What is the the total number of all employees?
14. What are the different jobs of employees? Do not list any job more than once.

Forms/Reports

1. Create a report listing all of the clerks, their departments, and their salaries.
2. Create a form for future data entry into the tables.

Submission Instructions

• Submit a printed copy of each query. (The queries to create and populate your tables must also be included.)
• Submit a printed copy of the query results. (The queries to create and populate your tables must also be included.)
• Submit a printed copy of your form(s) and report(s).
• If assignments are printed on perforated paper, please separate each sheet prior to submission.
  
• Do not use any fancy folders. Simply staple the papers together. Please use one staple in the upper left hand corner to bind all sheets together.
  
• Make sure your name is clearly printed on the first page of the assignment.