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
On this page of the site you can watch the video online ADVANCED SQL PART 1 with a duration of hours minute second in good quality, which was uploaded by the user Master SQL with Harshit Bhadiyadra 11 May 2024, share the link with friends and acquaintances, this video has already been watched 606 times on youtube and it was liked by 20 viewers. Enjoy your viewing!
