ADVANCED SQL PART 1
---------------------------------------ADVANCED SQL MASTERCLASS AGENDA
--1.WINDOW FUNCTIONS WITH SUM
--2.RECURSIVE QUERIES / RECURSIVE CTE
--3.SUBQUERY AND CORERALTED SUBQUERY
--4.RANKING FUNCTIONS
--5.SELF JOIN
--6.SUBQUERY IN THE FROM CLAUSE / DERIVED TABLES / INLINE VIEW
--7.ADVANCED ANALYTICAL FUNCTIONS - LEAD, LAG, FIRST VALUE, LAST VALUE
CREATE TABLE SALES_DATA
(
PRODUCT_NAME VARCHAR(100),
CATEGORY VARCHAR(50),
SALES DECIMAL(10,2)
)
--INSERT INTO SALES_DATA VALUES('PRODUCT A','CATEGORY 1',1000.00)
--INSERT INTO SALES_DATA VALUES('PRODUCT B','CATEGORY 1',2000.00)
--INSERT INTO SALES_DATA VALUES('PRODUCT C','CATEGORY 2',1500.00)
--INSERT INTO SALES_DATA VALUES('PRODUCT D','CATEGORY 2',2500.00)
---CALCULATE TOTAL SALES FOR EACH CATEGORY USING SUM WINDOW FUNCTION WITH THE PARTITION BY CLAUSE
SELECT * FROM EMPLOYEES65
CREATE TABLE EMPLOYEES65
(
EMPLOYEE_ID INT PRIMARY KEY,
EMPLOYEE_NAME VARCHAR(100),
MANAGER_ID INT,
FOREIGN KEY(MANAGER_ID) REFERENCES EMPLOYEES65(EMPLOYEE_ID)
);
--INSERT INTO EMPLOYEES65 VALUES(1,'JANVI',NULL)
--INSERT INTO EMPLOYEES65 VALUES(2,'HARSHIT',1)
--INSERT INTO EMPLOYEES65 VALUES(3,'HEMA',1)
--INSERT INTO EMPLOYEES65 VALUES(4,'POOJA',2)
--INSERT INTO EMPLOYEES65 VALUES(5,'NEHA',3)
CREATE TABLE CATEGORIES
(
CATEGORY_ID INT PRIMARY KEY,
CATEGORY_NAME VARCHAR(50)
)
CREATE TABLE PRODUCTS65
(
PRODUCT_ID INT PRIMARY KEY,
PRODUCT_NAME VARCHAR(100),
CATEGORY_ID INT,
FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORIES (CATEGORY_ID)
)
CREATE TABLE SALES_DATA65
(
SALE_ID INT PRIMARY KEY,
PRODUCT_ID INT,
SALES DECIMAL(10,2),
FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS65 (PRODUCT_ID)
)
--INSERT INTO CATEGORIES VALUES(1,'CATEGORY 1')
--INSERT INTO CATEGORIES VALUES(2,'CATEGORY 2')
--INSERT INTO PRODUCTS65 VALUES(1,'PRODUCT A',1)
--INSERT INTO PRODUCTS65 VALUES(2,'PRODUCT B',1)
--INSERT INTO PRODUCTS65 VALUES(3,'PRODUCT C',2)
--INSERT INTO PRODUCTS65 VALUES(4,'PRODUCT D',2)
--INSERT INTO SALES_DATA65 VALUES(1,1,1000.00)
--INSERT INTO SALES_DATA65 VALUES(2,1,500.00)
--INSERT INTO SALES_DATA65 VALUES(3,2,2000.00)
--INSERT INTO SALES_DATA65 VALUES(4,3,1500.00)
--INSERT INTO SALES_DATA65 VALUES(5,4,2500.00)
---------THIS QUERY COMBINES DATA FROM THE PRODUCTS AND CATEGORIES TABLE USING AN INNER JOIN
---------AND CALCULATES THE TOTAL SALES FOR EACH PRODUCT USING A CORERALTED SUBQUERY.
SELECT * FROM SALES_DATA65
SELECT * FROM PRODUCTS65
SELECT * FROM CATEGORIES