Answer to Question #244311 in Databases | SQL | Oracle | MS Access for Tarurendra Kushwah

Question #244311
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.
1
Expert's answer
2021-09-29T18:47:32-0400
//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`








Need a fast expert's response?

Submit order

and get a quick answer at the best price

for any assignment or question with DETAILED EXPLANATIONS!

Comments

No comments. Be the first!

Leave a comment

LATEST TUTORIALS
New on Blog
APPROVED BY CLIENTS