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.