SQL Queries

Sample Database:
DROP TABLE IF EXISTS emp;

CREATE TABLE emp (
  empno decimal(4,0) NOT NULL,
  ename varchar(10) default NULL,
  job varchar(9) default NULL,
  mgr decimal(4,0) default NULL,
  hiredate date default NULL,
  sal decimal(7,2) default NULL,
  comm decimal(7,2) default NULL,
  deptno decimal(2,0) default NULL
);

DROP TABLE IF EXISTS dept;

CREATE TABLE dept (
  deptno decimal(2,0) default NULL,
  dname varchar(14) default NULL,
  loc varchar(13) default NULL
);

INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20');
INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30');
INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30');
INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20');
INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30');
INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30');
INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10');
INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10');
INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30');
INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20');
INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30');
INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10');

INSERT INTO dept VALUES ('10','ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES ('20','RESEARCH','DALLAS');
INSERT INTO dept VALUES ('30','SALES','CHICAGO');
INSERT INTO dept VALUES ('40','OPERATIONS','BOSTON');

1. Display departmentno,dept name and total no of employees working in each deptment

     Select e.deptno, d.dname ,count(*)  from emp e,dept d
     where e.deptno = d.deptno  group by deptno;

    2. Query to fetch count of employees under a manager having manager’s salary greater than 5000


      select e.empno, e.sal,count(*) noofemployee from emp e  group by mgr  having e.sal >=2000               order by noofemployee;

   3. Difference between group by and having

• condition in WHERE clause is used to filter rows before you aggregate them and then HAVING clause comes in picture for final filtering,having is used to filter group
• you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query.
• One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause.
  

  4.  display the name of employees who are not managers.
     select ename,job from emp e where empno  
     not in (select mgr from emp where   e.empno= mgr)
 
  5. display the name of employees who are  managers.
    select ename,job from emp e where empno 
    in (select mgr from emp where e.empno= mgr)
  
  6. display name of employees who are managers 
    select * from emp where empno in (select mgr from emp)
  
  7. display name of employees who are not managers (ORACLE)
    select * from emp minus minus
   (select * from emp where empno in (select mgr from    emp))

 8. Show dept no and total no employees in ech dept
  select deptno, count(ename) from emp group by deptno

9. Show job title and various employee under each job title
select job,count(*) from emp group by job

10.Display dept no with more than 3 employees in each dept
 select deptno,count(*) from emp group by deptno having count(*) > 3 

11.Display the name of emp who earns highest sal
  select ename from emp where sal =(select max(sal) from emp)

12.Display the emp no and name,sal of the employee working as clerk and earning highest sal among clerks

select  empno,ename,sal from emp where job ='CLERK' and sal=(select max(sal) from emp where job='CLERK')