Contacts:

Data Basics: Intro to SQL

This is the first article in my new tutorial series about databases, SQL and data manipulation.

We are going to start with some SQL basics right away, to give you something useful from the start. Later, you will see an overview of different database systems and engines, get to know how to install them and also some helpful tools, and dive into much more interesting and complicated commands and queries.

Eventually, we will learn how to optimize our databases and various ways to analyze the data inside.

But for now, let's get to SQL and see how it looks like and what it actually is!

What is SQL?

SQL (Structured Query Language) - is a language used for managing data stored in a RDBMS (Relational Database Management System).

Both people (e.g. via some console) and programs can use this language to interact with databases.

We will get to know what is RDBMS and the differences between various Database Management Systems in the upcoming articles.
What we need to know now, is that SQL is being used to create, manipulate and retrieve data and data structures in MySQL, PostgreSQL, Microsoft SQL Server, SQLite and the other database management systems that are used by a huge amount of applications, both web\server ones and even mobile and desktop ones.

With SQL, you have the full control over data and structures in a database and can change or retrieve anything there, as long as you have the neccessary privileges.

When you visit some website, most likely the posts, articles, pages and comments there are being loaded from some database using SQL by the website CMS (Content Management System) - e.g. WordPress or Drupal.

When you are creating an account in some web app or signing up for some service, it is probably adding your data into some database using SQL.

Even in embedded systems and mobile\desktop apps, they often use SQL to store some data in a local or remote database.

Using SQL, you can easily find or combine data from a database with thousands and millions of records.

SQL Example

So, how does SQL actually looks like? Here's an example:

SELECT title, author FROM books WHERE genre = 'Fantasy';

What does it do? As you can probably guess, this query will retrieve a list of all fantasy books - the result will be the list where each record has author and title fields.

So, if the table "books" had the following data:

idtitleauthorgenre
1The SAS Survival HandbookJohn WisemanManual
2How to Win Friends and Influence PeopleDale CarnegieSelf-help
3The Lord of the RingsJ. R. R. TolkienFantasy
4The Last WishAndrzej SapkowskiFantasy
5Walden; or, Life in the WoodsHenry D. ThoreauMemoir
6Conan the ConquerorRobert E. HowardFantasy

Our SQL query would get the following results:

titleauthor
The Lord of the RingsJ. R. R. Tolkien
The Last WishAndrzej Sapkowski
Conan the ConquerorRobert E. Howard

This is essentially what SQL is all about - creating and manipulating tables and table data. A database in RDBMS is a collection of tables with data and relations between them.

Basic commands

In order to better understand the example above, let's have a look at some of the most used SQL commands.

SQL queries consist of SQL statements, like SELECT or DELETE - and SQL clauses, like WHERE or ORDER BY.

Statements

SELECT FROM

With SELECT you can retrieve records from a table in database.

Use * to select all columns in a table:

SELECT * FROM table_name;

You can also select only some particular columns:

SELECT column_1, column_2, column_n FROM table_name;

Add DISTINCT keyword to remove duplicate results:

SELECT DISTINCT * FROM table_name;

To get the total amount of results, use COUNT() instead of a column name:

SELECT COUNT(*) FROM table_name;

INSERT INTO

This statement is used to add new records to a table.

You can specify table name, column names and values for the columns:

INSERT INTO table_name (column_1, column_2, column_n)
VALUES (value_1, value_2, value_n);

Or, you can specify only values, but the order should be the same as the order of columns in the table structure:

INSERT INTO table_name
VALUES (value_1, value_2, value_n);

If some field in the table has a default value, you can omit that column when adding records.

UPDATE

Use this statement to change already existing records in a table.

You can specify table name, columns and the new values for the columns:

UPDATE table_name
SET column_1 = value_1, column_2 = value_2, column_n = value_n;

Keep in mind - that will update all the records in a table.

Most of the time, you will want to update only some records, so UPDATE almost always is used with the WHERE clause:

UPDATE table_name
SET column_1 = value_1, column_2 = value_2, column_n = value_n
WHERE condition;

DELETE FROM

The statement is used to delete existing records from a table.

To delete all records in a table:

DELETE FROM table_name;

To delete only some particular records, add the WHERE clause:

DELETE FROM table_name
WHERE condition;

Clauses

WHERE, AND, OR and NOT

The WHERE clause filters the records in a statement.

It's usually being added at the end of SELECT, UPDATE or DELETE statements to limit them to some specific records:

SELECT * FROM table_name
WHERE condition;

The condition can be some logical expression, e.g. "id = 3" or "column_a

You can combine conditions by using AND, OR and parenthesis:

SELECT * FROM table_name
WHERE condition AND condition OR (condition OR condition);

And you can invert the condition result by using NOT:

SELECT * FROM table_name
WHERE NOT condition OR NOT condition;

Some operators that you can use to form conditions:

OperatorDescription
Equal
Greater than
Less than
Not equal
Greater than or equal
Less than or equal

There are more of these operators - e.g. pattern searching - but we will return to this later.

ORDER BY

This clause is used to change the order of the results.

Use ASC keyword for setting ascending order and DESC for descending, and specify the columns:

SELECT * FROM table_name
ORDER BY column_1, column_2, column_n DESC;

By default, it uses ascending order, so you can omit ASC:

SELECT * FROM table_name
ORDER BY column_1, column_2, column_n;

You can also specify order for each column separately:

SELECT * FROM table_name
ORDER BY column_1 DESC, column_2 ASC, column_n ASC;

Some notes

Did you know?

  • SQL is a data query language and is considered to be a special-purpose programming language.
  • SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s.
  • SQL could be pronounced as either "S-Q-L" or "sequel".
  • SQL became an ISO standard ISO/IEC 9075 in 1987.

In some database systems you can execute multiple SQL statements in one call to the server - in this case, a semicolon is used to separate SQL statements. Some database management systems require a semicolon to end each statement.

SQL statements, clauses and other keywords do not have to be uppercase - they are not case sensitive. But it is a standard practice to write them in uppercase.

SQL is a standard - and the same basic syntax and commands stay the same across different database management systems - i.e. it will work in MySQL the same as it will work in PostgreSQL.

But most of these systems also have some additional commands and extensions that can be very efficient. For now, we will stick with the standard SQL, though some DBMS specifics will be mentioned, when possible.

Conclusion

Now you should understand the basic SQL - and that is already enough to do a lot of stuff! This is just the tip of the iceberg, though.

We still haven't looked at commands for adding new tables or changing their structure, as well as some other statements and clauses, such as UNION or JOIN to combine multiple queries, etc.
We will have a much deeper look into all the SQL statements and clauses later.

In the next articles, we will have an overview of the different database management systems and learn more commands - both common and also more advanced queries and operations.

SQL is a very powerful language and tool, and I hope you will have a lot of fun working with your data!