Query Examples

Query Examples

This page provides practical examples of SQL queries that can be executed using the Generic SQL API Framework.

---

Basic SELECT

SQL

SELECT
    CustomerID,
    CustomerName,
    City
FROM CustomerTable;

Request

{
    "query": "customers"
}

---

SELECT with WHERE

SQL

SELECT *
FROM CustomerTable
WHERE City = :City;

Request

{
    "query": "customers_by_city",
    "parameters": {
        "City": "Bangalore"
    }
}

---

Multiple Parameters

SQL

SELECT *
FROM CustomerTable
WHERE City = :City
AND Status = :Status;

Request

{
    "query": "active_customers",
    "parameters": {
        "City": "Bangalore",
        "Status": "Active"
    }
}

---

ORDER BY

SQL

SELECT *
FROM CustomerTable
ORDER BY CustomerName ASC;

---

GROUP BY

SQL

SELECT
    City,
    COUNT(*) AS TotalCustomers
FROM CustomerTable
GROUP BY City;

---

HAVING

SQL

SELECT
    City,
    COUNT(*) AS TotalCustomers
FROM CustomerTable
GROUP BY City
HAVING COUNT(*) > 10;

---

INNER JOIN

SQL

SELECT
    c.CustomerName,
    o.OrderNumber,
    o.OrderDate
FROM CustomerTable c
INNER JOIN OrderTable o
ON c.CustomerID = o.CustomerID;

---

LEFT JOIN

SQL

SELECT
    c.CustomerName,
    o.OrderNumber
FROM CustomerTable c
LEFT JOIN OrderTable o
ON c.CustomerID = o.CustomerID;

---

Aggregate Functions

SQL

SELECT
    COUNT(*) AS TotalCustomers,
    MAX(CreditLimit) AS HighestLimit,
    MIN(CreditLimit) AS LowestLimit,
    AVG(CreditLimit) AS AverageLimit
FROM CustomerTable;

---

SUM

SQL

SELECT
    SUM(NetAmount) AS TotalSales
FROM InvoiceTable;

---

TOP Records

SQL

SELECT TOP 10 *
FROM CustomerTable;

---

BETWEEN

SQL

SELECT *
FROM InvoiceTable
WHERE InvoiceDate
BETWEEN :StartDate
AND :EndDate;

---

LIKE

SQL

SELECT *
FROM CustomerTable
WHERE CustomerName
LIKE :SearchText;

Example

{
    "parameters": {
        "SearchText": "John%"
    }
}

---

IN

SQL

SELECT *
FROM CustomerTable
WHERE City IN ('Bangalore','Mumbai');

---

Date Functions

SQL

SELECT
    GETDATE() AS CurrentDate,
    YEAR(GETDATE()) AS CurrentYear,
    MONTH(GETDATE()) AS CurrentMonth;

---

String Functions

SQL

SELECT
    UPPER(CustomerName),
    LOWER(CustomerName),
    LEN(CustomerName)
FROM CustomerTable;

---

Mathematical Functions

SQL

SELECT
    ROUND(NetAmount,2),
    ABS(Balance),
    CEILING(Price)
FROM InvoiceTable;

---

Pagination

SQL

SELECT *
FROM CustomerTable
ORDER BY CustomerID
OFFSET :Offset ROWS
FETCH NEXT :Limit ROWS ONLY;

---

Stored Procedure

SQL

EXEC usp_GetCustomerDetails
    @CustomerID = :CustomerID;

Note: Stored procedure support depends on your framework version and configuration.

---

Best Practices

• Use parameterized queries.
• Avoid SELECT * in production.
• Keep one query per SQL file.
• Use indexes on frequently filtered columns.
• Test queries in SQL Server Management Studio before deploying.

---

Next Steps

Continue with:

• Response Format
• Error Handling
• Performance Tips
• Security Best Practices