CCA159 : Task Scenario-19

Problem Scenario 19:  Create a table emp as below. 

 empno    int,  

  ename    string,  

  job      string,  

  mgr      int,  

  hiredate timestamp,  

  sal      double,  

  coMM     double,  

  deptno   int

Once table is created as insert statment as below.

7698, 'BLAKE', 'MANAGER', 7839,      '01-05-1981',   2850, null, 30 

7782, 'CLARK', 'MANAGER', 7839,   '09-06-1981',   2450, null, 10 

7566, 'JONES', 'MANAGER', 7839,   '02-04-1981',   2975, null, 20 

7788, 'SCOTT', 'ANALYST', 7566,   '13-07-1987 ,   3000, null, 20 

7902, 'FORD', 'ANALYST', 7566,    '03-12-1981',   3000, null, 20 

7369, 'SMITH', 'CLERK', 7902,      '17-12-1980',   800, null, 20 

7499, 'ALLEN', 'SALESMAN', 7698,  '20-02-1981',   1600, 300, 30 

7521, 'WARD', 'SALESMAN', 7698,   '22-02-1981',   1250, 500, 30 

7654, 'MARTIN', 'SALESMAN', 7698, '28-09-1981',   1250, 1400, 30 

7844, 'TURNER', 'SALESMAN', 7698, '08-09-1981',   1500, 0, 30 

7876, 'ADAMS', 'CLERK', 7788,     '13-07-1987',   1100, null, 20 

7900, 'JAMES', 'CLERK', 7698,     '03-12-1981',   950, null, 30 

7934, 'MILLER', 'CLERK', 7782,    '23-01-1982',   1300, null, 10 

Create a dept table as below:

deptno     int,  

dname      string,  

loc        string  

Insert below data in the table.

10, 'ACCOUNTING', 'NEW YORK'

20, 'RESEARCH', 'DALLAS'

30, 'SALES', 'CHICAGO'

40, 'OPERATIONS', 'BOSTON'

Now accomplish following activities.

1. Please denormalize data between DEPT and EMP tables based on the for the dame DEPTNO.

2. Count the number of employees in each department, and pring depart name and its count.