Assignment
Question A
Create a query that SELECT all of the EMPLOYEES from the EMPLOYEE TABLE that are Mangers. Include Employee ID first name, Lastname and Salary (Hint: Self Join)
Question B
Write a query the gives the Employee ID first name, Lastname and Salary and Project Number of EMPLOYEES who aren't currently assigned to a project (hint outer join)
Question C
Create a query that lists the lastnme, edlevel , job, the number of years they've worked as of Jan 01/2002 ( hint : year function Jan 01/2002 minus hiredate), and their salary.
Get the employees that have the same Job as the employee named starts with J (hint subquery from employee) and hiredate < Jan 01/2002 Sort the listing by highest salary first.
Question D
Create a table with a COMPOSITE PRIMARY KEY (mininum 3 columns the table) and the second table will have will have a foreign keys to the PRIMARY KEY
Run the Reverse Engineer function in MySQL workbench on these tables and provide the .MWB file in your submission
Question E
Create a table in 1NF (mininum 5 columns the table). Then transform that table into as many tables as necessary to satisfy 2NF.Then transform that table into as many tables as necessary to satisfy 3NF
Question F
Create a View (V_EMP_DEPT_PROJ) statement that joins the EMPLOYEE and EMPPROJACT (join on EMPNO) and PROJECT table (join on PROJNO). Limit the view to contain:
o ID
o Name (FirstName and Lastname)
o WorkDept
o Salary
o ACTNO
o PROJNO
o PROJNAME
o Where Salary < 100000.