SQL STATEMENT SUMMARY
Format of Insert Statement:
INSERT INTO <table> (<field>)
VALUES <value1>, <value2>, … , <valuen>
//Insert a record with the values to the fields listed
Format of an Insert with a Select (Not for Grade 11)
INSERT INTO tblVisits (PrisonerID, VisitorName, VisitLength,VisitDate)
SELECT PrisonerID, ‘Joan’, VisitLength, NOW()
FROM tblVisits
WHERE PrisonerID = 3 AND VisitorName = ‘Susan’
//Use a SELECT statement in place of VALUES. Make sure the fields match the fields in the first line.
Format of Delete Statement: (use with caution)
DELETE FROM <table>
WHERE <condition> AND/OR <condition>
//Delete the record with the field that fits the condition
Updating Data in a Table
UPDATE <table>
SET <field> = <new value>
WHERE <field> = <existing value>
//Change the value in the fields of the record that fits the condition to the new value
Format of Order Statement (Sorting)
SELECT <field(s)>
FROM <table>
ORDER BY <field1>, … , <fieldn>
//Order the records alphabetically by the first field then the next field
SELECT <field(s)>
FROM <table>
ORDER BY <field1> DESC, … , <fieldn> DESC
//Order the records alphabetically in descending order by the first field then the next field
Use a comma to separate multiple sorting fields NOT AND.
1
Selecting Data in a Table
SELECT <field(s)>
FROM <table>
//Limit the amount of fields on the table
SELECT *
FROM <table>
//Show all fields of the table
Format to See Specific Records
SELECT <field(s)>
FROM <table>
WHERE <condition>// Show the records that fit the condition
SELECT <field(s)>
FROM <table>
WHERE <condition> LIKE <*pattern>
//The records where the pattern exists at the end of the value
LIKE <pattern*>
//The records where the pattern exists at the beginning of the value
LIKE <*pattern*>
//The records where the pattern exists anywhere in the value
SELECT <field(s)>
FROM <table>
WHERE <condition> AND <condition>
//The records where both the conditions are true
SELECT <field(s)>
FROM <table>
WHERE <condition> OR <condition>
//The records where either of the conditions are true
SELECT <field(s)>
FROM <table>
WHERE <field> BETWEEN <value1> AND <value2>
//The records where the field has values between the two values specified
SELECT <field(s)>
FROM <table>
WHERE <field> IN (<value1>, <value2>, …. ,<value2>)
//The records where the field has values in the square brackets. This is the same as
WHERE <field> = <value1> OR <field> = <value2> OR … OR <field> = <value2>
SELECT <field(s)>
FROM <table>
WHERE <field> NOT IN (<value1>, <value2>, …. ,<value2>)
//The records where the field has NONE of the values in the square brackets
2
SELECT <field(s)>
FROM <table>
WHERE <field> IS NULL
//The records where the field has a NULL value. Do not use =.
SELECT TOP <value> <field(s)>
FROM <table>
WHERE <condition>
ORDER BY <fields(s)>
//Only the records before and including the limit value are shown. E.g. if LIMIT is 5 you get 5 records. If
ORDER BY is not used you get the first records of the table in the order the table was created.
To get the top 5 rows, add DESC to ORDER BY <field(s)>
SELECT <fields>
FROM <table>
WHERE <condition>
GROUP BY <field>
HAVING (<condition1>, <condition2>…)
//Having is for the conditions for the new result set – i.e. use WHERE when the field is in the existing table,
use HAVING when you have GROUP BY and have created a new field.
NB* use only when using GROUP BY
SELECT DISTINCT <fields>
FROM <table>
//Removes all duplicates
Embedded Queries (Not Grade 11)
SELECT Name, Salary
FROM tblTeachers
WHERE Salary =
(SELECT MAX(Salary)
FROM tblTeachers)
//Criteria for the WHERE statement is another SQL statement
Finding the person with the above average salary
SELECT Name, Salary
FROM tblTeachers
WHERE Salary >
(SELECT AVG(Salary)
FROM tblTeachers)
Finding the person with the highest salary
//If you just want the the highest SALARY
SELECT MAX( Salary )
FROM tblTeachers
//If you want the person who got the highest salary - assuming only ONE person got it
SELECT TOP 1 name, salary
FROM tblTeachers
ORDER BY salary DESC
3
//If you want the person who got the highest salary. You may have more than one person who earns the
highest salary
SELECT Name, Salary
FROM tblTeachers
WHERE Salary = (SELECT MAX(Salary) FROM tblTeachers)
Format for Word Manipulation
Note - all strings numbered from 1
SELECT LEFT(<field>,<value>) AS <field>
FROM <table>
//characters from left until the value
SELECT RIGHT(<field> , <value>) AS <field>
FROM <table>
//all characters from right until the value from right
SELECT MID(<field>, <value1>, <value2>) AS <field>
FROM <table>
//all characters from value 1 until the amount of value 2 e.g. (3,2) will give you the 3rd and 4th values
SELECT LENGTH(<field>) AS <field>
FROM <table>
//give the length of the field
SELECT <value1> & <value2> & <value3> AS <field>
FROM <table>
//gives the values combined together as one string value
SELECT INSTR(<string>,<substring>) AS <field>
FROM <table>
//gives the position of the first letter of the substring in the string
Example:
SELECT INSTR(surname,'van') AS PositionVan
FROM tblUsers
//gives the position of 'van' in a surname. If 'van' is not in the surname 0 will be returned.
SELECT INSTR (<number>,<substring>,<string>) AS <field>
FROM <table>
//gives the position of the first letter of the substring in the string starting the search from position <number>
Format as a certain type
SELECT FORMAT (CALCULATION, “PERCENT”)
FROM <TABLE>
//displays a value as a %
SELECT FORMAT (<FIELD> , “Long date”)
FROM <TABLE>
//displayes a date in a certain format in this case a long date
Specify the format in “ “
Eg SELECT FORMAT(123456789, '##-##-#####');
Displays 12-34-56789
4
Format for Date Manipulation
SELECT MONTH(<field>) AS <field>
FROM <table>
//gives the month of the date
SELECT DAY(<field>) AS <field>
FROM <table>
//gives the day of the date
SELECT YEAR(<field>) AS <field>
FROM <table> //gives the year of the date
SELECT NOW() AS <field>
FROM <table>
//gives the current date from the computer clock
To calculate age accurately use: (NOW() – <DOB>)/365.25 where <DOB> is a field that stores the
birth date in your table.
Format for Number Manipulation
SELECT AVG(<field>) AS <field>
FROM <table>
//gives the average of the records for that field
SELECT COUNT(*)
FROM <table>
WHERE <condition>
//Counts the amount of records in the table with the condition
SELECT MAX(<field>) AS <field>
FROM <table>
//Show the record with the biggest value in the field
SELECT MIN(<fields>) AS <field>
FROM <table>
//Show the record with the smallest value in the field
SELECT SUM(<field>) AS <field>
FROM <table>
//Show total sum of the records in the field
SELECT <field>, COUNT(*) AS <field>
FROM <table>
WHERE <condition>
GROUP BY <field>
//Show the amount of records in every field in the group by condition
SELECT ROUND(<field>,<value>) AS <field>
FROM <table>
//Rounds the field off to the “valueth” decimal place e.g. (3.456,1) => 3.5
SELECT RAND( ) AS <field>
FROM <table>
//Gives a random number from 0 to 0.9999…
5
SELECT MOD(<value>,<value>) AS <field>
FROM <table>
//Gives the remainder of the first value after dividing by the second value, same as the % for java
SELECT INT(<value>,<value>) AS <field>
FROM <table>
//Gives the integer value of a number after truncating the decimals.
Also FLOOR and CEILING
SELECT TRUNCATE(<field manipulated>) FLOOR (7.9 to 7) and CEILING (7.1 to 8)
FROM <table>
//Gives the value in the same format as the field
SELECT FLOOR(RND(<field>) * (NoofOptions)) + StartNum
//The field must be a numeric field to seed the random function.
SELECT <field>
FROM <table>
WHERE <field> IN (<value>, <value> …)
//Same as saying <field> = <value> OR <field> = <value> …
Format for Table Joins (Not for Grade 11)
SELECT <table>.<field>
FROM <table1>, <table2>
WHERE <table1>.<field> = <table2>.<field>
//Inner join by just using where conditions
SELECT <table>.<field>
FROM <table1>
INNER JOIN <table2> ON <table1>.<field> = <table2>.<field>
//Inner join with full format and the order of the tables do not matter
SELECT <table>.<field>
FROM <table1>
LEFT OUTER JOIN <table2> ON <table1>.<field> = <table2>.<field>
//Outer join, table 1 contains info and the second has null value
To find the fields that are only in table1 but NOT table 2:
SELECT <table>.<field>
FROM <table1>
LEFT OUTER JOIN <table2> ON <table1>.<field> = <table2>.<field>
WHERE <table1.field> IS NULL
OR
SELECT <table1>.<field>
FROM <table1>
WHERE <table1.field> NOT IN
(SELECT <table2>.<field> FROM <table2>)
SELECT <table>.<field>
FROM <table1>
RIGHT OUTER JOIN <table2> ON <table1>.<field> = <table2>.<field>
//Outer join, table 2 contains info and the first has null value
6
SELECT <table>.<field>
FROM <table1>
FULL JOIN <table2> ON <table1>.<field> = <table2>.<field>
//Outer join, either table has info and either can have null value
Order of the Select Statement (Learn this order Grade 11 and Grade 12))
SELECT TOP <value> DISTINCT <field>
FROM <table>
WHERE <condition>
GROUP BY <field>
HAVING <condition>
ORDER BY <field>
