Learn SQL Programming and Database Queries

SQL, or Structured Query Language, is a language used by relational database management systems (RDBMSes) for defining data structures, updating data, and querying data.

The vast majority of relational database systems use some form of SQL, making “SQL database” and “relational database” effectively synonymous in everyday conversation.

Here we have collected a definitive list of the best SQL tutorials, guides, books and more.

Popular RDBMS systems that use SQL

These are the major implementations of the SQL language. Each one has its own peculiarities and its own deviations from the standard. Some of them do not implement every aspect of the SQL standard, and some provide additional features and syntax.

Open Source

• MySQL: by far the most commonly used Open Source SQL database system in the world. MySQL is used by a number of very popular web applications such as WordPress and Drupal, and is the M in the LAMP stack.
• PostgreSQL: the major alternative to MySQL in the Open Source RDBMS space. Many see it as a more advanced, better designed database.
• MariaDB: a drop-in replacement for MySQL, with increased performance and additional features. It began as a fork of MySQL, under the care of a group of mostly former-MySQL developers.
• SQLite: a library that can be included into a larger code base, not a fully-featured database management system. It provides SQL-based data storage for applications.

Proprietary

• Oracle
• Microsoft SQL Server

Books on SQL

You can learn a little bit of SQL from online tutorials, but the information you need to be a real database professional is mostly found in books.

General SQL

• Learning SQL: good book for those first getting into the language.
• SQL: The Complete Reference: detailed reference to SQL, considered the “standard” for the last 30 years.
• The Art of SQL: an excellent book that explains how to think about data modeling, schema design, and database querying.
• SQL Antipatterns: Avoiding the Pitfalls of Database Programming: This books details the many ways that people habitually misuse and poorly manage relational databases, along with help diagnosing and fixing each common problem. This is one of the best books on real-life database issues — essential for any database developer.

Once you’ve read one or two books on introductory SQL, you should dive into one of the specific implementations (see resources below) and work through an actual project. Then come back and look at some more theory and general principles.

Data Modeling (Database Schema Design)

Not all of these resources are about SQL specifically, but about the larger topic of data modeling. One of the most important aspects of writing SQL code is designing the schema for a database. To do this well, you must have a firm grasp on the underlying philosophy, art, and science of data modeling.

• Beginning Relational Data Modeling: one of the best introductory texts on data modeling, with lots of examples in SQL. If you don’t know what an ORM is, you should read this book.
• Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design: an introductory text with little jargon — excellent for novices.
• Data Model Patterns: Conventions of Thought: an in-depth guide to practical way of thinking about data modeling.
• The Data Model Resource Book: A three-volume resource that covers a huge amount of material relevant to professional data design for enterprise and industry.
  • Vol. 1: A Library of Universal Data Models for All Enterprises
  • Vol. 2: A Library of Data Models for Specific Industries
  • Vol. 3: Universal Patterns for Data Modeling

Data Querying and Analysis

Setting up your data structures properly, and filling your database with good, solid data are both critically important. But once you have all this data, what do you do with it? These books will help you learn how to do more than SELECT *, teaching you to create efficient and insightful queries that provide the data you really need.

• Beginning SQL Queries: From Novice to Professional
• SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL
• The Language of SQL: How to Access Data in Relational Databases
• Data Analysis Using SQL and Excel
• Real SQL Queries: 50 Challenges

Advanced SQL Programming

Here are a couple of books for database administrators and data professionals.

• SQL Tricks
• SQL for Smarties: a great book on advanced SQL topics, written by someone who contributed to the SQL specification. (Actually, everything by this author is worth reading.)

SQL Performance and Optimization

The prerequisites for high performance database designs are excellent data modeling and well-designed queries. So make sure you read the books from those sections before you start looking at advanced optimization techniques.

Also, be aware that many of the most powerful optimization techniques are implementation-specific. See the relevant sections below for books and resources about each of the major SQL implementations.

• SQL Performance Explained Everything Developers Need to Know about SQL Performance
• SQL Tuning
• SQL Performance Tuning

Data Science and Mathematics

Relational database theory is based on a branch of mathematics call “Relational Algebra.” Most SQL practitioners don’t need to explore the math behind SQL. But if you are interested in a deep dive into data science, these books will help you find your way.

• Theory and Practice of Relational Algebra: Transforming Relational Algebra to SQL
• Applied Mathematics for Database Professionals
• The Algebra of Data: A Foundation for the Data Economy

Books on Specific SQL Implementations

SQL is a standard, but it only exists in specific implementations. Each one has its own quirks. If you want to become a real database professional, you’ll need to know the particulars of the specific RDBMS you are dealing with.

• MySQL
  • Learning MySQL
  • MySQL (Developers Library)
  • MySQL Cookbook: Solutions for Database Developers and Administrators
  • Learning PHP, MySQL & JavaScript: With jQuery, CSS & HTML5
  • High Performance MySQL: Optimization, Backups, and Replication
• PostgreSQL
  • PostgreSQL: Up and Running
  • Beginning Databases with PostgreSQL
  • PostgreSQL for Data Architects
  • PostgreSQL 9 High Availability Cookbook
  • PostgreSQL 9.0 High Performance
• MariaDB
  • Getting Started with MariaDB
  • Mastering MariaDB
  • MariaDB Cookbook
  • MariaDB High Performance
• SQLite
  • The Definitive Guide to SQLite
  • SQLite for Mobile Apps Simplified
• Oracle
  • Oracle Essentials
  • Oracle PL/SQL Programming
  • Oracle Tuning: The Definitive Reference
  • Oracle Tuning Power Scripts
  • Oracle SQL Performance Tuning and Optimization
• Microsoft SQL Server
  • Introducing Microsoft SQL Server 2014
  • Microsoft SQL Server 2014 Unleashed
  • Microsoft SQL Server 2014 Query Tuning & Optimization

Online Resources

Although they may not provide quite the depth of the best books, there are a lot of very useful resources online.

Tutorials

Unfortunately, there are not a lot of really great online tutorials for SQL. The courses listed here are a great place to start as a beginner, but if you are serious about learning SQL, you’ll need to scroll up to the books section and buy some dead-tree resources.

• SQL Tutorial from W3 Schools
• SQL Course
• SQL Zoo

SQL Language Reference

It is useful to have a handy online reference for your particular implementation while in the middle of a coding job. None of these work particularly well as tutorials for learning SQL (unless you like reading reference material), but they are great for consulting when you need to know how a particular command works.

• SQL Quick Reference
• MySQL Syntax Reference
• PostgreSQL Manual
• Oracle SQL Language Reference
• SQL as understood by SQLite
• SQL Server Language Reference — Microsoft

SQL and Database Management Tools

Very few people really enjoy writing raw SQL code with their bare hands. These tools provide a range of interface features for handling database design and management in a more friendly and efficient manner.

• SQL Workbench/J
• MySQL Workbench
• Advanced Query Tool
• HeidiSQL
• Squirrel SQL

Online Communities

Sometimes the best way to find something out is to ask an expert. There are a number of online communities where you can talk to other SQL practitioners and get help, advice, encouragement, and moral support.

• Database Administrators Stack Exchange
• SQL Teams (Microsoft SQL Only)
• Oracle Database Forum
• MySQL Forums
• SQLite Mailing List

Related Concepts and Technologies

SQL professionals should have at least some familiarity with the following related concepts and technologies.

• NoSQL / Non-relational databases: SQL represents one particular way to store data. NoSQL, or non-relational data storage, is the other major alternative.
  • MongoDB
  • CouchDB
  • BigTable
  • Cassandra
• Active Record: Active Record is a common programming pattern that provides an abstraction layer between application code and database implementation.
  • Active Record in Ruby on Rails
  • Active Record for PHP
• Object Relational Mapping (ORM): ORM is a way of “translating” between object-oriented programming languages and relational database systems. It is a generally accepted design pattern, but some people do not approve.
• Relational Data Model: This is a visual way of representing a database design. It is a great technique for discussing and planning a database schema. If you end up really liking it, you’ll love UML.
• Knowledge Management: KM is the process of capturing and disseminating organizational or institutional knowledge. While data management and knowledge management are separate disciplines, there is a lot of overlap.
• Set Theory: Set theory is a branch of mathematics and logic. Along with Relational Algebra it is the basis for much of the theory behind database technology.

FAQ

Here are the answers to a few frequently asked questions.

What is SQL?

SQL, or Structured Query Language, is a language for interacting with a Relational Database Management System (RDBMS). It is comprised of two major parts

• Data definition language — commands and syntax used to define or reflect the structure of a dataset
• Data query language — commands and syntax used to insert, edit, manipulate, and retrieve stored data

SQL is a standard, not a specific piece of technology. In practice, SQL only exists in specific implementations such as MySQL or PostgreSQL.

Who manages the standard for SQL?

SQL is a standard of the International Organization for Standardization (ISO).

What are the alternatives to SQL?

There are two different sets of alternatives to SQL.

On the one hand there are alternatives languages for use in a relational database system. There are a handful of such language alternatives used in specialized settings, but none which have the kind of generalized adoption that SQL enjoys.

On the other hand are alternative to the relational model of database design — that is, non-relational databases, which are sometimes called “NoSQL” databases. There are a number of very popular non-relational database systems, including CouchDB and Mongo.

Which SQL database implementation should I use or learn how to use?

If you are working with and writing apps (or planning to write apps) in PHP, you should start with MySQL. You might also want to look into MariaDB.

Web apps in all other languages — such as Ruby or Python — tend to use PostgreSQL.

SQLite is often used as in-app storage for mobile apps. Ruby on Rails also uses SQLite as its default built-in database, but most production apps written in Rails use PostgreSQL.

Oracle is the leading database for Enterprise (large organization) applications.