Analytical Functions | Window Functions | Types of Analytical Functions | Part 1 |

Опубликовано: 03 Ноябрь 2024
на канале: GoLearningPoint

173

In this session we have discussed about Analytical functions and how powerful they are in resolving the SQL complex problems.
We discussed about how they are categorized and how they works on the input values.

Complete Playlist - • SQL: Introduction to Database Managem...

###DDL & DML
CREATE TABLE "EMPLOYEE"
("EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
"ADDRESS" VARCHAR2(200 BYTE)
);

Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (7369,'SMITH','ENGINEER',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20,'Salt City Pune 201305');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),6000,null,20,'Salt City Pune 201305');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10,'Gulshan Bellina Noida 201306');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20,'Salt City Pune 201305');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10,'Gulshan Bellina Noida 201306');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20,'Salt City Pune 201305');

Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20,'Salt City Pune 201305');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10,'Gulshan Bellina Noida 201306');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (8101,'RAVI','ENGINEER',8102,to_date('17-JAN-80','DD-MON-RR'),23000,1200,30,'Dehradun Uttarakhand 201309');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (8102,'SURYA','MANAGER',7839,to_date('12-APR-86','DD-MON-RR'),60000,1000,40,'Salt City Pune 201305');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (8103,'RAJAN','MANAGER',8104,to_date('08-JUN-89','DD-MON-RR'),24500,3000,30,'Gulshan Bellina Noida 201306');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (8104,'PRITESH','ANALYST',7566,to_date('19-APR-90','DD-MON-RR'),30000,700,20,'Salt City Pune 201305');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (8105,'ANKIT','PRESIDENT',null,to_date('18-NOV-91','DD-MON-RR'),50000,333,30,'Dehradun Uttarakhand 201309');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (8106,'AKIF','CLERK',7788,to_date('23-MAY-96','DD-MON-RR'),11000,5555,20,'Salt City Pune 201305');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (8107,'SUBHO','ANALYST',8106,to_date('03-DEC-91','DD-MON-RR'),30000,null,40,'Dehradun Uttarakhand 201309');
Insert into EMPLOYEE (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,ADDRESS) values (8108,'SUSHIL','CLERK',7782,to_date('23-JAN-92','DD-MON-RR'),13000,null,40,'Dehradun Uttarakhand 201309');

#sql #database #dataengineering #golearningpoint #oracle