The following ERD and two tables represent a partial model similar to what we used in class. Answer all questions by writing SQL syntax to solve each. If a question requires more time, please move on to the next. Employee EmpNo (PK) Ename Job ManagerID HireDate Salary Commission DeptNo (FK) Department DeptNo Dname Location 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Department DeptNo (PK) Dname Location 1400 Employee Monthly EmpNo Ename Job ManagerID HireDate Salary Commission DeptNo 7369 SMITH CLERK 7902 17-DEC-80 800 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 7521 WARD SALESMAN 7698 22-Feb-81 1250 500 7566 JONES MANAGER 7839 02-APR-81 2975 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 7698 BLAKE MANAGER 7839 01-MAY-81 2850 7782 CLARK MANAGER 7839 09-JUN-81 2450 7788 SCOTT ANALYST 7566 09-DEC-82 3000 7839 KING PRESIDENT 17-NOV-81 5000 7844 TURNER SALESMAN 7698 08-SEP-81 1500 7876 ADAMS CLERK 7788 12-JAN-83 1100 7900 JAMES CLERK 7698 03-DEC-01 950 7902 FORD ANALYST 7566 03-DEC-01 3000 7934 MILLER CLERK 7782 23-JAN-02 1300 1. List jobs and the number of employees who work in each job in descending order by job 2. List employee name and job in ascending order by job of those employees who were hired on or after 01-Jul-1980 3. List employee name of those without a manager MacBook Air 4. List employee names of those whose monthly salary is equal to the average of maximum and minimum salaries. 5. List the jobs of those employees whose monthly salary is more than 2000 after receiving a salary raise of 20%. 6. List employee name, annual salary, daily salary of all SALESMAN in ascending order by annual salary. The database stores monthly salaries; use 365 days/year and 12 months/year DF MacBook Air 7. List employee name, hire date, and each employee’s employment experience in months in ascending order by experience. Employee experience is the difference between today’s date and hiring date 8. List employee names that start with ‘M’ 9. List employee names who are working for department 10 or 20 with job titles as CLERK or ANALYST with employment experience more than 8 years and hiring date that does not belong to the months of March, April and May and working for managers with manager IDs that do not end with 88 and 66 10. List name, salary and location of employees who work at CHICAGO or DALLAS with experience more than 6 years 11. List employee name and the department number of those who are paid the highest salary in their departments 12.Display employee number and location of all employees working at CHICAGO or working for ACCOUNTING with annual salary more than 28000 but monthly salary that is not be between 2800 and 3000. Each employee’s job in the results should not be manager and his/her employee number must have the digit ‘7’ or ‘8’. Arrange column in ascending order by DeptNo
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more