SQL — Structured Query Language Grade 12

SQL (pronounced "S-Q-L" or "sequel") is the language used to communicate with a database. It lets you retrieve, add, update and delete records. In Delphi, SQL is added to a TADOQuery component.

What is SQL?

Think of a database as a massive spreadsheet with thousands of rows. SQL is how you ask the database a question: "Give me all students in Grade 12 with a mark above 80, sorted by surname." Without SQL, you would have to scroll through every row manually.

Anatomy of a SQL Statement

Using SQL in Delphi

SQL goes into the SQL property of a TADOQuery component. Always close the query before changing the SQL, then open it again.

Delphi — two ways to run a query

// Method 1: assign as one string
qryStudents.Close;
qryStudents.SQL.Text := 'SELECT * FROM tblStudents WHERE Grade = 12';
qryStudents.Open;

// Method 2: build line-by-line (easier to read for long queries)
qryStudents.Close;
qryStudents.SQL.Clear;
qryStudents.SQL.Add('SELECT Name, Surname, Mark');
qryStudents.SQL.Add('FROM tblStudents');
qryStudents.SQL.Add('WHERE Grade = 12');
qryStudents.SQL.Add('ORDER BY Surname ASC');
qryStudents.Open;

SELECT — Choosing What to Display

SQL

SELECT * FROM tblCD;                      -- all fields, all records
SELECT CDName, Artist FROM tblCD;          -- only these two fields
SELECT DISTINCT Genre FROM tblCD;          -- each genre shown once only
SELECT TOP 5 * FROM tblCD;               -- first 5 records only

WHERE — Filtering Records

The WHERE clause is how you filter — like asking "show me only the records that match this condition."

SQL — comparison operators

WHERE Genre = "Rock"             -- exact match (text in double quotes)
WHERE Price < 100               -- numbers (no quotes)
WHERE Grade <> 11              -- not equal
WHERE Mark >= 50              -- greater than or equal
WHERE Genre = "Pop" AND Price < 100       -- both must be true
WHERE Genre = "Pop" OR Genre = "Rock"     -- either can be true

Special WHERE Operators

Operator	Meaning	Example
`LIKE`	Pattern match. `%` = any chars, `_` = one char	`WHERE Name LIKE "S%"` — starts with S
`IN`	Match any value in a list	`WHERE Genre IN ("Rock","Pop","Jazz")`
`BETWEEN`	Within a range (inclusive for numbers)	`WHERE Price BETWEEN 50 AND 150`
`IS NULL`	Field is empty/blank	`WHERE CellNo IS NULL`
`IS NOT NULL`	Field has a value	`WHERE CellNo IS NOT NULL`
`#date#`	Date values use # symbols	`WHERE OrderDate = #2024-05-01#`

LIKE wildcard examples

WHERE CDName LIKE "S%"        -- starts with S
WHERE CDName LIKE "%love%"    -- contains the word "love"
WHERE CDName LIKE "%s"        -- ends with s
WHERE Code   LIKE "A__"       -- starts with A followed by exactly 2 chars

ORDER BY — Sorting Results

SQL

SELECT * FROM tblCD ORDER BY Artist;             -- ascending (default)
SELECT * FROM tblCD ORDER BY Price DESC;        -- most expensive first
SELECT * FROM tblCD ORDER BY Genre, CDName ASC; -- sort by genre, then name

Calculated Fields & Aliases

You can do maths inside a SELECT and give the result a name using AS.

SQL

SELECT Name, Price, Price * 1.15 AS PriceWithVAT FROM tblProducts;
SELECT Name, Mark, ROUND(Mark / 2, 0) AS HalfMark FROM tblStudents;

Aggregate Functions — Calculating Summaries

Function	What it calculates	Example
`COUNT(*)`	Number of records	`SELECT COUNT(*) FROM tblStudents`
`SUM(field)`	Total of numeric field	`SELECT SUM(Mark) FROM tblResults`
`AVG(field)`	Average of numeric field	`SELECT AVG(Mark) FROM tblResults`
`MIN(field)`	Lowest value	`SELECT MIN(Price) FROM tblProducts`
`MAX(field)`	Highest value	`SELECT MAX(Price) FROM tblProducts`

SQL — GROUP BY and HAVING

-- Count students per grade
SELECT Grade, COUNT(*) AS Total
FROM tblStudents
GROUP BY Grade;

-- Only show groups with more than 10 students
SELECT Grade, COUNT(*) AS Total
FROM tblStudents
GROUP BY Grade
HAVING COUNT(*) > 10;

WHERE vs HAVING

WHERE filters individual records before grouping. HAVING filters groups after GROUP BY. If you have both, WHERE runs first.

SQL String & Math Functions

Function	Returns	Example
`LEN(field)`	Character count	`SELECT LEN(Name) FROM tblStudents`
`LEFT(field, n)`	First n characters	`LEFT(Surname, 3)` → first 3 letters
`RIGHT(field, n)`	Last n characters	`RIGHT(IDNo, 4)`
`MID(field, start, n)`	Substring from position	`MID(IDNo, 3, 2)` → year of birth
`ROUND(field, dec)`	Rounded value	`ROUND(Avg, 2)` → 2 decimal places
`INT(field)`	Integer part (no decimal)	`INT(75.8)` = 75
`FORMAT(field, "0.00")`	Formatted string	`FORMAT(Price, "R0.00")`

Date Functions

SQL — date examples

SELECT Name, YEAR(BirthDate) AS BirthYear FROM tblStudents;
SELECT * FROM tblOrders WHERE MONTH(OrderDate) = 12;     -- December orders
SELECT * FROM tblStudents WHERE YEAR(BirthDate) = 2008;

INSERT, UPDATE, DELETE

SQL — modifying data

-- Add a new record
INSERT INTO tblStudents (Name, Surname, Grade)
VALUES ("Alice", "Smith", 12);

-- Change existing data
UPDATE tblStudents
SET Grade = 12
WHERE StudentID = 45;

-- Remove a record
DELETE FROM tblStudents
WHERE StudentID = 45;

Always use WHERE with UPDATE and DELETE

Without a WHERE clause, UPDATE changes every record and DELETE removes every record in the table. Always double-check your condition.

Parameterised Queries

Instead of hardcoding a value, use a parameter that is filled in by the user at runtime.

Delphi — parameterised query (by name method)

qryStudents.Close;
qryStudents.SQL.Clear;
qryStudents.SQL.Add('SELECT * FROM tblStudents');
qryStudents.SQL.Add('WHERE Grade = :pGrade');   // :pGrade is the parameter
qryStudents.Parameters.ParamByName('pGrade').Value := sedGrade.Value;
qryStudents.Open;

SQL in Delphi — Reading Field Values

Delphi

// Read a field value from the current record
sName   := qryStudents['Name'];
iGrade  := qryStudents['Grade'];
rMark   := qryStudents['Average'];

// Loop through all records
qryStudents.First;
while not qryStudents.Eof do
begin
  memOut.Lines.Add(qryStudents['Name'] + ' ' + qryStudents['Surname']);
  qryStudents.Next;
end;

← PreviousDatabases in Delphi Next →SQL — Joining Tables