Notes

13) (a)
CREATE TABLE EMP (
EMP_ID INT PRIMARY KEY,
EMP_NAME VARCHAR(50),
EMP_DEPARTMENT VARCHAR(50),
EMP_SALARY DECIMAL(10, 2)
);

INSERT INTO EMP (EMP_ID, EMP_NAME, EMP_DEPARTMENT, EMP_SALARY)
VALUES
(1, 'John Doe', 'IT', 60000.00),
(2, 'Jane Smith', 'HR', 50000.00),
(3, 'Michael Johnson', 'Finance', 75000.00),
(4, 'Emily Brown', 'Marketing', 55000.00),
(5, 'William Davis', 'IT', 62000.00);


(b)
SELECT * FROM EMP;

14) (a)
CREATE TABLE DEPT (
DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(50),
LOC VARCHAR2(50)
);

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (40, 'OPERATIONS', 'BOSTON');



(b)
SELECT * FROM DEPT;


15)
SELECT distinct emp_department FROM emp;

16)
SELECT emp_name, emp_salary FROM emp
where emp_salary=(select MAX(emp_salary) from emp);

17)
Question-- write a query to show left inner join in the above emp and dept tables.
alter table dept
add emp_id int;

update dept
set emp_id=1
where deptno=10;

update dept
set emp_id=3
where deptno=20;

update dept
set emp_id=2
where deptno=30;

update dept
set emp_id=5
where deptno=40;

select * from dept;

select dept.dname, emp.emp_name from dept
left join emp on emp.emp_id=dept.emp_id;