The Structured Query Language, SQL, is a standard language to manipulate databases. There are four basic operations of databases: Create, Read, Update and Delete (CRUD). The required SQL statement for each operation depends on the data object(s) handled. Here is a summary of the most common ones:

Operation	SQL Statement	Description
Create	CREATE TABLE…	Creates a new table, defining field names and data types
	SELECT… INTO…	Creates a new table from a query result
	INSERT INTO… VALUES…	Creates tuples in a table
	INSERT INTO… SELECT…	Creates tuples in a table obtained from a query
Read	SELECT… FROM… WHERE…	Selects columns from table where condition is met
Update	UPDATE… SET… WHERE…	Updates field values of tuples satisfying a condition
Delete	DELETE… FROM… WHERE	Deletes tuples satisfying a condition
	DROP TABLE…	Deletes table
	TRUNCATE TABLE…	Deletes all data in the table

This Wikipedia article in provides more details about SQL.

Formatting in SQL

Before continuing with statements, here are some SQL formatting rules:

1. SQL is NOT case sensitive: SELECT is the same as select.

Note: for this reason many people use underscore to name columns instead of camel case. For instance Student_ID instead of StudentID.

2. SQL ignores white space and newline characters. This gives you flexibility about formatting query statements: you can put SELECT, FROM, WHERE clauses on separate lines and indent each one, or whatever makes your code more human-readable, without affecting its machine-readability.

3. Semicolons after SQL statements are the standard way to separate multiple statements. Some DBMS don’t require them for single statements.

Creating Tables

In Access, when using the create table command, you need to define each field’s data type, and you should also specify the primary key. More details are here, including how to use the menu options to create a table. You can also create a query and type the following in “SQL View”:

CREATE TABLE Students (StudentID number, StudentName text, Program text, Year number, Age number, primary key (StudentID))

Data Types in SQL: note that these may be slightly different in different implementations of SQL: here is a list of the types in different versions, and here is a list of data types in Access specifically. For example, the type “text” in Access is a character string (may also contain numbers) that has a maximum of 255 characters, while the type “text” in MySQL has a maximum length of 65,535 characters, and in SQL Server the type “text” can contain up to 2 GB of data.

NULL values are allowed in all the types. Null represents an unknown value or that a value does not exist. To prohibit null values in a column you need to add the statement NOT NULL in the field declaration (e.g. StudentID Number NOT NULL).

Reading: A typical SQL query has the form:

SELECT Columns
FROM Tables
WHERE Conditions

The result of a SQL query is a new table called the result set.

SELECT INTO: creates a new table from a query result set. The fields will have the same data type of the original fields.

SELECT StudentID, StudentName INTO MM13
FROM Students
WHERE Program = “MMOR” AND Year = 2013 ;

Creating Tuples

You can insert a tuple into a table setting the value of each field:

INSERT INTO Students VALUES (536880, “Harry Potter”, “MMOR”, 2017, 23)

Or you can create a tuple but define only a subset of fields:

INSERT INTO Students (StudentID, StudentName) VALUES (536881, “Ron Weasley”)

Alternatively, you can insert the rows from another table:

INSERT INTO Students SELECT * FROM Admitted_Students

Updating

You can update field values of tuples satisfying a condition. E.g. increase the age of all students by 1:

 UPDATE Students SET Age = Age + 1 WHERE Age > 10

Deleting

Deleting tuples satisfying a condition:

DELETE FROM Students WHERE name = “Harry Potter”

Deleting a table:

DROP TABLE MM13 ;

Deleting all data inside a table but preserving the table structure:

TRUNCATE TABLE MM13 ;

You can find an extensive list of SQL commands and examples of how to use them here. The help page for Access is also very informative.