Learning SQL

• Part 1: Understanding Tables
• Part 2: SELECT
• Part 3: INSERT
• Part 4: UPDATE
• Part 5: DELETE

Using SQL

• Part 1: CREATE TABLE
• Part 2: ALTER TABLE
• Part 3: DROP TABLE
• Part 4: Working with Databases

Mastering SQL

• Part 1: Primary and Foreign Keys
• Part 2: WHERE, BY, AND
• Part 3: OR, AS, LIMIT
• Part 4: LIKE, WITH

Structured Query Language (SQL) is a declarative programming language used for accessing data storied in relational databases. It appears in or is a core part of many common software packages like MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database.

---

Primary Keys

SQL is used for accessing data in relational databases. Rows are related to their column and to their larger databases. However, as relationships can change as the data is updated and changed, there needs to be a single, very rarely (if ever) changed relationship.

In nearly all implementations of SQL, there exists this single relationship between a row and its databases. This is known as the primary key. Often, although not always, this a number such as an ID or other form of identification.

A primary key MUST be unique in a relational database table. This means data can always be accessed through this “key” because it will always refer to an individual row. Even when column names or other data is changed, this primary key will remain.

CREATE TABLE People (
        ID int NOT NULL,
        Name varchar(255) NOT NULL,
        PRIMARY KEY (ID)
 );

When using the keywords CREATE TABLE, most SQL implementations ask for a column to be identified as the primary key. This must be a NOT NULL (cannot be empty) column because a primary key cannot be NULL.

Table Name: People
ID	Name
1	Dan
2	Steve

Once created, primary keys are frequently used a part of a SELECT statement to get data based on this unchanging relationship.

Foreign Keys

Just as primary keys are used to create a relationship between a row and its table, a foreign key is used to link tables together. In relational database terminology, a foreign key is a column in one table that is a primary key in another.

Such a relationship creates a link between these tables that cannot easily be broken. Because of the foreign key connection, data can more easily be pulled from multiple tables based on a single column name.

Table Name: People
ID	Name
1	Dan
2	Steve

Table Name: Orders
OrderNumber	ID
4567	1
8901	2

Foreign keys are created using the CREATE TABLE keywords like primary keys.

CREATE TABLE Orders (
     ID int NOT NULL,
     OrderNumber int NOT NULL,
     PRIMARY KEY (ID),
     FOREIGN KEY (ID) REFERENCES People(ID)
 );