SQL Syntax

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.

SQL Example

#

List all customers in Italy.

SELECT FirstName, LastName, City, Country
  FROM Customer
 WHERE Country = 'Italy'

Result: This query returns 3 rows with customer data.

FirstName LastName City Country
Paolo Accorti Torino Italy
Giovanni Rovelli Bergamo Italy
Maurizio Moroni Reggio Emilia Italy

Using SQL

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.


CRUD

SQL supports four fundamental operations, collectively known as CRUD (Create, Read, Update, Delete). They are:

  1. SELECT  -- Read the data
  2. INSERT  -- Insert new data
  3. UPDATE  -- Update existing data
  4. DELETE  -- Remove data

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.


SQL SELECT Syntax

The general form of a SELECT statement.

SELECT column-names
  FROM table-name
 WHERE condition
 ORDER BY sort-order

Example

Problem:  List all customers in Paris sorted by last name.

SELECT FirstName, LastName, City, Country 
  FROM Customer
 WHERE City = 'Paris'
 ORDER BY LastName


SQL INSERT Syntax

The general form of an INSERT statement.

INSERT table-name (column-names)
VALUES (column-values)

Example

Problem:  Add Oxford Trading to the list of suppliers.

INSERT Supplier (CompanyName, ContactName, City, Country)
VALUES ('Oxford Trading', 'Ian Smith', 'Oxford', 'UK')


SQL UPDATE Syntax

The general form of an UPDATE statement.

UPDATE table-name
   SET column-name = column-value
 WHERE condition

Example

Problem:  Supplier with id 24 has moved. Change their city from Sydney to Melbourne.

UPDATE Supplier
   SET City = 'Melbourne'
 WHERE Id = 24


SQL DELETE Syntax

The general form of a DELETE statement.

DELETE table-name
 WHERE condition

Example

Problem:  Remove 'Stroopwafels' from the products.

DELETE Product
 WHERE ProductName = 'Stroopwafels'


You may also like



Guides


vsn 3.1