Q.
No
Q1 Here is a sample data set of employees with theri salaries. The sub parts of this are listed belo
employee_id first_name last_name dept_id manager_id salary
100 John White IT 103 120000
101 Mary Danner Account 109 80000
102 Ann Lynn Sales 107 140000
103 Peter O'connor IT 110 130000
106 Sue Sanchez Sales 107 110000
107 Marta Doe Sales 110 180000
109 Ann Danner Account 110 90000
110 Simon Yang CEO null 250000
111 Juan Graue Sales 102 37000
Questions-
a. list the last 25% employees (as per theri salary)
b. list all the employees who have salary greater than the average salary of the entire dataset
c. Employees with Salaries Higher Than Their Departmental Average
d. Find the Duplicate Rows
e. Find the employee with the second highest salary
Q2 compute the running balance using SQL
day daily_amount_credit
Jan 30, 2023 1000
Jan 31, 2023 800
Feb 1, 2023 700
Q3 employee_table employment_table
Emp_ID Emp_Name Emp_No Emp_ID Emp_Profile
101 Ashish Kaktan9450425345 101 Content Writer
102 Raj Choudhary8462309621 104 Data Analyst
103 Vivek Oberoi 7512309034 105 Software Engi
104 Shantanu Khan9020330023 108 Development E
105 Khanak Desai 8451004522 109 Marketing Man
109 Content Writer
101 Development E
105 Data Analyst
a. Fetch the following list using SQL- Emp_id, Emp_name, latest Emp_Profile, latest Emp_join_date.
Q4 employee_table employee_detail_table
Emp_ID Emp_Profile Emp_Email Emp_CountryEmp_Email
101 Content Writerashish@scaler.com Germany ashish@scaler
104 Data Analyst shantanu@scaler.com India shantanu@sca
105 Software Engi khanak@scaler.com India khanak@scale
109 Development Eakshay@scaler.com Europe akshay@scale
108 Marketing Mannikita@scaler.com
a. fetch the following - emp_id, emp_email, emp_country, emp_joindate. Which join would you use, a
rts of this are listed below. Answer the below questions using SQL query
expertise
Senior
junior
Semisenior
Senior
Junior
Senior
Senior
Senior
Junior
the entire dataset
Emp_CountryEmp_Join_Date
Germany 2021-04-20
India 2022-12-11
India 2022-01-03
Europe 2023-02-15
Mexico 2020-05-23
Germany 2021-05-23
Europe 2022-05-23
India 2023-05-23
ile, latest Emp_join_date. Each employee should have only one row in the output
Emp_JoinDate
2021-04-20
2022-12-11
2022-01-03
2023-02-15
hich join would you use, and why?
