Oct/Nov - 2016 ==Q5

PAPER
 nov - 2016

 CREATE TABLE emp1
 (
        empno NUMBER primary key,
        ename VARCHAR(15) NOT NULL,
        salary NUMBER CHECK(salary>0),
        deptno NUMBER,
        constraints fk_emp_dpt foreign key (deptno) references dept(deptno)
 );

 CREATE TABLE dept
 (
        deptno NUMBER primary key,
        dept_name VARCHAR(15) NOT NULL
 );

 INSERT INTO dept VALUES(101,'production');
 INSERT INTO dept VALUES(102,'Hr');
 INSERT INTO dept VALUES(103,'marketing');
 INSERT INTO dept VALUES(104,'sales');

 INSERT INTO emp1 VALUES(1,'fahad',260000,102);
 INSERT INTO emp1 VALUES(5,'aadesh',200000,102);
 INSERT INTO emp1 VALUES(2,'dhruv',125000,101);
 INSERT INTO emp1 VALUES(6,'Harsh',130000,101);
 INSERT INTO emp1 VALUES(3,'google',160000,104);
 INSERT INTO emp1 VALUES(7,'yahoo',60000,104);
 INSERT INTO emp1 VALUES(4,'Carryminati',250000,103);
 INSERT INTO emp1 VALUES(8,'Aashish',210000,103);
 INSERT INTO emp1 VALUES(9,'Bhuvan',200000,103);
  INSERT INTO emp1 VALUES(10,'Bhuvana',260000,104);
   INSERT INTO emp1 VALUES(11,'abc',28000,103);
   INSERT INTO emp1 VALUES(12,'cde',26000,103);
   INSERT INTO emp1 VALUES(13,'cer',25000,102);
   INSERT INTO emp1 VALUES(14,'ceo',270000,102);

 SELECT * FROM emp1 ORDER BY empno;
 SELECT * FROM dept;

 1>

 DECLARE
 no1 NUMBER :=1;
 name VARCHAR(15);
 BEGIN


        SELECT dept_name,ename INTO no1,name FROM emp1
        join emp1 ON dept
 WHERE empno = (SELECT empno FROM emp1 WHERE salary=MAX(salary) GROUP BY empno) ;
        DBMS_OUTPUT.put_line(no1||' => '||name);

 END;

 SELECT dept_name,ename FROM dept join emp1 ON emp1.deptno=dept.deptno
WHERE salary = MAX(salary) AND empno= empno;

SELECT ename FROM emp1 WHERE MAX(salary);





SELECT MAX(salary)AS ,dept_name
FROM dept "d"
join emp1 "e"
ON "e".deptno="d".deptno
GROUP BY "e".deptno,dept_name;


SELECT ename,deptno
FROM emp1
WHERE salary IN (SELECT MAX(salary) FROM emp1 GROUP BY deptno);

SELECT dept_name,ename AS "higest salary"
FROM emp1
join dept
ON dept.deptno=emp1.deptno
WHERE empno IN (SELECT empno FROM emp1 WHERE salary IN( SELECT MAX(salary) FROM emp1  GROUP BY deptno));