SQL Summary by Kate Lange
Note: Please use these notes as a reminder of work already learnt, do not study from these notes
alone.
SELECT:
SELECT* (wildcard selects all fields)
SELECT TOP <n>
SELECT <field1>, <field2>
SELECT DISTINCT (removes dupicates)
SELECT <Calculation> AS <Name of new Field>
SELECT INT <value>
SELECT ROUND <Value>
SELECT MOD <value> (modulus)
SELECT INT(RND <field/Value> )* (max-min +1) + min
SELECT LEFT, RIGHT (<Field>, <n>)
SELECT MID (<Field>, <n1> <n2>)
SELECT LEN <Field> ( gives number of characters)
o SELECT Surename & ‘ ’ & LEFT (FirstName, 1) AS FullNameAndInitals
SELECT MAX/MIN (<field>) AS <new Alias> (give larges and smallest numbers in their fields
respectively)
SELECT SUM (<field>) AS <new Alias> (adds all values in a field)
SELECT COUNT (<field>) As <new Alias> (Counts values in a field)
SELECT AVG (<field>) As <new Alias> (averages all numbers in a field)
FROM:
<tableName>
WHERE:
WHERE <condition> (something like totalPages > 3
WHERE <field> BETWEEN <condition> AND <condition>
WHERE <field> IN (values) AND NOT IN (values)
WHERE <field> LIKE ‘abc*’ (must start with abc but after it can end in anything cause wild
card *)
WHERE <Field> <Condition (<; > ect)> (<SELECT e.g. AVG(Pages)>)
ORDER BY:
<field1>, <field2>
ASC and DESC
GROUP BY:
Allows to order via an aggregate function. IE use Alias
HAVING works the same why that WHERE does for SELECT
Calculating Time working with years
SELECT
YEAR/MONTH/DAY/HOUR/MINUTE/SECOND (dateTimeValue)
ie YEAR (<field Value>) isolates the year from that value
NOW () can also be seen as a variable
EG
o SELECT DOB,
YEAR (DOB) AS Year,
(NOW ()- DOB)/ 365.25 AS ActualAge
INSERT INTO:
INSERT INTO <tableName>
VALUES <Values>
MULTIPLE RECORDS w/ EMBBEDDED SELECT
INSERT INTO <tableName> <Fields>
SELECT <fields>
FROM <tableName>
WHERE <Conditions>
Eg:
INSERT INTO PrintLogs (PDate, SureName,FirstName…)
SELECT PDATE, ‘Soap’, ‘joe’….)
FROME PrintLogs
Where SureName = ‘Schlute’ AND FirstName = ‘Faith’
This adds records for Joe that are exactly the same as Faiths
UPDATE:
UPDATE <tableName>
SET <Field> = <value>,<Field> = <value>,…
WHERE <condition>
DELETE :
DELETE FROM <tableName>
WHERE <condition>
