//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`
Comments
Leave a comment