DEV Community

Pavan Belagatti
Pavan Belagatti

Posted on

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.

what is database

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.

what is a relational database 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.

Notebooks tab

Create a blank Notebook selecting SQL.
sql tab

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

sql queries

1. Creating a Database

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

SQL Command



CREATE DATABASE database_name;


Enter fullscreen mode Exit fullscreen mode

Example:



CREATE DATABASE CompanyDB;


Enter fullscreen mode Exit fullscreen mode

2. Using a Database

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

SQL Command:



USE database_name;


Enter fullscreen mode Exit fullscreen mode

Example:



USE CompanyDB;


Enter fullscreen mode Exit fullscreen mode

You can confirm the database we just created.
Database 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,
    ...
);



Enter fullscreen mode Exit fullscreen mode

Example:



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



Enter fullscreen mode Exit fullscreen mode

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, ...);


Enter fullscreen mode Exit fullscreen mode

Example:



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


Enter fullscreen mode Exit fullscreen mode

5. Retrieving Data from a Table

The SELECT statement is used to retrieve data.

SQL Command:



SELECT column1, column2, ...
FROM table_name;


Enter fullscreen mode Exit fullscreen mode

Example:



SELECT FirstName, LastName
FROM Employees;


Enter fullscreen mode Exit fullscreen mode

6. Updating Data in a Table

To modify existing records.

SQL Command:



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


Enter fullscreen mode Exit fullscreen mode

Example:



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


Enter fullscreen mode Exit fullscreen mode

7. Deleting Data from a Table

To remove records.

SQL Command:



DELETE FROM table_name
WHERE condition;


Enter fullscreen mode Exit fullscreen mode

Example:



DELETE FROM Employees
WHERE LastName = 'Doe';


Enter fullscreen mode Exit fullscreen mode

8. Dropping a Table

To delete a table and all its data.

SQL Command:



DROP TABLE table_name;


Enter fullscreen mode Exit fullscreen mode

Example:



DROP TABLE TemporaryData;


Enter fullscreen mode Exit fullscreen mode

9. Dropping a Database

To delete a database.

SQL Command:



DROP DATABASE database_name;


Enter fullscreen mode Exit fullscreen mode

Example:



DROP DATABASE TestDB;


Enter fullscreen mode Exit fullscreen mode

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.

Top comments (11)

Collapse
 
rickdelpo1 profile image
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.

Collapse
 
michaeltharrington profile image
Michael Tharrington

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

Collapse
 
pavanbelagatti profile image
Pavan Belagatti

Sure, can extend this.

Collapse
 
respect17 profile image
Kudzai Murimi

Perfect Introduction, thanks Pavan.

Collapse
 
pavanbelagatti profile image
Pavan Belagatti

Glad you liked it.

Collapse
 
linuxguist profile image
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...

Collapse
 
thomasbnt profile image
Thomas Bnt

Very cool post!

Collapse
 
pavanbelagatti profile image
Pavan Belagatti

Thanks:)

Collapse
 
samareshdas profile image
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.

Collapse
 
pavanbelagatti profile image
Pavan Belagatti

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

Collapse
 
teachersfuture profile image
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?