I'm new to SQL and have created a database within Lazarus and the language pascal. I am currently using SQLlite3. So far i have created a database that has the current assets and liabilities (integers) of a business, however i need to create an algorithm that would divide these two numbers and input the "ratio" division of these two numbers into a new table, any help would be appreciated.
many thanks.
The Sql below creates two Sqlite tables
Companies with columns CompanyID, Assets and Liabilities
CompanyAL with columns CompanyID and Ratio (of Liabilites to Assets)
It then populates the Companies table with a couple of rows, then uses an
insert into ... select ...
to insert the ratio of the Assets/Liabilities into the CompanyAL table.
Important: In Sqlite, the "divide" symbol, /, gives an integer result by default and so would give the Ratio values 0 and 2. To overcome that, you need to use an expression which multiplies the result by 1.0; I've done that in the numerator and denomination for the expression to calculate the value in the Ratio column.
CREATE TABLE "main"."Companies" ("CompanyID" INTEGER PRIMARY KEY NOT NULL check(typeof("CompanyID") = 'integer') , "Assets" INTEGER check(typeof("Assets") = 'integer') , "Liabilities" INTEGER check(typeof("Liabilities") = 'integer') );
CREATE TABLE "main"."CompanyAL" ("CompanyID" INTEGER PRIMARY KEY NOT NULL check(typeof("CompanyID") = 'integer') , "Ratio" FLOAT);
insert into Companies(CompanyID, Assets, Liabilities) values(1, 100, 50);
insert into Companies(CompanyID, Assets, Liabilities) values(2, 50, 100);
insert into CompanyAL select CompanyID, (Liabilities * 1.0 / Assets * 1.0) from Companies
Tested for Sqlite using the Sqlite Manager add-in for Firefox. Obviously in your Lazarus app, you would construct the necessary Sql in code and use a suitable db component to execute it against the database.