Solution.txt

-- Q1. SELECT with WHERE, AND, DISTINCT, LIKE

-- a) Employee number, first name and last name of Sales Rep reporting to employee 1102

SELECT employeeNumber, firstName, lastName
FROM employees
WHERE jobTitle = 'Sales Rep'
  AND reportsTo = 1102;

-- b) Unique productline values containing the word 'cars' at the end
SELECT DISTINCT productLine
FROM products
WHERE productLine LIKE '%cars';

-- Q2. CASE STATEMENTS

SELECT customerNumber, customerName,
  CASE
    WHEN country IN ('USA', 'Canada') THEN 'North America'
    WHEN country IN ('UK', 'France', 'Germany') THEN 'Europe'
    ELSE 'Other'
  END AS CustomerSegment
FROM customers;

-- Q3. Group By, Aggregations, Date & Time

-- a) Top 10 products by total order quantity
SELECT productCode, SUM(quantityOrdered) AS total_quantity
FROM orderdetails
GROUP BY productCode
ORDER BY total_quantity DESC
LIMIT 10;

-- b) Payment frequency by month, only months > 20 payments
SELECT MONTHNAME(paymentDate) AS MonthName,
       COUNT(*) AS PaymentCount
FROM payments
GROUP BY MONTHNAME(paymentDate)
HAVING COUNT(*) > 20
ORDER BY PaymentCount DESC;

-- Q4. Constraints: Create Customers_Orders DB

CREATE DATABASE IF NOT EXISTS Customers_Orders;
USE Customers_Orders;

CREATE TABLE Customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone_number VARCHAR(20)
);

CREATE TABLE Orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2) CHECK (total_amount > 0),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

-- Q5. Joins

-- Top 5 countries by order count
SELECT c.country, COUNT(o.orderNumber) AS order_count
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY c.country
ORDER BY order_count DESC
LIMIT 5;

-- Q6. Self Join

CREATE TABLE project (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
    FullName VARCHAR(50) NOT NULL,
    Gender ENUM('Male', 'Female'),
    ManagerID INT
);

-- INSERT INTO project (FullName, Gender, ManagerID) VALUES ('John Doe','Male',NULL), ('Jane Smith','Female',1);

-- Query to find employees and their managers
SELECT e.FullName AS Employee, m.FullName AS Manager
FROM project e
LEFT JOIN project m ON e.ManagerID = m.EmployeeID;

-- Q7. DDL Command

CREATE TABLE facility (
    Facility_ID INT,
    Name VARCHAR(100),
    State VARCHAR(50),
    Country VARCHAR(50)
);

ALTER TABLE facility
  MODIFY Facility_ID INT AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE facility
  ADD COLUMN city VARCHAR(50) NOT NULL AFTER Name;

-- Q8. Views

CREATE VIEW product_category_sales AS
SELECT p.productLine,
       SUM(od.quantityOrdered * od.priceEach) AS total_sales,
       COUNT(DISTINCT o.orderNumber) AS number_of_orders
FROM products p
JOIN orderdetails od ON p.productCode = od.productCode
JOIN orders o ON od.orderNumber = o.orderNumber
GROUP BY p.productLine;

-- Q9. Stored Procedure with Parameters

DELIMITER //
CREATE PROCEDURE Get_country_payments(IN input_year INT, IN input_country VARCHAR(50))
BEGIN
  SELECT YEAR(p.paymentDate) AS Year,
         c.country,
         CONCAT(ROUND(SUM(p.amount)/1000,0),'K') AS TotalAmount
  FROM payments p
  JOIN customers c ON p.customerNumber = c.customerNumber
  WHERE YEAR(p.paymentDate) = input_year AND c.country = input_country
  GROUP BY YEAR(p.paymentDate), c.country;
END //
DELIMITER ;

-- Q10. Window Functions

-- a) Rank customers by order frequency
SELECT c.customerNumber, c.customerName,
       COUNT(o.orderNumber) AS order_count,
       RANK() OVER (ORDER BY COUNT(o.orderNumber) DESC) AS rank_no
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY c.customerNumber, c.customerName;

-- b) Year wise, month wise count of orders and YoY change

WITH order_summary AS (
  SELECT YEAR(orderDate) AS order_year,
         MONTHNAME(orderDate) AS order_month,
         COUNT(orderNumber) AS order_count
  FROM orders
  GROUP BY YEAR(orderDate), MONTHNAME(orderDate)
)
SELECT order_year, order_month, order_count,
       LAG(order_count) OVER (PARTITION BY order_month ORDER BY order_year) AS prev_year_count,
       CONCAT(ROUND(((order_count - LAG(order_count) OVER (PARTITION BY order_month ORDER BY order_year)) 
                    / LAG(order_count) OVER (PARTITION BY order_month ORDER BY order_year))*100,0),'%') AS YoY_PercentChange
FROM order_summary;

-- Q11. Subqueries

SELECT productLine, COUNT(*) AS product_count
FROM products
WHERE buyPrice > (SELECT AVG(buyPrice) FROM products)
GROUP BY productLine;

-- Q12. Error Handling in SQL

CREATE TABLE Emp_EH (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    EmailAddress VARCHAR(100)
);

DELIMITER //
CREATE PROCEDURE InsertEmp(IN p_EmpID INT, IN p_EmpName VARCHAR(50), IN p_Email VARCHAR(100))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT 'Error occurred' AS Message;
  END;
  INSERT INTO Emp_EH VALUES (p_EmpID, p_EmpName, p_Email);
END //
DELIMITER ;

-- Q13. Triggers

CREATE TABLE Emp_BIT (
    Name VARCHAR(50),
    Occupation VARCHAR(50),
    Working_date DATE,
    Working_hours INT
);

INSERT INTO Emp_BIT VALUES
('Robin', 'Scientist', '2020-10-04', 12),
('Warner', 'Engineer', '2020-10-04', 10),
('Peter', 'Actor', '2020-10-04', 13),
('Marco', 'Doctor', '2020-10-04', 14),
('Brayden', 'Teacher', '2020-10-04', 12),
('Antonio', 'Business', '2020-10-04', 11);

DELIMITER //
CREATE TRIGGER before_insert_Emp_BIT
BEFORE INSERT ON Emp_BIT
FOR EACH ROW
BEGIN
  IF NEW.Working_hours < 0 THEN
    SET NEW.Working_hours = ABS(NEW.Working_hours);
  END IF;
END //
DELIMITER ;