Exercise
Q1. Consider the following Employee table:
Table Name Employee
Employee_ID Employee_Name Job Title
Salary Bonus Age Manage
50000
NULL
President
29
Divya
1201
30000
2500
Manager
26
Amyra
1205
20000
1500
Analyst
23
Rahul
1211
Salesman
15000
NULL
22
1213
Manish
22000
1300
Analyst
25
Megha
1216
1201
Salesman
16000
NULL
22
Mohit
1217
The primary key of this table is Employee_ID and Manager_ID is a foreign key thatreler
The
ide
(a)
Employee_ID
Write SQL commands for the following:
(a) Create the above table.
(b) Insert values as shown above.
(c) Delete the Employee having Employee_ID 1217.
(d) Update the salary of "Amyra" to 40000.
(e) Alter the table Employee so that NULL values are not allowed for Age column.
(0) Write a query to display names and salaries of those employees whose salary
(g) Write a query to display details of employees who are not getting any bonus
hl Write a query to display the names of employees whose name contains a
greater than 20000.
Answers
Answer:
Explanation: See solution in the image.
Answer:
Explanation:
(a) Creating table
CREATE TABLE Employee (Employee_Id INT PRIMARY KEY NOT NULL,
Employee_Name VARCHAR (25) NOT NULL,
Job_Title VARCHAR (25),
Salary INT,
Bonus INT,
Age INT,
Manager_Id INT,
FOREIGN KEY (Manager_Id) REFERENCES Employee(Employee_Id));
(b) Inserting Value
INSERT INTO Employee
(Employee_Id, Employee_Name, Job_Title, Salary, Bonus, Age, Manager_Id)
Values
(1201, "Divya", "President", 50000, NULL, 29, NULL),
(1205, "Amyra", "Manager", 30000, 2500, 26, 1201),
(1211, "Rahul", "Analyst", 20000, 1500, 23, 1205),
(1213, "Manish", "Salesman", 15000, NULL, 22, 1205),
(1216, "Megha", "Analyst", 22000, 1300, 25, 1201),
(1217, "Mohit", "Salesman", 16000, NULL, 22, 1205);
(c) Deleting employee having Employee_Id 1217
DELETE FROM
Employee
WHERE
Employee_Id = 1217;
(d) Update the salary of "Amyra" to 40000.
UPDATE
Employee
SET
Salary = 40000
WHERE
Employee_Name = "Amyra";
(e) Alter the table Employee so that NULL values are not allowed for Age column.
ALTER TABLE
Employee
CHANGE
Age INT NOT NULL;
(f) Write a query to display names and salaries of those employees whose salaries are greater than, 20000
SELECT
Employee_Name, Salary
FROM
Employee
WHERE
Salary>20000;
(g) Write a query to display details of employees who are not getting any bonus
SELECT
*
FROM
Employee
WHERE
Bonus IS NULL;
(h) Write a query to display the names of employees whose name contains "a" as the last alphabet
SELECT
Employee_Name
FROM
Employee
WHERE
Employee_Name LIKE "%a";