Question

Excellent Consultancy Pvt.Ltd. maintains two tables for all its employees. 

                                               Table : Employee

Employee_id	First_name	Last_name	Salary	Joining_date	Department
E101	Monika	Das	100000	2019-01-20	Finance
E102	Mehek	Verma	600000	2019-01-15	IT
E103	Manan	Pant	890000	2019-02-05	Banking
E104	Shivam	Agarwal	200000	2019-02-25	Insurance
E105	Alisha	Singh	220000	2019-02-28	Finance
E106	Poonam	Sharma	400000	2019-05-10	IT
E107	Anshuman	Mishra	123000	2019-06-20	Banking

Table: Reward 

Employee_id	Date_reward	Amount
E101	2019-05-11	1000
E102	2019-02-15	5000
E103	2019-04-22	2000
E106	2019-06-20	8000

Write suitable SQL queries to perform the following task:

1. Display the year of joining of all the employees from the table Employee.
2. Display each department name and its corresponding average salary.
3. Display the first name and date of reward of those employees who joined on Monday from the tables Employee and Reward.
4. Display sum of salary of those employees whose reward amount is greater than 3000 from the tables Employee and Reward.
5. Remove the table Reward. 

Answer 1

1. SELECT YEAR(Joining_date) FROM Employee; 
2. SELECT Department, AVG(Salary) FROM Employee GROUP BY Department;
3. SELECT First name, Date_reward FROM Employee E, Reward R WHERE E.Employee_id=R. Employee_id AND DAYNAME(Joining_date) = 'MONDAY';
4. SELECT SUM(Salary) FROM Employee E, Reward R WHERE E.Employee_id=R.Employee_id AND Amount>3000;