What is a Table in SQL?

How to list them?

You can list the tables of your database via the information schema.

Example with

• Sql Server

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

• Sqlite, the information schema is the sqlite_master table.

SELECT name FROM sqlite_master WHERE type='table';

Creation

You can create tables via for database:

• without SQL, via their library API, they should have a create function
• with SQL support,
  • with the create table statement to have only the structure
  • with a create as statement to create a table from a query (ie values)

Example:

• create table statement

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    HireDate DATE
);

Insertion

You insert data into database table:

• without SQL, via their library API, they should have a insert function
• with SQL support, via the insert statement and the values statement.

Cardinality

The cardinality of a table is the size of a table, the name comes from the set theory.

Split For Data Loading

Even if it's possible to get all data in one table that has the same grain (unique key), you may split the columns that are not functionally related into 2 different tables

Why?

• because you get minder dependency. You can change your table and your process independently. For instance, the test data set doesn't need to be updated to reflect the changes of a second functional process.
• You can then set columns NULLABLE. If a process updates columns after another one (such as total calculation), the value of this column is unknown for the first process and the columns must be set as not nullable.
• You can add audit columns by process (Update and create (date|user))

Why not?

• You may get consistency problems. There may be not a one-to-one relationship. But as the two functional processes are independent, it may be not always needed.
• Performance: You add a join cost

¹⁾