DBMS - SOLUTION

CREATE DATABASE Practical ;

USE Practical ;

 CREATE TABLE Customers (

    CustomerID INT PRIMARY KEY,

    CustomerName VARCHAR(30),

    ContactNumber NUMERIC(10),

    City VARCHAR(20)

); 

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    CustomerID INT,

    OrderDate DATE,

    Amount INT,

    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

);

INSERT INTO Customers VALUES

(101, 'Aarav Sharma', 9123456780, 'Pune'),

(102, 'Vivaan Patel', 9876543210, 'Ahmedabad'),

(103, 'Aditya Singh', 9012345678, 'Mumbai'),

(104, 'Anaya Gupta', 9345678123, 'Jaipur'),

(105, 'Diya Mehta', 9567812345, 'Kochi');

INSERT INTO Orders VALUES

(321, 101, '2024-11-10', 300),

(322, 102, '2024-11-18', 650),

(323, 101, '2025-02-05', 200),

(324, 103, '2025-02-12', 350),

(325, 104, '2025-03-01', 500),

(326, 105, '2025-03-08', 750);

1. Customer name and city for orders with amount > 400

Query

SELECT CustomerName, City
FROM Customers
NATURAL JOIN Orders
WHERE Amount > 400;

Output

CustomerName	City
Vivaan Patel	Ahmedabad
Anaya Gupta	Jaipur
Diya Mehta	Kochi

---

2. Names of customers who placed orders in January 2025

Query

SELECT DISTINCT CustomerName
FROM Customers
NATURAL JOIN Orders
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-01-31';

or

SELECT DISTINCT CustomerName
FROM Customers
NATURAL JOIN Orders
WHERE YEAR(OrderDate) = 2025
AND MONTH(OrderDate) = 1;

or OrderDate LIKE '2025-01%' 

Output

CustomerName
No records found

---

3. Total amount spent by each customer

Query

SELECT CustomerName, SUM(Amount) AS TotalAmount
FROM Customers
NATURAL JOIN Orders
GROUP BY CustomerName;

Output

CustomerName	TotalAmount
Aarav Sharma	500
Vivaan Patel	650
Aditya Singh	350
Anaya Gupta	500
Diya Mehta	750

---

4. Maximum order amount placed by any customer

Query

SELECT MAX(Amount) AS MaximumOrder
FROM Orders;

or

use a sub query, to show who got that maximum order

Output

MaximumOrder
750

---

5. Average order amount city-wise (descending order)

Query

SELECT City, AVG(Amount) AS AverageAmount
FROM Customers
NATURAL JOIN Orders
GROUP BY City
ORDER BY AverageAmount DESC;

Output

City	AverageAmount
Kochi	750
Ahmedabad	650
Jaipur	500
Mumbai	350
Pune	250

---

6. Customers living in cities starting with letter ‘M’

Query

SELECT CustomerName
FROM Customers
WHERE City LIKE 'M%';

Output

CustomerName
Aditya Singh

---

7. CustomerID and total amount for customers whose spending exceeds 400

Query

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > 400;

Output

CustomerID	TotalAmount
101	500
102	650
104	500
105	750

---

8. Increase order amount of 2025 customers by 10%

Query

UPDATE Orders
SET Amount = Amount * 1.10
WHERE YEAR(OrderDate) = 2025;

Output (Effect on Orders Table)

OrderID	CustomerID	OrderDate	Amount
323	101	2025-02-05	220
324	103	2025-02-12	385
325	104	2025-03-01	550
326	105	2025-03-08	825

---

9. Delete orders of December 2024

Query

DELETE FROM Orders
WHERE OrderDate BETWEEN '2024-12-01' AND '2024-12-31';

Output

Result
No rows deleted