CREATE table customer(
    cust_id INT PRIMARY KEY,
    cust_name VARCHAR(50),
    phone VARCHAR(50),
    email VARCHAR(50),
    address VARCHAR(50)
);
CREATE TABLE accounts(
    account_no INT PRIMARY KEY,
    cust_id INT,
    account_type VARCHAR(50),
    opening_date DATE,
    balance decimal(10,2),
    FOREIGN KEY(cust_id) REFERENCES customer(cust_id)
);
CREATE TABLE transactions(
    transaction_id INT PRIMARY KEY,
    account_no INT,
    transaction_type VARCHAR(50),
    transaction_date DATE,
    amount decimal(10,2),
    FOREIGN KEY (account_no) REFERENCES accounts(account_no)
);
INSERT INTO customer VALUES
(101,"Ravi Sharma",9432253892,"ravisharma@gmail.com","Bridge lane"),
(102,"Priya Das",9432253892,"priyadas@gmail.com","Dover Lane"),
(103,"Giana Fernandes",9432253892,"gfernandes@gmail.com","Josephine street");

INSERT INTO accounts VALUES
(201,101,"Savings","2020-10-15",50000.0),
(202,101,"Savings","2020-10-15",20000.0),
(203,103,"Current","2024-10-09",9000.0),
(204,102,"Current","2020-04-03",2500.0);

INSERT INTO transactions VALUES
(301,202,"Deposit","2020-10-15",2000.0),
(302,203,"Withdrawal","2018-04-25",4000.0),
(303,201,"Deposit","2005-02-03",3000.0),
(304,202,"Withdrawal","2015-06-12",5000.0);

-- SELECT c.cust_name,a.account_no,a.account_type,a.opening_date,a.balance
-- FROM customer as c
-- JOIN accounts as a
-- ON c.cust_id=a.cust_id
-- WHERE c.cust_name="Ravi Sharma";

-- SELECT a.account_no,a.account_type,a.opening_date,a.balance
-- FROM accounts as a
-- WHERE balance>10000;

-- SELECT t.transaction_type,t.transaction_date,t.amount
-- From customer as c
-- JOIN accounts as a
-- ON c.cust_id=a.cust_id
-- JOIN transactions as t
-- ON a.account_no=t.account_no
-- Where c.cust_name="Ravi Sharma";

-- SELECT c.cust_name,SUM(a.balance) AS total_balance 
-- FROM customer as c
-- JOIN accounts as a
-- ON c.cust_id=a.cust_id
-- GROUP BY c.cust_name;

-- SELECT * 
-- From transactions
-- WHERE MONTH(transaction_date)=10
-- AND YEAR(transaction_date)=2020;

-- UPDATE customer 
-- SET address="DELHI"
-- WHERE cust_id=101;

SELECT *
FROM customer as c
JOIN accounts as a 
ON c.cust_id=a.cust_id
Where a.account_type="Savings";