Want to get things in and out of databases?
SQL, short for Structured Query Language, is a language used to communicate with databases, allowing you to store, manipulate, and retrieve data.
Structured Query Language, or SQL for short, is a language that allows you to write commands to work with databases. It works with a variety of database systems and is not specific to one application.
SQL allows you to do a range of activities with a database, such as running queries to retrieve data and update or delete records from a database, creating new databases and tables within databases, and create views and set permissions in a database.
SQL is a standard language, but there are different versions of it. They all tend to have the same major commands, but there are differences and most database programs that use SQL have some different or extra commands.
For more on databases and the terminology around them, see Essential Access: a Practical Guide.
SQL statements are typically how you perform actions on a database. Statements are made up of keywords that do particular things.
Unusually for coding, SQL keywords are not case sensitive, meaning that you can write them in upper or lower-case. For example SELECT and select would both work the same.
Some database systems require you to use a semicolon at the end of each SQL statement. This syntax is how you separate out each SQL statement to allow more than one SQL statement to be executed at the same time. Here are some useful SQL statements when getting started:
SELECT - extracts data from a database (the command to make a select query to 'get' a subset of the data)
SELECT DISTINCT - extracts onto distinct (different) values from the data
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database (this is a new entire database, not a table of data within a database)
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table in a database
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (a search key)
DROP INDEX - deletes an index
There are other elements of SQL syntax that are useful to know to add to your statements. For example:
FROM table_name - allows you to specify the table to use in a statement
WHERE - allows you to specify a condition to filter records (using operators like = > < BETWEEN and LIKE)
AND, OR, NOT - used to combine conditions when using WHERE
ORDER BY - used to sort the result set by particular columns in ascending or descending order
If you want to learn SQL, there are a range of online resources and courses, as well as books and videos. Below we've suggested a few you might want to look into (we've not used them all, so think critically about them), or you could search online for SQL resources that suit your interests.
w3school's SQL tutorial is a good starting point and can also be used as a reference for the commands.
There are also free online courses that look at database basics as well as how to write SQL to work with them, such as this Introduction to Databases and SQL course from Raspberry Pi on FutureLearn or this Introduction to Database Queries course from NYU on edX.
You can also find more specific SQL resources like IBM's SQL for Data Science on edX or the range of database-related courses that Stamford offer on edX which start at introductory and then cover specific topics.
And if you want to have some fun whilst learning SQL or practising what you're learnt, try out the SQL Murder Mystery from the Northwestern University Knight Lab or Lost at SQL: the SQL learning game.
If you're new to SQL but have used Microsoft Access, a good thing to try is to view queries you've already created in SQL view and work out what the SQL commands are doing. You can also use Access to write SQL commands and see if you get the results you expect.
Otherwise, you might want to try out one of the resources linked below, which often have exercises you can do alongside learning SQL.