Question
dont post a previous solution because the problem statement is an updated one. Proving yourself worthy of being able to handle bigger tasks, the tech
Proving yourself worthy of being able to handle bigger tasks, the tech lead has decided to give you a challenging job. However, this time, the data that you would be handling is very sensitive and no one wants this data to be leaked. Therefore, instead of getting the entire table, the tech lead has given you the list of attributes that the table contains and the table name. The information given is as follows:
Table Name: employees | |
Attribute Name | Attribute type |
employee_id | char(10) |
first_name | varchar(20) |
last_name | varchar(20) |
varchar(60) | |
phone_number | char(14) |
hire_date | date |
job_id | char(10) |
salary | int |
commission_pct | decimal(5,3) |
manager_id | char(10) |
department_id | char(10) |
You need to insert rows (around 10 or more to test all of the queries for the following tasks) as per the data types of the attributes/columns and problem statements of the following tasks.
Employee IDs can be EMP001, EMP002, etc. Job IDs can be JOB001, JOB002, etc. Manager IDs can be MNG001, MNG002, etc. Department IDs should be DPT001, ..., DPT005, ... DPT007, etc.
Write down the queries to retrieve the following information: [7 X 2 =14]
- Find the first_name, last_name, email, phone_number, hire_date and department_id of all the employees with the latest hire_date.
- Find the first_name, last_name, employee_id, phone_number, salary and department_id of all the employees with the lowest salary in each department.
- Find the first_name, last_name, employee_id, commission_pct and department_id of all the employees in the department 'DPT007' who have a lower commission_pct than all of the employees of the department 'DPT005'.
- Find the department_id and total number of employees of each department which does not have a single employee under it with a salary more than 30,000.
- For each of the departments, find the department_id, job_id and commission_pct with commission_pct less than at least one other job_id in that department.
- Find the manager_id who does not have any employee under them with a salary less than 3500.
- Find the first_name, last_name, employee_id, email, salary, department_id and commission_pct of the employee who has the lowest commission_pct under each manager.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started