CREATE TABLE INVENTORY (
    CarId VARCHAR(10) PRIMARY KEY,
    CarName VARCHAR(20),
    Price DECIMAL(10,2),
    Model VARCHAR(10),
    Year INT,
    FuelType VARCHAR(10)
);

Q2 Write the SQL command to create the CUSTOMER table.

CREATE TABLE CUSTOMER (
    CustId VARCHAR(10) PRIMARY KEY,
    CustName VARCHAR(30),
    CustAdd VARCHAR(50),
    Phone VARCHAR(15),
    Email VARCHAR(30)
);

Q3 Write the SQL command to create the SALE table with foreign keys.

CREATE TABLE SALE (
    Invoiceno VARCHAR(10) PRIMARY KEY,
    CarId VARCHAR(10),
    CustId VARCHAR(10),
    SaleDate DATE,
    PaymentMode VARCHAR(15),
    EmpID VARCHAR(10),
    SalePrice DECIMAL(10,2),
    FOREIGN KEY (CarId) REFERENCES INVENTORY(CarId),
    FOREIGN KEY (CustId) REFERENCES CUSTOMER(CustId),
    FOREIGN KEY (EmpID) REFERENCES EMPLOYEE(EmpID)
);

Q4 Write the SQL command to create the EMPLOYEE table.

CREATE TABLE EMPLOYEE (
    EmpID VARCHAR(10) PRIMARY KEY,
    EmpName VARCHAR(30),
    DOB DATE,
    DOJ DATE,
    Designation VARCHAR(20),
    Salary DECIMAL(8,2)
);

Q5 Write the command to add a column 'Commission' to the SALE table.

ALTER TABLE SALE ADD Commission DECIMAL(7,2);

Q6 Write the command to add a column 'Discount' to the INVENTORY table.

ALTER TABLE INVENTORY ADD Discount DECIMAL(5,2);

Q7 Write the command to modify the data type of Salary column to DECIMAL(10,2).

ALTER TABLE EMPLOYEE MODIFY Salary DECIMAL(10,2);

Q8 Write the command to rename the column 'CustAdd' to 'CustomerAddress'.

ALTER TABLE CUSTOMER CHANGE CustAdd CustomerAddress VARCHAR(50);

Q9 Write the command to drop the column 'Discount' from INVENTORY table.

ALTER TABLE INVENTORY DROP Discount;

Q10 Write the command to add a PRIMARY KEY constraint on CarId.

ALTER TABLE INVENTORY ADD PRIMARY KEY (CarId);

Q11 Write the command to add a FOREIGN KEY constraint on EmpID.

ALTER TABLE SALE ADD FOREIGN KEY (EmpID) REFERENCES EMPLOYEE(EmpID);

Q12 Write the command to add NOT NULL constraint on CustName.

ALTER TABLE CUSTOMER MODIFY CustName VARCHAR(30) NOT NULL;

Q13 Write the command to add UNIQUE constraint on Phone.

ALTER TABLE CUSTOMER ADD UNIQUE (Phone);

Q14 Write the command to set default value 'Cash' for PaymentMode.

ALTER TABLE SALE MODIFY PaymentMode VARCHAR(15) DEFAULT 'Cash';

Q15 Write the command to drop the PRIMARY KEY from CUSTOMER table.

ALTER TABLE CUSTOMER DROP PRIMARY KEY;

SECTION 2: DATA MANIPULATION LANGUAGE (INSERT) Q16-Q25

Q16 Insert a new car into INVENTORY table.

INSERT INTO INVENTORY VALUES ('S003', 'SWIFT', 650000, 'ZXI', 2019, 'Petrol');

Q17 Insert a new customer into CUSTOMER table.

INSERT INTO CUSTOMER VALUES ('C0005', 'Rahul Sharma', '15, Connaught Place, Delhi', '9876543210', 'rahul@gmail.com');

Q18 Insert a new sale record.

INSERT INTO SALE VALUES ('I00007', 'S003', 'C0005', '2020-01-15', 'Cash', 'E001', 620000);

Q19 Insert a new employee.

INSERT INTO EMPLOYEE VALUES ('E011', 'Priya Singh', '1992-05-10', '2020-01-01', 'Salesman', 30000);

Q20 Insert multiple cars into INVENTORY table at once.

INSERT INTO INVENTORY VALUES 
('S004', 'WAGONR', 500000, 'LXI', 2020, 'Petrol'),
('S005', 'WAGONR', 550000, 'VXI', 2020, 'Petrol');

Q21 Insert a new customer with only CustId, CustName and Phone.

INSERT INTO CUSTOMER (CustId, CustName, Phone) VALUES ('C0006', 'Neha Gupta', '9988776655');

Q22 Insert a new sale without specifying InvoiceNo.

INSERT INTO SALE (CarId, CustId, SaleDate, PaymentMode, EmpID, SalePrice) 
VALUES ('S001', 'C0001', '2020-01-20', 'Online', 'E004', 590321);

Q23 Insert a new employee with NULL value for Commission.

INSERT INTO EMPLOYEE (EmpID, EmpName, DOB, DOJ, Designation, Salary) 
VALUES ('E012', 'Arun Kumar', '1990-08-15', '2019-12-01', 'Salesman', 28000);

Q24 Insert a record in SALE table with SalePrice as 0.

INSERT INTO SALE VALUES ('I00008', 'D002', 'C0002', '2020-01-25', 'Cheque', 'E007', 0);

Q25 Insert a car with Price NULL.

INSERT INTO INVENTORY (CarId, CarName, Model, Year, FuelType) 
VALUES ('S006', 'ALTO', 'LXI', 2018, 'Petrol');

SECTION 3: SELECT STATEMENTS (BASIC) Q26-Q40

Q26 Display all records from the INVENTORY table.

SELECT * FROM INVENTORY;

Q27 Display only CarId, CarName and Price from INVENTORY.

SELECT CarId, CarName, Price FROM INVENTORY;

Q28 Display Customer Name and Phone from CUSTOMER table.

SELECT CustName, Phone FROM CUSTOMER;

Q29 Display Employee Name, Designation and Salary from EMPLOYEE.

SELECT EmpName, Designation, Salary FROM EMPLOYEE;

Q30 Display InvoiceNo, SaleDate and SalePrice from SALE.

SELECT Invoiceno, SaleDate, SalePrice FROM SALE;

Q31 Display all records from SALE table.

SELECT * FROM SALE;

Q32 Display distinct CarId from SALE table.

SELECT DISTINCT CarId FROM SALE;

Q33 Display distinct PaymentMode from SALE table.

SELECT DISTINCT PaymentMode FROM SALE;

Q34 Display distinct Model from INVENTORY.

SELECT DISTINCT Model FROM INVENTORY;

Q35 Display distinct FuelType from INVENTORY.

SELECT DISTINCT FuelType FROM INVENTORY;

Q36 Display CarName and Price with alias Car_Price.

SELECT CarName, Price AS Car_Price FROM INVENTORY;

Q37 Display EmpName as Employee_Name from EMPLOYEE.

SELECT EmpName AS Employee_Name FROM EMPLOYEE;

Q38 Display SalePrice as Selling_Price from SALE.

SELECT SalePrice AS Selling_Price FROM SALE;

Q39 Display all cars with price greater than 600000.

SELECT * FROM INVENTORY WHERE Price > 600000;

Q40 Display all employees with salary greater than 30000.

SELECT * FROM EMPLOYEE WHERE Salary > 30000;

SECTION 4: SELECT WITH WHERE CLAUSE Q41-Q55

Q41 Display cars with fuel type 'Petrol'.

SELECT * FROM INVENTORY WHERE FuelType = 'Petrol';

Q42 Display cars with model 'VXI'.

SELECT * FROM INVENTORY WHERE Model = 'VXI';

Q43 Display employees with designation 'Salesman'.

SELECT * FROM EMPLOYEE WHERE Designation = 'Salesman';

Q44 Display sales made through 'Credit Card'.

SELECT * FROM SALE WHERE PaymentMode = 'Credit Card';

Q45 Display cars with price between 500000 and 700000.

SELECT * FROM INVENTORY WHERE Price BETWEEN 500000 AND 700000;

Q46 Display employees with salary between 25000 and 35000.

SELECT * FROM EMPLOYEE WHERE Salary BETWEEN 25000 AND 35000;

Q47 Display cars made in 2018 and having fuel type 'Petrol'.

SELECT * FROM INVENTORY WHERE Year = 2018 AND FuelType = 'Petrol';

Q48 Display sales made by employee 'E007'.

SELECT * FROM SALE WHERE EmpID = 'E007';

Q49 Display customers whose name starts with 'A'.

SELECT * FROM CUSTOMER WHERE CustName LIKE 'A%';

Q50 Display employees whose name ends with 'k'.

SELECT * FROM EMPLOYEE WHERE EmpName LIKE '%k';

Q51 Display cars whose name contains 'SWIFT'.

SELECT * FROM INVENTORY WHERE CarName LIKE '%SWIFT%';

Q52 Display customers with 'yahoo' in their email.

SELECT * FROM CUSTOMER WHERE Email LIKE '%yahoo%';

Q53 Display employees whose salary is NULL.

SELECT * FROM EMPLOYEE WHERE Salary IS NULL;

Q54 Display sales where SalePrice is not NULL.

SELECT * FROM SALE WHERE SalePrice IS NOT NULL;

Q55 Display cars with price greater than 500000 and year 2017.

SELECT * FROM INVENTORY WHERE Price > 500000 AND Year = 2017;

SECTION 5: ORDER BY, GROUP BY, HAVING Q56-Q70

Q56 Display all cars in ascending order of price.

SELECT * FROM INVENTORY ORDER BY Price ASC;

Q57 Display all cars in descending order of price.

SELECT * FROM INVENTORY ORDER BY Price DESC;

Q58 Display employees in descending order of salary.

SELECT * FROM EMPLOYEE ORDER BY Salary DESC;

Q59 Display customers in ascending order of name.

SELECT * FROM CUSTOMER ORDER BY CustName ASC;

Q60 Count the total number of cars in INVENTORY.

SELECT COUNT(*) FROM INVENTORY;

Q61 Count the total number of customers.

SELECT COUNT(*) FROM CUSTOMER;

Q62 Count the total number of sales.

SELECT COUNT(*) FROM SALE;

Q63 Find the maximum price of cars.

SELECT MAX(Price) FROM INVENTORY;

Q64 Find the minimum salary of employees.

SELECT MIN(Salary) FROM EMPLOYEE;

Q65 Find the average price of cars.

SELECT AVG(Price) FROM INVENTORY;

Q66 Find the sum of all sale prices.

SELECT SUM(SalePrice) FROM SALE;

Q67 Group cars by model and count each model.

SELECT Model, COUNT(*) FROM INVENTORY GROUP BY Model;

Q68 Group employees by designation and count.

SELECT Designation, COUNT(*) FROM EMPLOYEE GROUP BY Designation;

Q69 Group sales by payment mode and count.

SELECT PaymentMode, COUNT(*) FROM SALE GROUP BY PaymentMode;

Q70 Display models with more than 1 car.

SELECT Model, COUNT(*) FROM INVENTORY GROUP BY Model HAVING COUNT(*) > 1;

SECTION 6: SINGLE ROW FUNCTIONS Q71-Q85

Q71 Display car names in uppercase.

SELECT UPPER(CarName) FROM INVENTORY;

Q72 Display customer names in lowercase.

SELECT LOWER(CustName) FROM CUSTOMER;

Q73 Display the length of each customer name.

SELECT CustName, LENGTH(CustName) FROM CUSTOMER;

Q74 Display the first 3 characters of each car name.

SELECT CarName, LEFT(CarName, 3) FROM INVENTORY;

Q75 Display the last 3 characters of each employee name.

SELECT EmpName, RIGHT(EmpName, 3) FROM EMPLOYEE;

Q76 Display car price rounded to 1 decimal place.

SELECT CarName, ROUND(Price, 1) FROM INVENTORY;

Q77 Display car price rounded to the nearest integer.

SELECT CarName, ROUND(Price, 0) FROM INVENTORY;

Q78 Calculate 10% of car price as discount.

SELECT CarName, Price, Price * 0.10 AS Discount FROM INVENTORY;

Q79 Find the power of 2 raised to 3.

SELECT POWER(2, 3);

Q80 Display the day name of employee's date of birth.

SELECT EmpName, DAYNAME(DOB) FROM EMPLOYEE;

Q81 Display the month name of employee's joining date.

SELECT EmpName, MONTHNAME(DOJ) FROM EMPLOYEE;

Q82 Display the year of sale.

SELECT Invoiceno, YEAR(SaleDate) FROM SALE;

Q83 Display the current date and time.

SELECT NOW();

Q84 Extract month from sale date.

SELECT Invoiceno, MONTH(SaleDate) FROM SALE;

Q85 Display all details where employee name contains 'a'.

SELECT * FROM EMPLOYEE WHERE EmpName LIKE '%a%';

SECTION 7: JOIN OPERATIONS Q86-Q95

Q86 Display sales with car details using JOIN.

SELECT S.*, I.CarName, I.Model, I.FuelType 
FROM SALE S 
JOIN INVENTORY I ON S.CarId = I.CarId;

Q87 Display sales with customer details using JOIN.

SELECT S.*, C.CustName, C.Phone, C.Email 
FROM SALE S 
JOIN CUSTOMER C ON S.CustId = C.CustId;

Q88 Display sales with employee details using NATURAL JOIN.

SELECT * FROM SALE NATURAL JOIN EMPLOYEE;

Q89 Display all sales with customer name and employee name.

SELECT S.Invoiceno, C.CustName, E.EmpName, S.SalePrice 
FROM SALE S 
JOIN CUSTOMER C ON S.CustId = C.CustId 
JOIN EMPLOYEE E ON S.EmpID = E.EmpID;

Q90 Display car name and total sales amount for each car.

SELECT I.CarName, SUM(S.SalePrice) AS TotalSales 
FROM INVENTORY I 
JOIN SALE S ON I.CarId = S.CarId 
GROUP BY I.CarName;

Q91 Display customer name and total purchase amount.

SELECT C.CustName, SUM(S.SalePrice) AS TotalPurchase 
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
GROUP BY C.CustName;

Q92 Display employee name and total sales made by each.

SELECT E.EmpName, COUNT(S.Invoiceno) AS TotalSales 
FROM EMPLOYEE E 
JOIN SALE S ON E.EmpID = S.EmpID 
GROUP BY E.EmpName;

Q93 Display sales with car details where price > 600000.

SELECT S.*, I.CarName, I.Price 
FROM SALE S 
JOIN INVENTORY I ON S.CarId = I.CarId 
WHERE I.Price > 600000;

Q94 Display customers who purchased 'Baleno' cars.

SELECT DISTINCT C.CustName, C.Phone 
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
JOIN INVENTORY I ON S.CarId = I.CarId 
WHERE I.CarName = 'Baleno';

Q95 Display cars that were sold in 2019.

SELECT DISTINCT I.CarName, I.Price, I.Model 
FROM INVENTORY I 
JOIN SALE S ON I.CarId = S.CarId 
WHERE YEAR(S.SaleDate) = 2019;

SECTION 8: ADVANCED QUERIES Q96-Q100

Q96 Display customers who have purchased more than 1 car.

SELECT C.CustName, COUNT(S.Invoiceno) AS CarCount 
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
GROUP BY C.CustName 
HAVING COUNT(S.Invoiceno) > 1;

Q97 Display the employee who made the maximum sale amount.

SELECT E.EmpName, SUM(S.SalePrice) AS TotalSales 
FROM EMPLOYEE E 
JOIN SALE S ON E.EmpID = S.EmpID 
GROUP BY E.EmpName 
ORDER BY TotalSales DESC 
LIMIT 1;

Q98 Display the most expensive car that was sold.

SELECT I.CarName, I.Price 
FROM INVENTORY I 
JOIN SALE S ON I.CarId = S.CarId 
ORDER BY I.Price DESC 
LIMIT 1;

Q99 Display all cars with their sale count (including cars not sold).

SELECT I.CarName, COUNT(S.CarId) AS TimesSold 
FROM INVENTORY I 
LEFT JOIN SALE S ON I.CarId = S.CarId 
GROUP BY I.CarName;

Q100 Display payment mode with maximum sales amount.

SELECT PaymentMode, SUM(SalePrice) AS TotalAmount 
FROM SALE 
GROUP BY PaymentMode 
ORDER BY TotalAmount DESC 
LIMIT 1;

SECTION 9: COMPLEX DDL OPERATIONS Q101-Q110

Q101 Create a table SALE_DETAILS with composite primary key.

CREATE TABLE SALE_DETAILS (
    InvoiceNo VARCHAR(10),
    CarId VARCHAR(10),
    CustId VARCHAR(10),
    SaleDate DATE,
    PaymentMode VARCHAR(15),
    EmpID VARCHAR(10),
    SalePrice DECIMAL(10,2),
    Commission DECIMAL(7,2),
    Discount DECIMAL(7,2),
    PRIMARY KEY (InvoiceNo, CarId),
    FOREIGN KEY (CarId) REFERENCES INVENTORY(CarId),
    FOREIGN KEY (CustId) REFERENCES CUSTOMER(CustId),
    FOREIGN KEY (EmpID) REFERENCES EMPLOYEE(EmpID)
);

Q102 Create a table with CHECK constraint.

CREATE TABLE INVENTORY_NEW (
    CarId VARCHAR(10) PRIMARY KEY,
    CarName VARCHAR(20),
    Price DECIMAL(10,2) CHECK (Price > 0),
    Model VARCHAR(10),
    Year INT CHECK (Year BETWEEN 2000 AND 2025),
    FuelType VARCHAR(10)
);

Q103 Add a composite primary key to SALE table.

ALTER TABLE SALE ADD PRIMARY KEY (InvoiceNo, CarId);

Q104 Add a foreign key with ON DELETE CASCADE.

ALTER TABLE SALE ADD CONSTRAINT fk_car 
FOREIGN KEY (CarId) REFERENCES INVENTORY(CarId) ON DELETE CASCADE;

Q105 Add a CHECK constraint to ensure SalePrice is not negative.

ALTER TABLE SALE ADD CONSTRAINT chk_price CHECK (SalePrice >= 0);

Q106 Add a UNIQUE constraint on (CustName, Phone).

ALTER TABLE CUSTOMER ADD CONSTRAINT unique_customer UNIQUE (CustName, Phone);

Q107 Create an index on CarName in INVENTORY table.

CREATE INDEX idx_carname ON INVENTORY(CarName);

Q108 Create a view 'Expensive_Cars'.

CREATE VIEW Expensive_Cars AS 
SELECT * FROM INVENTORY WHERE Price > 600000;

Q109 Modify the view to include only VXI and ZXI models.

CREATE OR REPLACE VIEW Expensive_Cars AS 
SELECT * FROM INVENTORY WHERE Price > 600000 AND Model IN ('VXI', 'ZXI');

Q110 Create a view 'Sales_Summary'.

CREATE VIEW Sales_Summary AS 
SELECT C.CustId, C.CustName, COUNT(S.InvoiceNo) AS TotalPurchases, 
SUM(S.SalePrice) AS TotalAmount 
FROM CUSTOMER C LEFT JOIN SALE S ON C.CustId = S.CustId 
GROUP BY C.CustId, C.CustName;

SECTION 10: COMPLEX INSERT OPERATIONS Q111-Q120

Q111 Insert multiple employees using a single INSERT.

INSERT INTO EMPLOYEE VALUES 
('E013', 'Suresh Kumar', '1988-03-15', '2015-06-01', 'Manager', 55000),
('E014', 'Meena Sharma', '1990-07-20', '2016-08-15', 'Salesman', 32000),
('E015', 'Rajesh Patel', '1985-11-05', '2014-02-10', 'Salesman', 34500);

Q112 Insert a car and its sale using transactions.

START TRANSACTION;
INSERT INTO INVENTORY VALUES ('S007', 'CIAZ', 750000, 'ZXI', 2020, 'Petrol');
INSERT INTO SALE VALUES ('I00009', 'S007', 'C0001', '2020-02-15', 'Bank Finance', 'E004', 720000);
COMMIT;

Q113 Insert with calculated Commission.

INSERT INTO SALE (InvoiceNo, CarId, CustId, SaleDate, PaymentMode, EmpID, SalePrice, Commission) 
VALUES ('I00010', 'B001', 'C0003', '2020-02-20', 'Credit Card', 'E002', 635074, 635074 * 0.12);

Q114 Insert with DEFAULT value for Price.

ALTER TABLE INVENTORY MODIFY Price DECIMAL(10,2) DEFAULT 0;
INSERT INTO INVENTORY (CarId, CarName, Model, Year, FuelType) 
VALUES ('S008', 'NEXON', 'XZA', 2019, 'Diesel');

Q115 Insert with Commission using ROUND function.

INSERT INTO SALE VALUES ('I00011', 'S002', 'C0004', '2020-02-25', 'Online', 'E010', 687680, ROUND(687680 * 0.12, 2));

Q116 Copy records to backup table.

CREATE TABLE EMPLOYEE_BACKUP AS SELECT * FROM EMPLOYEE WHERE 1=0;
INSERT INTO EMPLOYEE_BACKUP SELECT * FROM EMPLOYEE;

Q117 Insert from SELECT with calculated values.

INSERT INTO SALE (InvoiceNo, CarId, CustId, SaleDate, PaymentMode, EmpID, SalePrice, Commission)
SELECT 
    CONCAT('I', LPAD(ROW_NUMBER() OVER (ORDER BY CarId) + 11, 5, '0')),
    CarId, 'C0001', CURDATE(), 'Cash', 'E001', 
    Price * 1.05, Price * 0.05
FROM INVENTORY 
WHERE Price > 600000;

Q118 Insert conditional data using SELECT.

INSERT INTO SALE (InvoiceNo, CarId, CustId, SaleDate, PaymentMode, EmpID, SalePrice)
SELECT CONCAT('I', FLOOR(RAND()*100000)), CarId, 'C0001', CURDATE(), 'Cash', 'E001', Price
FROM INVENTORY WHERE Year = 2019;

Q119 Insert multiple customers using recursive CTE.

INSERT INTO CUSTOMER (CustId, CustName, Phone, Email)
WITH RECURSIVE customer_cte AS (
    SELECT 'C0010' AS id, 'Amit Kumar' AS name, '9876543201' AS phone, 'amit@gmail.com' AS email
    UNION ALL
    SELECT CONCAT('C', LPAD(CAST(SUBSTR(id,2) AS UNSIGNED) + 1, 4, '0')),
           CONCAT('Customer', CAST(SUBSTR(id,2) AS UNSIGNED) + 1),
           CONCAT('98', LPAD(FLOOR(RAND()*100000000), 8, '0')),
           CONCAT('cust', CAST(SUBSTR(id,2) AS UNSIGNED) + 1, '@gmail.com')
    FROM customer_cte WHERE CAST(SUBSTR(id,2) AS UNSIGNED) < 5
)
SELECT id, name, phone, email FROM customer_cte;

Q120 Insert with validation using IFNULL and COALESCE.

INSERT INTO CUSTOMER (CustId, CustName, CustAdd, Phone, Email)
VALUES ('C0020', 'Deepak Gupta', 'Delhi', '9876543212', COALESCE(NULL, 'deepak@gmail.com'));

SECTION 11: ADVANCED SELECT WITH COMPLEX WHERE Q121-Q130

Q121 Find cars with price between 400000 and 600000 OR (model = 'VXI' AND year >= 2018).

SELECT * FROM INVENTORY 
WHERE (Price BETWEEN 400000 AND 600000) 
OR (Model = 'VXI' AND Year >= 2018);

Q122 Find employees whose salary is NOT between 25000 and 40000.

SELECT * FROM EMPLOYEE 
WHERE Salary NOT BETWEEN 25000 AND 40000;

Q123 Find customers whose name has exactly 5 characters.

SELECT * FROM CUSTOMER 
WHERE LENGTH(CustName) = 5;

Q124 Find employees whose name starts with 'A' OR 'S'.

SELECT * FROM EMPLOYEE 
WHERE (EmpName LIKE 'A%' OR EmpName LIKE 'S%') AND Salary > 30000;

Q125 Find sales where sale price > 600000 OR payment mode is 'Bank Finance'.

SELECT * FROM SALE 
WHERE SalePrice > 600000 OR PaymentMode = 'Bank Finance';

Q126 Find cars NOT sold in 2019.

SELECT I.* FROM INVENTORY I 
WHERE I.CarId NOT IN (
    SELECT DISTINCT CarId FROM SALE WHERE YEAR(SaleDate) = 2019
);

Q127 Find customers who have NOT purchased any car.

SELECT * FROM CUSTOMER 
WHERE CustId NOT IN (SELECT DISTINCT CustId FROM SALE);

Q128 Find employees who have NOT made any sale.

SELECT * FROM EMPLOYEE 
WHERE EmpID NOT IN (SELECT DISTINCT EmpID FROM SALE);

Q129 Find cars sold at least twice.

SELECT CarId, COUNT(*) AS SaleCount 
FROM SALE 
GROUP BY CarId 
HAVING COUNT(*) >= 2;

Q130 Find customers who bought 'Petrol' and 'Diesel' cars.

SELECT C.CustName 
FROM CUSTOMER C 
WHERE EXISTS (
    SELECT 1 FROM SALE S 
    JOIN INVENTORY I ON S.CarId = I.CarId 
    WHERE S.CustId = C.CustId AND I.FuelType = 'Petrol'
) AND EXISTS (
    SELECT 1 FROM SALE S 
    JOIN INVENTORY I ON S.CarId = I.CarId 
    WHERE S.CustId = C.CustId AND I.FuelType = 'Diesel'
);

SECTION 12: COMPLEX AGGREGATE FUNCTIONS Q131-Q140

Q131 Calculate average, max, min, count by model.

SELECT Model, 
       COUNT(*) AS Count, 
       AVG(Price) AS AvgPrice, 
       MAX(Price) AS MaxPrice, 
       MIN(Price) AS MinPrice,
       SUM(Price) AS TotalPrice
FROM INVENTORY 
GROUP BY Model;

Q132 Find total sales amount and average by payment mode.

SELECT PaymentMode, 
       SUM(SalePrice) AS TotalAmount, 
       AVG(SalePrice) AS AvgSale, 
       COUNT(*) AS Count
FROM SALE 
GROUP BY PaymentMode 
HAVING AVG(SalePrice) > 500000;

Q133 Calculate month-wise and year-wise sales totals.

SELECT YEAR(SaleDate) AS Year, 
       MONTH(SaleDate) AS Month, 
       COUNT(*) AS TotalSales, 
       SUM(SalePrice) AS TotalAmount
FROM SALE 
GROUP BY YEAR(SaleDate), MONTH(SaleDate) 
ORDER BY Year DESC, Month DESC;

Q134 Calculate employee performance.

SELECT E.EmpName, 
       COUNT(S.InvoiceNo) AS SalesCount, 
       SUM(S.SalePrice) AS TotalSales,
       AVG(S.SalePrice) AS AvgSale,
       MAX(S.SalePrice) AS MaxSale,
       MIN(S.SalePrice) AS MinSale
FROM EMPLOYEE E 
LEFT JOIN SALE S ON E.EmpID = S.EmpID 
GROUP BY E.EmpName 
HAVING COUNT(S.InvoiceNo) > 0
ORDER BY TotalSales DESC;

Q135 Find top 3 most expensive cars by model.

WITH RankedCars AS (
    SELECT CarName, Model, Price,
           RANK() OVER (PARTITION BY Model ORDER BY Price DESC) AS RankInModel
    FROM INVENTORY
)
SELECT CarName, Model, Price, RankInModel
FROM RankedCars 
WHERE RankInModel <= 3;

Q136 Calculate running total of sales by date.

SELECT SaleDate, 
       SUM(SalePrice) AS DailyTotal,
       SUM(SUM(SalePrice)) OVER (ORDER BY SaleDate) AS RunningTotal
FROM SALE 
GROUP BY SaleDate 
ORDER BY SaleDate;

Q137 Group by fuel type and year.

SELECT FuelType, Year, 
       COUNT(*) AS CarCount,
       AVG(Price) AS AvgPrice,
       ROUND(AVG(Price) * 1.2, 2) AS FuturePrice
FROM INVENTORY 
GROUP BY FuelType, Year
HAVING COUNT(*) > 0
ORDER BY FuelType, Year DESC;

Q138 Calculate standard deviation and variance.

SELECT Model, 
       COUNT(*) AS Count,
       AVG(Price) AS AvgPrice,
       STD(Price) AS StdDev,
       VARIANCE(Price) AS Variance
FROM INVENTORY 
GROUP BY Model;

Q139 Find months with maximum sales.

WITH MonthlySales AS (
    SELECT YEAR(SaleDate) AS Year, 
           MONTH(SaleDate) AS Month,
           SUM(SalePrice) AS TotalSales
    FROM SALE 
    GROUP BY YEAR(SaleDate), MONTH(SaleDate)
)
SELECT Year, Month, TotalSales,
       RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM MonthlySales;

Q140 Calculate 3-month moving average.

WITH DailySales AS (
    SELECT SaleDate, SUM(SalePrice) AS TotalSales
    FROM SALE 
    GROUP BY SaleDate
)
SELECT SaleDate, TotalSales,
       AVG(TotalSales) OVER (ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg3
FROM DailySales;

SECTION 13: ADVANCED STRING AND DATE FUNCTIONS Q141-Q150

Q141 Extract domain from email and count by domain.

SELECT SUBSTRING_INDEX(Email, '@', -1) AS Domain,
       COUNT(*) AS CustomerCount
FROM CUSTOMER 
WHERE Email IS NOT NULL
GROUP BY SUBSTRING_INDEX(Email, '@', -1)
ORDER BY CustomerCount DESC;

Q142 Display day of week and sales count.

SELECT DAYNAME(SaleDate) AS DayOfWeek,
       COUNT(*) AS SalesCount,
       SUM(SalePrice) AS TotalAmount
FROM SALE 
GROUP BY DAYNAME(SaleDate)
ORDER BY FIELD(DAYNAME(SaleDate), 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');

Q143 Show employee age at joining.

SELECT EmpName, DOB, DOJ,
       TIMESTAMPDIFF(YEAR, DOB, DOJ) AS AgeAtJoining,
       TIMESTAMPDIFF(YEAR, DOB, CURDATE()) AS CurrentAge
FROM EMPLOYEE;

Q144 Extract first name and last name.

SELECT EmpName,
       SUBSTRING_INDEX(EmpName, ' ', 1) AS FirstName,
       CASE 
           WHEN LENGTH(EmpName) - LENGTH(REPLACE(EmpName, ' ', '')) >= 1 
           THEN SUBSTRING_INDEX(EmpName, ' ', -1) 
           ELSE ''
       END AS LastName
FROM EMPLOYEE;

Q145 Format sale price in Indian currency.

SELECT InvoiceNo,
       SalePrice,
       FORMAT(SalePrice, 2) AS FormattedPrice,
       CONCAT('₹', FORMAT(SalePrice, 0)) AS IndianCurrency
FROM SALE;

Q146 Find cars manufactured in same year as sale.

SELECT I.CarName, I.Year AS ManufacturingYear, 
       YEAR(S.SaleDate) AS SaleYear
FROM INVENTORY I 
JOIN SALE S ON I.CarId = S.CarId 
WHERE I.Year = YEAR(S.SaleDate);

Q147 Convert date to different formats.

SELECT SaleDate,
       DATE_FORMAT(SaleDate, '%W, %M %d, %Y') AS LongDate,
       DATE_FORMAT(SaleDate, '%d/%m/%Y') AS ShortDate,
       DATE_FORMAT(SaleDate, '%b %d') AS Abbreviated,
       DATE_FORMAT(SaleDate, '%Y-%m-%d') AS ISODate
FROM SALE;

Q148 Calculate days between sale date and current date.

SELECT InvoiceNo,
       SaleDate,
       DATEDIFF(CURDATE(), SaleDate) AS DaysSinceSale,
       TIMESTAMPDIFF(DAY, SaleDate, CURDATE()) AS DaysDiff,
       TIMESTAMPDIFF(MONTH, SaleDate, CURDATE()) AS MonthsSince
FROM SALE;

Q149 Mask phone number and email.

SELECT CustName,
       Phone,
       CONCAT(LEFT(Phone, 3), '******', RIGHT(Phone, 2)) AS MaskedPhone,
       CONCAT(LEFT(Email, 2), '****', SUBSTRING_INDEX(Email, '@', -1)) AS MaskedEmail
FROM CUSTOMER;

Q150 Find quarter and fiscal year for each sale.

SELECT InvoiceNo,
       SaleDate,
       QUARTER(SaleDate) AS Quarter,
       YEAR(SaleDate) AS Year,
       CONCAT('Q', QUARTER(SaleDate), '-', YEAR(SaleDate)) AS QuarterYear,
       CASE 
           WHEN MONTH(SaleDate) BETWEEN 4 AND 6 THEN 'Q1'
           WHEN MONTH(SaleDate) BETWEEN 7 AND 9 THEN 'Q2'
           WHEN MONTH(SaleDate) BETWEEN 10 AND 12 THEN 'Q3'
           ELSE 'Q4'
       END AS FinancialQuarter
FROM SALE;

SECTION 14: ADVANCED JOINS AND SUBQUERIES Q151-Q160

Q151 Find customers who bought from more than one fuel type.

SELECT C.CustName, 
       COUNT(DISTINCT I.FuelType) AS FuelTypesCount,
       GROUP_CONCAT(DISTINCT I.FuelType) AS FuelTypes
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
JOIN INVENTORY I ON S.CarId = I.CarId 
GROUP BY C.CustId, C.CustName
HAVING COUNT(DISTINCT I.FuelType) > 1;

Q152 Find the second highest price car.

SELECT * FROM INVENTORY 
ORDER BY Price DESC 
LIMIT 1 OFFSET 1;

Q153 Find employees who sold more than average.

WITH EmployeeSales AS (
    SELECT EmpID, COUNT(*) AS TotalSales
    FROM SALE 
    GROUP BY EmpID
)
SELECT E.EmpName, ES.TotalSales, AVG(ES.TotalSales) OVER () AS AvgSales
FROM EMPLOYEE E 
JOIN EmployeeSales ES ON E.EmpID = ES.EmpID
WHERE ES.TotalSales > (SELECT AVG(TotalSales) FROM EmployeeSales);

Q154 Find the most frequent customer.

SELECT C.CustName, COUNT(S.InvoiceNo) AS PurchaseCount,
       DENSE_RANK() OVER (ORDER BY COUNT(S.InvoiceNo) DESC) AS Rank
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
GROUP BY C.CustId, C.CustName
LIMIT 1;

Q155 Display sales with ranking by price.

SELECT InvoiceNo, CarId, SalePrice,
       RANK() OVER (ORDER BY SalePrice DESC) AS PriceRank,
       DENSE_RANK() OVER (ORDER BY SalePrice DESC) AS DenseRank,
       ROW_NUMBER() OVER (ORDER BY SalePrice DESC) AS RowNum
FROM SALE;

Q156 Find cars never sold.

SELECT I.* 
FROM INVENTORY I 
LEFT JOIN SALE S ON I.CarId = S.CarId 
WHERE S.CarId IS NULL;

Q157 Calculate percentage contribution of each payment mode.

SELECT PaymentMode,
       COUNT(*) AS Count,
       SUM(SalePrice) AS TotalAmount,
       ROUND(SUM(SalePrice) * 100.0 / (SELECT SUM(SalePrice) FROM SALE), 2) AS PercentageContribution
FROM SALE 
GROUP BY PaymentMode
ORDER BY TotalAmount DESC;

Q158 Find customers who spent more than average.

SELECT C.CustName, SUM(S.SalePrice) AS TotalSpent
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
GROUP BY C.CustId, C.CustName
HAVING SUM(S.SalePrice) > (SELECT AVG(TotalSpent) 
                          FROM (SELECT SUM(SalePrice) AS TotalSpent 
                                FROM SALE 
                                GROUP BY CustId) AS CustomerTotals);

Q159 Display difference between car price and model average.

SELECT CarName, Model, Price,
       AVG(Price) OVER (PARTITION BY Model) AS ModelAvgPrice,
       Price - AVG(Price) OVER (PARTITION BY Model) AS PriceDifference,
       ROUND((Price - AVG(Price) OVER (PARTITION BY Model)) * 100.0 / AVG(Price) OVER (PARTITION BY Model), 2) AS PercentDifference
FROM INVENTORY;

Q160 Find sales where price differs by more than 10%.

SELECT S.InvoiceNo, I.CarName, I.Price AS ListPrice, S.SalePrice,
       ROUND((I.Price - S.SalePrice) * 100.0 / I.Price, 2) AS DiscountPercentage,
       CASE 
           WHEN I.Price - S.SalePrice > I.Price * 0.10 THEN 'Heavy Discount'
           WHEN S.SalePrice - I.Price > I.Price * 0.10 THEN 'Premium Price'
           ELSE 'Normal'
       END AS PriceCategory
FROM SALE S 
JOIN INVENTORY I ON S.CarId = I.CarId;

SECTION 15: SET OPERATIONS AND CTEs Q161-Q170

Q161 Find customers who bought 'SWIFT' or 'BALENO' using UNION.

SELECT C.CustName, I.CarName
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
JOIN INVENTORY I ON S.CarId = I.CarId
WHERE I.CarName = 'SWIFT'
UNION
SELECT C.CustName, I.CarName
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
JOIN INVENTORY I ON S.CarId = I.CarId
WHERE I.CarName = 'BALENO';

Q162 Find customers who bought both 'SWIFT' and 'BALENO'.

SELECT CustName FROM CUSTOMER WHERE CustId IN (
    SELECT CustId FROM SALE WHERE CarId IN 
        (SELECT CarId FROM INVENTORY WHERE CarName = 'SWIFT')
)
INTERSECT
SELECT CustName FROM CUSTOMER WHERE CustId IN (
    SELECT CustId FROM SALE WHERE CarId IN 
        (SELECT CarId FROM INVENTORY WHERE CarName = 'BALENO')
);

Q163 Find cars sold by E001 but not by E002.

SELECT CarId FROM SALE WHERE EmpID = 'E001'
MINUS
SELECT CarId FROM SALE WHERE EmpID = 'E002';

Q164 Use CTE to find employees with more than 2 sales.

WITH SalesSummary AS (
    SELECT EmpID, 
           COUNT(*) AS SaleCount,
           AVG(SalePrice) AS AvgSale
    FROM SALE 
    GROUP BY EmpID
    HAVING COUNT(*) > 2
)
SELECT E.EmpName, SS.SaleCount, SS.AvgSale
FROM EMPLOYEE E 
JOIN SalesSummary SS ON E.EmpID = SS.EmpID
ORDER BY SS.SaleCount DESC;

Q165 Create recursive CTE to generate date series.

WITH RECURSIVE DateSeries AS (
    SELECT MIN(SaleDate) AS SaleDate FROM SALE
    UNION ALL
    SELECT DATE_ADD(SaleDate, INTERVAL 1 DAY)
    FROM DateSeries
    WHERE SaleDate < (SELECT MAX(SaleDate) FROM SALE)
)
SELECT DS.SaleDate,
       COUNT(S.InvoiceNo) AS SalesCount,
       COALESCE(SUM(S.SalePrice), 0) AS TotalAmount
FROM DateSeries DS
LEFT JOIN SALE S ON DS.SaleDate = S.SaleDate
GROUP BY DS.SaleDate
ORDER BY DS.SaleDate;

Q166 Find top 2 performing employees.

WITH EmployeePerformance AS (
    SELECT EmpID, 
           SUM(SalePrice) AS TotalSales,
           COUNT(*) AS SalesCount,
           RANK() OVER (ORDER BY SUM(SalePrice) DESC) AS RankByAmount
    FROM SALE 
    GROUP BY EmpID
)
SELECT E.EmpName, EP.TotalSales, EP.SalesCount
FROM EMPLOYEE E 
JOIN EmployeePerformance EP ON E.EmpID = EP.EmpID
WHERE EP.RankByAmount <= 2
ORDER BY EP.TotalSales DESC;

Q167 Find customers who bought most expensive car.

WITH MostExpensiveCar AS (
    SELECT CarId, Price
    FROM INVENTORY 
    ORDER BY Price DESC 
    LIMIT 1
)
SELECT C.CustName, I.CarName, I.Price, S.SaleDate
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
JOIN INVENTORY I ON S.CarId = I.CarId
JOIN MostExpensiveCar MEC ON I.CarId = MEC.CarId;

Q168 Calculate cumulative sales for each employee.

SELECT EmpID, SaleDate, SalePrice,
       SUM(SalePrice) OVER (PARTITION BY EmpID ORDER BY SaleDate) AS CumulativeSales,
       AVG(SalePrice) OVER (PARTITION BY EmpID ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg3
FROM SALE
ORDER BY EmpID, SaleDate;

Q169 Find sales where same car sold within 30 days.

WITH CarSales AS (
    SELECT CarId, SaleDate, InvoiceNo,
           LAG(SaleDate, 1) OVER (PARTITION BY CarId ORDER BY SaleDate) AS PreviousSaleDate
    FROM SALE
)
SELECT CarId, SaleDate, PreviousSaleDate,
       DATEDIFF(SaleDate, PreviousSaleDate) AS DaysBetween
FROM CarSales
WHERE PreviousSaleDate IS NOT NULL 
AND DATEDIFF(SaleDate, PreviousSaleDate) <= 30;

Q170 Calculate year-over-year growth in sales.

WITH YearlySales AS (
    SELECT YEAR(SaleDate) AS Year,
           SUM(SalePrice) AS TotalSales
    FROM SALE 
    GROUP BY YEAR(SaleDate)
)
SELECT Year, TotalSales,
       LAG(TotalSales, 1) OVER (ORDER BY Year) AS PreviousYearSales,
       TotalSales - LAG(TotalSales, 1) OVER (ORDER BY Year) AS GrowthAmount,
       ROUND((TotalSales - LAG(TotalSales, 1) OVER (ORDER BY Year)) * 100.0 / LAG(TotalSales, 1) OVER (ORDER BY Year), 2) AS GrowthPercentage
FROM YearlySales
ORDER BY Year;

SECTION 16: PIVOT AND WINDOW FUNCTIONS Q171-Q180

Q171 Create pivot table by model and fuel type.

SELECT FuelType,
       SUM(CASE WHEN Model = 'VXI' THEN 1 ELSE 0 END) AS VXI_Count,
       SUM(CASE WHEN Model = 'LXI' THEN 1 ELSE 0 END) AS LXI_Count,
       SUM(CASE WHEN Model = 'ZXI' THEN 1 ELSE 0 END) AS ZXI_Count,
       COUNT(*) AS TotalCount
FROM INVENTORY 
GROUP BY FuelType;

Q172 Find percentile ranks of car prices.

SELECT CarName, Price,
       ROUND(PERCENT_RANK() OVER (ORDER BY Price) * 100, 2) AS PercentileRank,
       ROUND(CUME_DIST() OVER (ORDER BY Price) * 100, 2) AS CumulativePercent,
       NTILE(4) OVER (ORDER BY Price) AS Quartile
FROM INVENTORY
ORDER BY Price;

Q173 Find median price of cars by model.

SELECT DISTINCT Model,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Price) OVER (PARTITION BY Model) AS MedianPrice
FROM INVENTORY;

Q174 Show first and last sale in each month.

SELECT InvoiceNo, SaleDate, SalePrice,
       FIRST_VALUE(InvoiceNo) OVER (PARTITION BY YEAR(SaleDate), MONTH(SaleDate) ORDER BY SaleDate) AS FirstSaleOfMonth,
       LAST_VALUE(InvoiceNo) OVER (PARTITION BY YEAR(SaleDate), MONTH(SaleDate) ORDER BY SaleDate 
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSaleOfMonth
FROM SALE
ORDER BY SaleDate;

Q175 Create pivot table by payment mode and year.

SELECT YEAR(SaleDate) AS SaleYear,
       SUM(CASE WHEN PaymentMode = 'Cash' THEN SalePrice ELSE 0 END) AS CashSales,
       SUM(CASE WHEN PaymentMode = 'Credit Card' THEN SalePrice ELSE 0 END) AS CreditCardSales,
       SUM(CASE WHEN PaymentMode = 'Online' THEN SalePrice ELSE 0 END) AS OnlineSales,
       SUM(CASE WHEN PaymentMode = 'Bank Finance' THEN SalePrice ELSE 0 END) AS BankFinanceSales,
       SUM(CASE WHEN PaymentMode = 'Cheque' THEN SalePrice ELSE 0 END) AS ChequeSales,
       SUM(SalePrice) AS TotalSales
FROM SALE 
GROUP BY YEAR(SaleDate)
ORDER BY SaleYear;

Q176 Find gaps in sale dates.

WITH DateSeries AS (
    SELECT MIN(SaleDate) AS SaleDate,
           MAX(SaleDate) AS MaxSaleDate
    FROM SALE
    UNION ALL
    SELECT DATE_ADD(SaleDate, INTERVAL 1 DAY), MaxSaleDate
    FROM DateSeries
    WHERE SaleDate < MaxSaleDate
)
SELECT DS.SaleDate
FROM DateSeries DS
LEFT JOIN SALE S ON DS.SaleDate = S.SaleDate
WHERE S.InvoiceNo IS NULL
ORDER BY DS.SaleDate;

Q177 Calculate days between sales for each employee.

WITH EmployeeSalesDates AS (
    SELECT EmpID, SaleDate,
           LAG(SaleDate, 1) OVER (PARTITION BY EmpID ORDER BY SaleDate) AS PreviousSale
    FROM SALE
)
SELECT EmpID, SaleDate, PreviousSale,
       DATEDIFF(SaleDate, PreviousSale) AS DaysBetweenSales,
       AVG(DATEDIFF(SaleDate, PreviousSale)) OVER (PARTITION BY EmpID) AS AvgDaysBetween
FROM EmployeeSalesDates
WHERE PreviousSale IS NOT NULL;

Q178 Use LEAD and LAG for price changes.

SELECT CarName, Price,
       LAG(Price, 1) OVER (ORDER BY Price) AS LowerPrice,
       LEAD(Price, 1) OVER (ORDER BY Price) AS HigherPrice,
       Price - LAG(Price, 1) OVER (ORDER BY Price) AS DifferenceFromLower,
       LEAD(Price, 1) OVER (ORDER BY Price) - Price AS DifferenceToHigher
FROM INVENTORY
ORDER BY Price;

Q179 Create summary with ROLLUP.

SELECT 
    CASE WHEN GROUPING(Year) = 1 THEN 'Total' ELSE CAST(Year AS CHAR) END AS Year,
    CASE WHEN GROUPING(FuelType) = 1 THEN 'All Fuels' ELSE FuelType END AS FuelType,
    COUNT(*) AS CarCount,
    ROUND(AVG(Price), 2) AS AvgPrice,
    ROUND(SUM(Price), 2) AS TotalPrice
FROM INVENTORY 
GROUP BY Year, FuelType WITH ROLLUP;

Q180 Identify customers with increasing purchases.

WITH CustomerPurchases AS (
    SELECT CustId, SaleDate, SalePrice,
           LAG(SalePrice, 1) OVER (PARTITION BY CustId ORDER BY SaleDate) AS PreviousPurchase
    FROM SALE
)
SELECT CustId, SaleDate, SalePrice, PreviousPurchase,
       CASE 
           WHEN PreviousPurchase IS NULL THEN 'First Purchase'
           WHEN SalePrice > PreviousPurchase THEN 'Increased Spending'
           WHEN SalePrice = PreviousPurchase THEN 'Same Spending'
           ELSE 'Decreased Spending'
       END AS SpendingPattern
FROM CustomerPurchases
ORDER BY CustId, SaleDate;

SECTION 17: COMPLEX UPDATE AND DELETE Q181-Q190

Q181 Update price and discount for 'Baleno'.

UPDATE INVENTORY 
SET Price = Price * 1.10,
    Discount = Price * 0.08
WHERE CarName = 'Baleno';

Q182 Update NULL prices to model average.

UPDATE INVENTORY I1
SET Price = (SELECT AVG(Price) 
            FROM INVENTORY I2 
            WHERE I2.Model = I1.Model AND I2.Price IS NOT NULL)
WHERE Price IS NULL;

Q183 Update commission to 15% for sales above 500000.

UPDATE SALE 
SET Commission = SalePrice * 0.15
WHERE SalePrice > 500000;

Q184 Delete sales before 2019 for discontinued cars.

DELETE FROM SALE 
WHERE YEAR(SaleDate) < 2019 
AND CarId IN (SELECT CarId FROM INVENTORY WHERE Year < 2015);

Q185 Update employee status based on performance.

UPDATE EMPLOYEE E 
SET Designation = CASE 
    WHEN (SELECT COUNT(*) FROM SALE S WHERE S.EmpID = E.EmpID) >= 3 THEN 'Star Salesman'
    WHEN (SELECT COUNT(*) FROM SALE S WHERE S.EmpID = E.EmpID) >= 1 THEN 'Salesman'
    ELSE 'Trainee'
END;

Q186 Delete duplicate customers based on phone.

DELETE FROM CUSTOMER 
WHERE CustId NOT IN (
    SELECT MIN(CustId) 
    FROM CUSTOMER 
    GROUP BY Phone
);

Q187 Update SalePrice to current inventory price.

UPDATE SALE S 
JOIN INVENTORY I ON S.CarId = I.CarId 
SET S.SalePrice = I.Price * (1 + RAND() * 0.1);

Q188 Delete sales where customer not confirmed.

DELETE FROM SALE 
WHERE CustId IN (
    SELECT CustId FROM CUSTOMER 
    WHERE Email LIKE '%temp%' OR Phone IS NULL
);

Q189 Update multiple columns using subquery.

UPDATE SALE S
SET SalePrice = (SELECT Price FROM INVENTORY I WHERE I.CarId = S.CarId),
    Commission = (SELECT Price * 0.05 FROM INVENTORY I WHERE I.CarId = S.CarId)
WHERE PaymentMode = 'Cash';

Q190 Archive old sales to backup.

START TRANSACTION;
INSERT INTO SALE_BACKUP SELECT * FROM SALE WHERE YEAR(SaleDate) = 2018;
DELETE FROM SALE WHERE YEAR(SaleDate) = 2018;
COMMIT;

SECTION 18: PERFORMANCE AND OPTIMIZATION Q191-Q200

Q191 Create EXPLAIN plan for complex query.

EXPLAIN SELECT C.CustName, I.CarName, S.SalePrice 
FROM SALE S 
JOIN CUSTOMER C ON S.CustId = C.CustId 
JOIN INVENTORY I ON S.CarId = I.CarId 
WHERE S.SalePrice > (SELECT AVG(SalePrice) FROM SALE)
AND YEAR(S.SaleDate) = 2019
ORDER BY S.SalePrice DESC;

Q192 Use partitioning for large tables.

CREATE TABLE SALE_PARTITIONED (
    InvoiceNo VARCHAR(10),
    CarId VARCHAR(10),
    CustId VARCHAR(10),
    SaleDate DATE,
    PaymentMode VARCHAR(15),
    EmpID VARCHAR(10),
    SalePrice DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(SaleDate)) (
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021)
);

Q193 Create materialized view.

CREATE TABLE SalesSummaryMonthly AS
SELECT YEAR(SaleDate) AS SaleYear,
       MONTH(SaleDate) AS SaleMonth,
       COUNT(*) AS TotalSales,
       SUM(SalePrice) AS TotalAmount,
       AVG(SalePrice) AS AvgPrice
FROM SALE 
GROUP BY YEAR(SaleDate), MONTH(SaleDate);

Q194 Use query hints for index usage.

SELECT * FROM INVENTORY USE INDEX (idx_carname)
WHERE CarName LIKE 'BAL%';

Q195 Use EXISTS instead of IN for performance.

SELECT I.* FROM INVENTORY I 
WHERE EXISTS (
    SELECT 1 FROM SALE S 
    WHERE S.CarId = I.CarId AND S.SalePrice > 500000
);

Q196 Use batch processing for updates.

SET @batch_size = 100;
SET @start_id = 0;
WHILE @start_id < (SELECT MAX(InvoiceNo) FROM SALE) DO
    UPDATE SALE 
    SET Commission = SalePrice * 0.12 
    WHERE InvoiceNo BETWEEN @start_id AND @start_id + @batch_size;
    SET @start_id = @start_id + @batch_size;
END WHILE;

Q197 Create composite indexes.

CREATE INDEX idx_composite ON SALE(CustId, SaleDate, PaymentMode);
CREATE INDEX idx_sale_employee ON SALE(EmpID, SaleDate);
CREATE INDEX idx_inventory_model_year ON INVENTORY(Model, Year);

Q198 Use temporary table for calculations.

CREATE TEMPORARY TABLE TempSales AS
SELECT EmpID, 
       SUM(SalePrice) AS TotalSales,
       COUNT(*) AS SaleCount
FROM SALE 
GROUP BY EmpID;

SELECT E.EmpName, TS.TotalSales, TS.SaleCount
FROM EMPLOYEE E 
JOIN TempSales TS ON E.EmpID = TS.EmpID
WHERE TS.TotalSales > 1000000;

DROP TEMPORARY TABLE TempSales;

Q199 Analyze tables for optimizer.

ANALYZE TABLE INVENTORY;
ANALYZE TABLE SALE;
ANALYZE TABLE CUSTOMER;
ANALYZE TABLE EMPLOYEE;

Q200 Stored procedure for commissions.

DELIMITER //
CREATE PROCEDURE UpdateCommissions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE inv_no VARCHAR(10);
    DECLARE sale_price DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT InvoiceNo, SalePrice FROM SALE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO inv_no, sale_price;
        IF done THEN
            LEAVE read_loop;
        END IF;
        UPDATE SALE 
        SET Commission = ROUND(sale_price * 0.12, 2) 
        WHERE InvoiceNo = inv_no;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

CALL UpdateCommissions();

SECTION 19: BONUS - TRIGGERS & MORE Q201-Q205

Q201 Create trigger to update inventory when car sold.

DELIMITER //
CREATE TRIGGER after_sale_insert
AFTER INSERT ON SALE
FOR EACH ROW
BEGIN
    UPDATE INVENTORY 
    SET Status = 'Sold' 
    WHERE CarId = NEW.CarId;
END //
DELIMITER ;

Q202 Create trigger to validate sale price.

DELIMITER //
CREATE TRIGGER before_sale_insert
BEFORE INSERT ON SALE
FOR EACH ROW
BEGIN
    DECLARE inventory_price DECIMAL(10,2);
    SELECT Price INTO inventory_price 
    FROM INVENTORY 
    WHERE CarId = NEW.CarId;
    
    IF NEW.SalePrice < inventory_price * 0.70 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Sale price cannot be less than 70% of inventory price';
    END IF;
END //
DELIMITER ;

Q203 Find customers who spent above city average.

SELECT C.CustName, C.City, SUM(S.SalePrice) AS TotalSpent,
       AVG(SUM(S.SalePrice)) OVER (PARTITION BY C.City) AS CityAverage,
       SUM(S.SalePrice) - AVG(SUM(S.SalePrice)) OVER (PARTITION BY C.City) AS Difference
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
GROUP BY C.CustId, C.CustName, C.City
HAVING SUM(S.SalePrice) > AVG(SUM(S.SalePrice)) OVER (PARTITION BY C.City)
ORDER BY C.City, TotalSpent DESC;

Q204 Calculate Customer Lifetime Value.

SELECT C.CustName,
       COUNT(S.InvoiceNo) AS Purchases,
       SUM(S.SalePrice) AS TotalSpent,
       AVG(S.SalePrice) AS AvgPurchase,
       MAX(S.SaleDate) AS LastPurchase,
       DATEDIFF(CURDATE(), MAX(S.SaleDate)) AS DaysSinceLast,
       CASE 
           WHEN DATEDIFF(CURDATE(), MAX(S.SaleDate)) < 30 THEN 'Active'
           WHEN DATEDIFF(CURDATE(), MAX(S.SaleDate)) < 90 THEN 'Engaged'
           ELSE 'At Risk'
       END AS CustomerStatus
FROM CUSTOMER C 
LEFT JOIN SALE S ON C.CustId = S.CustId 
GROUP BY C.CustId, C.CustName
ORDER BY TotalSpent DESC;

Q205 Find most profitable product lines.

SELECT I.CarName, 
       COUNT(S.InvoiceNo) AS UnitsSold,
       SUM(S.SalePrice) AS Revenue,
       AVG(S.SalePrice) AS AvgSellingPrice,
       I.Price AS ListPrice,
       AVG(I.Price - S.SalePrice) AS AvgDiscount,
       ROUND(((I.Price - S.SalePrice) * 100.0 / I.Price), 2) AS DiscountPercent
FROM INVENTORY I 
JOIN SALE S ON I.CarId = S.CarId 
GROUP BY I.CarId, I.CarName, I.Price
ORDER BY Revenue DESC;

SECTION 20: ADVANCED REPORTING & ANALYTICS Q206-Q220

Q206 Create pivot table by year and fuel type.

SELECT YEAR(SaleDate) AS SaleYear,
       SUM(CASE WHEN FuelType = 'Petrol' THEN 1 ELSE 0 END) AS Petrol_Sales,
       SUM(CASE WHEN FuelType = 'Diesel' THEN 1 ELSE 0 END) AS Diesel_Sales,
       SUM(CASE WHEN FuelType = 'CNG' THEN 1 ELSE 0 END) AS CNG_Sales
FROM SALE S 
JOIN INVENTORY I ON S.CarId = I.CarId
GROUP BY YEAR(SaleDate);

Q207 Calculate monthly commission per employee.

SELECT EmpID, 
       YEAR(SaleDate) AS SaleYear,
       MONTH(SaleDate) AS SaleMonth,
       SUM(Commission) AS TotalCommission
FROM SALE 
WHERE Commission IS NOT NULL
GROUP BY EmpID, YEAR(SaleDate), MONTH(SaleDate)
ORDER BY EmpID, SaleYear DESC, SaleMonth DESC;

Q208 Find customers with consecutive monthly purchases.

WITH CustomerPurchases AS (
    SELECT CustId, 
           YEAR(SaleDate) AS SaleYear,
           MONTH(SaleDate) AS SaleMonth,
           DATE_FORMAT(SaleDate, '%Y-%m') AS YearMonth
    FROM SALE 
    GROUP BY CustId, YEAR(SaleDate), MONTH(SaleDate)
),
RankedPurchases AS (
    SELECT CustId, YearMonth,
           LAG(YearMonth, 1) OVER (PARTITION BY CustId ORDER BY YearMonth) AS PrevMonth
    FROM CustomerPurchases
)
SELECT DISTINCT CustId 
FROM RankedPurchases 
WHERE PERIOD_DIFF(YearMonth, PrevMonth) = 1;

Q209 Calculate percentage of total sales by model.

SELECT I.Model,
       SUM(S.SalePrice) AS TotalSales,
       ROUND(SUM(S.SalePrice) * 100.0 / (SELECT SUM(SalePrice) FROM SALE), 2) AS PercentageContribution
FROM INVENTORY I 
JOIN SALE S ON I.CarId = S.CarId 
GROUP BY I.Model
ORDER BY TotalSales DESC;

Q210 Find employee with highest average sale price.

SELECT EmpID, AVG(SalePrice) AS AvgSalePrice
FROM SALE 
GROUP BY EmpID 
ORDER BY AvgSalePrice DESC 
LIMIT 1;

Q211 Identify cars sold above list price.

SELECT S.InvoiceNo, I.CarName, I.Price AS ListPrice, S.SalePrice
FROM SALE S 
JOIN INVENTORY I ON S.CarId = I.CarId 
WHERE S.SalePrice > I.Price;

Q212 Calculate revenue per employee with commission.

SELECT E.EmpName,
       COUNT(S.InvoiceNo) AS TotalSales,
       SUM(S.SalePrice) AS TotalRevenue,
       SUM(S.Commission) AS TotalCommission,
       ROUND(SUM(S.Commission) * 100.0 / SUM(S.SalePrice), 2) AS CommissionRate
FROM EMPLOYEE E 
JOIN SALE S ON E.EmpID = S.EmpID 
WHERE S.Commission IS NOT NULL
GROUP BY E.EmpName
ORDER BY TotalRevenue DESC;

Q213 Find most popular payment mode by month.

WITH MonthlyPayment AS (
    SELECT YEAR(SaleDate) AS SaleYear,
           MONTH(SaleDate) AS SaleMonth,
           PaymentMode,
           COUNT(*) AS PaymentCount,
           RANK() OVER (PARTITION BY YEAR(SaleDate), MONTH(SaleDate) ORDER BY COUNT(*) DESC) AS RankNum
    FROM SALE 
    GROUP BY YEAR(SaleDate), MONTH(SaleDate), PaymentMode
)
SELECT SaleYear, SaleMonth, PaymentMode, PaymentCount
FROM MonthlyPayment 
WHERE RankNum = 1
ORDER BY SaleYear, SaleMonth;

Q214 Calculate running total per employee.

SELECT EmpID, 
       SaleDate, 
       SalePrice,
       SUM(SalePrice) OVER (PARTITION BY EmpID ORDER BY SaleDate) AS RunningTotal
FROM SALE 
ORDER BY EmpID, SaleDate;

Q215 Find customers with increasing purchases.

WITH CustomerPurchases AS (
    SELECT CustId, SaleDate, SalePrice,
           LAG(SalePrice, 1) OVER (PARTITION BY CustId ORDER BY SaleDate) AS PrevPurchase
    FROM SALE 
)
SELECT CustId, SaleDate, SalePrice, PrevPurchase
FROM CustomerPurchases 
WHERE SalePrice > PrevPurchase;

Q216 Calculate average days between sales per customer.

WITH CustomerSalesDates AS (
    SELECT CustId, SaleDate,
           LEAD(SaleDate, 1) OVER (PARTITION BY CustId ORDER BY SaleDate) AS NextSaleDate
    FROM SALE 
)
SELECT CustId, 
       AVG(DATEDIFF(NextSaleDate, SaleDate)) AS AvgDaysBetween
FROM CustomerSalesDates 
WHERE NextSaleDate IS NOT NULL
GROUP BY CustId;

Q217 Identify best-selling model per year.

WITH YearlySales AS (
    SELECT YEAR(SaleDate) AS SaleYear,
           I.Model,
           COUNT(*) AS SalesCount,
           RANK() OVER (PARTITION BY YEAR(SaleDate) ORDER BY COUNT(*) DESC) AS RankNum
    FROM SALE S 
    JOIN INVENTORY I ON S.CarId = I.CarId 
    GROUP BY YEAR(SaleDate), I.Model
)
SELECT SaleYear, Model, SalesCount
FROM YearlySales 
WHERE RankNum = 1;

Q218 Calculate profit margin per sale.

SELECT InvoiceNo, 
       I.CarId, 
       I.Price AS ListPrice, 
       S.SalePrice,
       (S.SalePrice - I.Price * 0.70) AS Profit,
       ROUND((S.SalePrice - I.Price * 0.70) * 100.0 / S.SalePrice, 2) AS ProfitMargin
FROM SALE S 
JOIN INVENTORY I ON S.CarId = I.CarId;

Q219 Find employees with improved performance.

WITH EmployeeQuarterlySales AS (
    SELECT EmpID,
           QUARTER(SaleDate) AS SaleQuarter,
           YEAR(SaleDate) AS SaleYear,
           SUM(SalePrice) AS TotalSales
    FROM SALE 
    GROUP BY EmpID, YEAR(SaleDate), QUARTER(SaleDate)
),
EmployeeSalesTrend AS (
    SELECT EmpID, SaleYear, SaleQuarter, TotalSales,
           LAG(TotalSales, 1) OVER (PARTITION BY EmpID ORDER BY SaleYear, SaleQuarter) AS PrevQuarterSales
    FROM EmployeeQuarterlySales
)
SELECT EmpID, SaleYear, SaleQuarter, TotalSales, PrevQuarterSales,
       CASE 
           WHEN PrevQuarterSales IS NULL THEN 'First Quarter'
           WHEN TotalSales > PrevQuarterSales THEN 'Improved'
           WHEN TotalSales = PrevQuarterSales THEN 'Same'
           ELSE 'Declined'
       END AS Performance
FROM EmployeeSalesTrend;

Q220 Calculate total value of inventory.

SELECT SUM(Price) AS TotalInventoryValue,
       AVG(Price) AS AvgPrice,
       COUNT(*) AS TotalCars
FROM INVENTORY;

SECTION 21: COMPLEX DATA VALIDATION Q221-Q235

Q221 Find orphan records in SALE table.

SELECT S.* 
FROM SALE S 
LEFT JOIN INVENTORY I ON S.CarId = I.CarId 
WHERE I.CarId IS NULL;

Q222 Find customers with duplicate phone numbers.

SELECT Phone, COUNT(*) AS DuplicateCount, GROUP_CONCAT(CustName) AS Customers
FROM CUSTOMER 
WHERE Phone IS NOT NULL
GROUP BY Phone 
HAVING COUNT(*) > 1;

Q223 Find sales with invalid employee IDs.

SELECT S.* 
FROM SALE S 
LEFT JOIN EMPLOYEE E ON S.EmpID = E.EmpID 
WHERE E.EmpID IS NULL;

Q224 Find customers with invalid email addresses.

SELECT * FROM CUSTOMER 
WHERE Email IS NOT NULL AND Email NOT LIKE '%@%';

Q225 Find duplicate car entries.

SELECT CarId, CarName, COUNT(*) AS DuplicateCount
FROM INVENTORY 
GROUP BY CarId, CarName 
HAVING COUNT(*) > 1;

Q226 Identify invalid payment modes.

SELECT DISTINCT PaymentMode 
FROM SALE 
WHERE PaymentMode NOT IN ('Cash', 'Credit Card', 'Online', 'Bank Finance', 'Cheque');

Q227 Find sales with negative sale price.

SELECT * FROM SALE WHERE SalePrice < 0;

Q228 Find employees with duplicate email addresses.

SELECT Email, COUNT(*) AS DuplicateCount
FROM EMPLOYEE 
WHERE Email IS NOT NULL
GROUP BY Email 
HAVING COUNT(*) > 1;

Q229 Find sales with future dates.

SELECT * FROM SALE WHERE SaleDate > CURDATE();

Q230 Find customers with missing contact information.

SELECT * FROM CUSTOMER 
WHERE Phone IS NULL AND Email IS NULL;

Q231 Find cars with invalid manufacturing year.

SELECT * FROM INVENTORY WHERE Year < 2000 OR Year > YEAR(CURDATE());

Q232 Find sales where commission is negative.

SELECT * FROM SALE WHERE Commission < 0;

Q233 Find employees with salaries below minimum wage.

SELECT * FROM EMPLOYEE WHERE Salary < 15000;

Q234 Find customers with invalid phone numbers.

SELECT * FROM CUSTOMER 
WHERE Phone IS NOT NULL AND LENGTH(Phone) < 10;

Q235 Find sales where sale date is before car manufacture date.

SELECT S.*, I.Year 
FROM SALE S 
JOIN INVENTORY I ON S.CarId = I.CarId 
WHERE YEAR(S.SaleDate) < I.Year;

SECTION 22: COMPLEX REPORTS Q236-Q250

Q236 Create executive summary dashboard.

SELECT 
    (SELECT COUNT(*) FROM CUSTOMER) AS TotalCustomers,
    (SELECT COUNT(*) FROM INVENTORY) AS TotalInventory,
    (SELECT COUNT(*) FROM SALE) AS TotalSales,
    (SELECT SUM(SalePrice) FROM SALE) AS TotalRevenue,
    (SELECT AVG(SalePrice) FROM SALE) AS AvgSalePrice,
    (SELECT MAX(SalePrice) FROM SALE) AS MaxSale;

Q237 Generate monthly sales report with growth.

WITH MonthlySales AS (
    SELECT DATE_FORMAT(SaleDate, '%Y-%m') AS Month,
           SUM(SalePrice) AS TotalSales,
           COUNT(*) AS SaleCount
    FROM SALE 
    GROUP BY DATE_FORMAT(SaleDate, '%Y-%m')
)
SELECT Month, TotalSales, SaleCount,
       LAG(TotalSales, 1) OVER (ORDER BY Month) AS PrevMonthSales,
       ROUND((TotalSales - LAG(TotalSales, 1) OVER (ORDER BY Month)) * 100.0 / LAG(TotalSales, 1) OVER (ORDER BY Month), 2) AS GrowthPercent
FROM MonthlySales
ORDER BY Month;

Q238 Generate employee performance report.

SELECT E.EmpName,
       E.Designation,
       COUNT(S.InvoiceNo) AS SalesCount,
       SUM(S.SalePrice) AS TotalSales,
       AVG(S.SalePrice) AS AvgSale,
       SUM(S.Commission) AS TotalCommission,
       RANK() OVER (ORDER BY SUM(S.SalePrice) DESC) AS PerformanceRank
FROM EMPLOYEE E 
LEFT JOIN SALE S ON E.EmpID = S.EmpID 
GROUP BY E.EmpName, E.Designation
ORDER BY TotalSales DESC;

Q239 Generate customer purchase report.

SELECT C.CustName,
       COUNT(S.InvoiceNo) AS Purchases,
       SUM(S.SalePrice) AS TotalSpent,
       AVG(S.SalePrice) AS AvgPurchase,
       MIN(S.SaleDate) AS FirstPurchase,
       MAX(S.SaleDate) AS LastPurchase,
       DATEDIFF(MAX(S.SaleDate), MIN(S.SaleDate)) AS CustomerLifetime
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
GROUP BY C.CustName
ORDER BY TotalSpent DESC;

Q240 Generate inventory valuation report.

SELECT Model,
       COUNT(*) AS Quantity,
       AVG(Price) AS AvgPrice,
       MIN(Price) AS MinPrice,
       MAX(Price) AS MaxPrice,
       SUM(Price) AS TotalValue,
       ROUND(SUM(Price) * 100.0 / (SELECT SUM(Price) FROM INVENTORY), 2) AS PercentageValue
FROM INVENTORY 
GROUP BY Model
ORDER BY TotalValue DESC;

Q241 Generate sales by payment mode report.

SELECT PaymentMode,
       COUNT(*) AS TransactionCount,
       SUM(SalePrice) AS TotalAmount,
       AVG(SalePrice) AS AvgAmount,
       ROUND(SUM(SalePrice) * 100.0 / (SELECT SUM(SalePrice) FROM SALE), 2) AS PercentageShare
FROM SALE 
GROUP BY PaymentMode
ORDER BY TotalAmount DESC;

Q242 Generate product performance report.

SELECT I.CarName,
       I.Model,
       COUNT(S.InvoiceNo) AS UnitsSold,
       SUM(S.SalePrice) AS Revenue,
       I.Price AS ListPrice,
       ROUND(AVG(S.SalePrice - I.Price), 2) AS AvgDiscount,
       ROUND(AVG(S.SalePrice) * 100.0 / I.Price, 2) AS PriceAchievement
FROM INVENTORY I 
LEFT JOIN SALE S ON I.CarId = S.CarId 
GROUP BY I.CarId, I.CarName, I.Model, I.Price
ORDER BY Revenue DESC;

Q243 Generate monthly employee performance.

SELECT E.EmpName,
       YEAR(S.SaleDate) AS Year,
       MONTH(S.SaleDate) AS Month,
       COUNT(S.InvoiceNo) AS SalesCount,
       SUM(S.SalePrice) AS TotalSales,
       AVG(S.SalePrice) AS AvgSale
FROM EMPLOYEE E 
JOIN SALE S ON E.EmpID = S.EmpID 
GROUP BY E.EmpName, YEAR(S.SaleDate), MONTH(S.SaleDate)
ORDER BY Year DESC, Month DESC, TotalSales DESC;

Q244 Generate customer segmentation report.

SELECT 
       CASE 
           WHEN TotalSpent >= 1000000 THEN 'Platinum'
           WHEN TotalSpent >= 500000 THEN 'Gold'
           WHEN TotalSpent >= 200000 THEN 'Silver'
           ELSE 'Bronze'
       END AS CustomerSegment,
       COUNT(*) AS CustomerCount,
       SUM(TotalSpent) AS TotalRevenue,
       AVG(TotalSpent) AS AvgSpending
FROM (
    SELECT CustId, SUM(SalePrice) AS TotalSpent
    FROM SALE 
    GROUP BY CustId
) AS CustomerSpending
GROUP BY CustomerSegment
ORDER BY TotalRevenue DESC;

Q245 Generate year-over-year comparison report.

SELECT YEAR(SaleDate) AS Year,
       SUM(SalePrice) AS TotalSales,
       COUNT(*) AS SaleCount,
       AVG(SalePrice) AS AvgSale,
       LAG(SUM(SalePrice), 1) OVER (ORDER BY YEAR(SaleDate)) AS PreviousYearSales,
       ROUND((SUM(SalePrice) - LAG(SUM(SalePrice), 1) OVER (ORDER BY YEAR(SaleDate))) * 100.0 / LAG(SUM(SalePrice), 1) OVER (ORDER BY YEAR(SaleDate)), 2) AS GrowthRate
FROM SALE 
GROUP BY YEAR(SaleDate)
ORDER BY Year;

Q246 Generate daily sales trend report.

SELECT SaleDate,
       COUNT(*) AS SalesCount,
       SUM(SalePrice) AS DailyRevenue,
       AVG(SalePrice) AS AvgSalePrice,
       SUM(SUM(SalePrice)) OVER (ORDER BY SaleDate) AS CumulativeRevenue
FROM SALE 
GROUP BY SaleDate
ORDER BY SaleDate;

Q247 Generate commission report.

SELECT E.EmpName,
       YEAR(S.SaleDate) AS Year,
       MONTH(S.SaleDate) AS Month,
       COUNT(S.InvoiceNo) AS SalesCount,
       SUM(S.Commission) AS TotalCommission,
       AVG(S.Commission) AS AvgCommission
FROM EMPLOYEE E 
JOIN SALE S ON E.EmpID = S.EmpID 
WHERE S.Commission IS NOT NULL
GROUP BY E.EmpName, YEAR(S.SaleDate), MONTH(S.SaleDate)
ORDER BY Year DESC, Month DESC, TotalCommission DESC;

Q248 Generate fuel type performance report.

SELECT I.FuelType,
       COUNT(S.InvoiceNo) AS UnitsSold,
       SUM(S.SalePrice) AS Revenue,
       AVG(S.SalePrice) AS AvgPrice,
       COUNT(DISTINCT S.CustId) AS UniqueCustomers,
       ROUND(SUM(S.SalePrice) * 100.0 / (SELECT SUM(SalePrice) FROM SALE), 2) AS RevenueShare
FROM INVENTORY I 
JOIN SALE S ON I.CarId = S.CarId 
GROUP BY I.FuelType
ORDER BY Revenue DESC;

Q249 Generate employee retention report.

SELECT YEAR(DOJ) AS JoiningYear,
       COUNT(*) AS EmployeesJoined,
       SUM(CASE WHEN Designation LIKE '%Salesman%' THEN 1 ELSE 0 END) AS SalesCount,
       AVG(Salary) AS AvgSalary,
       MAX(Salary) AS MaxSalary,
       MIN(Salary) AS MinSalary
FROM EMPLOYEE 
GROUP BY YEAR(DOJ)
ORDER BY JoiningYear;

Q250 Generate quarterly business report.

SELECT YEAR(SaleDate) AS Year,
       QUARTER(SaleDate) AS Quarter,
       COUNT(*) AS SalesCount,
       SUM(SalePrice) AS TotalRevenue,
       AVG(SalePrice) AS AvgSale,
       COUNT(DISTINCT CustId) AS UniqueCustomers,
       COUNT(DISTINCT EmpID) AS ActiveEmployees
FROM SALE 
GROUP BY YEAR(SaleDate), QUARTER(SaleDate)
ORDER BY Year DESC, Quarter DESC;

SECTION 23: ANALYTICAL FUNCTIONS Q251-Q265

Q251 Find top 3 customers by purchase amount.

SELECT CustId, 
       SUM(SalePrice) AS TotalPurchases,
       DENSE_RANK() OVER (ORDER BY SUM(SalePrice) DESC) AS RankNum
FROM SALE 
GROUP BY CustId 
HAVING RankNum <= 3;

Q252 Calculate percentage contribution per model.

SELECT Model,
       SUM(Price) AS TotalValue,
       ROUND(SUM(Price) * 100.0 / SUM(SUM(Price)) OVER (), 2) AS PercentageShare
FROM INVENTORY 
GROUP BY Model
ORDER BY TotalValue DESC;

Q253 Find customers with more than average purchases.

WITH CustomerStats AS (
    SELECT CustId, 
           COUNT(*) AS PurchaseCount,
           AVG(COUNT(*)) OVER () AS AvgPurchases
    FROM SALE 
    GROUP BY CustId
)
SELECT CustId, PurchaseCount, AvgPurchases
FROM CustomerStats 
WHERE PurchaseCount > AvgPurchases;

Q254 Calculate year-to-date sales.

SELECT EmpID,
       SaleDate,
       SalePrice,
       SUM(SalePrice) OVER (PARTITION BY EmpID ORDER BY SaleDate) AS YTD_Sales
FROM SALE 
WHERE YEAR(SaleDate) = YEAR(CURDATE())
ORDER BY EmpID, SaleDate;

Q255 Find employees with above-average sales.

WITH EmpSales AS (
    SELECT EmpID, 
           SUM(SalePrice) AS TotalSales,
           AVG(SUM(SalePrice)) OVER () AS AvgSales
    FROM SALE 
    GROUP BY EmpID
)
SELECT EmpID, TotalSales, AvgSales
FROM EmpSales 
WHERE TotalSales > AvgSales;

Q256 Calculate month-over-month growth.

WITH MonthlyRevenue AS (
    SELECT DATE_FORMAT(SaleDate, '%Y-%m') AS Month,
           SUM(SalePrice) AS Revenue
    FROM SALE 
    GROUP BY DATE_FORMAT(SaleDate, '%Y-%m')
)
SELECT Month, Revenue,
       LAG(Revenue, 1) OVER (ORDER BY Month) AS PrevMonth,
       ROUND((Revenue - LAG(Revenue, 1) OVER (ORDER BY Month)) * 100.0 / LAG(Revenue, 1) OVER (ORDER BY Month), 2) AS MoMGrowth
FROM MonthlyRevenue
ORDER BY Month;

Q257 Find customer purchase frequency.

SELECT CustId,
       COUNT(*) AS Purchases,
       MIN(SaleDate) AS FirstPurchase,
       MAX(SaleDate) AS LastPurchase,
       DATEDIFF(MAX(SaleDate), MIN(SaleDate)) AS Duration,
       ROUND(COUNT(*) / (DATEDIFF(MAX(SaleDate), MIN(SaleDate)) / 30), 2) AS PurchasesPerMonth
FROM SALE 
GROUP BY CustId
HAVING COUNT(*) > 1
ORDER BY PurchasesPerMonth DESC;

Q258 Find model performance trend.

SELECT YEAR(S.SaleDate) AS Year,
       I.Model,
       COUNT(*) AS SalesCount,
       SUM(S.SalePrice) AS Revenue,
       RANK() OVER (PARTITION BY YEAR(S.SaleDate) ORDER BY SUM(S.SalePrice) DESC) AS ModelRank
FROM SALE S 
JOIN INVENTORY I ON S.CarId = I.CarId 
GROUP BY YEAR(S.SaleDate), I.Model
ORDER BY Year DESC, ModelRank;

Q259 Calculate employee sales efficiency.

SELECT E.EmpName,
       COUNT(S.InvoiceNo) AS SalesCount,
       SUM(S.SalePrice) AS Revenue,
       DATEDIFF(CURDATE(), E.DOJ) AS DaysEmployed,
       ROUND(SUM(S.SalePrice) / DATEDIFF(CURDATE(), E.DOJ), 2) AS RevenuePerDay,
       ROUND(COUNT(S.InvoiceNo) / DATEDIFF(CURDATE(), E.DOJ) * 30, 2) AS SalesPerMonth
FROM EMPLOYEE E 
JOIN SALE S ON E.EmpID = S.EmpID 
GROUP BY E.EmpName, E.DOJ
ORDER BY RevenuePerDay DESC;

Q260 Find seasonal sales patterns.

SELECT MONTHNAME(SaleDate) AS Month,
       YEAR(SaleDate) AS Year,
       SUM(SalePrice) AS Revenue,
       AVG(SUM(SalePrice)) OVER (PARTITION BY MONTH(SaleDate)) AS AvgForMonth,
       RANK() OVER (PARTITION BY YEAR(SaleDate) ORDER BY SUM(SalePrice) DESC) AS MonthRank
FROM SALE 
GROUP BY YEAR(SaleDate), MONTH(SaleDate)
ORDER BY Year, MONTH(SaleDate);

Q261 Find cross-selling opportunities.

SELECT DISTINCT S1.CustId, I1.CarName AS Car1, I2.CarName AS Car2
FROM SALE S1 
JOIN INVENTORY I1 ON S1.CarId = I1.CarId
JOIN SALE S2 ON S1.CustId = S2.CustId AND S1.CarId != S2.CarId
JOIN INVENTORY I2 ON S2.CarId = I2.CarId
WHERE I1.CarName < I2.CarName
ORDER BY S1.CustId;

Q262 Calculate customer churn risk.

SELECT CustId,
       MAX(SaleDate) AS LastPurchase,
       DATEDIFF(CURDATE(), MAX(SaleDate)) AS DaysSinceLast,
       CASE 
           WHEN DATEDIFF(CURDATE(), MAX(SaleDate)) > 180 THEN 'High Risk'
           WHEN DATEDIFF(CURDATE(), MAX(SaleDate)) > 90 THEN 'Medium Risk'
           ELSE 'Low Risk'
       END AS ChurnRisk
FROM SALE 
GROUP BY CustId
ORDER BY DaysSinceLast DESC;

Q263 Find price elasticity of demand.

SELECT Model,
       AVG(Price) AS AvgPrice,
       COUNT(*) AS Quantity,
       ROUND(COUNT(*) / AVG(Price), 4) AS DemandElasticity
FROM INVENTORY 
GROUP BY Model
ORDER BY DemandElasticity DESC;

Q264 Calculate sales velocity.

SELECT I.CarName,
       COUNT(S.InvoiceNo) AS UnitsSold,
       DATEDIFF(MAX(S.SaleDate), MIN(S.SaleDate)) AS SalesPeriod,
       ROUND(COUNT(S.InvoiceNo) / NULLIF(DATEDIFF(MAX(S.SaleDate), MIN(S.SaleDate)), 0) * 30, 2) AS SalesPerMonth
FROM INVENTORY I 
JOIN SALE S ON I.CarId = S.CarId 
GROUP BY I.CarName
ORDER BY SalesPerMonth DESC;

Q265 Find top sales days.

SELECT SaleDate,
       COUNT(*) AS SalesCount,
       SUM(SalePrice) AS Revenue,
       DAYNAME(SaleDate) AS DayOfWeek,
       RANK() OVER (ORDER BY SUM(SalePrice) DESC) AS RevenueRank
FROM SALE 
GROUP BY SaleDate
ORDER BY RevenueRank
LIMIT 10;

SECTION 24: ADVANCED JOINS Q266-Q280

Q266 Self-join to find customers with multiple purchases.

SELECT DISTINCT S1.CustId, S1.SaleDate AS FirstPurchase, S2.SaleDate AS SecondPurchase
FROM SALE S1 
JOIN SALE S2 ON S1.CustId = S2.CustId AND S1.SaleDate < S2.SaleDate
WHERE NOT EXISTS (
    SELECT 1 FROM SALE S3 
    WHERE S3.CustId = S1.CustId AND S3.SaleDate > S1.SaleDate AND S3.SaleDate < S2.SaleDate
);

Q267 Full outer join simulation for inventory and sales.

SELECT I.CarId, I.CarName, I.Price, COUNT(S.InvoiceNo) AS SalesCount
FROM INVENTORY I 
LEFT JOIN SALE S ON I.CarId = S.CarId 
GROUP BY I.CarId, I.CarName, I.Price
UNION ALL
SELECT S.CarId, NULL, NULL, COUNT(*) 
FROM SALE S 
LEFT JOIN INVENTORY I ON S.CarId = I.CarId 
WHERE I.CarId IS NULL
GROUP BY S.CarId;

Q268 Join across all four tables.

SELECT S.InvoiceNo, S.SaleDate, C.CustName, E.EmpName, I.CarName, I.Model, S.SalePrice
FROM SALE S 
JOIN CUSTOMER C ON S.CustId = C.CustId 
JOIN EMPLOYEE E ON S.EmpID = E.EmpID 
JOIN INVENTORY I ON S.CarId = I.CarId
ORDER BY S.SaleDate DESC;

Q269 Find customers who bought same car model.

SELECT C.CustName, I.Model, COUNT(*) AS Purchases
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
JOIN INVENTORY I ON S.CarId = I.CarId 
GROUP BY C.CustName, I.Model
HAVING COUNT(*) > 1;

Q270 Self-join to find employee-employee sales comparison.

SELECT E1.EmpName AS Employee1, E2.EmpName AS Employee2,
       SUM(S1.SalePrice) AS Sales1, SUM(S2.SalePrice) AS Sales2
FROM EMPLOYEE E1 
JOIN SALE S1 ON E1.EmpID = S1.EmpID
JOIN EMPLOYEE E2 ON E1.EmpID < E2.EmpID
JOIN SALE S2 ON E2.EmpID = S2.EmpID
WHERE YEAR(S1.SaleDate) = YEAR(S2.SaleDate)
GROUP BY E1.EmpName, E2.EmpName
ORDER BY Sales1 DESC;

Q271 Multi-table join with aggregations.

SELECT C.CustName,
       COUNT(S.InvoiceNo) AS TotalPurchases,
       SUM(S.SalePrice) AS TotalSpent,
       AVG(S.SalePrice) AS AvgPurchase,
       GROUP_CONCAT(DISTINCT I.CarName) AS CarsPurchased
FROM CUSTOMER C 
LEFT JOIN SALE S ON C.CustId = S.CustId 
LEFT JOIN INVENTORY I ON S.CarId = I.CarId 
GROUP BY C.CustName
ORDER BY TotalSpent DESC;

Q272 Find employees with no sales.

SELECT E.EmpName, E.Designation
FROM EMPLOYEE E 
LEFT JOIN SALE S ON E.EmpID = S.EmpID 
WHERE S.EmpID IS NULL;

Q273 Join to find most popular car per customer.

WITH CustomerCars AS (
    SELECT C.CustName, I.CarName, COUNT(*) AS PurchaseCount,
           RANK() OVER (PARTITION BY C.CustName ORDER BY COUNT(*) DESC) AS RankNum
    FROM CUSTOMER C 
    JOIN SALE S ON C.CustId = S.CustId 
    JOIN INVENTORY I ON S.CarId = I.CarId 
    GROUP BY C.CustName, I.CarName
)
SELECT CustName, CarName, PurchaseCount
FROM CustomerCars 
WHERE RankNum = 1;

Q274 Three-table join with conditions.

SELECT S.InvoiceNo, C.CustName, E.EmpName, I.CarName, S.SalePrice
FROM SALE S 
INNER JOIN CUSTOMER C ON S.CustId = C.CustId 
INNER JOIN EMPLOYEE E ON S.EmpID = E.EmpID 
INNER JOIN INVENTORY I ON S.CarId = I.CarId 
WHERE S.SalePrice > (SELECT AVG(SalePrice) FROM SALE)
AND YEAR(S.SaleDate) = 2019
ORDER BY S.SalePrice DESC;

Q275 Join with subquery in FROM clause.

SELECT C.CustName, Agg.TotalSpent, Agg.PurchaseCount
FROM CUSTOMER C 
JOIN (
    SELECT CustId, SUM(SalePrice) AS TotalSpent, COUNT(*) AS PurchaseCount
    FROM SALE 
    GROUP BY CustId
) Agg ON C.CustId = Agg.CustId
WHERE Agg.TotalSpent > 500000;

Q276 Find cars that customers bought together.

SELECT DISTINCT C.CustName, I1.CarName AS Car1, I2.CarName AS Car2
FROM CUSTOMER C 
JOIN SALE S1 ON C.CustId = S1.CustId 
JOIN INVENTORY I1 ON S1.CarId = I1.CarId
JOIN SALE S2 ON C.CustId = S2.CustId AND S1.InvoiceNo != S2.InvoiceNo
JOIN INVENTORY I2 ON S2.CarId = I2.CarId
WHERE I1.CarName < I2.CarName
ORDER BY C.CustName;

Q277 Join to find employee customer preferences.

SELECT E.EmpName, C.CustName, COUNT(*) AS Interactions
FROM EMPLOYEE E 
JOIN SALE S ON E.EmpID = S.EmpID 
JOIN CUSTOMER C ON S.CustId = C.CustId 
GROUP BY E.EmpName, C.CustName
HAVING COUNT(*) > 1
ORDER BY Interactions DESC;

Q278 Join with date range conditions.

SELECT C.CustName, I.CarName, S.SaleDate
FROM CUSTOMER C 
JOIN SALE S ON C.CustId = S.CustId 
JOIN INVENTORY I ON S.CarId = I.CarId 
WHERE S.SaleDate BETWEEN '2019-01-01' AND '2019-12-31'
AND I.FuelType = 'Petrol'
ORDER BY S.SaleDate;

Q279 Find sales with employee and customer details.

SELECT S.InvoiceNo, S.SaleDate, E.EmpName AS SalesPerson, C.CustName AS Customer,
       I.CarName, S.SalePrice, S.PaymentMode
FROM SALE S 
JOIN EMPLOYEE E ON S.EmpID = E.EmpID 
JOIN CUSTOMER C ON S.CustId = C.CustId 
JOIN INVENTORY I ON S.CarId = I.CarId
WHERE S.SalePrice > 500000
ORDER BY S.SaleDate DESC;

Q280 Complex join with multiple aggregations.

SELECT E.EmpName,
       COUNT(DISTINCT C.CustId) AS UniqueCustomers,
       COUNT(DISTINCT I.CarId) AS UniqueCarsSold,
       COUNT(S.InvoiceNo) AS TotalSales,
       SUM(S.SalePrice) AS Revenue,
       AVG(S.SalePrice) AS AvgSale
FROM EMPLOYEE E 
JOIN SALE S ON E.EmpID = S.EmpID 
JOIN CUSTOMER C ON S.CustId = C.CustId 
JOIN INVENTORY I ON S.CarId = I.CarId 
GROUP BY E.EmpName
ORDER BY Revenue DESC;

SECTION 25: PERFORMANCE TUNING Q281-Q295

Q281 Optimize slow query with proper indexing.

-- Before optimization
SELECT * FROM SALE WHERE YEAR(SaleDate) = 2019;

-- After optimization (use range condition)
SELECT * FROM SALE WHERE SaleDate BETWEEN '2019-01-01' AND '2019-12-31';

-- Add index on SaleDate
CREATE INDEX idx_sale_date ON SALE(SaleDate);

Q282 Use covering index for query performance.

-- Create covering index
CREATE INDEX idx_covering ON SALE(EmpID, SaleDate, SalePrice);

-- Query that can use covering index
SELECT EmpID, SaleDate, SalePrice 
FROM SALE 
WHERE EmpID = 'E001' AND SaleDate > '2019-01-01';

Q283 Optimize JOIN query with proper indexes.

-- Create indexes on foreign keys
CREATE INDEX idx_sale_carid ON SALE(CarId);
CREATE INDEX idx_sale_custid ON SALE(CustId);
CREATE INDEX idx_sale_empid ON SALE(EmpID);

-- Optimized query
SELECT S.*, I.CarName, C.CustName 
FROM SALE S 
JOIN INVENTORY I ON S.CarId = I.CarId 
JOIN CUSTOMER C ON S.CustId = C.CustId 
WHERE S.SaleDate > '2020-01-01';

Q284 Use query rewriting for better performance.

-- Slow query with NOT IN
SELECT * FROM INVENTORY 
WHERE CarId NOT IN (SELECT DISTINCT CarId FROM SALE);

-- Optimized using NOT EXISTS
SELECT I.* FROM INVENTORY I 
WHERE NOT EXISTS (SELECT 1 FROM SALE S WHERE S.CarId = I.CarId);

Q285 Use LIMIT for pagination optimization.

-- Pagination query
SELECT * FROM SALE 
ORDER BY SaleDate DESC 
LIMIT 100 OFFSET 1000;

-- Better with seek method
SELECT * FROM SALE 
WHERE SaleDate < '2020-01-15' 
ORDER BY SaleDate DESC 
LIMIT 100;

Q286 Optimize COUNT queries.

-- Slow COUNT with condition
SELECT COUNT(*) FROM SALE WHERE PaymentMode = 'Cash';

-- Optimized with index on PaymentMode
CREATE INDEX idx_payment ON SALE(PaymentMode);
SELECT COUNT(*) FROM SALE WHERE PaymentMode = 'Cash';

Q287 Use batch processing for large updates.

-- Batch update procedure
DELIMITER //
CREATE PROCEDURE BatchUpdateCommissions(IN batch_size INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_count INT DEFAULT 0;
    
    REPEAT
        UPDATE SALE 
        SET Commission = SalePrice * 0.12 
        WHERE Commission IS NULL 
        LIMIT batch_size;
        
        SET batch_count = ROW_COUNT();
    UNTIL batch_count = 0 END REPEAT;
END //
DELIMITER ;

CALL BatchUpdateCommissions(1000);

Q288 Use partition pruning for better performance.

-- Create partitioned table
CREATE TABLE SALE_BY_YEAR (
    InvoiceNo VARCHAR(10),
    CarId VARCHAR(10),
    SaleDate DATE,
    SalePrice DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(SaleDate)) (
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

-- Query that uses partition pruning
SELECT * FROM SALE_BY_YEAR 
WHERE SaleDate BETWEEN '2019-01-01' AND '2019-12-31';

Q289 Use materialized views for frequent queries.

-- Create a summary table
CREATE TABLE DailySalesSummary AS
SELECT SaleDate, 
       COUNT(*) AS TotalSales,
       SUM(SalePrice) AS Revenue,
       AVG(SalePrice) AS AvgPrice
FROM SALE 
GROUP BY SaleDate;

-- Add index on the summary table
CREATE INDEX idx_summary_date ON DailySalesSummary(SaleDate);

-- Query the summary instead of base table
SELECT * FROM DailySalesSummary 
WHERE SaleDate BETWEEN '2020-01-01' AND '2020-01-31';

Q290 Optimize LIKE queries with full-text search.

-- Slow LIKE query
SELECT * FROM CUSTOMER WHERE CustName LIKE '%singh%';

-- Create full-text index
ALTER TABLE CUSTOMER ADD FULLTEXT INDEX ft_name (CustName);

-- Use full-text search
SELECT * FROM CUSTOMER 
WHERE MATCH(CustName) AGAINST('singh' IN NATURAL LANGUAGE MODE);

Q291 Use derived tables for complex aggregations.

SELECT D.EmpID, D.SaleYear, D.TotalSales
FROM (
    SELECT EmpID, YEAR(SaleDate) AS SaleYear, SUM(SalePrice) AS TotalSales
    FROM SALE 
    GROUP BY EmpID, YEAR(SaleDate)
) D
WHERE D.TotalSales > 500000
ORDER BY D.TotalSales DESC;

Q292 Optimize OR conditions with UNION.

-- Slow OR query
SELECT * FROM INVENTORY 
WHERE CarName = 'SWIFT' OR CarName = 'BALENO';

-- Optimized with UNION
SELECT * FROM INVENTORY WHERE CarName = 'SWIFT'
UNION ALL
SELECT * FROM INVENTORY WHERE CarName = 'BALENO';

Q293 Use EXISTS for existence checks.

-- Slow with COUNT
SELECT * FROM CUSTOMER C 
WHERE (SELECT COUNT(*) FROM SALE S WHERE S.CustId = C.CustId) > 0;

-- Optimized with EXISTS
SELECT * FROM CUSTOMER C 
WHERE EXISTS (SELECT 1 FROM SALE S WHERE S.CustId = C.CustId);

Q294 Use CASE for conditional aggregations.

-- Slow with multiple queries
SELECT 
    (SELECT SUM(SalePrice) FROM SALE WHERE PaymentMode = 'Cash') AS CashTotal,
    (SELECT SUM(SalePrice) FROM SALE WHERE PaymentMode = 'Credit Card') AS CreditTotal;

-- Optimized with CASE
SELECT 
    SUM(CASE WHEN PaymentMode = 'Cash' THEN SalePrice ELSE 0 END) AS CashTotal,
    SUM(CASE WHEN PaymentMode = 'Credit Card' THEN SalePrice ELSE 0 END) AS CreditTotal
FROM SALE;

Q295 Use SHOW PROFILE for query analysis.

-- Enable profiling
SET profiling = 1;

-- Run your query
SELECT * FROM SALE S 
JOIN CUSTOMER C ON S.CustId = C.CustId 
WHERE S.SalePrice > 500000;

-- Show profile
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

SECTION 26: FINAL CHALLENGE Q296-Q305

Q296 Complete business intelligence dashboard query.

SELECT 
    'Total Revenue' AS Metric,
    FORMAT(SUM(SalePrice), 0) AS Value,
    CONCAT(ROUND((SUM(SalePrice) - LAG(SUM(SalePrice), 1) OVER ()) * 100.0 / LAG(SUM(SalePrice), 1) OVER (), 1), '%') AS YoY_Growth
FROM SALE 
WHERE YEAR(SaleDate) >= YEAR(CURDATE()) - 1
UNION ALL
SELECT 'Active Customers', COUNT(DISTINCT CustId), NULL
FROM SALE 
WHERE YEAR(SaleDate) = YEAR(CURDATE())
UNION ALL
SELECT 'Cars Sold', COUNT(*), NULL
FROM SALE 
WHERE YEAR(SaleDate) = YEAR(CURDATE())
UNION ALL
SELECT 'Top Employee', EmpName, NULL
FROM (
    SELECT E.EmpName, SUM(S.SalePrice) AS TotalSales,
           RANK() OVER (ORDER BY SUM(S.SalePrice) DESC) AS RankNum
    FROM EMPLOYEE E 
    JOIN SALE S ON E.EmpID = S.EmpID 
    WHERE YEAR(S.SaleDate) = YEAR(CURDATE())
    GROUP BY E.EmpName
) T WHERE RankNum = 1
UNION ALL
SELECT 'Top Customer', CustName, NULL
FROM (
    SELECT C.CustName, SUM(S.SalePrice) AS TotalSpent,
           RANK() OVER (ORDER BY SUM(S.SalePrice) DESC) AS RankNum
    FROM CUSTOMER C 
    JOIN SALE S ON C.CustId = S.CustId 
    WHERE YEAR(S.SaleDate) = YEAR(CURDATE())
    GROUP BY C.CustName
) T WHERE RankNum = 1;

Q297 Complete sales forecasting query.

WITH MonthlyTrend AS (
    SELECT DATE_FORMAT(SaleDate, '%Y-%m') AS Month,
           SUM(SalePrice) AS Revenue,
           ROW_NUMBER() OVER (ORDER BY DATE_FORMAT(SaleDate, '%Y-%m')) AS RowNum
    FROM SALE 
    WHERE SaleDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    GROUP BY DATE_FORMAT(SaleDate, '%Y-%m')
),
Trendline AS (
    SELECT 
        AVG(Revenue) AS AvgRevenue,
        (SUM(RowNum * Revenue) - SUM(RowNum) * AVG(Revenue)) / 
        (SUM(RowNum * RowNum) - SUM(RowNum) * AVG(RowNum)) AS Slope
    FROM MonthlyTrend
)
SELECT 
    CONCAT('Next Month Forecast: ₹', 
           FORMAT(AvgRevenue + Slope * (MAX(RowNum) + 1), 0)) AS Forecast
FROM MonthlyTrend, Trendline;

Q298 Complete customer segmentation analysis.

WITH CustomerSegments AS (
    SELECT CustId,
           SUM(SalePrice) AS TotalSpent,
           COUNT(*) AS PurchaseCount,
           AVG(SalePrice) AS AvgPurchase,
           DATEDIFF(CURDATE(), MAX(SaleDate)) AS DaysSinceLast,
           NTILE(4) OVER (ORDER BY SUM(SalePrice) DESC) AS SpendingTier,
           NTILE(4) OVER (ORDER BY COUNT(*) DESC) AS FrequencyTier
    FROM SALE 
    GROUP BY CustId
)
SELECT 
    CASE 
        WHEN SpendingTier <= 2 AND FrequencyTier <= 2 THEN 'VIP'
        WHEN SpendingTier <= 2 THEN 'High Spender'
        WHEN FrequencyTier <= 2 THEN 'Frequent Buyer'
        ELSE 'Regular'
    END AS Segment,
    COUNT(*) AS CustomerCount,
    AVG(TotalSpent) AS AvgLifetimeValue,
    AVG(PurchaseCount) AS AvgPurchases,
    AVG(DaysSinceLast) AS AvgDaysSinceLast
FROM CustomerSegments
GROUP BY Segment
ORDER BY AvgLifetimeValue DESC;

Q299 Complete product recommendation system.

WITH PurchasedPairs AS (
    SELECT S1.CustId, S1.CarId AS Car1, S2.CarId AS Car2, COUNT(*) AS Frequency
    FROM SALE S1 
    JOIN SALE S2 ON S1.CustId = S2.CustId AND S1.CarId < S2.CarId
    GROUP BY S1.CustId, S1.CarId, S2.CarId
),
ProductAffinity AS (
    SELECT Car1, Car2, 
           COUNT(DISTINCT CustId) AS SharedCustomers,
           ROUND(COUNT(DISTINCT CustId) * 100.0 / 
                 (SELECT COUNT(DISTINCT CustId) FROM SALE WHERE CarId = Car1), 2) AS AffinityScore
    FROM PurchasedPairs
    GROUP BY Car1, Car2
    HAVING SharedCustomers > 1
)
SELECT I1.CarName AS Product, I2.CarName AS RecommendedProduct, PA.AffinityScore
FROM ProductAffinity PA 
JOIN INVENTORY I1 ON PA.Car1 = I1.CarId
JOIN INVENTORY I2 ON PA.Car2 = I2.CarId
ORDER BY PA.AffinityScore DESC
LIMIT 20;

Q300 Complete anomaly detection query.

WITH DailyStats AS (
    SELECT SaleDate,
           SUM(SalePrice) AS DailyRevenue,
           AVG(SUM(SalePrice)) OVER (ORDER BY SaleDate ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS MovingAvg,
           STD(SUM(SalePrice)) OVER (ORDER BY SaleDate ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS MovingStd
    FROM SALE 
    GROUP BY SaleDate
)
SELECT SaleDate, DailyRevenue, MovingAvg, MovingStd,
       CASE 
           WHEN ABS(DailyRevenue - MovingAvg) > 3 * MovingStd THEN 'Critical Anomaly'
           WHEN ABS(DailyRevenue - MovingAvg) > 2 * MovingStd THEN 'Potential Anomaly'
           ELSE 'Normal'
       END AS AnomalyStatus
FROM DailyStats
WHERE MovingAvg IS NOT NULL
ORDER BY SaleDate DESC;

Q301 Complete employee performance scorecard.

WITH EmployeeMetrics AS (
    SELECT E.EmpID, E.EmpName,
           COUNT(S.InvoiceNo) AS SalesCount,
           SUM(S.SalePrice) AS TotalRevenue,
           AVG(S.SalePrice) AS AvgSale,
           COUNT(DISTINCT S.CustId) AS UniqueCustomers,
           SUM(S.Commission) AS TotalCommission
    FROM EMPLOYEE E 
    LEFT JOIN SALE S ON E.EmpID = S.EmpID 
    GROUP BY E.EmpID, E.EmpName
),
Scorecard AS (
    SELECT *,
           PERCENT_RANK() OVER (ORDER BY TotalRevenue DESC) * 100 AS RevenueScore,
           PERCENT_RANK() OVER (ORDER BY SalesCount DESC) * 100 AS VolumeScore,
           PERCENT_RANK() OVER (ORDER BY AvgSale DESC) * 100 AS EfficiencyScore
    FROM EmployeeMetrics
)
SELECT EmpName,
       TotalRevenue,
       SalesCount,
       AvgSale,
       ROUND((RevenueScore + VolumeScore + EfficiencyScore) / 3, 2) AS OverallScore,
       CASE 
           WHEN (RevenueScore + VolumeScore + EfficiencyScore) / 3 >= 80 THEN 'Top Performer'
           WHEN (RevenueScore + VolumeScore + EfficiencyScore) / 3 >= 60 THEN 'Strong Performer'
           WHEN (RevenueScore + VolumeScore + EfficiencyScore) / 3 >= 40 THEN 'Adequate Performer'
           ELSE 'Needs Improvement'
       END AS PerformanceRating
FROM Scorecard
ORDER BY OverallScore DESC;

Q302 Complete market basket analysis.

WITH Basket AS (
    SELECT S1.CustId, S1.CarId AS CarA, S2.CarId AS CarB
    FROM SALE S1 
    JOIN SALE S2 ON S1.CustId = S2.CustId AND S1.CarId < S2.CarId
),
BasketStats AS (
    SELECT CarA, CarB,
           COUNT(*) AS Frequency,
           (SELECT COUNT(DISTINCT CustId) FROM SALE WHERE CarId = CarA) AS SupportA,
           (SELECT COUNT(DISTINCT CustId) FROM SALE WHERE CarId = CarB) AS SupportB,
           (SELECT COUNT(DISTINCT CustId) FROM SALE) AS TotalCustomers
    FROM Basket
    GROUP BY CarA, CarB
)
SELECT I1.CarName AS ProductA, I2.CarName AS ProductB,
       ROUND(Frequency * 100.0 / SupportA, 2) AS Confidence_A_to_B,
       ROUND(Frequency * 100.0 / TotalCustomers, 2) AS Support,
       ROUND((Frequency * 1.0 / TotalCustomers) / (SupportA * 1.0 / TotalCustomers * SupportB * 1.0 / TotalCustomers), 2) AS Lift
FROM BasketStats BS 
JOIN INVENTORY I1 ON BS.CarA = I1.CarId
JOIN INVENTORY I2 ON BS.CarB = I2.CarId
WHERE Frequency > 2
ORDER BY Lift DESC
LIMIT 20;

Q303 Complete sales pipeline analysis.

SELECT 
    CASE 
        WHEN DATEDIFF(CURDATE(), SaleDate) <= 30 THEN 'Hot Lead'
        WHEN DATEDIFF(CURDATE(), SaleDate) <= 90 THEN 'Warm Lead'
        WHEN DATEDIFF(CURDATE(), SaleDate) <= 180 THEN 'Cold Lead'
        ELSE 'Lost'
    END AS PipelineStage,
    COUNT(*) AS Opportunities,
    SUM(SalePrice) AS PotentialValue,
    AVG(SalePrice) AS AvgDealSize,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM SALE), 2) AS Percentage
FROM SALE 
WHERE YEAR(SaleDate) = YEAR(CURDATE())
GROUP BY PipelineStage
ORDER BY FIELD(PipelineStage, 'Hot Lead', 'Warm Lead', 'Cold Lead', 'Lost');

Q304 Complete financial performance analysis.

SELECT 
    YEAR(SaleDate) AS FiscalYear,
    COUNT(*) AS UnitsSold,
    SUM(SalePrice) AS GrossRevenue,
    SUM(I.Price * 0.70) AS COGS,
    SUM(SalePrice) - SUM(I.Price * 0.70) AS GrossProfit,
    ROUND((SUM(SalePrice) - SUM(I.Price * 0.70)) * 100.0 / SUM(SalePrice), 2) AS GrossMargin,
    SUM(S.Commission) AS TotalCommission,
    (SUM(SalePrice) - SUM(I.Price * 0.70) - SUM(S.Commission)) AS NetProfit,
    ROUND((SUM(SalePrice) - SUM(I.Price * 0.70) - SUM(S.Commission)) * 100.0 / SUM(SalePrice), 2) AS NetMargin
FROM SALE S 
JOIN INVENTORY I ON S.CarId = I.CarId 
LEFT JOIN EMPLOYEE E ON S.EmpID = E.EmpID 
GROUP BY YEAR(SaleDate)
ORDER BY FiscalYear;

Q305 Complete data governance and quality report.

SELECT 'Data Quality Report' AS ReportTitle, CURDATE() AS ReportDate
UNION ALL
SELECT 'Table', 'Total Records', 'Missing Values', 'Unique Values', 'Data Completeness'
UNION ALL
SELECT 'CUSTOMER', 
       CAST(COUNT(*) AS CHAR), 
       CAST(SUM(CASE WHEN CustName IS NULL OR Phone IS NULL THEN 1 ELSE 0 END) AS CHAR),
       CAST(COUNT(DISTINCT CustId) AS CHAR),
       CONCAT(ROUND(100 - SUM(CASE WHEN CustName IS NULL OR Phone IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%')
FROM CUSTOMER
UNION ALL
SELECT 'INVENTORY',
       CAST(COUNT(*) AS CHAR),
       CAST(SUM(CASE WHEN Price IS NULL OR CarName IS NULL THEN 1 ELSE 0 END) AS CHAR),
       CAST(COUNT(DISTINCT CarId) AS CHAR),
       CONCAT(ROUND(100 - SUM(CASE WHEN Price IS NULL OR CarName IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%')
FROM INVENTORY
UNION ALL
SELECT 'SALE',
       CAST(COUNT(*) AS CHAR),
       CAST(SUM(CASE WHEN SalePrice IS NULL OR PaymentMode IS NULL THEN 1 ELSE 0 END) AS CHAR),
       CAST(COUNT(DISTINCT InvoiceNo) AS CHAR),
       CONCAT(ROUND(100 - SUM(CASE WHEN SalePrice IS NULL OR PaymentMode IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%')
FROM SALE
UNION ALL
SELECT 'EMPLOYEE',
       CAST(COUNT(*) AS CHAR),
       CAST(SUM(CASE WHEN EmpName IS NULL OR Salary IS NULL THEN 1 ELSE 0 END) AS CHAR),
       CAST(COUNT(DISTINCT EmpID) AS CHAR),
       CONCAT(ROUND(100 - SUM(CASE WHEN EmpName IS NULL OR Salary IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%')
FROM EMPLOYEE;

🏁 END OF DOCUMENT

Total Questions: 305 | Database: CARSHOWROOM

Difficulty: Beginner to Advanced | Based on NCERT Class 12 Informatics Practices

Chapter 9: Structured Query Language (SQL) | MySQL RDBMS

📊 26 Sections | 🏆 From Basic DDL to Advanced Analytics

Rprogrammingstatistics

SQL 300 Question and Answer

📚 SQL QUESTIONS AND ANSWERS

📑 TABLE OF CONTENTS

SECTION 1: DATA DEFINITION LANGUAGE (DDL) Q1-Q15

SECTION 2: DATA MANIPULATION LANGUAGE (INSERT) Q16-Q25

SECTION 3: SELECT STATEMENTS (BASIC) Q26-Q40

SECTION 4: SELECT WITH WHERE CLAUSE Q41-Q55

SECTION 5: ORDER BY, GROUP BY, HAVING Q56-Q70

SECTION 6: SINGLE ROW FUNCTIONS Q71-Q85

SECTION 7: JOIN OPERATIONS Q86-Q95

SECTION 8: ADVANCED QUERIES Q96-Q100

SECTION 9: COMPLEX DDL OPERATIONS Q101-Q110

SECTION 10: COMPLEX INSERT OPERATIONS Q111-Q120

SECTION 11: ADVANCED SELECT WITH COMPLEX WHERE Q121-Q130

SECTION 12: COMPLEX AGGREGATE FUNCTIONS Q131-Q140

SECTION 13: ADVANCED STRING AND DATE FUNCTIONS Q141-Q150

SECTION 14: ADVANCED JOINS AND SUBQUERIES Q151-Q160

SECTION 15: SET OPERATIONS AND CTEs Q161-Q170

SECTION 16: PIVOT AND WINDOW FUNCTIONS Q171-Q180

SECTION 17: COMPLEX UPDATE AND DELETE Q181-Q190

SECTION 18: PERFORMANCE AND OPTIMIZATION Q191-Q200

SECTION 19: BONUS - TRIGGERS & MORE Q201-Q205

SECTION 20: ADVANCED REPORTING & ANALYTICS Q206-Q220

SECTION 21: COMPLEX DATA VALIDATION Q221-Q235

SECTION 22: COMPLEX REPORTS Q236-Q250

SECTION 23: ANALYTICAL FUNCTIONS Q251-Q265

SECTION 24: ADVANCED JOINS Q266-Q280

SECTION 25: PERFORMANCE TUNING Q281-Q295

SECTION 26: FINAL CHALLENGE Q296-Q305

🏁 END OF DOCUMENT

Posted by Manibhushan

You may like these posts

Post a Comment

0 Comments

Social Plugin

Shop Here

Popular Posts

Online Voice to text Generator for free 2022

Log-likelihood function

Stochastic processes

Facebook

About Me

Featured Post

SQL 300 Question and Answer

Contact Form

R Programming Statistics

Search This Blog

Footer Menu Widget