Commands

Info

This only contains required commands by OCR.
Useful commands beyond the OCR specification can be found in Additional Commands.

Select

Fetches data from a given set.

SELECT <field>
FROM <table>
(?) WHERE <condition>

Order By

Sorts the output by either ascending (ASC) or descending.

SELECT <field>
FROM <table>
(?) WHERE <condition>
ORDER BY <field> <ASC|DESC>

This may be chained along to perform more complex orders, i.e., sort ascending by country but descending by forename.

SELECT *
FROM Person
ORDER BY Country ASC, Forename DESC 

Nested Statement

Within SQL, you can nest statements. They are executed furthest-first.

SELECT *
FROM Person
WHERE Age > (
	SELECT Age
	FROM Person
	WHERE Name = "Peterson"
)

assuming Peterson’s age is 16, the statement then becomes:

SELECT *
FROM Person
WHERE Age > 16

Insert

Insert inserts a row into a table.

INSERT INTO <table>(<fields...>)
VALUES (<values...>)

Example

INSERT INTO Person(ID, Name, Age, Country)
VALUES(127, "Darragh", 16, "Great Britain")

Delete

Delete deletes all matching rows. Alone, it will delete all rows within a table. It can be filtered using a where condition.

DELETE FROM <table>
(?) WHERE <condition>

Drop Table

Drops (deletes) a table from a database.

DROP TABLE <table>

Update

Update updates a field of records. Alone, it will update all rows within a table. It can be filtered using a where condition.

UPDATE <table> 
SET <field> 
(?) WHERE <condition>

Join

Used to combine two or more tables through a common relationship.

JOIN <table>
ON <table>.<field> <table>.<field>

Addition reading., outside of OCR specification.

Examples

Example 1

SELECT Student.Forename, Student.Surname, TutorGroup.Room
FROM Student
JOIN TutorGroup 
ON Student.TutorGroup = TutorGroup.TutorGroup

First, Person, C100
Second, Person, C100
Third, Persona, C100
Single, Student, C200

Example 2

SELECT Student.Forename, Student.Surname, TutorGroup.Room
FROM Student
JOIN TutorGroup 
ON Student.TutorGroup = TutorGroup.TutorGroup
WHERE TutorGroup.Room = "C100" AND Student.Surname = "Personal"

First, Person, C100
Second, Person, C100

Table

Examples tables the statement would enact upon.

Student

ID	Forename	Surname	TutorGroup
1	First	Person	11A
2	Second	Person	11A
3	Third	Persona	11A
4	Single	Student	11B

Table

TutorGroup	Teacher	Room
11A	Mr. Abc	C100
11B	Ms. Miss	C200