SQL

SQL stands for Structured Query Language. It enables access and manipulation of databases. SQL became a standard of the American National Standards Institute (ANSI) and International Organization for Standardization (ISO) in 1986, 1987 subsequently. There are different versions of the SQL language with proprietary extensions. But they all support the SQL standards with similar core functions.

SQL Syntax

SQL keywords are NOT case sensitive, but it is good practice and convention to use ALL UPPERCASE for KEYWORDS. It is typical to use semicolon at the end of each SQL statement.

Important SQL commands:

SELECT

SELECT selects columns from a table.

Example 1:

SELECT column1, column2, ...

FROM table_name;


SELECT * selects all columns from a table.

Example 2:

SELECT *

FROM table_name;

SELECT DISTINCT

SELECT DISTINCT selects only the DISTINCT values from columns.

Example 1:

SELECT DISTINCT column1, column2, ...

FROM table_name;


Example 2:

SELECT COUNT(DISTINCT Country)

FROM Customers;

WHERE

WHERE clause is used to filter records.

Example:

SELECT

column1, column2, ...

FROM table_name

WHERE condition;

Operators in The WHERE Clause:

=

<

>

<=

>=

<> not equal

BETWEEN between a range

LIKE search for a pattern

IN to specify multiple possible values for a column

AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

Example 1:

SELECT * FROM Customers

WHERE Country='China' AND (City='Beijing' OR City='Xian');

Example 2:

SELECT * FROM Customers

WHERE NOT Country='Russia' AND NOT Country='USA';

ORDER BY

ORDER BY sorts the results in ascending order by default. Use DESC if we want to sort in descending order.

Example 1:

SELECT * FROM Customers

ORDER BY Country ASC, CustomerName DESC;

INSERT INTO

INSERT INTO inserts new records into a table.

Two ways to write the INSERT INTO statement:

  1. Specify both column names and the values for each column:
  2. To add values for all columns in the table, we do not need to specify the column names. But we need to make sure the order of the values inserted matches the order of the columns in the table.

Syntax 1:

INSERT INTO table_name(column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);


Syntax 2:

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

NULL

A field with NULL value is a field with missing values.

To test for NULL values, use IS NULL, IS NOT NULL.

UPDATE

Use UPDATE to modify existing records in a table.

Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

DELETE

DELETE deletes existing records in a table.

Syntax:

DELETE FROM table_name WHERE condition;


Delete All Records

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name;

Resources for learning SQL:

  1. SQL Tutorial from W3 schools