SQL Test in Delphi - Questions with Examples
This guide contains possible SQL-related questions you may encounter in a Delphi test, complete
with examples and Delphi integration tips.
1. Basic SQL Questions with Examples
- What is SQL and how is it used in Delphi?
Example: SQL (Structured Query Language) is used to interact with databases. In Delphi, SQL is
used in components like TFDQuery or TADOQuery to run commands like SELECT, INSERT,
UPDATE, etc.
- Write a basic SQL SELECT query to retrieve all rows from a table called Customers.
Example: SELECT * FROM Customers;
- How do you connect a Delphi application to a database using FireDAC or ADO?
Example: You use components like TFDConnection or TADOConnection to specify database
parameters and connect.
- How do you use parameters in a SQL query in Delphi?
Example: FDQuery.SQL.Text := 'SELECT * FROM Customers WHERE City = :City';
FDQuery.ParamByName('City').AsString := 'Pretoria';
- What is the purpose of the WHERE clause in SQL?
Example: The WHERE clause filters records.
Example: SELECT * FROM Customers WHERE City = 'Pretoria';
- Write a query to select all customers from the Customers table where the city is 'Pretoria'.
Example: SELECT * FROM Customers WHERE City = 'Pretoria';
- How do you use the ORDER BY clause in a SELECT statement?
Example: SELECT * FROM Customers ORDER BY LastName ASC;
- Explain the purpose of the LIKE operator in SQL and give an example.
Example: It is used for pattern matching.
Example: SELECT * FROM Customers WHERE LastName LIKE 'S%';
2. Intermediate SQL and Delphi Integration Questions with Examples
- How do you execute an INSERT SQL statement from Delphi?
Example: FDQuery.SQL.Text := 'INSERT INTO Customers (Name, Email) VALUES (:Name,
:Email)';
FDQuery.ParamByName('Name').AsString := 'John';
FDQuery.ParamByName('Email').AsString := 'john@example.com';
FDQuery.ExecSQL;
- Write a SQL query to update the email of a customer with CustomerID = 5.
Example: UPDATE Customers SET Email = 'newemail@example.com' WHERE CustomerID = 5;
- Explain how SQL injection works and how to prevent it in Delphi.
Example: SQL injection is when users inject SQL code into inputs. Prevent it by using parameters
instead of concatenating SQL strings.
- Describe the difference between INNER JOIN and LEFT JOIN.
Example: INNER JOIN shows matching records in both tables. LEFT JOIN shows all records from
the left table and matching ones from the right.
- Write a query that joins the Orders and Customers tables using CustomerID.
Example: SELECT Customers.Name, Orders.OrderDate FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
- How do you use SQL transactions in Delphi?
Example: FDConnection.StartTransaction;
try
FDQuery.ExecSQL;
FDConnection.Commit;
except
FDConnection.Rollback;
end;
- What is the purpose of a primary key in a database table?
Example: It uniquely identifies each row in a table.
- How can you use TFDQuery or TADOQuery to show SQL results in a DBGrid?
Example: Connect TDataSource to the query, then connect DBGrid to the DataSource:
DBGrid1.DataSource := DataSource1;
DataSource1.DataSet := FDQuery1;
3. Advanced / Hard Questions with Examples
- Write a query to count the number of orders per customer and display only those with more
than 3 orders.
Example: SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 3;
- How do you use stored procedures in Delphi, and how do you pass parameters to them?
Example: FDStoredProc.StoredProcName := 'GetCustomerOrders';
FDStoredProc.ParamByName('CustomerID').AsInteger := 1;
FDStoredProc.ExecProc;
- Write a SQL query to find duplicate email addresses in the Customers table.
Example: SELECT Email, COUNT(*) FROM Customers
GROUP BY Email
HAVING COUNT(*) > 1;
- How do you handle database errors (like constraint violations) in Delphi?
Example: Use try-except blocks:
try
FDQuery.ExecSQL;
except
on E: Exception do ShowMessage(E.Message);
end;
- What are the differences between FireDAC and ADO for database access in Delphi?
Example: FireDAC is newer, supports more databases, and is more flexible. ADO is older and
mainly for MS Access/SQL Server.
- Write a Delphi function that runs a parameterized SELECT query and returns the result as a
string.
Example: function GetCustomerName(ID: Integer): string;
begin
FDQuery.SQL.Text := 'SELECT Name FROM Customers WHERE CustomerID = :ID';
FDQuery.ParamByName('ID').AsInteger := ID;
FDQuery.Open;
Result := FDQuery.FieldByName('Name').AsString;
end;
- Describe how you would optimize a slow-performing SQL query in a Delphi application.
Example: Use indexes, avoid SELECT *, minimize joins, and check query execution plans.
- How would you implement role-based access control (RBAC) in a SQL database used by
Delphi?
Example: Create Roles table, UserRoles table, and check permissions before allowing operations
in Delphi code.
