Understanding SQL and PostgreSQL

One of the most common forms of storing data is in a database.

A database is comprised of many tables, each with rows and columns that are used to store data.

SQL, or, structured query language, is a language used to create, query, modify, or delete databases and their components. SQL is used for relational databases, which means that each table is usually related to at least one other table.

This is achieved through entering foreign key values in each table, that then links its value to another table.

E.g. Assume we have a table called Colors of the Rainbow, where we have ROYGBIV with ID’s 1–7. We then have another table titled Favourite Color’s By Student. In this table, we will have a column for student name, and their favourite color. But, instead of writing out the color, we instead switch that with the ID from the ROYGBIV table, such that if a student’s favorite color is “red”, we would use 1.

Databases can store many data types in each table, including: Strings, Numbers — Integers and Floats, Booleans etc.

PostgreSQL is a special vendor for SQL. It is mostly similar, but has some dramatic improvements to its functionality, such as:

  1. Being able to efficiently DROP(delete) tables
  2. Better SELECT query functionality
  3. Better ability to string multiple queries together
  4. Better for dealing with CSV