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. Change the Department of Shivam to IT in the table Employee.
2. Remove the record of Alisha from the table Employee.
3. Add a new column Experience of integer type in the table Employee.
4. Display the first name, last name and amount of reward for all employees from the tables Employee and Reward.
5. Display first name and salary of all the employees whose amount is less than 2000 from the tables Employee and Reward.

Answer 1

1. UPDATE Employee SET Department = 'IT'
   WHERE First_name = 'Shivam';
2. DELETE FROM Employee WHERE First_name = 'Alisha';
3. ALTER TABLE Employee ADD Experience int; 
4. SELECT First_name, Last_ name, Amount FROM Employee E, Reward R WHERE E.Employee_id = R. Employee_id;
5. SELECT First_ name, Salary FROM Employee E, Reward R WHERE E.Employee_id = R.Employee_id AND Amount<2000;