SQL statements are English-like database queries.
Keywords include SELECT, UPDATE, WHERE, ORDER BY, etc.
ANSI Standard SQL is the lingua franca for relational databases.
List all customers in Italy.
SELECT FirstName, LastName, City, Country FROM Customer WHERE Country = 'Italy'
Result: This query returns 3 rows with customer data.
Many people don't know this, but SQL was originally designed to be entered on a console and results would display back to a screen.
Hence the English like syntax.
However, this never happened because by mistyping a query the operator could potentially do great damage to the database. Imagine typing 'DELETE Customer WHERE Id = 1442', but accidentally hitting the Enter key after the word 'Customer'.
Today, SQL is mostly used by programmers who use SQL embedded in their programs to build applications that require data from a database.
SQL supports four fundamental operations, collectively known as CRUD (Create, Read, Update, Delete). They are:
CRUD is an important concept because it gives users total control over their data. It allows them to retrieve, add, update, and remove any data item. Next, we'll review each of the CRUD operations.
The general form of a SELECT statement.
SELECT column-names FROM table-name WHERE condition ORDER BY sort-order
Problem: List all customers in Paris sorted by last name.
SELECT FirstName, LastName, City, Country FROM Customer WHERE City = 'Paris' ORDER BY LastName
The general form of an INSERT statement.
INSERT table-name (column-names) VALUES (column-values)
Problem: Add Oxford Trading to the list of suppliers.
INSERT Supplier (CompanyName, ContactName, City, Country) VALUES ('Oxford Trading', 'Ian Smith', 'Oxford', 'UK')
The general form of an UPDATE statement.
UPDATE table-name SET column-name = column-value WHERE condition
Problem: Supplier with id 24 has moved. Change their city from Sydney to Melbourne.
UPDATE Supplier SET City = 'Melbourne' WHERE Id = 24
The general form of a DELETE statement.
DELETE table-name WHERE condition
Problem: Remove 'Stroopwafels' from the products.
DELETE Product WHERE ProductName = 'Stroopwafels'