What is SQL - Part 2
Tags: sql, beginners
Published: 2021-04-12 15:35:21

---
title: What is SQL - Part 2
published: true
description: Creating tables with SQL
tags: sql, beginners
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j9rnxam9e350k45gvey3.png
---

## Creating tables with SQL

**This is part 2 of a four-part article which explains SQL**

- [Part 1](https://dev.to/marcegarba/what-is-sql-part-1-3dnb)
- **Part 2**
- [Part 3](https://dev.to/marcegarba/what-is-sql-part-3-4ica)
- [Part 4](https://dev.to/marcegarba/what-is-sql-part-4-9c1)

In the example that follows, three very simple tables are created and content is added to them.

The three tables schema depicted in the image above is created and populated.

### DDL for creating the tables

Data Definition Language is the subset of SQL which is used for creating, altering and dropping elements in a DB schema:
* **CREATE**: creates databases, tables, [views](https://en.wikipedia.org/wiki/View_(SQL)), indexes
* **ALTER**: modifies existing object in a DB
* **DROP**: destroys DB objects

Here are the statements for creating the tables:

#### Products
```sql
CREATE TABLE products (
    id    INTEGER NOT NULL,
    name  VARCHAR(50),
    PRIMARY KEY (id)
);
```
* This table holds the products to be sold
* Notice the names, types and constraints
  * id is NOT NULL, that is, it doesn't admit NULL values
  * name is of type VARCHAR(50), which means it admits up to 50 characters, and the length is not fixed, and it **admits** NULL values
  * Besides, the PRIMARY KEY constraint identifies this column as the one that **can't be repeated** in the table
  * In fact, the NOT NULL isn't strictly required, as PKs do not allow NULL VALUES

#### Sellers
```sql
CREATE TABLE sellers (
    id    INTEGER NOT NULL,
    name  VARCHAR(50),
    PRIMARY KEY (id)
);
```
* The list of employees that sell `products`
* Like `products`, id is the Primary Key, which assures there are no more than one row with the same values

#### Sales
```sql
CREATE TABLE sales (
    id          INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    id_product  INTEGER NOT NULL,
    id_seller   INTEGER NOT NULL,
    date        DATE NOT NULL,
    quantity    INTEGER NOT NULL,
    amount      REAL NOT NULL,
    FOREIGN KEY (id_product) REFERENCES products (id),
    FOREIGN KEY (id_seller)  REFERENCES sellers (id)
);
```
* Here is the `sales` table, which is related with `products` and `sellers`
* The PK is also named id, but notice that it in this case, there is the **AUTOINCREMENT** keyword; this is the [SQLite](https://sqlite.org/autoinc.html) syntax, **AUTO_INCREMENT** for [MySQL](https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html) and **SERIAL** for [PostgreSQL](https://www.postgresqltutorial.com/postgresql-serial/), it means that when creating new rows in this table, the DB will automatically populate it with consecutive numbers
* Importance to notice here are the FOREIGN KEY clauses:
  * The first one links the possible values in `sales` with the values in the PK for `products`
  * The other does the same with table `sellers`
* What does it mean?
  * Values for `id_product` and `id_seller` could not be _any_ number; they have to match with existing rows in `products` and `sellers`
  * Besides, this means that rows in the tables `products` and `sellers` **cannot be deleted** if at least one row in `sales` point to them ... unless the DDL were different:
    * REFERENCES ... ON DELETE CASCADE ... ON UPDATE CASCADE
    * `ON DELETE CASCADE` would mean that removing a row in the referenced table, would _cascade_ to delete it in this table too
    * `ON UPDATE CASCADE` would mean that if the PK would be changed in the reference table(s), this would _cascade_ the update to this table
    * As these are not the case, these clauses are **not** added to the `CREATE TABLE` sentence

With these SQL DDL clauses, the tables will be created and:
* Proper names and data types will be established
* Proper constraints, which will enforce the natural rules which _should always be enforced in the DB_, such as:
  * **NOT NULL**: NULL values are not permitted in those attributes, or columns
    * A `NULL` value does not mean zero, empty string, nor other type of _'natural'_, so to speak, empty values
    * It means that for a combination of row and column (in Math, it would be a _tuple_ and an _attribute_) there is **no** value present, and in SQL it's treated differently
  * **PRIMARY KEY**: Does not allow duplicate rows
    * Remember that Sets, in math, do not allow duplicate elements in it
    * For a Table to be a perfect Set, there should not be any duplicate element, and each element in this Set is a tuple
  * **FOREIGN KEY**: Prevents related table to hold invalid values

### DML for inserting rows into the tables

Data Manipulation Language is the SQL subset used for modifying the contents of tables in a DB:
* **INSERT**: inserts new rows into tables <sup><a name="cbtables">[1](#tables)</a></sup>
* **UPDATE**: modifies values in existing rows
* **DELETE**: removes rows
* **SELECT**: retrieves content from the DB

Here are the statements for inserting rows into the tables created above in the DDL.

#### Products
```sql
INSERT INTO products (id, name) VALUES
    (1, 'Sport shoes A'),
    (2, 'Sport watch B'),
    (3, 'Suit C');
```

#### Sellers
```sql
INSERT INTO sellers (id, name) VALUES
    (234, 'John S.'),
    (281, 'Luisa G.'),
    (341, 'Mary T.');
```

#### Sales
```sql
INSERT INTO sales
    (id_product, id_seller, date, quantity, amount)
 VALUES
    (1, 234, '2020-01-23', 2, 142.38),
    (2, 234, '2020-02-01', 4, 813.34),
    (2, 341, '2020-01-31', 1, 215.48),
    (1, 234, '2020-03-01', 1, 75.00),
    (3, 341, '2020-02-15', 1, 348.50),
    (1, 341, '2020-01-17', 3, 210.00),
    (3, 281, '2020-04-15', 1, 350.00),
    (2, 281, '2020-02-28', 5, 1000.00),
    (3, 281, '2020-05-13', 2, 605.25),
    (1, 234, '2020-06-10', 2, 148.34),
    (2, 341, '2020-12-01', 2, 448.50),
    (2, 234, '2020-12-25', 1, 220.00),
    (3, 341, '2020-11-18', 2, 600.00);
```
* Since the `id` in this table is auto incremental, there is no need to include that column in the `INSERT` above
* There are other ways to generate unique default values for a column
  * In particular, and in a syntax that varies according to the DBMS engine used, an auto-generated [UUID](https://en.wikipedia.org/wiki/Universally_unique_identifier) may be used

----
<a name="tables">[1](#cbtables) Tables and updatable views in DB engines that support them.</a>