SQL

⇦ Back

Structured Query Language (SQL - pronounced either “es cue el” or “sequel”) is a programming language used to manage data that is stored in a database. It is not a program itself but it is used in many programs, especially relational database management systems (RDBMSs).

While the syntax and semantics of the SQL language is defined in an official standard (maintained by ISO/IEC) it is common for RDBMSs to have their own dialects. These dialects are extensions to the standard, meaning that the core elements of the language will always be there but with some additions and quirks that depend on the exact RDBMS you are using. The pages on this site use commands written in SQLite, one of the most accessible and easy-to-use of the RDBMSs that uses SQL.

SQL is most useful when:

• You need to handle input data that is being collected from or generated by other people or programs (not just by you typing it into a spreadsheet or inserting it into an upstream database yourself)
• There is a huge amount of data and a lot of it is not pertinent to the project at hand
• More than one person or program needs to use the data at the same time
• You want fine-grained access control, allowing you to restrict who can read or write data more robustly than flat files or spreadsheets would allow
• The amount of data and size of the project could scale up in the future

SQL is less useful when:

• You’re only working with a small amount of data
• The data is simple and tabular such that it can be easily held and worked with in a spreadsheet
• It is only a small, one-time analysis that needs to be done

In these cases, working with the data directly in Microsoft Excel, LibreOffice Calc or Google Sheets might be a better option if the project is small. For in-between sized projects, importing into Python and using pandas for analysis is usually best.

Relational Database Management Systems

An RDBMS is a program that allows you to create, update and administer a relational database – one that stores data in a structured format using rows and columns. Examples of RDBMSs are:

• SQLite (open source)
• MySQL (open source, developed by Oracle)
• Oracle DB (closed source, developed by Oracle)
• PostgreSQL (open source)
• SQL Server (closed source, developed by Microsoft)
  • Express is a free, entry-level version

As mentioned above, pages on this site use commands tested in SQLite:

SQLite

1. Introduction
2. Worked Example
3. Cheat Sheet

⇦ Back