Description
Total points: 14.
For this assignment, we will again be using the hr database from HW9.
The following is the structure of the hr database:
*Provided by w3resource website
It’s good practice to make SQL keywords uppercase to distinguish them from other parts of your query. Please do this to make it easier for the grader. Also remember to include a semicolon at the end of your query. This tells SQL where the end of your query is!
-
[1.5 pts] Provide the SQL code to display the last day of the month (in date format) three months before the current month.
-
[1 pt] Provide the SQL code to display the current date in the given format: e.g. 11/29/2021
-
[1.5 pts] Provide the SQL code to display the job ID and maximum salary of the employees where maximum salary is greater than or equal to $4000.
-
[1.5 pts] Provide the SQL code to display the job ID and average salary for each job ID excluding programmer (IT_PROG) from the employees table.
-
[1.5 pts] Provide the SQL code to find the manager ID and the salary of the lowest-paid employee for that manager from the employees table, displaying them in descending minimum salary order.
-
[1 pt] Provide the SQL code to display the first name, hire date and experience of the employees in terms of years (how long they have been working up to the current date).
-
[1.5 pts] Provide the SQL code to display the years in which more than 10 employees joined.
-
[1.5 pts] Provide the SQL code to display the department name, manager’s full name (renamed name, concatenated with first and last names), and manager’s salary for all managers whose experience up to 08/01/1992 is more than 5 years.
-
[1.5 pts] Provide the SQL code to display the department ID, year of hiring date, and the number of employees joined sorted in ascending order by department_id.
