3rd YEAR

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

CREATE TABLE "Account"
(
    A_ID INTEGER NOT NULL,
    A_Number INTEGER,
    A_Interest NUMBER (7,2),
    A_Stock INTEGER,
    Currency_Type_CT_ID INTEGER NOT NULL,
    CONSTRAINT Account_PK PRIMARY KEY (A_ID),
    CONSTRAINT Account_Currency_Type_FK FOREIGN KEY (Currency_Type_CT_ID) REFERENCES Currency_Type (CT_ID)
);

CREATE TABLE "Client"
(
    C_ID INTEGER NOT NULL,
    C_Name VARCHAR (32),
    C_PIN INTEGER,
    C_Address VARCHAR (32),
    C_Phone_Number VARCHAR (32),
    Account_A_ID INTEGER NOT NULL,
    CONSTRAINT Client_PK PRIMARY KEY (C_ID),
    CONSTRAINT Client_Account_FK FOREIGN KEY (Account_A_ID) REFERENCES "Account" (A_ID)
);

CREATE TABLE Position_Type
(
    PT_ID INTEGER NOT NULL,
    PT VARCHAR (32),
    CONSTRAINT Position_PK PRIMARY KEY (PT_ID)
);
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 TABLE Currency_Type
(
    CT_ID INTEGER NOT NULL,
    CT VARCHAR (32),
    CONSTRAINT Currency_Type_PK PRIMARY KEY (CT_ID)
);

CREATE TABLE "Account"
(
    A_ID INTEGER NOT NULL,
    A_Number INTEGER,
    A_Interest NUMBER (7,2),
    A_Stock INTEGER,
    Currency_Type_CT_ID INTEGER NOT NULL,
    CONSTRAINT Account_PK PRIMARY KEY (A_ID),
    CONSTRAINT Account_Currency_Type_FK FOREIGN KEY (Currency_Type_CT_ID) REFERENCES Currency_Type (CT_ID)
);

CREATE TABLE "Client"
(
    C_ID INTEGER NOT NULL,
    C_Name VARCHAR (32),
    C_PIN INTEGER,
    C_Address VARCHAR (32),
    C_Phone_Number VARCHAR (32),
    Account_A_ID INTEGER NOT NULL,
    CONSTRAINT Client_PK PRIMARY KEY (C_ID),
    CONSTRAINT Client_Account_FK FOREIGN KEY (Account_A_ID) REFERENCES "Account" (A_ID)
);

CREATE TABLE Position_Type
(
    PT_ID INTEGER NOT NULL,
    PT VARCHAR (32),
    CONSTRAINT Position_PK PRIMARY KEY (PT_ID)
);
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)
);
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;

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

UPDATE "Account"
SET A_Number = 425
WHERE A_ID = 5;

DELETE FROM "Account"
WHERE A_ID = 4;

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

UPDATE "Client"
SET C_Name = 'Miril'
WHERE C_ID = 2;

DELETE FROM "Client"
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 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 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 "Transaction" VALUES(1, 301.27, '2001-08-08', 1, 1, 1);
INSERT INTO "Transaction" VALUES(2, 302.26, '2002-08-08', 2, 2, 2);
INSERT INTO "Transaction" VALUES(3, 303.25, '2003-08-08', 3, 3, 3);
INSERT INTO "Transaction" VALUES(4, 304.24, '2004-08-08', 5, 4, 4);
INSERT INTO "Transaction" VALUES(5, 305.23, '2005-08-08', 5, 5, 5);
INSERT INTO "Transaction" VALUES(6, 306.22, '2006-08-08', 6, 4, 3);

UPDATE "Transaction"
SET T_Total = 404.25
WHERE T_ID = 4;

DELETE FROM "Transaction"
WHERE T_ID = 6;

SELECT c.C_Name, a.A_Interest, a.A_Stock, ct.CT FROM "Client" c
JOIN "Account" a ON a.A_ID = c.Account_A_ID
JOIN Currency_Type ct ON ct.CT_ID = a.Currency_Type_CT_ID
WHERE c.C_Name LIKE '%&C_Name%';

SELECT c.C_Name, a.A_Interest, a.A_Stock, ct.CT FROM "Client" c
JOIN "Account" a ON a.A_ID = c.Account_A_ID
JOIN Currency_Type ct ON ct.CT_ID = a.Currency_Type_CT_ID
WHERE a.A_Number LIKE '%&A_Number%';

SELECT c.C_Name, a.A_Interest, a.A_Stock, ct.CT FROM "Client" c
JOIN "Account" a ON a.A_ID = c.Account_A_ID
JOIN Currency_Type ct ON ct.CT_ID = a.Currency_Type_CT_ID
WHERE a.A_Number LIKE '%&A_Number%';

SELECT a.A_Number, c.C_Name, ct.CT, a.A_Interest, a.A_Stock FROM "Client" c
JOIN "Account" a ON a.A_ID = c.Account_A_ID
JOIN Currency_Type ct ON ct.CT_ID = a.Currency_Type_CT_ID
WHERE c.C_Name LIKE '%&C_Name%';

SELECT a.A_Number, c.C_Name, ct.CT, a.A_Interest, a.A_Stock FROM "Client" c
JOIN "Account" a ON a.A_ID = c.Account_A_ID
JOIN Currency_Type ct ON ct.CT_ID = a.Currency_Type_CT_ID
WHERE c.C_Name LIKE '%&C_Name%';

SELECT e.E_Name, tt.TT, t.T_Date FROM "Transaction" 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
WHERE e.E_Name LIKE '%&E_Name%';

SELECT e.E_Name, tt.TT, t.T_Date FROM "Transaction" 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
WHERE e.E_Name LIKE '%&E_Name%';