Structured Query Language — commonly known as SQL — is a language used to define, control, manipulate, and query data held in a relational database. SQL has been widely adopted since it was first developed in the 1970s, and today it’s the predominant language used to manage relational database management systems.
Ideal for managing structured data (data that can fit neatly into an existing data model), SQL is an essential tool for developers and system administrators in a wide variety of contexts. Also, because of its maturity and prevalence, candidates with SQL experience are highly sought after for jobs across a number of industries.
This series is intended to help you get started with using SQL. It includes a mix of conceptual articles and tutorials which provide introductions to various SQL concepts and practices. You can also use the entries in this series for reference while you continue to hone your skills with SQL.
Note: Please be aware that the tutorials in this series use MySQL in examples, but many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.
This conceptual article outlines the history of the relational model, how relational databases organize data, and how they’re used today.
SQL (Structured Query Language) defines, manipulates, and queries data held in relational databases. SQL is used in many industries and with the most popular tools. Even if you don’t use SQL directly, you can still benefit from understanding its concepts. This conceptual article outlines why it’s worth learning SQL and reviews where and how you can apply that knowledge.
Relational database management systems allow you to control what data gets added to a table with constraints. A constraint is a special rule that applies to one or more columns — or to an entire table — that restricts what changes can be made to a table’s data. This article will go over in detail what constraints are and how they’re used in relational databases. It will also walk through each of the five constraints defined in the SQL standard and explain their respective functions.
Tables are the primary organizational structure in SQL databases. They consist of any number of columns, which reflect individual attributes of each row, or record, in the table. Being such a fundamental aspect of data organization, creating and managing tables are common tasks for anyone who builds or maintains databases. This guide outlines how to create tables in SQL, as well as how to modify and delete existing tables.
When designing an SQL database, there may be cases where you want to impose restrictions on what data can be added to certain columns in a table. SQL makes this possible through the use of constraints. After applying a constraint to a column or table, any attempts to add…
SQL provides a great deal of flexibility in terms of how it allows you to insert data into tables. You can specify individual rows of data with the VALUES keyword, copy entire sets of data from existing tables with SELECT queries, as well as define columns in ways that will cause SQL to insert data into them automatically. In this guide, we’ll go over how to employ each of these methods to load tables with data using SQL’s INSERT INTO syntax.
When working with a database, there may be times when you need to change data that’s already been inserted into it; you may need to correct a misspelled entry, or perhaps you have information to add to an incomplete record. SQL provides the UPDATE statement which allows users to change existing data in a table. This guide outlines how you can use SQL’s UPDATE syntax to change data in one or more tables. It will also explain how SQL handles UPDATE operations that conflict with foreign keys.
As the name implies, DELETE operations irreversibly delete one or more rows of data from a database table. Being such a fundamental aspect of data management, it’s important for SQL users to understand how the DELETE statement behaves and how it deletes data. This guide will go over how to use SQL’s DELETE syntax to delete data from one or more tables. It will also explain how foreign key constraints handle DELETE operations.
One of the most fundamental parts of working with databases is the practice of retrieving information about the data held within them. In relational database management systems, any operation used to retrieve information from a table is referred to as a query. In this guide, we will discuss the basic syntax of queries in Structured Query Language (SQL) as well as some of their more commonly used functions and operators.
In Structured Query Language (SQL) statements, WHERE clauses limit what rows the given operation will affect. This guide will go over the general syntax used in WHERE clauses. It will also outline how to combine multiple search condition predicates in a single WHERE clause to filter data with more granularity, as well as how to use the NOT operator to exclude, rather than include, rows that meet a given search condition.
In certain Structured Query Language (SQL) statements, WHERE clauses can be used to limit what rows the given operation will affect. SQL allows users to retrieve granular result sets by providing a variety of different types of predicates, each of which use a specific operator to evaluate rows. This guide will outline two types of predicates: range predicates which use the BETWEEN operator, and set membership predicates which use the IN operator.
In certain Structured Query Language (SQL) statements, WHERE clauses can be used to limit what rows the given operation will affect. SQL allows users to retrieve granular result sets by providing a variety of different types of predicates, each of which use a specific operator to evaluate rows. This guide will outline two types of predicates and the operators they use: comparison operators and the IS NULL operator.
Like many computer languages, SQL allows the use of various wildcard characters. Wildcards are special placeholder characters that can represent one or more other characters or values. This is a convenient feature in SQL, as it allows you to search your database for your data without knowing the exact values held within it. This tutorial will go over how to query data using SQL’s designated wildcards.
Many database designs separate information into different tables based on how certain data points relate to one another. Even in cases like this, it’s likely that there will be times when someone will want to retrieve information from more than one table at a time. A common way of accessing data from multiple tables in a single SQL operation is to combine the tables with a JOIN clause. This guide outlines how to retrieve data from multiple tables by joining them together.
In this tutorial, you’ll practice using mathematical expressions. First, you’ll use numeric operations on a calculator, then use those operators on sample data to perform queries with aggregate functions, and finish with a business scenario to query sample data for more complex information and analysis.
In this tutorial, you will learn how to use dates and times in SQL. You’ll begin by performing arithmetic and using various functions with dates and times using only the SELECT statement. Then you’ll practice by running queries on sample data, and you’ll learn how to implement the CAST function to make the output more digestible to read.
In this tutorial, you’ll learn how to manipulate data with CAST functions to change the data type of a value or set of values into another, and use the concatenation expression to string character and numerical data values together. You’ll also practice running the CAST function and concatenation expression in the same query to result in a complete statement.
CASE expressions are a feature in Structured Query Language (SQL) that allow you to apply similar logic to database queries and set conditions on how you want to return or display the values in your result set. In this tutorial, you’ll learn how to use the CASE expression to set conditions on your data using WHEN, THEN, ELSE, and END keywords.
In Structured Query Language (SQL), a “view” is a virtual table whose contents are the result of a specific query to one or more tables. This guide provides an overview of what SQL views are and why they can be useful. It also highlights how you can create, query, modify, and destroy views using standard SQL syntax.
With large data sets, it’s important to understand how to sort data, especially for analyzing result sets or organizing data for reports or external communications. In this tutorial, you will sort query results in SQL using the GROUP BY and ORDER BY statements. You’ll also practice implementing aggregate functions and the WHERE clause in your queries to sort the results even further.
A useful function in SQL is creating a query within a query, also known as a subquery or nested query. A nested query is a SELECT statement that is typically enclosed in parentheses, and embedded within a primary SELECT, INSERT, or DELETE operation.
When retrieving data from a database, you may need to refer to more than one table at a time. In Structured Query Language (SQL), you can use the
UNION operation, which takes the results of two queries with matching columns and merges them into one. In this guide, you will use
UNION operations to retrieve data from more than one table simultaneously and combine the results. You will also combine the
UNION operator with filtering to order the results.
When working with relational databases and Structured Query Language (SQL), you can store, manage, and retrieve data from the relational database management system. SQL can also perform calculations and manipulate data through the use of functions. In this tutorial, you’ll use different SQL functions to perform mathematical calculations, manipulate strings and dates, and calculate summaries using aggregate functions.
When working with relational databases and SQL, most operations on the data are performed as a result of explicitly executed queries, such as SELECT, INSERT, or UPDATE. However, SQL databases can also be instructed to perform pre-defined actions automatically every time a specific event occurs through triggers. In this tutorial, you’ll use different SQL triggers to automatically perform actions where rows are inserted, updated, or deleted.
As the data in a database increases, search performance can suffer. The larger the dataset, the harder it is for the database engine to find the documents that match the query quickly. Database administrators can use indexes to aid the database engine and improve its performance. In this tutorial, you’ll learn what indexes are, how to create them, and confirm whether they’re used to query the database.
In SQL, primary keys serve as identifiers for individual rows in a table. In this tutorial, you’ll learn about primary keys and use a few different kinds to identify unique rows in database tables. Using some sample datasets, you’ll create primary keys on single columns and multiple columns, and use autoincrementing sequential keys.
MySQL supports the use of stored procedures, which group one or multiple SQL statements for reuse under a common name. Using stored procedures, you can create reusable routines for common tasks to be used across multiple applications, provide data validation, or deliver an additional layer of data access security. In this tutorial, you’ll learn what stored procedures are and how to create basic stored procedures that return data and use both input and output parameters.