Assignment #8 =========== 1. LIST ALL EMPLOYEES WHO WERE HIRED IN YEAR 1983. 2. LIST ALL EMPLOYEES WHO WERE HIRED BETWEEN 30-MAR-81 TO 01-JUN-82. 3. FIND THE INCOME TAX OF ALL EMPLOYEES @5% OF ITS ANNUAL SALARY. 4. FIND THE DURATION OF SERVICE IN YEARS OF ALL EMPLOYEES. 5. FIND THE FIRST OCCURRENCE OF THE LETTER 'L� IN EMPLOYEE�S NAME AND CHANGE 'L' WITH 'X'. 6. DISPLAY THE EMPLOYEE�S NAMES, HIREDATE AND SALARY REVIEW DATE, WHICH IS THE FIRST MONDAY AFTER SIX MONTHS OF SERVICE. LABEL THE COLUMN REVIEW. FORMAT THE DATES TO APPEAR IN THE FORMAT SIMILAR TO �SUNDAY, THE SEVENTH OF SEPTEMBER, 1981�. 7. FOR EACH EMPLOYEE DISPLAY THE EMPLOYEE NAME AND CALCULATE THE NUMBER OF MONTHS BETWEEN TODAY AND THE DATE THE EMPLOYEE WAS HIRED. LABEL THE COLUMN MONTHS_WORKED. ORDER YOUR RESULTS BY THE NUMBER OF MONTHS EMPLOYED. ROUND THE NUMBER OF MONTHS UP TO THE CLOSEST WHOLE NUMBER. 8. DISPLAY THE ENAME AND THE SALARY FOR ALL EMPLOYEES. FORMAT THE SALARY TO BE 15 CHARACTERS LONG, LEFT-PADDED WITH $. LABEL THE COLUMN SALARY. 9. WRITE A QUERY THAT WILL DISPLAY THE EMPLOYEE NAMES WITH THE FIRST LETTER CAPITALIZED AND ALL OTHER LETTERS LOWERCASE AND LENGTH OF THEIR NAME, FOR ALL EMPLOYEES WHOSE NAME STARTS WITH J, A OR M. GIVE EACH COLUMN AN APPROPRIATE LABEL. 10. DISPLAY THE NAME, HIRE DATE AND THE DAY OF THE WEEK ON WHICH THE EMPLOYEE STARTED HIS JOB. LABEL THE COLUMN DAY. ORDER THE RESULT BY THE DAY OF THE WEEK STARTING WITH MONDAY. 11. CREATE A QUERY TO DISPLAY THE EMPLOYEE NAME AND COMMISSION AMOUNT. IF THE EMPLOYEE DOES NOT EARN COMMISSION, PUT �NO COMMISSION�. LABEL THE COLUMN COMM. 12. CREATE A QUERY THAT DISPLAYS THE EMPLOYEE�S NAMES AND INDICATES THE AMOUNT OF THEIR SALARIES THROUGH ASTERISKS. EACH ASTERISK SIGNIFIES A HUNDRED RUPEES. SORT THE DATA IN DESCENDING ORDER OF SALARY. LABEL THE COLUMN EMPLOYEE_AND_THEIR_SALARIES. 13. REPLACE ALL 'A'S WITH AN 'X' AND ALL 'D'S WITH A 'Y' 14. LIST EACH EMPLOYEES NAME, JOB, DEPT AND EXPERIENCE IN YEAR ROUNDED 15. DISPLAY THE LAST CHARACTER OF EACH EMPLOYEE 16. DISPLAY THE SYSTEM DATE 6TH MONTHS AGO 17. FIND THE FIRST OCCURRENCE OF 'E' IN EACH EMPLOYEE NAME 18. LIST ALL CHARACTERS FROM EMPLOYEE NAME BEFORE AN OCCURRENCE OF AN 'S' 19. DISPLAY THE YEAR ONLY FROM THE HIREDATE. 20. LIST ALL EMPLOYEES WITH THEIR GRADES. 21. LIST ALL EMPLOYEES WITH THEIR DEPARTMENT�S NAME AND LOC WHERE THEY ARE WORKING. 22. LIST AVERAGE SALARIES OF ALL EMPLOYEES WORKING IN NEWARK. 23. FIND ALL EMPLOYEES WHOSE DEPARTMENT IS NOT IN THE DEPT TABLE. 24. DISPLAY THE EMPLOYEE NAME, DEPARTMENT NAME AND PAY SCALE OF THOSE EMPLOYEES WHOSE GRADES ARE IN BETWEEN 3 AND 5 IN ALPHABETICAL ORDER BY EMPLOYEE NAME. 25. WRITE A QUERY TO DISPLAY THE NAME, JOB DEPARTMENT NUMBER AND DEPARTMENT NAME FOR ALL EMPLOYEES WHO WORK IN DALLAS. 26. LIST ALL EMPLOYEE INFORMATION INCLUDING THE NAME OF THE BOSSES FOR EACH EMPLOYEE 27. FIND HOW MUCH PAID TO EACH JOB. 28. FIND THE MAXIMUM, MINIMUM SAL OF ALL EMPLOYEES. 29. FIND THE DAY OF ALL EMPLOYEES WHEN THEY WERE HIRED. 30. FIND THE NEXT FRIDAY AFTER THE �12-MAR-98'. 31. FIND THE LAST DAY OF EACH HIRE DATE. 32. LIST MINIMUM AND MAXIMUM SALARIES OF EACH JOB IN EACH DEPARTMENT HAVING AVERAGE SALARIES GREATER THEN 2500. 33. COUNT ALL EMPLOYEES WHO WERE HIRED AFTER 22-MAR-81. 34. LIST THE MOST EXPERIENCED EMPLOYEE.
35. 36. 37. 38. 39. 40. 41. 42. 43. 44. 45.
LIST MAX, MIN SALARY OF EACH DEPT. LIST SUM OF THE SALARIES OF EACH JOB. LIST AVERAGE SALARIES OF EACH DEPT. LIST AVERAGE SALARIES OF EACH JOB IN EACH DEPT. LIST DEPT WISE SUM OF SALARIES OF ALL DEPT HAVING LESS THAN 3 EMPLOYEES. LIST DEPT WISE AVERAGE SALARIES OF EACH EMPLOYEE EXCLUDING MANAGERS. LIST AVERAGE SALARIES OF EACH JOB HAVING AVERAGE SALARIES EXCEEDING 2000. LIST AVERAGE SALARIES OF ALL MANAGERS. LIST AVERAGE SALARIES OF ALL ANALYSTS WHO WERE HIRED AFTER 04-DEC-99. LIST DEPT WISE NUMBER OF EMPLOYEES. HOW MANY EMPLOYEES HAVE JOBS AS MANAGER, SALESMAN AND ANALYST GIVE YOUR ANSWER IN THREE DIFFERENT COLUMNS. 46. FIND THE MOST RECENTLY HIRED EMPLOYEE OF THE COMPANY USING GROUP FUNCTIONS. 47. LIST EMPLOYEES WHO ARE GETTING MAX SALARIES OF THEIR RESPECTIVE DEPARTMENTS 48. LIST ALL EMPLOYEES WHO ARE GETTING LESS THAN THE AVG. SALARIES OF THEIR DEPARTMENTS 49. LIST ALL EMPLOYEES WHO ARE GETTING MAX AND MIN SALARIES OF THEIR RESPECTIVE DEPARTMENTS 50. LIST ALL EMPLOYEES HAVING ANNUAL SALARIES LESS THAN THE AVG. ANNUAL SALARIES OF THEIR DEPARTMENTS 51. LIST ALL EMPLOYEES WITH THEIR GRADES. 52. LIST ALL EMPLOYEES WITH THEIR DEPARTMENT NAME AND LOC WHERE THEY ARE WORKING. 53. LIST AVERAGE SALARIES OF ALL EMPLOYEES WORKING IN NEWARK. 54. FIND ALL EMPLOYEES WHOSE DEPARTMENT IS NOT IN THE DEPT TABLE. 55. DISPLAY THE EMPLOYEE NAME, DEPARTMENT NAME AND PAY SCALE OF THOSE EMPLOYEES WHOSE GRADES ARE IN BETWEEN 3 AND 5 IN ALPHABETICAL ORDER BY EMPLOYEE NAME. 56. WRITE A QUERY TO DISPLAY THE NAME, JOB, AND DEPARTMENT NUMBER AND DEPARTMENT NAME FOR ALL EMPLOYEES WHO WORK IN DALLAS. 57. LIST ALL EMPLOYEE INFORMATION INCLUDING THE NAME OF THE BOSSES FOR EACH EMPLOYEE.