1. SQL Tutorial (3) ORDER BY, INSERT INTO, NULL Values, UPDATE/DELETE

ORDER BY Keyword

: to sort the result-set in ascending/descending order

• ascending order by default
• use DESC keyword for descending order

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

---

INSERT INTO Statement

: to insert new records in a table

Syntax

• Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

• If you are adding values for all the columns of the table, you do not need to specify the column names in the query. However, make sure the order of the values is in the same order as the columns in the table.

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

Insert Data Only in Specified Columns

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

---

NULL Values

: a field w/ no value

If a field in a table is optional, it is possible to insert a new record or update a record w/o adding a value to this field. Then, the field will be saved w/ a NULL value.

How to Test for NULL values

IS NULL & IS NOT NULL

IS NULL:

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL:

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

---

UPDATE Statement

: to modify the existing records in a table

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• w/o the WHERE clause, all records in the table will be updated
  UPDATE Multiple Records:
• updates the ContactName to ‘Juan’ for all records where country is ‘Mexico’

UPDATE Customers
SET ContactName = 'Juan'
WHERE Country = 'Mexico';

---

DELETE Statement

: to delete existing records in a table

DELETE FROM table_name WHERE condition;

Delete all records: deleting all rows without deleting the table

DELETE FROM table_name;

---

This post is based on W3School's articles about SQL.