1. SQL query to find the second-highest salary from an Employee table without using LIMIT:

```sql
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);
```

2. Query to find the cumulative revenue per product over time:

```sql
SELECT a.Date, a.Product_ID, SUM(b.Revenue) as CumulativeRevenue
FROM Sales a
JOIN Sales b ON a.Date >= b.Date AND a.Product_ID = b.Product_ID
GROUP BY a.Date, a.Product_ID
ORDER BY a.Date, a.Product_ID;
```

3. Normalized database schema for an e-commerce application handling customers, orders, and payments:

```sql
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
Address VARCHAR(255)
);

CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10,2)
);

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE OrderDetails (
OrderDetailID INT PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

CREATE TABLE Payments (
PaymentID INT PRIMARY KEY,
OrderID INT,
PaymentDate DATE,
Amount DECIMAL(10,2),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
```

4. Stored procedure to automatically update product stock levels after a purchase:

```sql
DELIMITER //
CREATE PROCEDURE UpdateStockLevels(IN orderID INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE pID INT;
DECLARE pQuantity INT;
DECLARE cur CURSOR FOR SELECT ProductID, Quantity FROM OrderDetails WHERE OrderID = orderID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP
FETCH cur INTO pID, pQuantity;
IF done THEN
LEAVE read_loop;
END IF;

UPDATE Products SET Stock = Stock - pQuantity WHERE ProductID = pID;
END LOOP;

CLOSE cur;
END//
DELIMITER ;
```
This stored procedure will loop through all the products in a given order and decrease the stock level of each product by the quantity ordered.

Got a question with my answer?

Message Me

Cheapmath.com is completely free!

Asking question is free. Getting answers is free.

+99

Continue