Question

Question 4. Create a table Employee(E_ID PRIMARY KEY, F_NAME ,L_NAME , SALARY, JOINING_DATE ,DEPT_ID)

Create a table Department(DEPT_ID, DEPT_NAME, MGR_ID, LOC_ID)

For each of the above attributes, define their corresponding data types as per your understanding.

Write an SQL query to Insert 6 tuples to each table
Write an SQL query to fetch the departments that have less than one person in it.
Write an SQL query to fetch employee names with salaries >= 40000 and <= 100000.
Write an SQL query to print details of the Employee whose FIRST NAME ends with ‘n’ and contains six alphabets.
Write an SQL query to print details for department excluding department name as “HR” and “IT” from department table.
Write an SQL query to Add a new column to the table employee with the column name as Email_id.
Write an SQL query to rename the table employee to employees and department to departments.
Write an SQL query to List out the maximum salary of the employee.

Answer 1

//Creating table Employee
CREATE TABLE Employee(E_ID INT PRIMARY KEY, 
F_NAME VARCHAR(30) ,L_NAME VARCHAR(30) ,
 SALARY INT (10), JOINING_DATE datetime ,DEPT_ID INT (20));
//Creating table Department
CREATE TABLE Department(DEPT_ID INT(20), 
DEPT_NAME VARCHAR(20), MGR_ID INT(20), LOC_ID INT (10));

//Inseting tuples into the department table
INSERT INTO `employee`(`E_ID`, `F_NAME`, `L_NAME`, `SALARY`, `JOINING_DATE`, `DEPT_ID`) 
VALUES (1,'John','Ham',8000,'2020-09-12',1);
INSERT INTO `employee`(`E_ID`, `F_NAME`, `L_NAME`, `SALARY`, `JOINING_DATE`, `DEPT_ID`) 
VALUES (2,'Carlos','Tim',40000,'2012-09-12',2);
INSERT INTO `employee`(`E_ID`, `F_NAME`, `L_NAME`, `SALARY`, `JOINING_DATE`, `DEPT_ID`) 
VALUES (3,'Robert','Ham',50000,'2010-09-12',3);
INSERT INTO `employee`(`E_ID`, `F_NAME`, `L_NAME`, `SALARY`, `JOINING_DATE`, `DEPT_ID`) 
VALUES (4,'Grusha','Lincoln',60000,'2014-09-12',4);
INSERT INTO `employee`(`E_ID`, `F_NAME`, `L_NAME`, `SALARY`, `JOINING_DATE`, `DEPT_ID`) 
VALUES (5,'Abraham','Lincoln',30000,'2001-09-12',5);
INSERT INTO `employee`(`E_ID`, `F_NAME`, `L_NAME`, `SALARY`, `JOINING_DATE`, `DEPT_ID`) 
VALUES (6,'George','Bush',140000,'2008-09-12',6);
//Inseting tuples into the department table
INSERT INTO `department`(`DEPT_ID`, `DEPT_NAME`, `MGR_ID`, `LOC_ID`) VALUES (1,'HR',1,1);
INSERT INTO `department`(`DEPT_ID`, `DEPT_NAME`, `MGR_ID`, `LOC_ID`) VALUES (2,'IT',2,2);
INSERT INTO `department`(`DEPT_ID`, `DEPT_NAME`, `MGR_ID`, `LOC_ID`) VALUES (3,'Sales',3,3);
INSERT INTO `department`(`DEPT_ID`, `DEPT_NAME`, `MGR_ID`, `LOC_ID`) VALUES (4,'Finance',4,4);
INSERT INTO `department`(`DEPT_ID`, `DEPT_NAME`, `MGR_ID`, `LOC_ID`) VALUES (5,'HR',5,5);
INSERT INTO `department`(`DEPT_ID`, `DEPT_NAME`, `MGR_ID`, `LOC_ID`) VALUES (6,'Production',6,6);

SELECT `DEPT_NAME` FROM `department` WHERE `DEPT_ID` < 1;
SELECT `F_NAME`, `L_NAME`FROM `employee` WHERE `SALARY`  BETWEEN 40000 AND 100000;



SELECT * FROM employee WHERE length(`F_NAME`) = 6 and `F_NAME` like ('n%');
SELECT `DEPT_NAME` FROM `department` WHERE `DEPT_NAME`  !=  'IT' AND `DEPT_NAME` != 'HR';
ALTER TABLE  employee  ADD Email_id VARCHAR(20);
RENAME TABLE employee TO employees;
RENAME TABLE department TO departments;
SELECT MAX(`SALARY`) FROM `employees`