Summary - Information Technology

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




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

SELECT <field(s)>



2