BASIC SQL

What is SQL?

• SQL stands for Structured Query Language

SQL Data Manipulation Language (DML)

SQL (Structured Query Language) is syntax for executing queries. But the SQL language also includes a syntax to update, insert, and delete records.

These query and update commands together form the Data Manipulation Language (DML) part of SQL:

• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a database table

SQL Data Definition Language (DDL)

The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.

The most important DDL statements in SQL are:

• CREATE TABLE - creates a new database table
• ALTER TABLE - alters (changes) a database table
• DROP TABLE - deletes a database table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

The SQL SELECT Statement

The SELECT statement is used to select data from a table. The tabular result is stored in a result table (called the result-set).

Syntax

SELECT column_name(s) FROM table_name

SELECT LastName,FirstName FROM Persons

SELECT * FROM Persons

SELECT DISTINCT column_name(s) FROM table_name (The SELECT DISTINCT Statement)

SELECT column FROM table WHERE column operator value

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns

SELECT * FROM Persons WHERE City='Sandnes' (Using the WHERE Clause)

SELECT column FROM table WHERE column LIKE pattern (The LIKE Condition)

The INSERT INTO Statement

The INSERT INTO statement is used to insert new rows into a table.

Syntax

INSERT INTO table_name VALUES (value1, value2,....)
You can also specify the columns for which you want to insert data:
INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

The Update Statement

The UPDATE statement is used to modify the data in a table.

Syntax

UPDATE table_name SET column_name = new_value
WHERE column_name = some_value

Update one Column in a Row

We want to add a first name to the person with a last name of "Rasmussen":
UPDATE Person SET FirstName = 'Nina'
WHERE LastName = 'Rasmussen'

Update several Columns in a Row

We want to change the address and add the name of the city:
UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'

The DELETE Statement

The DELETE statement is used to delete rows in a table.

Syntax

DELETE FROM table_name
WHERE column_name = some_value

Delete All Rows

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
or
DELETE * FROM table_name

Sort the Rows

The ORDER BY clause is used to sort the rows.
SELECT column_name1, column_name2 FROM table_name
ORDER BY column_name1