Skip to Main Content
University of York Library
Library Subject Guides

Coding: a Practical Guide

SQL

SQL

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.

What is SQL?

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.

Tip

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.

Key SQL commands

SQL statements are typically how you perform actions on a database. Statements are made up of keywords that do particular things.

Tip

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

Resources for learning SQL

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.

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.

Exercises

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.

Feedback
X