Get Started with SQL - A Brief Guide

When I started my career in IT in the early 2000s, SQL was one of the most important programming concepts because it was one of the only ways to work with databases and structured data. SQL continues to enjoy a lot of popularity and is a great skill to learn to be successful in the tech industry.

My name is Nat Miletic, and I own Clio Websites, a Calgary-based web development agency. I was always fascinated by working with data and building powerful applications that require storing and working with data. One of my first jobs in tech was working as a PL/SQL developer for Oracle. I worked in this capacity for over three years and enjoyed learning about structured data and working with databases. Understanding and working with SQL is one of the most important skills a developer can have.

This article teaches you the basics of SQL and how to get started. It also covers why SQL is important and what you can do with it.

History of SQL

In the 1970s, IBM introduced SQL as a query language for its System R relational database management system. System R was the first database management system based on the relational model and was designed to give users an efficient way to manage and access data.

Over time, SQL has become one of the most widely-utilized programming languages worldwide, with applications across many industries. Its use has been implemented in databases such as MySQL, Oracle and Microsoft SQL Server, which are still popular today.

Additionally, SQL has been designed to be highly extensible, allowing developers to create custom extensions and functions to suit their particular needs better. This extensibility has allowed SQL to remain a powerful and versatile language, and its use continues to expand as technology advances.

What is SQL?

SQL stands for Structured Query Language and is a declarative language that enables you to communicate with a relational database and manipulate data. It allows you to store, retrieve, modify, and delete data.

Since SQL is a declarative language, you only need to describe the data you want without having to specify how to retrieve it. It consists of commands, or statements, that allow you to manipulate data. The most common SQL statements are:

• SELECT: Retrieve data from the database
• INSERT: Insert new data into the database
• UPDATE: Update existing data in the database
• DELETE: Delete data from the database

SQL also supports several other commands, but the ones mentioned above are the most commonly used. As for what it can do, SQL can be used to:

• Create databases and tables
• Insert, update, and delete data
• Retrieve data from databases and tables
• Run queries against databases and tables
• Set permissions, and create views and reports

SQL is a versatile language with a wide range of applications. It is used in fields such as finance, healthcare, data science, and web development, to name just a few.

Why is SQL important?

SQL is important for developers and anyone who works with data. Data is at the heart of every application, and being able to manipulate it is crucial. Here are some of the things SQL enables you to do:

• Understand data better: You can use SQL to query databases and get the information that you need
• Work with databases more efficiently: SQL can help you to insert, update, and delete data in databases
• Analyze data: It enables you to create reports and find trends in data
• Make better decisions: SQL can help you make better decisions by allowing you to read all the data, export it, and then plot it to gain insight into it

Get started with SQL

In this section, you learn the basic SQL statements needed to perform CRUD operations. You can use the W3Schools SQL editor to run the commands from this section.

Create a table with SQL

Let's start practising by creating a table. You can create a table with the following SQL statement:

CREATE TABLE friends (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);

The above command creates a table named "friends" with three columns:

• "id" of type "INTEGER"
• "name" of type "TEXT"
• "age" of type "INTEGER"

The id column is the primary key, meaning it is unique for each row.

Insert data with SQL

Now that there is a table let's insert some data into it. You can insert data with the following SQL statement:

INSERT INTO friends (id, name, age) VALUES (1, 'John', 20), (2, 'Jane', 21), (3, 'Joe', 22);

The above statement inserts three rows of data into the "friends" table:

• first row contains the id of 1, the name "John", and an age of 20
• second row contains the id of 2, the name "Jane", and an age of 21
• third row contains the id of 3, the name "Joe", and an age of 22

Retrieve data with SQL

So far, you have a table with some data inside. But the question is - how do you retrieve that data?

SELECT * FROM friends;

The above SQL statement retrieves all data from the "friends" table. The asterisk * is a wildcard that means "all." As a result, this statement returns all the columns and rows from the "friends" table.

Update data with SQL

There are scenarios where you need to update existing records. You can do that as follows:

UPDATE friends
SET age = 22
WHERE id = 1;

This SQL statement updates John's age to 22 from 20. It's able to locate this particular record by using the id specified in the statement. Once you have updated the table, you can perform another SELECT command to verify that your changes were successful.

Delete data with SQL

In case you want to delete records, there is a SQL statement for that too.

DELETE FROM friends
WHERE id = 1;

The above statement deletes the record whose id matches the id specified in the SQL statement. In this case, it deletes "John" from the database.

Be cautious with the "DELETE" statement, as it can wipe out your entire database if you don't provide the "WHERE" clause.

Summary

This brief guide introduced you to the SQL language. You learnt the history behind it, why it is important and how to perform the basic CRUD operations. You are now equipped with the fundamentals, and you are ready to jump into the more advanced stuff.

If you are using MySQL, you might want to use a MySQL GUI client.

If you want to go in-depth and learn more about SQL and databases, I recommend the Complete SQL + Database Bootcamp. By the end of the course, you'll be skilled in SQL, database management and database design.