SQL Magic: Aggregating Marks & Delivery Dates with Advanced Queries!

Unlock the power of SQL with these advanced query techniques! In this video, we'll dive into two common interview scenarios: calculating the average percentage across multiple semesters for students and determining when all products were delivered to consumers. Whether you're preparing for an SQL interview or just looking to enhance your data analysis skills, this video is for you. Learn how to leverage the AVG, MAX, and MIN functions effectively, and make your SQL queries more powerful than ever!"

CREATE TABLE Max_Value_From_Table_1 (
student_id VARCHAR(50),
student_name VARCHAR(50),
pc_semester_1 DECIMAL(5,2),
pc_semester_2 DECIMAL(5,2),
pc_semester_3 DECIMAL(5,2),
pc_semester_4 DECIMAL(5,2),
pc_semester_5 DECIMAL(5,2),
pc_semester_6 DECIMAL(5,2)
);

-- Insert first record (already provided)
INSERT INTO Max_Value_From_Table_1
(student_id, student_name, pc_semester_1, pc_semester_2, pc_semester_3, pc_semester_4, pc_semester_5, pc_semester_6)
VALUES
('Analytics_hub_001', 'Vijay', 67.5, 62.3, 66.4, 68.7, 72.4, 74.6);

-- Insert second record
INSERT INTO Max_Value_From_Table_1
(student_id, student_name, pc_semester_1, pc_semester_2, pc_semester_3, pc_semester_4, pc_semester_5, pc_semester_6)
VALUES
('Analytics_hub_002', 'Ravi', 75.2, 78.5, 80.3, 82.1, 79.9, 81.0);

-- Insert third record
INSERT INTO Max_Value_From_Table_1
(student_id, student_name, pc_semester_1, pc_semester_2, pc_semester_3, pc_semester_4, pc_semester_5, pc_semester_6)
VALUES
('Analytics_hub_003', 'Priya', 65.4, 67.1, 69.2, 70.8, 72.5, 73.9);

-- Insert fourth record
INSERT INTO Max_Value_From_Table_1
(student_id, student_name, pc_semester_1, pc_semester_2, pc_semester_3, pc_semester_4, pc_semester_5, pc_semester_6)
VALUES
('Analytics_hub_004', 'Anil', 60.3, 62.8, 64.9, 67.3, 68.7, 70.1);

-- Insert fifth record
INSERT INTO Max_Value_From_Table_1
(student_id, student_name, pc_semester_1, pc_semester_2, pc_semester_3, pc_semester_4, pc_semester_5, pc_semester_6)
VALUES
('Analytics_hub_005', 'Meena', 72.1, 74.3, 76.5, 78.2, 79.8, 80.4);

-- Scenario 1
you have a tbale with semester wise percent of marks.
write a sql query which provides aggregated percentage as an output

Select * from Max_Value_From_Table_1

select student_name,
(select cast(round(avg(PC),2) as decimal(5,2)) from (VALUES(pc_semester_1),(pc_semester_2),(pc_semester_3),(pc_semester_4),(pc_semester_5),
(pc_semester_6)) as value(PC)) as Aggregated_Result
from Max_Value_From_Table_1

CREATE TABLE delivery_items (
item_id VARCHAR(50),
consumer_name VARCHAR(100),
delivery_iphone DATETIME,
delivery_laptop DATETIME,
delivery_smartwatch DATETIME
);
-- Insert first record (based on the image)
INSERT INTO delivery_items
(item_id, consumer_name, delivery_iphone, delivery_laptop, delivery_smartwatch)
VALUES
('Analytics_hub_001', 'Vijay', '2024-07-01 00:00:00', '2024-06-01 00:00:00', '2024-08-01 00:00:00');

-- Insert additional sample records
INSERT INTO delivery_items
(item_id, consumer_name, delivery_iphone, delivery_laptop, delivery_smartwatch)
VALUES
('Analytics_hub_002', 'Ravi', '2024-07-15 00:00:00', '2024-06-10 00:00:00', '2024-08-05 00:00:00');

INSERT INTO delivery_items
(item_id, consumer_name, delivery_iphone, delivery_laptop, delivery_smartwatch)
VALUES
('Analytics_hub_003', 'Priya', '2024-07-20 00:00:00', '2024-06-12 00:00:00', '2024-08-10 00:00:00');

INSERT INTO delivery_items
(item_id, consumer_name, delivery_iphone, delivery_laptop, delivery_smartwatch)
VALUES
('Analytics_hub_004', 'Anil', '2024-07-25 00:00:00', '2024-06-15 00:00:00', '2024-08-15 00:00:00');

INSERT INTO delivery_items
(item_id, consumer_name, delivery_iphone, delivery_laptop, delivery_smartwatch)
VALUES
('Analytics_hub_005', 'Meena', '2024-07-30 00:00:00', '2024-06-20 00:00:00', '2024-08-20 00:00:00');

-------------------------------------------------------------------------------
-- Scenario 2
you have a tbale with date of delivery of multiple products .
write a sql query which tells when each consumer received all the products

---When All the items got delivered to each consumer?
-- When First Item was delivered to each consumer?

Select * from delivery_items

select consumer_name,
(select max(delivery_date) from ( values(delivery_iphone),(delivery_laptop),(delivery_smartwatch))
as value(delivery_date)) as delivery_date
from delivery_items

Top 20% vs Bottom 20% of Students in SQL: NTILE vs TOP PERCENT

SQL Tricks: Find the 3rd Lowest Salary in Each Department with Two Powerful Methods!

Subscribe and hit the bell icon for more SQL tutorials and tips!