Summary Grade 11 - 12 Information Technology Delphi SQL Study Guide

DELPHI SQL NOTES

, SQL COMMAND SUMMARY

SQL Description Usage
SELECT Used to select data from a database SELECT * FROM tblTowns
SELECT * FROM tblTowns
WHERE Used to specify a filter WHERE Population > 1000
SELECT MAX(Population)
as HighestPopulation
AS Used to assign a name to a column from tblTowns;
SELECT * FROM tblTowns
WHERE Province LIKE
LIKE Used to search for a pattern 'G%';
SELECT * FROM tblTowns
WHERE Province IN
('Gauteng','Eastern
IN Used to specify multiple values Cape') ;

SELECT * FROM tblTowns
BETWEEN Used to select a given range WHERE Population BETWEEN

SELECT * FROM tblTowns
ORDER BY Used to sort data in ascending or descending ORDER BY TownName;
SELECT Province, COUNT
(*) AS CriticalTowns
GROUP BY Used in conjuntion with Aggregate Functions to group data FROM
tblTowns WHERE WaterRe-
strictions = TRUE GROUP
BY Province;
INSERT INTO tblGames
VALUES (76,
#2017/12/24#, "HM008",
INSERT INTO Inserts data from a table 250, 5566);
UPDATE tblTowns SET Wa-
terRestrictions = True
WHERE Province = "North
UPDATE Updates data from in a table West";

DELETE FROM tblDams
DELETE Delete data from a table WHERE HeightOfWall <
SELECT Count(Population)
COUNT Return the number of occurences FROM tblTowns;
SELECT MIN(Population)
MIN Returns smallest value of a column from tblTowns;
SELECT MAX(Population)
MAX Returns highest value of a column from tblTowns;
SELECT AVG(Population)
AVG Returns the average of a column from tblTowns;
SELECT SUM(Population)
SUM Returns the total of a column from tblTowns;


NOW Returns current date and time SELECT NOW();
SELECT DAY(#2019-01-
DAY Returns day of month for a given date 23#);
SELECT MONTH(#2011-02-
MONTH Returns the month part of a date 14#);
SELECT YEAR(#2015-04-
YEAR Returns the year part of a date 14#);
SELECT ROUND(123.321,
ROUND Rounds a number to a specific number of decimal places 2);
Rounds a number to a specific number of decimal places, re- SELECT FORMAT
FORMAT turns string (48934892.34893, 3);