Programming Tutorials

  • View all tutorials
  • ,
    Introduction to SQL
    SQL, or Structured Query Language, allows you to operate on databases by providing you a method with which to add, delete, update, and fetch elements from database tables. There are several different database management systems, such as MySQL, MS Access, Oracle, and others, but they all allow you to write SQL commands to interact with the database in a similar way. In this tutorial we will focus on SQL in general without focusing on a particular system, such as MySQL or MS Access.

    Executing SQL

    Depending on what server-side language you are using to power your application, executing an SQL query will require different code in that language. For example, in PHP, it would be easy to use MySQL as the database system, and then to actually execute commands in PHP you could use the built-in mysqli function like so:
      // connnect to database
      $mysqli = new mysqli("", "username", "password", "database");
      if ($mysqli->connect_errno) {
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
      // retrieve a set of rows from a database
      $rows = $mysqli->query("SELECT * FROM People ORDER BY ID ASC");
    In Node.js (JavaScript), the code to execute the SQL commands would look a bit different though:
    var mysql = require("mysql");
    // connect to database
    var con = mysql.createConnection({
      host: "",
      user: "username",
      password: "password"
    // retrieve a set of rows from a database
    con.query('SELECT * FROM People ORDER BY ID ASC', function(err, rows) {
      if (err) throw err;
    Depending on the language being used on the server, the methods used to connect to the database and execute commands will differ, but the actual SQL syntax remains the same, as you can see in the above example with the command: SELECT * FROM People ORDER BY ID ASC In this tutorial we'll focus specifically on the SQL syntax, without regard to what language you may use in the future to run the commands. Below is a list of useful resources on how to get started with SQL and your language of choice. PHP and MySQL Node.js and MySQL Python and SQLite Ruby and MySQL

    How relational databases work

    Data stored in a relational database is stored in objects called tables. A table is a collection of data entries that consists of rows and columns. For example, below is a table called People where we store certain properties for each person in the table, where each person is represented by a row. This is a visual representation of the table we want. To actually create this table, we need to first create a database, and then create this People table. The following SQL code creates a database, selects it for all the operations we will perform, and then creates a table.
    // create database
    CREATE DATABASE test_db;
    // choose database, because we can have several
    USE test_db;
    // create People table
    Name varchar(255),
    Age int,
    Country varchar(255),
    Job varchar(255)
    To create a table, the syntax requires a column name and a data type, which can be one of several that are offered by SQL, for example:
    varchar, int, decimal, date, and more. The NOT NULL UNIQUE constraints force the column to never be NULL and always be UNIQUE. This ensures the ID column is always an integer and never repeats itself.


    Inserting elements into a table is done using the INSERT INTO SQL statement. To insert a row, you first specify the column names, and then the values. You can also just specify the values to be inserted. Below we'll add two more rows into the People table.
    INSERT INTO People (Name, Age, Country, Job)
    VALUES ('Guy Johnson', 52, 'Germany', 'Professor');
    INSERT INTO People 
    VALUES ('Jane Miller', 31, 'USA', 'Nurse')
    With these two insertions, the table now looks like this:


    We can retrieve any number of rows from a table using the SELECT statement in combination with the WHERE statement. We can also combine the statement with other conditionals to get precise results back. Let's start with a few simple SQL commands.
    This statement returns the following rows, sorted in descending order based on ID. We can also add in a conditional to get a more complex statement:
    SELECT * FROM People WHERE Name LIKE '%Smith%' OR AGE = 31
    The LIKE statement searched for a pattern within a column, and the % operator defines a wildcard that matches zero or more characters. So this statement matches anyone who has 'Smith' in their name (including names like 'Smither' or 'Smithy') or anyone who is at the age of 31. The rows retrieved from this SQL command are below. For more examples on SELECT and WHERE statements, check out this resource.


    You are able to update specific rows in a table using the UPDATE statement in combination with the WHERE statement. Below are a few examples with the necessary syntax required:
    UPDATE People SET Age = 32 WHERE ID = 5
    UPDATE People SET Country = 'United States' WHERE Country = 'USA'
    // multiple updates to a row
    UPDATE People SET Age = 25, Country = 'Sweden' WHERE ID = 3
    With the above updates, the table now looks like the following: But be careful with the UPDATE command because if you omit the WHERE part, then the whole table will be updated with the new value. For example, if we run the statement below, then the whole table would get updated to the new Age.
    // don't actually run this because everyones
    // age will get updated to 53
    UPDATE People SET Age = 53


    Deleting rows from a table is straightforward, it just requires using the DELETE statement in combination with the WHERE statement. Below are a few examples of deleting rows from our People table.
    DELETE FROM People WHERE Age > 30 AND Age < 50
    Running the SQL command above produces the following updated table now:


    In this tutorial we covered how to create a database and table, and then how to do some common operations such as inserting, selecting, updating, and deleting. For more advanced SQL topics check out the following resources: W3Schools SQL tutorials TutorialsPoint Advanced SQL
    mrdaniel wrote this tutorial on 5/13/16 | database, sql
    Log in to submit a comment.