SQL Project

CREATE TABLE Currency_Type
(
    CT_ID INTEGER NOT NULL,
    CT VARCHAR (32),
    CONSTRAINT Currency_Type_PK PRIMARY KEY (CT_ID)
);

CREATE OR REPLACE Trigger CT_ID_AUTO_TR
BEFORE INSERT ON Currency_Type
FOR EACH ROW
WHEN(NEW.CT_ID IS NULL)
BEGIN
    :NEW.CT_ID:=CT_ID_AUTO_S.NEXTVAL;
END;

CREATE TABLE Accounts
(
    A_ID INTEGER NOT NULL,
    A_Number INTEGER,
    A_Interest NUMBER (7,2),
    A_Stock INTEGER,
    Currency_Type_CT_ID INTEGER NOT NULL,
    CONSTRAINT Accounts_PK PRIMARY KEY (A_ID),
    CONSTRAINT Accounts_Currency_Type_FK FOREIGN KEY (Currency_Type_CT_ID) REFERENCES Currency_Type (CT_ID)
);
CREATE OR REPLACE Trigger A_ID_AUTO_TR
BEFORE INSERT ON Accounts
FOR EACH ROW
WHEN(NEW.A_ID IS NULL)
BEGIN
    :NEW.A_ID:=A_ID_AUTO_S.NEXTVAL;
END;

CREATE TABLE Clients
(
    C_ID INTEGER NOT NULL,
    C_Name VARCHAR (32),
    C_PIN VARCHAR(32),
    C_Address VARCHAR (32),
    C_Phone_Number VARCHAR (32),
    Accounts_A_ID INTEGER NOT NULL,
    CONSTRAINT Clients_PK PRIMARY KEY (C_ID),
    CONSTRAINT Clients_Accounts_FK FOREIGN KEY (Accounts_A_ID) REFERENCES Accounts (A_ID)
);
CREATE OR REPLACE Trigger C_ID_AUTO_TR
BEFORE INSERT ON Clients
FOR EACH ROW
WHEN(NEW.C_ID IS NULL)
BEGIN
    :NEW.C_ID:=C_ID_AUTO_S.NEXTVAL;
END;

CREATE TABLE Position_Type
(
    PT_ID INTEGER NOT NULL,
    PT VARCHAR (32),
    CONSTRAINT Position_PK PRIMARY KEY (PT_ID)
);
CREATE OR REPLACE Trigger PT_ID_AUTO_TR
BEFORE INSERT ON Position_Type
FOR EACH ROW
WHEN(NEW.PT_ID IS NULL)
BEGIN
    :NEW.PT_ID:=PT_ID_AUTO_S.NEXTVAL;
END;

CREATE TABLE Employee
(
    E_ID INTEGER NOT NULL,
    E_Name VARCHAR (32),
    E_Phone_Number VARCHAR (32),
    Position_Type_PT_ID INTEGER NOT NULL,
    CONSTRAINT Employee_PK PRIMARY KEY (E_ID),
    CONSTRAINT Employee_Position_Type_FK FOREIGN KEY (Position_Type_PT_ID) REFERENCES Position_Type (PT_ID)
);
CREATE OR REPLACE Trigger E_ID_AUTO_TR
BEFORE INSERT ON Employee
FOR EACH ROW
WHEN(NEW.E_ID IS NULL)
BEGIN
    :NEW.E_ID:=E_ID_AUTO_S.NEXTVAL;
END;

CREATE TABLE T_Type
(
    TT_ID INTEGER NOT NULL,
    TT VARCHAR (32),
    CONSTRAINT T_Type_PK PRIMARY KEY (TT_ID)
);
CREATE OR REPLACE Trigger TT_ID_AUTO_TR
BEFORE INSERT ON T_Type
FOR EACH ROW
WHEN(NEW.TT_ID IS NULL)
BEGIN
    :NEW.TT_ID:=TT_ID_AUTO_S.NEXTVAL;
END;

CREATE TABLE Transactions
(
    T_ID INTEGER NOT NULL,
    T_Total NUMBER (7,2),
    T_Date VARCHAR(50),
    T_Type_TT_ID INTEGER NOT NULL,
    Employee_E_ID INTEGER NOT NULL,
    Clients_C_ID INTEGER NOT NULL,
    Accounts_A_ID INTEGER NOT NULL,
    Currency_Type_CT_ID INTEGER NOT NULL,
    CONSTRAINT Transactions_PK PRIMARY KEY (T_ID),
    CONSTRAINT Transactions_T_Type_FK FOREIGN KEY (T_Type_TT_ID) REFERENCES T_Type (TT_ID),
    CONSTRAINT Transactions_Employee_FK FOREIGN KEY (Employee_E_ID) REFERENCES Employee (E_ID),
    CONSTRAINT Transactions_Clients_FK FOREIGN KEY (Clients_C_ID) REFERENCES Clients (C_ID),
    CONSTRAINT Transactions_Accounts_FK FOREIGN KEY (Accounts_A_ID) REFERENCES Accounts (A_ID),
    CONSTRAINT Transactions_Currency_Type_FK FOREIGN KEY (Currency_Type_CT_ID) REFERENCES Currency_Type (CT_ID)
);
CREATE OR REPLACE Trigger T_ID_AUTO_TR
BEFORE INSERT ON Transactions
FOR EACH ROW
WHEN(NEW.T_ID IS NULL)
BEGIN
    :NEW.T_ID:=T_ID_AUTO_S.NEXTVAL;
END;

INSERT INTO Currency_Type VALUES (1, 'BGN');
INSERT INTO Currency_Type VALUES (2, 'INR');
INSERT INTO Currency_Type VALUES (3, 'TKL');
INSERT INTO Currency_Type VALUES (4, 'KLM');
INSERT INTO Currency_Type VALUES (5, 'EUR');
INSERT INTO Currency_Type VALUES (6, 'USD');
INSERT INTO Currency_Type VALUES (7, 'RUB');

UPDATE Currency_Type
SET CT = 'SLD'
WHERE CT_ID = 4;

DELETE FROM Currency_Type
WHERE CT_ID = 4;

CREATE OR REPLACE PROCEDURE CT_Type
(ct_type Currency_Type.CT%TYPE)
AS
    BEGIN
        INSERT INTO Currency_Type(CT)
        VALUES (ct_type);
END;
BEGIN
CT_Type('AD');
END;

INSERT INTO Accounts VALUES (1, 420, 10.25, 30, 1);
INSERT INTO Accounts VALUES (2, 421, 11.26, 31, 2);
INSERT INTO Accounts VALUES (3, 422, 12.27, 32, 3);
INSERT INTO Accounts VALUES (4, 423, 13.28, 33, 5);
INSERT INTO Accounts VALUES (5, 423, 14.29, 34, 6);
INSERT INTO Accounts VALUES (6, 424, 15.24, 35, 7);
INSERT INTO Accounts VALUES (7, 426, 16.23, 36, 5);

UPDATE Accounts
SET A_Number = 425
WHERE A_ID = 5;

DELETE FROM Accounts
WHERE A_ID = 4;

CREATE OR REPLACE PROCEDURE Acc_INS
(acc_number Accounts.A_Number%TYPE,
 acc_interest Accounts.A_Interest%TYPE,
 acc_stock Accounts.A_Stock%TYPE)
AS
    BEGIN
        INSERT INTO Accounts(A_Number,A_Interest,A_Stock)
        VALUES (acc_number,acc_interest,acc_stock);
END;
BEGIN
Acc_INS(427, 17.88, 37,8);
END;

INSERT INTO Clients VALUES (1, 'Yancho', '20621639' , 'Ul. Alexander Veliki', '0883040601', 1);
INSERT INTO Clients VALUES (2, 'Kiril', '20621637', 'Ul. Aleko Veliki', '088344060', 2);
INSERT INTO Clients VALUES (3, 'Georgi', '20621643', 'Ul. Lyuben Keliki', '088304064', 3);
INSERT INTO Clients VALUES (4, 'Marko_Polo', '20621354', 'Ul. Karamfil 32', '088355430', 5);
INSERT INTO Clients VALUES (5, 'Marko_Polo', '20621554', 'Ul. Georgi Sava', '023304430', 6);
INSERT INTO Clients VALUES (6, 'Marvel', '20621454', 'Ul. Dunav 24', '088304435', 7);

UPDATE Clients
SET C_Name = 'Miril'
WHERE C_ID = 2;

DELETE FROM Clients
WHERE C_ID = 6;

INSERT INTO Position_Type VALUES (1, 'Customer Care Representative');
INSERT INTO Position_Type VALUES (2, 'Judge');
INSERT INTO Position_Type VALUES (3, 'Cook');
INSERT INTO Position_Type VALUES (4, 'Bartender');
INSERT INTO Position_Type VALUES (5, 'Barista');
INSERT INTO Position_Type VALUES (6, 'Waiter');

UPDATE Position_Type
SET PT = 'Librarian'
WHERE PT_ID = 3;

DELETE FROM Position_Type
WHERE PT_ID = 4;

INSERT INTO Employee VALUES (1, 'Misho', '0884204200', 1);
INSERT INTO Employee VALUES (2, 'Georgi', '0884204221', 2);
INSERT INTO Employee VALUES (3, 'Stoyan', '08842042021', 3);
INSERT INTO Employee VALUES (4, 'Grisho', '08842042043', 6);
INSERT INTO Employee VALUES (5, 'Tisho', '08842042054', 5);
INSERT INTO Employee VALUES (6, 'Pisho', '08842042065', 6);

UPDATE Employee
SET E_Name = 'Michael'
WHERE E_ID = 1;

DELETE FROM Employee
WHERE E_ID = 6;

INSERT INTO T_Type VALUES (1, 'Deposit');
INSERT INTO T_Type VALUES (2, 'Invest');
INSERT INTO T_Type VALUES (3, 'Credit');
INSERT INTO T_Type VALUES (4, 'withdraw');
INSERT INTO T_Type VALUES (5, 'Saving');
INSERT INTO T_Type VALUES (6, 'Crypto');

UPDATE T_Type
SET TT = 'Withdraw'
WHERE TT_ID = 4;

DELETE FROM T_Type
WHERE TT_ID = 4;

INSERT INTO Transactions VALUES(1, 301.27, '2001-08-08', 1, 1, 1, 1, 1);
INSERT INTO Transactions VALUES(2, 302.26, '2002-08-08', 2, 2, 2, 2, 2);
INSERT INTO Transactions VALUES(3, 303.25, '2003-08-08', 3, 3, 3, 3, 3);
INSERT INTO Transactions VALUES(4, 304.24, '2004-08-08', 5, 4, 4, 4, 4);
INSERT INTO Transactions VALUES(5, 305.23, '2005-08-08', 5, 5, 5, 5, 5);
INSERT INTO Transactions VALUES(6, 306.22, '2006-08-08', 6, 4, 3, 6, 6);

UPDATE Transactions
SET T_Total = 404.25
WHERE T_ID = 4;

DELETE FROM Transactions
WHERE T_ID = 6;

--tursene po nomer na account
SELECT c.C_Name, a.A_Interest, a.A_Stock,  a.A_Number, ct.CT FROM Clients c
JOIN Accounts a ON a.A_ID = c.Accounts_A_ID
JOIN Currency_Type ct ON ct.CT_ID = a.Currency_Type_CT_ID
WHERE a.A_Number = &A_Number;

--tursene po EGN
SELECT c.C_Name, c.C_PIN, a.A_Interest, a.A_Stock,  a.A_Number, ct.CT FROM Clients c
JOIN Accounts a ON a.A_ID = c.Accounts_A_ID
JOIN Currency_Type ct ON ct.CT_ID = a.Currency_Type_CT_ID
WHERE c.C_PIN LIKE '%&C_PIN%';

--tursene po Ime na klient
SELECT a.A_Number, c.C_Name, ct.CT, a.A_Interest, a.A_Stock FROM Clients c
JOIN Accounts a ON a.A_ID = c.Accounts_A_ID
JOIN Currency_Type ct ON ct.CT_ID = a.Currency_Type_CT_ID
WHERE c.C_Name LIKE '%&C_Name%';

--smetki na klient
SELECT c.c_phone_number,c.C_Name, a.A_Interest, a.A_Stock, a.A_Number, ct.CT FROM Clients c
JOIN Accounts a ON a.A_ID = c.Accounts_A_ID
JOIN Currency_Type ct ON ct.CT_ID = a.Currency_Type_CT_ID
WHERE c.C_PHONE_NUMBER LIKE '%&C_PHONE_NUMBER%';

--tranzakcii na klient podredeni po data
SELECT c.C_Name, a.A_Number, tt.TT, t.T_Total, t.T_Date FROM Transactions t
JOIN Accounts a ON a.A_ID = t.Accounts_A_ID
JOIN Clients c ON c.C_ID = t.Clients_C_ID
JOIN T_Type tt ON tt.TT_ID = t.T_Type_TT_ID
WHERE c.C_Name LIKE '%&C_Name%'
ORDER BY t.T_Date;

--tranzakcii na slujitel
SELECT e.E_Name, tt.TT, t.T_Date, ct.CT FROM Transactions t
JOIN Employee e ON e.E_ID = t.Employee_E_ID
JOIN T_Type tt ON tt.TT_ID = t.T_Type_TT_ID
JOIN Currency_Type ct ON ct.CT_ID = t.Currency_Type_CT_ID
WHERE e.E_Name LIKE '%&E_Name%';

--tranzakcii za period
SELECT e.E_Name, c.C_Name, t.T_Total, tt.TT, t.T_Date, ct.CT FROM Transactions t
JOIN Employee e ON e.E_ID = t.Employee_E_ID
JOIN T_Type tt ON tt.TT_ID = t.T_Type_TT_ID
JOIN Clients c ON c.C_ID = t.Clients_C_ID
JOIN Currency_Type ct ON ct.CT_ID = t.Currency_Type_CT_ID
WHERE t.T_Date BETWEEN '&start_date' AND '&end_date';