Before we learn how to create a schema on SQL we must understand what a schema is. A schema defines the structure of data including: the tables that contain the data, the columns and their corresponding data type in each table and the relationships between each table. Schemas allow us to understand data better and allows us to prevent inconsistencies.

In order to set up a schema we must follow these steps:

1) We start off by creating our schema. This can be done using the following code:

CREATE SCHEMA database.schema_name

We must ensure that we specify the database that we want the schema to be created in and name the schema accordingly.

2) The next step is to create tables using the following code:

CREATE TABLE database.schema_name

(

column_1 data_type

,column_2 data_type

...

)

When using the data type 'varchar()', make sure to select the maximum number of characters in the column. In addition, if there are columns from different tables you would like to join, make sure they are set to be the same data type. If you later need to change the data type of a table you can use the following code:

alter table table_name alter column_n data_type

3) You would then need to insert data into the table. This is done using the following code:

INSERT INTO database.schema.destination_table

(

select *

from database.schema.source_table

)

4) Our final step is to set a primary key and foreign key. A primary key is the unique identifier for each row. There are no two rows with the same value within our primary key column. The foreign key is a column or multiple column which references the primary key of another table. We use the foreign key to create a link between tables. We first set our primary key using the code:

ALTER TABLE table_name ADD PRIMARY KEY (column_n)

We then set our foreign key:

ALTER TABLE table_name ADD FOREIGN KEY (column_n) references another_table(another_table_primary_key_column)

We have now created our schema and delve into analysis of our data.