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
Auf dieser Seite können Sie das Online-Video ADVANCED SQL PART 1 mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer Master SQL with Harshit Bhadiyadra 11 Mai 2024 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 606 Mal angesehen und es wurde von 20 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
