Simple introduction to understanding SQL queries

If you wanted to get started learning SQL queries, but always feared it might be too complex, start by following my quick guide to getting started.

SQL queries can get complex, really complex. But at its heart, SQL was meant to be a simple way to interact a database of records using almost spoken commands.

Prerequisite : Understand database concepts

Databases group similar data together in a table. The grouping is given a name that is recognised by the user, for example 'Users', 'Customers', 'Books'. A database will normally have more than one table.

Each record is called a row. For example, if you store information about two books, you have two records in your table.

database:my_database
├── table:Books
│   ├── 1984;George Orwell
│   └── Letters from the Earth;Mark Twain
├── table:Customers
└── table:Users

A table contains columns that allow you to store different data about the record. The Book table contains two columns "author" and title. All books will allow you store this information about it.

+---------+--------------------------------+
| author        | title                    |
+---------+--------------------------------+
| George Orwell | 1984                     |
| Mark Twain    | Letters from the Earth   |
+---------+--------------------------------+

Prerequisite : Understand your database structure

Understanding your database structure is essential to interacting with it.

In the example above, we will interact with the list of books, so we will query the 'Books" table with two fields "author" and "title".

Interacting with your database.

SQL is an abbreviation for Structured Query Language. It allows you to "speak" to the database in a structured manner by passing queries (sometimes also called statements) to the database.

A single request that is passed to the database is made up of clauses that provide the details of your query. For example. a FROM clause tells the database which table you want to interact with, while a WHERE clause explains which records you are interested in.

More about this later.

Four SQL statements you must know.

The four queries that to secure your understanding is :

1. SELECT. which allows you to retrieve one or more rows from your database table;

2. INSERT, which allows you to insert records into your table;

3. MODIFY which allows you to modify records from your table;

4. DELETE which allows you to remove records into your table;

SELECTing data

The SELECT statement tells your database which rows and fields you are interested in.

The simplest form of the SELECT statement is to return all data from a table.

SELECT
   *
FROM
   Books;

+---------+--------------------------------+
| author        | title                    |
+---------+--------------------------------+
| George Orwell | 1984                     |
| Mark Twain    | Letters from the Earth   |
+---------+--------------------------------+

This will return all rows and all columns from the Books table.

You can choose to return certain columns only

SELECT
   title
FROM
   Books;

+---------+----------------+
| title                    |
+---------+----------------+
| 1984                     |
| Letters from the Earth   |
+---------+----------------+

You can choose to return certain rows only

SELECT
   *
FROM
   Books
WHERE
   author = "George Orwell";

+---------+--------------------------------+
| author        | title                    |
+---------+--------------------------------+
| George Orwell | 1984                     |
+---------+--------------------------------+


SELECT
   title
FROM
   Books
WHERE
   author = "George Orwell";

+---------+----------------+
| title                    |
+---------+----------------+
| 1984                     |
+---------+----------------+

You can add additional clauses, like a clause to sort your data. In the example below, the data is sorted in descending order based on the author.

SELECT
   title, author
FROM
   Books
ORDER BY
   author DESC;

+--------------------------+---------------+
| title                    | author        |
+--------------------------+---------------+
| Letters from the Earth   | Mark Twain    |
| 1984                     | George Orwell |
+--------------------------+---------------+

(Psst. Did you notice that we changed the order of the columns as well?)

INSERTing data

Inserting data has a few versions. We present one of the simplest ones.

INSERT INTO
   Books
SET
   author = "Mark Twain",
   title  = "Letters from the Flat Earth";

UPDATE-ing data

Updating normally has a where clause to only affected certain rows. In the INSERT statement above, we want to correct the new records

UPDATE
   Books
SET
   title  = "Letters from the Earth";
WHERE
   author = "Mark Twain";

DELETE-ing data

Removing a record is just as simple. Remember to add a WHERE clause to prevent all of your data being removed.

DELETE FROM
   Books
WHERE
   author = "Mark Twain";

So, there you have it - a simple introduction to understanding SQL queries.

There are a few sites you can safely try your SQL queries on, for example

http://www.sqlfiddle.com/

https://www.tutorialspoint.com/codingground.htm

Happy hacking.