EXP5_CSE2004Lab_20BRS1064

--EXP5_CSE104Lab_20BRS1064
CREATE TABLE department_20BRS1064(
dept_no NUMBER,
dept_name varchar2(100),
location varchar2(100)
);

INSERT INTO department_20BRS1064 VALUES(1001,'ADMIN','ALPHA BLOCK');
INSERT INTO department_20BRS1064 VALUES(1002,'SALES','Beta Block');
INSERT INTO department_20BRS1064 VALUES(1003,'PRODUCTION','Gamma Block');
INSERT INTO department_20BRS1064 VALUES(1004,'TESTING','Four Square Block');
INSERT INTO department_20BRS1064 VALUES(1005,'SERVICE','Main Blockcreate');

CREATE TABLE employee_20BRS1064(
emp_no NUMBER,
emp_name varchar2(15),
mobile NUMBER,
dept_no NUMBER,
salary NUMBER
);

INSERT INTO employee_20BRS1064 VALUES(101,'Arron','9911991199',1001,20000);
INSERT INTO employee_20BRS1064 VALUES(102,'David','9911991119',1001,19000);
INSERT INTO employee_20BRS1064 VALUES(103,'Jacob','2911991119',1002,19200);
INSERT INTO employee_20BRS1064 VALUES(104,'Jerome','2911691119',1002,15000);
INSERT INTO employee_20BRS1064 VALUES(105,'Simon','2911691114',1003,55000);
INSERT INTO employee_20BRS1064 VALUES(106,'John','2911694114',1003,61000);
INSERT INTO employee_20BRS1064 VALUES(107,'Atony','2911695114',1004,31500);
INSERT INTO employee_20BRS1064 VALUES(108,'Nancy','2911615114',1004,32500);
INSERT INTO employee_20BRS1064 VALUES(109,'Gonzalez','2911125114',1005,43050);
INSERT INTO employee_20BRS1064 VALUES(110,'Reena','2922325114',1005,43000);

--1
SELECT emp_name FROM employee_20BRS1064 WHERE dept_no IN (1001,1004,1005);
--2
SELECT emp_name FROM employee_20BRS1064 WHERE salary>(SELECT salary FROM employee_20BRS1064 WHERE emp_no=102);
--3
SELECT salary FROM employee_20BRS1064 WHERE salary>=(SELECT avg(salary) FROM employee_20BRS1064 WHERE dept_no=1005);
--4
SELECT emp_name FROM employee_20BRS1064 WHERE salary=(SELECT MAX(salary) FROM employee_20BRS1064);
--5
SELECT emp_name,salary FROM employee_20BRS1064 WHERE salary BETWEEN 20000 AND 40000;
--6
UPDATE employee_20BRS1064 SET salary=salary/4;
--7
DELETE FROM employee_20BRS1064 WHERE dept_no=1001;
--8
SELECT emp_name FROM employee_20BRS1064 WHERE salary=(SELECT MIN(salary) FROM employee_20BRS1064);
--9
SELECT * FROM employee_20BRS1064 WHERE dept_no=1003 AND salary>(SELECT salary FROM employee_20BRS1064 WHERE emp_no=110);
--10
SELECT * FROM employee_20BRS1064 WHERE salary>(SELECT avg(salary) FROM employee_20BRS1064 WHERE dept_no IN (1001,1004,1005));
--11
SELECT COUNT(*) FROM employee_20BRS1064 WHERE dept_no=1003;


DROP TABLE employee_20BRS1064;
DROP TABLE department_20BRS1064;