A data science newbie’s guide through SQL.

Chapter three— Creating tables in SQL

It is finally time to start writing statements. In chapter 3 of the SQL series, we shall look deeper into the rules around writing SQL and we shall write our first statements.

Syntax and semantics of SQL

Like any other programming language, SQL has its rules and styles of writing that we shall need to know these as we start writing statements.

Here are resources that will guide you:

SQL syntax and semantics guide 👉🏾Here

SQL data types 👉🏾Here

SQL Keywords 👉🏾 Here

Keep in mind, practice makes perfect. So let's dive in!

Create a table in your database:

This is the generic code structure to create a table.

 CREATE TABLE table_name (
 column1 datatype[(size)][column level constraints],
 column2 datatype,
 column3 datatype,
 CONSTRAINT table_name_constraint CONSTRAINT TYPE (column_name)
); 

From last week's exercise here, let us create the table in the task. I envisioned my table to look like this:

There are two ways to create this table;

using the terminal (psql)it can be created with this code;

CREATE TABLE name_table
(
   name "char",
   age integer,
   fav_number
)

using pgAdmin: Ensure that your server is connected.

pgAdmin>Servers>Databases > database name> Schemas> public> Tables> right click and select create, fill in the column names, constraints and finish.

Constraints: When a table is created we are able to parse rules that will apply to either a column or the table these are called constraints. For example, the primary key commonly known as pk, table constraint that sets a column as the primary key or the not null column constraint that ensures that this column is never left blank.

There are about 7 commonly used constraints in SQL and you can read more about them here.

From the above example, let's make column name the primary key and ensure none of the columns is left blank.

CREATE TABLE name_table
(
   name "char" NOT NULL,
   age integer NOT NULL,
   fav_number integer NOT NULL,
   CONSTRAINT name_table_pkey PRIMARY KEY (name)
)

Creating a table using another table

As a data scientist, it is very important to know table inheritance because it comes in hand when you need to use another table's columns to carry out a test. This means you can use parts of an existing table without working/altering that table.

CREATE TABLE new_table_name AS
 SELECT column1, column2,
 FROM existing_table_name
 WHERE (condition);

We shall look more of this when we start to SELECT.

Inserting values into an SQL table

If not already inserted there is an SQl statement to insert values in a table.

INSERT INTO name_table(name, age, fav_number)
VALUES (value1, value2, value3);
-- Alternatively if inserting in all columns 
INSERT INTO name_table
VALUES (value1, value2, value3);

Alternatively using the GUI(pgAdmin) can go as far as allowing one to import a csv file although you have to take caution, the data should be clean especially at the data type level to ease the import.

This is a very crucial observation as a data scientist because you need to understand what data types will make your work easier in SQL so that your data cleaning process can match that.

Conclusion

Congrats, you have your database ready to query!👏🏾 Go ahead, try to insert about 10 values. (Remember, you don’t have to get it right, you’re just practising.)

Thank you so much for catching up with chapter 3 of the SQL series, next week shall start to write queries.

Feel free to share with me feedback by leaving a clap, comment or tweeting me @kalmpublication or @lornamariak

Catch up with previous chapters here: 1, 2,

Happy Learning!😻