LeetCodeSQLPractice-Session2.sql

#LEETCODE SESSION2 JUN21-2025

#SALES AND PRODUCT TABLEs
CREATE TABLE IF NOT EXISTS Sales (
    sale_id INT,
    product_id INT,
    year INT,
    quantity INT,
    price INT
);

CREATE TABLE IF NOT EXISTS Product (
    product_id INT,
    product_name VARCHAR(10)
);

TRUNCATE TABLE Sales;

INSERT INTO Sales (sale_id, product_id, year, quantity, price) VALUES 
    (1, 100, 2008, 10, 5000),
    (2, 100, 2009, 12, 5000),
    (7, 200, 2011, 15, 9000);

TRUNCATE TABLE Product;

INSERT INTO Product (product_id, product_name) VALUES 
    (100, 'Nokia'),
    (200, 'Apple'),
    (300, 'Samsung');
#1068. Product Sales Analysis I
/*
Write a solution to report the product_name, year, and price for each sale_id in the Sales table.
*/
SELECT Product.product_name, year, price
FROM Sales
JOIN Product on Product.Product_id=Sales.Product_id;

#VISITS AND TRANSACTIONS TABLE
CREATE TABLE IF NOT EXISTS Visits (
    visit_id INT,
    customer_id INT
);

CREATE TABLE IF NOT EXISTS Transactions (
    transaction_id INT,
    visit_id INT,
    amount INT
);

TRUNCATE TABLE Visits;

INSERT INTO Visits (visit_id, customer_id) VALUES 
    (1, 23),
    (2, 9),
    (4, 30),
    (5, 54),
    (6, 96),
    (7, 54),
    (8, 54);

TRUNCATE TABLE Transactions;

INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES 
    (2, 5, 310),
    (3, 5, 300),
    (9, 5, 200),
    (12, 1, 910),
    (13, 2, 970);
/*
1581. Customer Who Visited but Did Not Make Any Transactions

Write a solution to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.

*/
# Write your MySQL query statement below
SELECT 
    v.customer_id,
    COUNT(*) AS count_no_trans
FROM 
    Visits v
LEFT JOIN 
    Transactions t 
ON 
    v.visit_id = t.visit_id
WHERE 
    t.transaction_id IS NULL
GROUP BY 
    v.customer_id;



#WEATHER TABLE
-- Ensure you're using the correct database
USE health;

-- Create the Weather table
CREATE TABLE IF NOT EXISTS Weather (
    id INT,
    recordDate DATE,
    temperature INT
);

-- Clear previous data
TRUNCATE TABLE Weather;

-- Insert records
INSERT INTO Weather (id, recordDate, temperature) VALUES 
    (1, '2015-01-01', 10),
    (2, '2015-01-02', 25),
    (3, '2015-01-03', 20),
    (4, '2015-01-04', 30);


#Write a solution to find all dates' id with higher temperatures compared to its previous dates (yesterday).


SELECT w1.id
FROM Weather w1
JOIN Weather w2 
  ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;



#BONUS and EMPLOYEE TABLEs
CREATE TABLE IF NOT EXISTS Employee (
    empId INT,
    name VARCHAR(255),
    supervisor INT,
    salary INT
);

CREATE TABLE IF NOT EXISTS Bonus (
    empId INT,
    bonus INT
);

TRUNCATE TABLE Employee;

INSERT INTO Employee (empId, name, supervisor, salary) VALUES 
    (3, 'Brad', NULL, 4000),
    (1, 'John', 3, 1000),
    (2, 'Dan', 3, 2000),
    (4, 'Thomas', 3, 4000);

TRUNCATE TABLE Bonus;

INSERT INTO Bonus (empId, bonus) VALUES 
    (2, 500),
    (4, 2000);



/*
Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.


*/

SELECT e.name, b.bonus AS bonus
FROM Employee e
LEFT JOIN Bonus b ON e.empId = b.empId
WHERE b.bonus < 1000 or bonus is null
;