SQL Course - Part 1. Introduction

Today we're starting a series of posts on the SQL language.

SQL (Structured Query Language) is a language for accessing data in databases. Informally, the word "database" has two meanings:

  1. an organized set of data
  2. a system for organizing and managing that data

 

The latter is more formally termed a Database Management System (DBMS for short). There are many DBMSs out there: from commercial ones like Oracle, IBM DB2, and MS SQL, to open-source DBMSs like PostgreSQL and MySQL, to lightweight in-memory databases like HSQLDB. They all use SQL to access their data. The dialects are slightly different but we'll focus on their common core.

SQL is an old language. It was initially developed in the early 1970s, so it's now more than 40 years old. For a computer science technology that's ancient. It's one of the oldest technologies which is still widely used. (Though Fortran and COBOL are still alive and kicking, and they're even older.)

Many people don't like SQL. It's not some new, cutting-edge buzzword language. Despite efforts to move on (see Rafał's post about the NoSQL movement) it's still the technology most enterprise systems are based on and it's still worth learning.

Interestingly, there is no agreement on how the name of the language should be pronounced. The initial version was called SEQUEL (Structured English Query Language). This acronym was later changed to SQL because of a trademark issue. Some people pronounce it ess-cue-ell, while others still use the name "sequel."  Personally, I say ess-cue-ell, but I'm not a native English speaker. It's the way SQL is pronounced in my mother tongue. Wikipedia (I'm using only the most reliable sources of information ;-) ) also uses ess-cue-ell.

SQL is a language for accessing databases. So before learning the language, we should know how a database is organized. A database is rather like a very large spreadsheet (the kind you would make in Excel, OpenOffice Calc, and the like), except databases usually have thousands or millions of rows. Each database consists of tables. Each table consists of columns and rows. Each table corresponds to a single page in Excel, and every page consists of a different set of columns.

For example, you could have a table (page) named book which contains information about books, like this:

id author title publication_year number_of_pages
1 Joshua Bloch Effective Java 2001 272
2 Joel Spolsky Smart & Gets Things Done 2007 192

 

Here's a table called person:

id first_name last_name city street
1 Joshua Bloch Los Angeles 15 Sunset Boulevard
2 Joel Spolsky Warsaw Aleje Jerozolimskie 135

 

All SQL commands deal with tables. There are administration commands which create or delete tables, and there are data commands which deal with the contents of tables (data). We'll focus on the latter, as table structure design is beyond the scope of this series.

SQL data commands can do four types of things:

  • create a row in a table,
  • retrieve information from a table,
  • update information in a table,
  • delete rows from a table

Those four operations are referred to with the lovely acronym CRUD. In our SQL course we'll be focusing on retrieval first, since this is the most common type of operation.

But before we can do that, we need to set up our SQL environment.

Stay tuned for the next post!