Test_Cases.sql

-- TO FIND OUT IF THE QUERY IS GIVING US THE CORRECT COUNT OF MAXIMUM COUNT OF STOCK PURCHASED

SELECT COUNT(STOCK_ID) FROM STOCK_TRANSACTIONS WHERE STOCK_ID = 'STOCK012';

-- PROFIT LOSS CALCULATION

SELECT SALE_COST - PURCHASE_COST AS PROFIT_LOSS
FROM
(SELECT (SUM(MONEY_INVESTMENT_VALUE)*SUM(PURCHASED_CURRENCY_VALUE)) AS PURCHASE_COST FROM FOREIGN_EXCHANGE_TRANSACTIONS WHERE CUSTOMER_ID = '87454' AND CURRENCY_TRANSACTION_TYPE = 'B') ,
(SELECT (SUM(MONEY_INVESTMENT_VALUE)*SUM(PURCHASED_CURRENCY_VALUE)) AS SALE_COST FROM FOREIGN_EXCHANGE_TRANSACTIONS WHERE CUSTOMER_ID = '87454' AND CURRENCY_TRANSACTION_TYPE = 'S');

--- investment 0

SELECT * FROM VW_FINAL_INVESTMENT WHERE STOCK_RESULT IS NULL AND MUTUAL_RESULT IS NULL AND CRYPTO_RESULT IS NULL AND CURRENCY_RESULT IS NULL;

-----access related test cases