sql-use

CREATE TABLE Consumers (
  ConsumerID INT NOT NULL PRIMARY KEY,
  CustomerName VARCHAR(50) NOT NULL,
  Email VARCHAR(100) NOT NULL UNIQUE, -- Added UNIQUE constraint to Email
  MobileNumber VARCHAR(20) NOT NULL,
  UserID VARCHAR(50) NOT NULL UNIQUE,
  Password VARCHAR(30) NOT NULL,
  ConfirmPassword VARCHAR(30) NOT NULL,
  Status VARCHAR(8) CHECK (Status IN ('Active', 'Inactive'))  
    )
INSERT INTO Consumers (ConsumerID, CustomerName, Email, MobileNumber, UserID, Password, ConfirmPassword, Status)
VALUES
    (1234567890123, 'John Doe', 'john.doe@example.com', '1234567890', 'john123', 'password123', 'password123', 'Active'),
    (9876543210987, 'Jane Smith', 'jane.smith@example.com', '9876543210', 'jane456', 'securepass', 'securepass', 'Active'),
    (1112223334445, 'Alice Johnson', 'alice.j@example.com', '1112223334', 'alice87', 'pass123', 'pass123', 'Inactive'),
    (5556667778889, 'Bob Williams', 'bob.w@example.com', '5556667778', 'bob99', 'mypassword', 'mypassword', 'Active'),
    (8889990001112, 'Eva Davis', 'eva.d@example.com', '8889990001', 'eva123', 'evapass', 'evapass', 'Inactive');
    
    select * from Consumers;
    
 -- problem 2
 ALTER TABLE Consumers
ADD COLUMN Title VARCHAR(20);
 ALTER TABLE Consumers
ADD COLUMN ConsumerBill INT;
ALTER TABLE Consumers
ADD BillNumber INT(5);

UPDATE Consumers
SET BillNumber = RIGHT(CONVERT(VARCHAR, ConsumerBill), 5);

-- problem 3
CREATE TABLE Customers (
  CustomerID int NOT NULL PRIMARY KEY,
  CustomerName varchar(50) NOT NULL,
  Address varchar(255),
  City varchar(50),
  State varchar(25),
  ZipCode varchar(10),
  Email varchar(100)
);
INSERT INTO Customers (Customerid,CustomerName, Address, City, State, ZipCode, Email)
VALUES
('110','Ana Doe', '123 Main St', 'Anytown', 'CA', '98765', 'johndoe@example.com'),
('111','Jane Smith', '456 Elm St', 'Springfield', 'NY', '12345', 'janesmith@example.com'),
('112','Bob Johnson', '789 Oak St', 'Anytown', 'CA', '98765', 'bobjohnson@example.com'),
('113','Ary Williams', '1001 Pine St', 'Metropolis', 'NY', '10001', 'marywilliams@example.com'),
('114','David Brown', '555 Broadway Ave', 'New York City', 'NY', '10019', 'davidbrown@example.com');

SELECT * FROM Customers WHERE CustomerName LIKE 'A%' ORDER BY CustomerName DESC;

-- problem 4
CREATE TABLE CustomerDetails (
    ConsumerID INT PRIMARY KEY,
    Name VARCHAR(50),
    Address VARCHAR(255),
    City VARCHAR(50),
    Bill DECIMAL(10, 2)
);

INSERT INTO CustomerDetails (ConsumerID, Name, Address, City, Bill)
VALUES
    (1, 'John Doe', '123 Main St', 'New York', 100.50),
    (2, 'Alice Smith', '456 Oak St', 'Los Angeles', 150.75),
    (3, 'Bob Johnson', '789 Pine St', 'Chicago', 200.25),
    (4, 'Eva Brown', '101 Cedar St', 'New York', 120.00),
    (5, 'Maria Garcia', '202 Elm St', 'Los Angeles', 180.30),
    (6, 'Michael Miller', '303 Maple St', 'Chicago', 250.50),
    (7, 'Sophia Davis', '404 Birch St', 'New York', 90.75),
    (8, 'William Wilson', '505 Spruce St', 'Los Angeles', 130.00),
    (9, 'Emma Martinez', '606 Pine St', 'Chicago', 190.25),
    (10, 'James Anderson', '707 Cedar St', 'New York', 220.80);

select City, AVG(Bill) as avgbill from CustomerDetails
group by City order by avgbill DESC;

-- problem 5
-- Create Consumers Table
CREATE TABLE Consumerss (
    ConsumerID INT PRIMARY KEY,
    ConsumerName VARCHAR(50)
);

-- Create MonthlyBills Table for June
CREATE TABLE Bills (
    BillNumber INT PRIMARY KEY,
    ConsumerID INT,
    Month VARCHAR(25),
    Amount DECIMAL(10, 2),
    FOREIGN KEY (ConsumerID) REFERENCES Consumerss(ConsumerID)
);

INSERT INTO Consumerss (ConsumerID, ConsumerName)
VALUES
    (1, 'John Doe'),
    (2, 'Alice Smith'),
		(3, 'Sayan Manna'),
		(4, 'Harry Potter'),
		(5, 'Nilesh Kumar'),
		(6, 'Rohan Rana'),
		(7, 'Sohom Kundu'),
		(8, 'Alex Jae'),
		(9, 'Joe Root'),
		(10,'Jason Roy');
        
INSERT INTO Bills (BillNumber, ConsumerID, Month, Amount)
VALUES
    (101, 1, 'June', 150.75),
    (102, 2, 'June', 200.25),
		(103, 3, 'June', 0),
		(104, 4, 'June', 2000.50),
		(105, 5, 'June', 0),
		(106, 6, 'June', 4000.00),
		(107, 7, 'June', 250.50),
		(108, 8, 'June', 3000.50),
		(109, 9, 'June', 700.50),
		(110, 10, 'June', 0);
        
select c.ConsumerId, c.ConsumerName from Consumerss c
inner join Bills b on c.ConsumerId = b.ConsumerId
where Amount > 0;