Answer Happy

create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
)
create table emp( empno number(4,0), ename varchar2(10), job
varchar2(9), mgr number(4,0), hiredate date, sal number(7,2), comm
number(7,2), deptno number(2,0), constraint pk_emp primary key
(empno), constraint fk_deptno foreign key (deptno) references dept
(deptno) )
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');
insert into emp values(7839, 'KING', 'PRESIDENT', null,
to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10 );
insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,
to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30 );
insert into emp values(7782, 'CLARK', 'MANAGER', 7839,
to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values(7566, 'JONES', 'MANAGER', 7839,
to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20 );
insert into emp values(7788, 'SCOTT', 'ANALYST', 7566,
to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
insert into emp values(7902, 'FORD', 'ANALYST', 7566,
to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20 );
insert into emp values(7369, 'SMITH', 'CLERK', 7902,
to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20);
insert into emp values(7499, 'ALLEN', 'SALESMAN', 7698,
to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30);
insert into emp values(7521, 'WARD', 'SALESMAN', 7698,
to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30);
insert into emp values(7654, 'MARTIN', 'SALESMAN', 7698,
to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30);
insert into emp values(7844, 'TURNER', 'SALESMAN', 7698,
to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30);
insert into emp values(7876, 'ADAMS', 'CLERK', 7788,
to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20);
insert into emp values(7900, 'JAMES', 'CLERK', 7698,
to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30);
insert into emp values(7934, 'MILLER', 'CLERK', 7782,
to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10);
select ename, dname, job, empno, hiredate, loc from emp, dept
where emp.deptno = dept.deptno order by ename
select dname, count(*) count_of_employees from dept, emp where
dept.deptno = emp.deptno group by DNAME order by 2 desc
Task: From this Sql code write the codes
to:
1. Display total salary spent for each job category.
2. Display lowest paid employee details under each
manager.
3. Display number of employees working in each department
and their department name.
4. Display the details of employees sorting the salary in
increasing order.
5. Show the record of employee earning salary greater than
1600 in each department.
6. Write queries to implement and practice the clause
(Having clause, order by clause, group by clause)