Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/Adarsh275/PetrolPump-Management-System/llms.txt

Use this file to discover all available pages before exploring further.

The Query section in the sidebar exposes a raw SQL query runner and a dedicated function calculator. This lets power users interrogate the database with any SELECT statement without leaving the browser. Both sub-tools are accessible from the same sidebar menu entry and render their output as interactive, sortable DataFrames rendered by Streamlit.

​
Custom Query Runner

The runner is wired directly to the c cursor object that database.py establishes at import time. When you click Run Query, the app executes your statement and pipes every returned row through st.dataframe().
app.py
   elif choice == "Query":
      menu = ["Custom Query","Function"]
      choice2 = st.sidebar.selectbox("Query", menu)
      if choice2 == "Custom Query":
         query = st.text_input("Enter Your Query:")
         if st.button("Run Query"):
            c.execute(query)
            data = c.fetchall()
            st.dataframe(data)
      elif choice2 == "Function":
         net_value()
Navigation steps:
  1. Open the app in your browser.
  2. In the left sidebar, select Query from the Tables dropdown.
  3. A second dropdown appears — select Custom Query.
  4. Type any valid SQL statement into the Enter Your Query: text input.
  5. Click Run Query.
  6. Results render immediately below as a Pandas-style DataFrame — rows are scrollable and columns are resizable.
The runner passes your query directly to c.execute() with no parameterization or sanitization. Only use this feature in a trusted, local environment. Never expose it publicly — any user with browser access could read, modify, or drop every table in the database.

​
Example Queries

All queries below are taken directly from Backend/querry.sql. You can copy-paste any of them into the Enter Your Query: field.

​
JOIN Queries

1 — INNER JOIN: pumps that have at least one employee assigned Returns only the registration numbers of pumps that appear in the Employee table’s Petrolpump_No column, i.e. pumps with at least one active employee. 
SELECT PetrolPump.Registration_No
FROM PetrolPump
INNER JOIN Employee ON PetrolPump.Registration_No = Employee.Petrolpump_No;
2 — LEFT JOIN: pumps with no employees Uses a LEFT JOIN and filters on NULL to find pumps that have no employee records at all. 
SELECT Petrolpump.Registration_No
FROM Petrolpump
LEFT JOIN Employee ON Petrolpump.Registration_No = Employee.Petrolpump_No
WHERE Employee.Petrolpump_No IS NULL;
3 — LEFT JOIN: all pumps (with or without employees) Returns the registration numbers of all pumps in the Petrolpump table, including those that have no employee records. Unlike query 2, rows are not filtered by NULL — every pump appears regardless of staffing. 
SELECT PetrolPump.Registration_No FROM PetrolPump LEFT JOIN Employee ON PetrolPump.Registration_No = Employee.Petrolpump_No;
4 — RIGHT OUTER JOIN: all customers and their invoices Returns the full customer list, with invoice details filled in where an invoice exists. Customers who have never purchased anything still appear, with NULL in the invoice columns. 
SELECT Invoice.Invoice_No ,Invoice.Date ,Invoice.Payment_Type, Customer.C_Name , Customer.Phone_No FROM Invoice RIGHT OUTER JOIN Customer ON Customer.Customer_Code = Invoice.Customer_Code;

​
Aggregate Functions

1 — Average age of male customers Computes the mean Age across all rows in the Customer table where Gender = 'M'. 
SELECT AVG(Age) FROM Customer WHERE Gender = 'M';
2 — Employee age calculated from DOB Uses the TIMESTAMPDIFF function to derive each employee’s current age in years from their stored date of birth. 
SELECT Emp_Name, TIMESTAMPDIFF(YEAR, DOB, CURDATE()) AS age
FROM Employee;
3 — Invoice with the maximum total price Returns all columns from every invoice row alongside the global maximum Total_Price value. Useful for spotting the single highest-value transaction at a glance. 
SELECT *, MAX(Total_Price) FROM Invoice;

​
SET Operations

1 — UNION of all names (owners + employees) Combines owner names and employee names into a single deduplicated list of all named individuals associated with the pump network. The Owners query uses the alias Names and the Employee column is referenced as EMP_Name (case-insensitive in MySQL, but used verbatim here as stored in the query file). 
SELECT Owner_Name from Owners as Names UNION SELECT EMP_Name from Employee ;
2 — INTERSECT — pumps that have at least one employee Returns only the registration numbers that appear in both Petrolpump and Employee, effectively the intersection of the two sets. 
SELECT Registration_No FROM Petrolpump
INTERSECT
SELECT Petrolpump_No FROM Employee;
3 — Subquery — names of pumps with no employees A correlated subquery variant of the LEFT JOIN null-check above. The inner query finds registration numbers with no matching employee; the outer query resolves those numbers to human-readable pump names. 
SELECT Petrolpump_Name FROM Petrolpump where Registration_No IN(SELECT Petrolpump.Registration_No FROM Petrolpump left join Employee on Petrolpump.Registration_No = Employee.Petrolpump_No WHERE Employee.Petrolpump_No is NULL);
4 — UNION of all customer and owner names Combines customer names and owner names from their respective tables into a single deduplicated list. Useful for auditing the full roster of named individuals across both roles. 
SELECT  C_Name from Customer UNION SELECT Owner_Name from Owners;

​
Function Calculator

The Function sub-menu provides a focused UI for calling the TOTAL_AMOUNT stored function defined in MySQL. Rather than writing a raw SQL call yourself, you supply only the Tanker ID and the app handles the two-step SET @p0 / SELECT call sequence automatically. Navigation steps:
  1. In the left sidebar, select Query from the Tables dropdown.
  2. From the second dropdown, select Function.
  3. Type a valid Tanker ID (e.g. BR6872) into the Enter Tanker ID: field.
  4. Click RUN Function.
  5. The result appears as a single-column DataFrame labelled Total Amount.
The net_value() helper in app.py drives this flow:
app.py
def net_value():
   tanker_id = st.text_input("Enter Tanker ID:")
   result = TOTAL_Amount(tanker_id)
   if st.button("RUN Function"):
      df2=pd.DataFrame(result, columns = ["Total Amount"])
      st.dataframe(df2)
The TOTAL_Amount() Python function (defined in database.py) issues two consecutive SQL statements: SET @p0='<tanker_id>' followed by SELECT TOTAL_AMOUNT(@p0). The MySQL function itself multiplies FUEL_PRICE × FUEL_AMOUNT for the given tanker. Example: Tanker JH7523 is a Diesel tanker with Fuel_Price = 87.89 and Fuel_Amount = 751.89 litres. Entering JH7523 and clicking RUN Function returns a Total Amount of ₹66,063.56.
For the full SQL definition of the TOTAL_AMOUNT stored function — including the DECLARE, SET, and RETURN statements — see the Triggers & Functions reference page.

Build docs developers (and LLMs) love

Get started for freeTalk to us