SGS assignment 4 for Abdul

-- ASSIGNMENT 4
create database gourav_ass_4;
use gourav_ass_4;
CREATE TABLE customer(CID INT PRIMARY KEY,CNAME VARCHAR(30));

CREATE TABLE BRANCH( BCODE VARCHAR(5) PRIMARY KEY, BNAME VARCHAR(30) );

ALTER TABLE BRANCH ADD CONSTRAINT CPK CHECK (BCODE LIKE 'B');
CREATE TABLE ACCOUNT(ANO VARCHAR(5) PRIMARY KEY, ATYPE CHAR(2), BALANCE numeric(7), CID int, BCODE VARCHAR(5));
ALTER TABLE ACCOUNT ADD FOREIGN KEY(CID) REFERENCES CUSTOMER(CID);
ALTER TABLE ACCOUNT ADD FOREIGN KEY(BCODE) REFERENCES BRANCH(BCODE);
ALTER TABLE ACCOUNT ADD CONSTRAINT CPK2 CHECK (atype IN('S','C'));
ALTER TABLE ACCOUNT ADD CONSTRAINT CPK3 CHECK (ano LIKE 'A%');

CREATE TABLE TRANSACTION( TID VARCHAR(5) PRIMARY KEY,
ANO VARCHAR(5), TTYPE CHAR(2), TDATE DATE, TAMOUNT INTEGER );
ALTER TABLE TRANSACTION ADD FOREIGN KEY(ANO) REFERENCES ACCOUNT(ANO);
ALTER TABLE TRANSACTION ADD CONSTRAINT CPK4 CHECK(TTYPE IN('W','D'));
ALTER TABLE TRANSACTION ADD CONSTRAINT CPK5 CHECK (tid LIKE 'T%');

INSERT INTO customer (CID, CNAME)
VALUES
(10001, 'Gourav Upadhyay'),
(10002, 'Priya Patel'),
(10003, 'Rahul Singh'),
(10004, 'Ananya Sharma'),
(10005, 'Aarav Gupta'),
(10006, 'Neha Verma'),
(10007, 'Vivek Mishra'),
(10008, 'Kritika Sharma'),
(10009, 'Sandeep Yadav'),
(10010, 'Shreya Joshi');

INSERT INTO branch (BCODE, BNAME)
VALUES
('B0001', 'Ahmedabad'),
('B0002', 'Surat'),
('B0003', 'Vadodara'),
('B0004', 'Rajkot');

INSERT INTO account (ANO, ATYPE, BALANCE, CID, BCODE)
VALUES
('A0001', 'S', 5000, 10001, 'B0001'),
('A0002', 'S', 7000, 10002, 'B0004'),
('A0003', 'S', 3000, 10002, 'B0001'),
('A0004', 'C', 9000, 10004, 'B0001'),
('A0005', 'S', 6000, 10001, 'B0002'),
('A0006', 'C', 2000, 10002, 'B0004'),
('A0007', 'S', 4000, 10007, 'B0004'),
('A0008', 'C', 8000, 10002, 'B0001'),
('A0009', 'S', 10000, 10009, 'B0002'),
('A0010', 'C', 12000, 10010, 'B0004'),
('A0011', 'S', 15000, 10001, 'B0001'),
('A0012', 'C', 17000, 10002, 'B0001'),
('A0013', 'S', 20000, 10003, 'B0002'),
('A0014', 'S', 22000, 10001, 'B0001'),
('A0015', 'S', 25000, 10002, 'B0004');

INSERT INTO account (ANO, ATYPE, BALANCE, CID, BCODE)
VALUES
('A0016', 'C', 1000, 10001, 'B0001');


INSERT INTO transaction (TID, ANO, TTYPE, TDATE, TAMOUNT)
VALUES
('T0001', 'A0001', 'W', '2024-03-30', 1000),
('T0002', 'A0001', 'D', '2024-03-30', 2000),
('T0003', 'A0001', 'W', '2024-03-30', 1500),
('T0004', 'A0001', 'D', '2024-03-30', 3000),
('T0005', 'A0001', 'W', '2024-03-30', 2500),
('T0006', 'A0001', 'D', '2024-03-30', 4000),
('T0007', 'A0007', 'W', '2024-03-10', 2000),
('T0008', 'A0008', 'D', '2024-03-30', 3500),
('T0009', 'A0009', 'W', '2024-03-30', 3000),
('T0010', 'A0010', 'D', '2024-03-30', 5000),
('T0011', 'A0011', 'W', '2024-03-20', 4000),
('T0012', 'A0012', 'D', '2024-04-30', 6000),
('T0013', 'A0003', 'W', '2024-04-30', 4500),
('T0014', 'A0014', 'D', '2024-03-30', 7000),
('T0015', 'A0015', 'W', '2024-03-30', 5500),
('T0016', 'A0001', 'D', '2024-06-30', 8000),
('T0017', 'A0002', 'W', '2024-03-10', 1000),
('T0018', 'A0003', 'D', '2024-07-20', 2000),
('T0019', 'A0002', 'W', '2024-03-30', 1500),
('T0020', 'A0002', 'D', '2024-01-30', 3000),
('T0021', 'A0002', 'W', '2024-03-30', 2500),
('T0022', 'A0002', 'D', '2024-01-30', 4000),
('T0023', 'A0008', 'W', '2024-03-30', 2000),
('T0024', 'A0009', 'D', '2024-03-30', 3500),
('T0025', 'A0003', 'W', '2024-03-30', 3000);

-- C
SELECT *FROM CUSTOMER WHERE CID IN
(SELECT A1.CID FROM ACCOUNT A1, ACCOUNT A2 WHERE A1.ATYPE = 'S' AND A2.ATYPE = 'C' AND A1.CID = A2.CID);

SELECT DISTINCT C.Cid, C.Cname  FROM Customer C
INNER JOIN Account A ON C.Cid = A.Cid
INNER JOIN Account A1 ON C.Cid = A1.Cid
WHERE A.Atype = 'S' AND A1.Atype = 'C';

-- D
SELECT B.BCODE, B.BNAME, COUNT(A.ANO) as No_of_accounts FROM BRANCH B, ACCOUNT A
WHERE B.BCODE=A.BCODE GROUP BY B.BCODE;


SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS No_of_accounts
FROM Branch B
JOIN Account A ON B. Bcode=A. Bcode
GROUP BY B. Bcode, B. Bname;

-- E

SELECT B.BCODE, B. BNAME, COUNT(A.bcode) AS No_of_Accounts
FROM BRANCH B, ACCOUNT A WHERE B.BCODE = A.BCODE
GROUP BY B.BCODE, B.BNAME
HAVING COUNT(A.bcode) <
(SELECT COUNT(a1.ano)/4 FROM account AS a1);


SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS No_of_Accounts
FROM Branch B, Account A WHERE B.Bcode = A.Bcode
GROUP BY B.Bcode, B. Bname
HAVING COUNT(A. Ano)<
(SELECT COUNT(Ano)/(SELECT COUNT(Bcode) FROM Branch) FROM Account);

SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS NoOfAccount
FROM Branch B JOIN Account A ON B.Bcode=A.Bcode
GROUP BY B. Bcode, B. Bname
HAVING COUNT(A. Ano) <
(SELECT COUNT(Ano)/ (SELECT COUNT(Bcode) FROM Branch) FROM Account);

-- F
SELECT c.cid, c.cname
FROM customer c, account a, transaction t
WHERE c.cid = a.cid AND a.ano = t.ano
group by t.tdate
having COUNT(t.tdate) = 3;

SELECT c.cid, c.cname
FROM customer c
JOIN account a ON c.cid = a.cid
JOIN transaction t ON a.ano = t.ano
GROUP BY c.cid, c.cname
HAVING COUNT(t.tdate) = 3;


SELECT c.cid, c.cname FROM customer c, account a, transaction t
WHERE c.cid = a.cid AND a.ano = t.ano
GROUP BY c.cid, c.cname, t.tdate
HAVING COUNT(t.tdate) = 3;


-- G
CREATE VIEW BRANCH_DATA AS SELECT B.BCODE, B.BNAME, COUNT(A.ANO) as No_of_accounts FROM BRANCH B, ACCOUNT A
WHERE B.BCODE=A.BCODE GROUP BY B.BCODE;

create view BRANCH_DATA2 AS
SELECT B.Bcode, B. Bname, COUNT(A. Ano) AS No_of_accounts
FROM Branch B
JOIN Account A ON B. Bcode=A. Bcode
GROUP BY B. Bcode, B. Bname;