Introduction to SQL for Absolute Beginners!

Diving into the world of databases might seem daunting, but with the right guidance, it becomes an intriguing journey. SQL, or Structured Query Language, is the cornerstone of most modern relational databases. It provides a systematic and powerful means to create, manipulate, and query data.

This tutorial offers a step-by-step introduction to fundamental SQL commands, guiding you from the initial stages of creating a database to more advanced operations. Whether you're an aspiring data analyst, a budding web developer, or just curious about databases, this guide will equip you with the foundational knowledge to get started with SQL.

But first, let's understand what is a database.

What is a Database?

A database is a structured collection of data that allows for efficient storage, retrieval, and manipulation of information. It can be electronic, as in computer databases, or physical, like a library's card catalog.

In the digital realm, databases are managed by Database Management Systems (DBMS), which provide tools for organizing, querying, and maintaining the data. Databases can store various types of information, from simple lists, like contacts in a phonebook, to complex data sets, such as inventory for large corporations. Their design can range from flat structures, like spreadsheets, to more complex relational or non-relational models, depending on the nature and purpose of the data they hold.

What is SQL?

SQL, or Structured Query Language, is a standardized programming language specifically designed for managing and manipulating relational databases. It provides a systematic way to create, retrieve, update, and delete data from a database.

SQL encompasses a wide range of operations, from simple data retrieval using the SELECT statement to more complex tasks like transaction control, data modification, and schema creation. Due to its robustness and versatility, SQL has become the de facto standard for database operations and is supported by almost all modern relational database management systems (RDBMS) such as MySQL, SingleStore, PostgreSQL, Oracle, and Microsoft SQL Server.

What is a Relational Database?

A relational database is a type of database that organizes data into structured tables with rows and columns, where each row represents a unique record and each column represents a specific attribute of that record. These tables, also known as relations, can be linked or related to one another based on common attributes, enabling efficient data retrieval.

Image credits: insightssoftware

The primary advantage of a relational database is its ability to maintain data integrity and accuracy through the use of constraints, keys, and relationships. The design and structure of relational databases are based on the principles of the relational model proposed by Dr. E.F. Codd in 1970. SQL (Structured Query Language) is the standard language used to query and manipulate data in relational databases.

Prerequisite:

• Install SingleStore Notebooks for FREE - We will use SingleStore's Notebook feature to play around with our SQL queries.

What is SingleStore?
SingleStore (formerly known as MemSQL) is a distributed, relational database management system (RDBMS) designed for high-performance, real-time analytics, and massive data ingestion.

What is SingleStore Notebooks Feature?
Notebooks have become increasingly popular in the data science community as they provide an efficient way to explore, analyze and visualize data, making it easier to communicate insights and results. SingleStore's Notebook feature is based on the popular Jupyter Notebook, which is widely used in data science and machine learning communities.

One interesting fact about SingleStore Notebooks is that, they allow users to query SingleStore's distributed SQL database directly from within the notebook interface.

As soon as you sign up, make sure to select the 'Notebooks' tab.

Create a blank Notebook selecting SQL.

You will see the dashboard where you can run our SQL queries/commands below.

1. Creating a Database

Before we can work with tables and data, we need a database.

SQL Command

CREATE DATABASE database_name;

Example:

CREATE DATABASE CompanyDB;

2. Using a Database

To start working with the database you've created, you need to select it.

SQL Command:

USE database_name;

Example:

USE CompanyDB;

You can confirm the database we just created.

3. Creating a Table

Tables are where the data in a database is stored. Each table has columns (fields) and rows (records).

SQL Command:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

4. Inserting Data into a Table

Now that we have a table, we can insert data into it.

SQL Command:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (1, 'John', 'Doe', 'Sales');

5. Retrieving Data from a Table

The SELECT statement is used to retrieve data.

SQL Command:

SELECT column1, column2, ...
FROM table_name;

Example:

SELECT FirstName, LastName
FROM Employees;

6. Updating Data in a Table

To modify existing records.

SQL Command:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE Employees
SET Department = 'Marketing'
WHERE LastName = 'Doe';

7. Deleting Data from a Table

To remove records.

SQL Command:

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Employees
WHERE LastName = 'Doe';

8. Dropping a Table

To delete a table and all its data.

SQL Command:

DROP TABLE table_name;

Example:

DROP TABLE TemporaryData;

9. Dropping a Database

To delete a database.

SQL Command:

DROP DATABASE database_name;

Example:

DROP DATABASE TestDB;

This is a basic introduction to SQL. As you progress, you'll encounter more advanced topics like joins, subqueries, functions, and more. For now, practice these basics to get a solid foundation. Remember, the best way to learn is by doing. As mentioned in the tutorial, signup to SingleStore to use their Notebook feature where you can run all your queries and keep practicing these commands.

Comments

[Rick Delpo]

Thanks Pavan for the easy to understand CRUD operations on SQL (create, read, update, delete). To extend this a bit further we also have the javascript equivalents of SQL CRUD operations here
dev.to/rickdelpo1/crud-4-sql-comma...
From a Javascript frontend we can store data in NoSQL using a JSON file. We then call Javascript Array methods on the data. This NoSQL approach is mainly for small use cases that don't involve relational data, such as a simple dashboard.

[Michael Tharrington]

Great post, Pavan! I hope there's a sequel.

[Pavan Belagatti]

Sure, can extend this.

[Kudzai Murimi]

Perfect Introduction, thanks Pavan.

[Pavan Belagatti]

Glad you liked it.

[Nathan S.R.]

Thanks for this very useful post. I just wanted to add that, there is a very easy way now, to test all the SQLs described here, using the free & portable tools, mentioned in my latest post here : dev.to/linuxguist/learn-sql-quickl...

[Thomas Bnt ☕]

Very cool post!

[Pavan Belagatti]

Thanks:)

[Samaresh Das]

SingleStore Notebooks is quite a delight. Many of the tools I used for SQL are always ugly looking and not the best, I loved the modern tool. Thanks.

[Pavan Belagatti]

Thanks a lot. Please explore more on SingleStore Notebooks and also we have a discord channel I can add you:)

[Mark Lew]

I need to set up a data base and do joins with another data base, about 100,000 property records. Can I hire someone to help me with this in Nov?