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
HAVINGCOUNT(*) >10;

---

INNER JOIN

SQL

SELECTc.CustomerName,
o.OrderNumber,
o.OrderDateFROM CustomerTable c
INNER JOIN OrderTable o
ONc.CustomerID=o.CustomerID;

---

LEFT JOIN

SQL

SELECTc.CustomerName,
o.OrderNumberFROM CustomerTable c
LEFT JOIN OrderTable o
ONc.CustomerID=o.CustomerID;

---

Aggregate Functions

SQL

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

---

SUM

SQL

SELECTSUM(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

SELECTUPPER(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