SQL — Structured Query Language Grade 12

// 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 * 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 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

WHERE Genre = "Pop" OR Genre = "Rock" AND Price < 100
-- reads as: Genre = "Pop"  OR  (Genre = "Rock" AND Price < 100)
-- ALL Pop CDs are included, regardless of price!

-- To make BOTH genres respect the price limit, use brackets:
WHERE (Genre = "Pop" OR Genre = "Rock") AND Price < 100

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

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

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

-- 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;

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

-- 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;

qryStudents.Close;
qryStudents.SQL.Text := 'INSERT INTO tblStudents (Name, Surname) VALUES (' +
  QuotedStr(edtName.Text) + ', ' + QuotedStr(edtSurname.Text) + ')';
qryStudents.ExecSQL;   // use ExecSQL (not Open) for INSERT/UPDATE/DELETE — they don't return records

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;

// 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;

procedure TfrmMain.btnSearchClick(Sender: TObject);
var
  iFound : integer;
begin
  memOut.Clear;
  iFound := 0;

  // 1. Build a parameterised query from the user's input
  qryStudents.Close;
  qryStudents.SQL.Clear;
  qryStudents.SQL.Add('SELECT Name, Surname, Grade, Average');
  qryStudents.SQL.Add('FROM tblStudents');
  qryStudents.SQL.Add('WHERE Surname LIKE :pName');
  qryStudents.SQL.Add('ORDER BY Surname ASC');
  // add a wildcard so 'coe' matches 'Coetzee'
  qryStudents.Parameters.ParamByName('pName').Value := edtSearch.Text + '%';
  qryStudents.Open;

  // 2. Loop through the result set and display each record
  qryStudents.First;
  while not qryStudents.Eof do
  begin
    memOut.Lines.Add(qryStudents['Name'] + ' ' + qryStudents['Surname']
                     + '  (Gr ' + IntToStr(qryStudents['Grade'])
                     + ', ' + FloatToStrF(qryStudents['Average'], ffFixed, 4, 1) + '%)');
    Inc(iFound);
    qryStudents.Next;
  end;

  // 3. Report how many were found
  if iFound = 0 then
    ShowMessage('No learner found starting with "' + edtSearch.Text + '".')
  else
    lblCount.Caption := IntToStr(iFound) + ' learner(s) found';
end;