What is SQL - Part 2

Creating tables with SQL

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

• Part 1
• Part 2
• Part 3
• Part 4

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, indexes
• ALTER: modifies existing object in a DB
• DROP: destroys DB objects

Here are the statements for creating the tables:

Products

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

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

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 syntax, AUTO_INCREMENT for MySQL and SERIAL for PostgreSQL, 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 ¹
• 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

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

Sellers

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

Sales

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 may be used

1 Tables and updatable views in DB engines that support them.