TU_SIA queries

SELECT Bank_Name, City, country FROM Banks;

CREATE INDEX ix_linii_bank_city_country ON banks(Bank_Name, City, country);
DROP INDEX ix_linii_bank_city_country;
COMMIT;
SET autotrace traceonly

SELECT  Bank_id, Customer_ID ,Transaction_date,  Installement_Date FROM Transactions WHERE Transaction_Date BETWEEN DATE '2019-01-01' AND DATE '2019-02-15';
CREATE INDEX ix_transaction_date ON transactions(Transaction_date);
COMMIT;

SELECT Bank_name, phone, email FROM Banks WHERE phone LIKE '8102%';
CREATE INDEX ix_bank_phone ON banks(phone);

CREATE INDEX Transactions_FK1_IDX ON Transactions(Customer_ID) tablespace TU_SIA_TBS_IDX;
CREATE INDEX Transactions_FK2_IDX ON Transactions(Bank_id) tablespace TU_SIA_TBS_IDX;
CREATE INDEX Transactions_FK3_IDX ON Transactions(Category_ID) tablespace TU_SIA_TBS_IDX;
SELECT cat.Category_name, t.Transaction_Date, bk.bank_name FROM transactions t join Category cat ON t.category_id = cat.category_id join banks bk ON bk.bank_id = t.bank_id;

SELECT Customer_ID,Transaction_date FROM transactions WHERE FLOOR(YEAR_MONTH/100) = 2007; --neoptimizat
SELECT Customer_ID,Transaction_date FROM transactions WHERE YEAR_MONTH > 200700 AND YEAR_MONTH <= 200712; -- optimizat

-- Q6 optimizat
SELECT t.year_month, cs.First_Name || ' ' || cs.Last_Name AS client_name, cs.phone, cs.email, ba.Bank_Name, ba.City
FROM transactions t join customers cs ON t.customer_id = cs.customer_id
  join Banks ba ON ba.bank_id = cs.bank_id
  WHERE cs.active = 1;

-- Q6 neoptimizat
SELECT t.year_month, cs.First_Name || ' ' || cs.Last_Name AS client_name, cs.phone, cs.email, ba.Bank_Name, ba.City
FROM transactions t join Banks ba ON ba.bank_id = t.bank_id
  join customers cs ON t.customer_id = cs.customer_id
  WHERE cs.active = 1;