assignment 6.sql

CREATE TABLE DEPARTMENT_3 (dept_no INT PRIMARY KEY, dept_name VARCHAR(50), location VARCHAR(50));

CREATE TABLE EMPLOYEE_3 (emp_no INT PRIMARY KEY, emp_name VARCHAR(50), DOB DATE, address VARCHAR(100), doj DATE, mobile_no VARCHAR(15), dept_no INT, salary DECIMAL(10, 2), FOREIGN KEY (dept_no) REFERENCES DEPARTMENT_3(dept_no));

INSERT INTO DEPARTMENT_3 (dept_no, dept_name, location) VALUES
(1001, 'Admin', 'New York'),
(1002, 'Research', 'San Francisco'),
(1003, 'HR', 'Chicago'),
(1004, 'Finance', 'Boston'),
(1005, 'IT', 'Seattle');

INSERT INTO EMPLOYEE_3 (emp_no, emp_name, DOB, address, doj, mobile_no, dept_no, salary) VALUES
(1231, 'John', '1985-05-14', '123 Elm St', '2010-06-20', '1234567890', 1001, 35000),
(1232, 'Reena', '1990-07-22', '456 Oak St', '2015-09-15', '1234567891', 1001, 25000),
(1233, 'Amit', '1982-03-11', '789 Pine St', '2005-11-30', '1234567892', 1002, 40000),
(1234, 'Sita', '1988-01-20', '321 Birch St', '2013-03-25', '1234567893', 1003, 30000),
(1235, 'Raj', '1987-04-18', '654 Maple St', '2012-05-15', '1234567894', 1005, 50000),
(1236, 'Anita', '1992-11-05', '987 Cedar St', '2017-07-10', '1234567895', 1005, 45000),
(1237, 'Vikram', '1991-08-22', '135 Spruce St', '2014-08-20', '1234567896', 1002, 30000),
(1238, 'Neha', '1989-02-14', '246 Redwood St', '2011-12-05', '1234567897', 1004, 55000),
(1239, 'Arjun', '1985-06-30', '369 Cypress St', '2010-04-18', '1234567898', 1005, 60000),
(1240, 'Kiran', '1995-09-09', '741 Aspen St', '2018-09-30', '1234567899', 1003, 18000);

SELECT EMP_NAME FROM EMPLOYEE_3 WHERE DEPT_NO=1001;

SELECT EMP_NAME FROM EMPLOYEE_3 WHERE SALARY>(SELECT SALARY FROM EMPLOYEE_3 WHERE EMP_NO=1234);

SELECT EMP_NAME FROM EMPLOYEE_3 WHERE SALARY>(SELECT AVG(SALARY) FROM EMPLOYEE_3 WHERE DEPT_NO=1005);

SELECT EMP_NAME FROM EMPLOYEE_3 WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEE_3);

SELECT EMP_NAME, SALARY FROM EMPLOYEE_3 WHERE SALARY BETWEEN 20000 AND 40000;

UPDATE EMPLOYEE_3 SET SALARY=SALARY*1.25 WHERE DEPT_NO=1002;

DELETE FROM EMPLOYEE_3 WHERE DEPT_NO=(SELECT DEPT_NO FROM DEPARTMENT_3 WHERE DEPT_NAME='ADMIN');

SELECT d.DEPT_NAME FROM DEPARTMENT_3 d JOIN EMPLOYEE_3 e ON d.DEPT_NO=e.DEPT_NO WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEE_3);

SELECT * FROM EMPLOYEE_3 WHERE SALARY>(SELECT SALARY FROM EMPLOYEE_3 WHERE EMP_NAME='REENA') AND DEPT_NO=(SELECT DEPT_NO FROM EMPLOYEE_3 WHERE EMP_NAME='JOHN');

SELECT EMP_NAME FROM EMPLOYEE_3 WHERE SALARY<(SELECT AVG(SALARY) FROM EMPLOYEE_3 WHERE DEPT_NO=1001);

SELECT COUNT(*) FROM EMPLOYEE_3 WHERE DEPT_NO = (SELECT DEPT_NO FROM EMPLOYEE_3 WHERE EMP_NAME='JOHN');