Database Fundamentals

Why Databases?

Databases are the best way to store large amounts of data, and 21st century organizations have huge, exponentially growing datasets.

Databases allow multiple concurrent users to access information rapidly. This differentiates them from other, more limited data storage solutions. It also enables them to scale to sizes that other software storage solutions would not be able accommodate.

Databases also ensure data integrity, in two ways. First, they ensure that data cannot be readily changed. It isn’t possible to simply click into a cell and change the data. Second, databases require the use of SQL, which automates the steps to derive the same data.

Spreadsheets make sense in lieu of databases for straightforward analyses with relatively small and simple datasets.

Why SQL?

SQL stands for Structured Query Language. SQL, which has existed since the 1970s, is a language used to interact with databases. It is the most common way of accessing databases today, and it has a variety of functions that enable it to read, manipulate, and change data.

SQL is so widely used because it:

  • Is easy to understand and use,
  • Enables you to access data directly,
  • Makes it easy to audit and replicate queries, and it
  • Enables you to work on multiple tables at once, across large datasets.

SQL functionality includes performing the sort of aggregations you would normally perform in an Excel pivot table, but Excel maxes out at around a million rows. SQL, on the other hand, can cope with billions. SQL is also far more flexible than most out-of-the-box business dashboard software, an example of which is Google Analytics. This flexibility combined with relative ease of use may be the best reasons for its continued popularity.

Given appropriately constructed databases, companies can use SQL to answer questions like:

  • Which product lines are best performing?
  • Which marketing channel should be invested in?
  • How many users return to my site within 1 and 3 weeks of their first visit?
  • What strategies typically bring website users back?

SQL’s Importance in the Job Market

This article at Zippia.com discusses a list of 50 job titles that likely require the SQL skillset. Among these are:

  • Business Analyst,
  • Software Engineer,
  • Developer,
  • Data Analyst, and 46 more.

A cursory glance at job postings makes it clear how crucial this skillset is to 21st century knowledge workers. SQL was the most in-demand skill in the tech sector in 2016, according to data from Indeed.com.

Types of SQL

There are many different dialects of SQL that are used with different types of SQL databases. Some of the most popular databases include:

  • Access,
  • Oracle,
  • MySQL,
  • Postgres, and
  • Microsoft SQL Server.

Three of the most popularly used types of SQL are:

  • SQLite,
  • PostgreSQL, and
  • MySQL.

Each of these different types of SQL have slight variations in functionality and syntax, but they are sufficiently similar that skills gained with one type are generally transferrable to the others.

Entity Relationship Diagrams

ERDs, Entity Relationship Diagrams, describe relationships between tables of data in a database. These diagrams contain such information as:

  • names of the tables,
  • columns in each table, and
  • how the tables “fit together.”



Each of the tables in the image above can be thought of as its own Excel table. These tables are linked together by a series of keys, which allows SQL to join them together or otherwise manipulate them as a group.