Window Analytic Function in SQL | Analytical Function in Oracle | Analytical Function practice

In this video I have listed 7 queries that can be solved using analytical and window function in SQL.
Please try solving these on your own and paste your answers in the comments section
I will post the explanation with solutions in the next video
#AnalyticalFuntion #TechCoach #WindowFunction

[TechCoach]

-- PLEASE FIND THE DDL AND DML USED IN THESE QUESTIONS HERE
DROP TABLE PRODUCT_WD;
DROP TABLE CMLTV_SALES_WD;
DROP TABLE CMLTV_PDCT_SALES_WD;
DROP TABLE CITY_CASES_WD;
DROP TABLE EMP_SALARY_WD;
DROP TABLE ACTUAL_ESTIMATED_WD;
DROP TABLE CLIENT_SALES_WD;

CREATE TABLE PRODUCT_WD
(
PID INT,
PNAME VARCHAR2(10),
SALES INT
);


INSERT INTO PRODUCT_WD VALUES (1, 'P1', 10);
INSERT INTO PRODUCT_WD VALUES (2, 'P2', 20);
INSERT INTO PRODUCT_WD VALUES (3, 'P3', 30);
INSERT INTO PRODUCT_WD VALUES (4, 'P4', 40);
INSERT INTO PRODUCT_WD VALUES (5, 'P5', 50);
INSERT INTO PRODUCT_WD VALUES (6, 'P6', 60);
INSERT INTO PRODUCT_WD VALUES (7, 'P7', 70);


CREATE TABLE CMLTV_SALES_WD
(
ID INT,
SALES_DATE DATE,
SALES INT
);

INSERT INTO CMLTV_SALES_WD
SELECT ROWNUM,
SYSDATE -ROWNUM,
DBMS_RANDOM.VALUE(10, 90)
FROM DUAL
CONNECT BY LEVEL <=10;

CREATE TABLE CMLTV_PDCT_SALES_WD
(
ID INT,
PNAME VARCHAR2(10),
SALES_DATE DATE,
SALES INT
);


INSERT INTO CMLTV_PDCT_SALES_WD
SELECT ROWNUM,
CONCAT('P', ROUND(DBMS_RANDOM.VALUE(1, 3))),
SYSDATE - MOD(ROWNUM, 10)+1,
ROUND(DBMS_RANDOM.VALUE(10, 90))
FROM DUAL
CONNECT BY LEVEL <=30;


DELETE FROM CMLTV_PDCT_SALES_WD
WHERE ROWID NOT IN ( SELECT MAX(ROWID) FROM CMLTV_PDCT_SALES_WD GROUP BY SALES_DATE, pname);
COMMIT;

CREATE TABLE CITY_CASES_WD
(
ID INT,
CASES_DATE DATE,
CITY VARCHAR2(10),
CASES INT
);


INSERT INTO CITY_CASES_WD
SELECT ROWNUM,
SYSDATE - MOD(ROWNUM, 4)+1,
'NEW DELHI',
ROUND(DBMS_RANDOM.VALUE(10, 90))
FROM DUAL
CONNECT BY LEVEL <=12;





update CITY_CASES_WD SET city='London' where id in (4, 5, 6, 7);
update CITY_CASES_WD SET city='New York' where id in (8, 9, 10, 11);
commit;


CREATE TABLE EMP_SALARY_WD
(
EMP_ID INT,
EMP_NAME varchar2(20),
EXP_IN_MONTHS INT,
DEPT_ID int,
DESG_ID INt,
SALARY INT
);




insert into EMP_SALARY_WD
select
rownum,
concat('EMP ', rownum),
ROUND(DBMS_RANDOM.VALUE(10, 100)),
ROUND(DBMS_RANDOM.VALUE(1, 8)),
ROUND(DBMS_RANDOM.VALUE(1, 8)),
ROUND(DBMS_RANDOM.VALUE(10000, 80000))
from dual
connect by level <=50;





CREATE TABLE ACTUAL_ESTIMATED_WD
(
ID INT,
SALES_DATE DATE,
SALES INT,
NEXT_3_DAYS_EST_SALES INT

);

INSERT INTO ACTUAL_ESTIMATED_WD
SELECT ROWNUM,
SYSDATE -ROWNUM,
DBMS_RANDOM.VALUE(10, 90),
DBMS_RANDOM.VALUE(30, 270)
FROM DUAL
CONNECT BY LEVEL <=10;





CREATE TABLE CLIENT_SALES_WD
(
ID INT,
CLIENT VARCHAR2(10),
CASES_DATE DATE,
SALES INT
);


INSERT INTO CLIENT_SALES_WD
SELECT ROWNUM,
'ABC',
SYSDATE - MOD(ROWNUM, 4)+1,
ROUND(DBMS_RANDOM.VALUE(10, 90))
FROM DUAL
CONNECT BY LEVEL <=12;

update CLIENT_SALES_WD SET CLIENT='PQR' where id in (4, 5, 6, 7);
update CLIENT_SALES_WD SET CLIENT='XYZ' where id in (8, 9, 10, 11);
commit;

[florincopaci]

Nice topic- analytical functions.is something else în oracle but nobody make video about this-match recognize clause în oracle.thank you for this video