Getting Started with SQL: A Beginners Hands-On Guide!

Pavan Belagatti - Sep 21 '23 - - Dev Community

In today's data-driven landscape, the ability to extract and manipulate information from databases efficiently is paramount. Structured Query Language (SQL) remains a cornerstone of data retrieval, enabling us to interact with vast amounts of data stored in databases. This article delves into an interesting tutorial and explains how efficiently we can retrieve the data from databases without breaking our heads.

The Story of Structured Query Language (SQL)

Structured Query Language (SQL) was developed in the early 1970s by IBM researchers Raymond Boyce and Donald Chamberlin, initially as a part of an IBM project called System R. The project aimed to create a prototype relational database management system (RDBMS), inspired by Edgar F. Codd's seminal paper on the relational model. 

database history

Before SQL and RDBMS, databases were often hierarchical or network-based, which made them complex and inflexible for querying and data manipulation. 

SQL was designed to provide a more straightforward, readable syntax for data manipulation and retrieval, abstracting much of the complexity involved in database management. It allowed users to interact with relational databases using high-level commands, making it easier to store, manipulate, and retrieve data. 

Over time, SQL became the standard language for RDBMS, supported by various vendors and organizations, and has evolved to include features like triggers, stored procedures, and more.

What is Structured Query Language (SQL)?

SQL Image

Image Credits: CSVeda

SQL is a A domain-specific language used for managing and querying relational databases. It serves as the standard means of communicating with databases, enabling users to create, modify, retrieve, and delete data. 

SQL operates through a variety of commands, including SELECT for data retrieval, INSERT for data insertion, UPDATE for data modification, and DELETE for data removal. Its syntax is designed to be both intuitive and expressive, allowing users to define relationships between tables, perform complex queries, and aggregate data. SQL plays a critical role in database management systems, enabling efficient storage, retrieval, and manipulation of structured data, making it a cornerstone technology in a wide range of applications and industries.

Today, we will see how to easily and automatically create our database, feed some data with SQL using a special Chatbot (you will love it, will talk about it in the tutorial) and retrieve it through a simple Node.js app. This is going to be exciting, let's get started!

Prerequisites

Note: We will use SingleStore DB as our Database in this tutorial. 

Tutorial

Sign Up to SingleStore & claim your $600 worth of free resource usage.

After signup, you will receive an email for confirmation and that will guide you to set up your fully managed database in minutes.

start with singlestore

First, you will create a workspace to provide compute resources for your databases.

What is a workspace?
Workspaces enable customers to run multiple workloads on isolated compute deployments while providing ultra low-latency access to shared data. This ensures applications are always operating on the latest data. Know more about workspaces here.

workspace group

Your workspace gets provisioned with end points and ready to use.
workspace db

It also gets loaded with a sample dataset for you to have a look and feel.
You can create a database of your choice from here.
You can see that I created 3 databases.

three databases

SingleStore Notebooks

Let's feed our database with some content through Notebooks.
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.

Let us see how we can do that.

Let's head over to our Notebooks section to play around.
singlestore notebooks

Create a new blank Notebook.
blank notebook

Will be using SingleStore's Chatbot SQrL, which is powered by OpenAI's GPT-4. It can provide immediate and relevant responses to SingleStoreDB-related questions. It can assist you with deployments, code optimization, integrations, resource management, troubleshooting, etc.

So we will use SQrl's help to query in our Notebooks and feed some content to our database we created. Make sure you have 'Code with SQrL' in the ON mode.

sqrl bot

Let's say you want to create a Database, you can simply ask the SQrL and it will generate that for you.

code with sqrl

Similarly, let's create a table named 'cities'.
Let's ask SQrL to do the same.

add table and 100 Indian cities in the database named 'saman' (we just created)
Enter fullscreen mode Exit fullscreen mode

Indian cities

Similarly, ask it to create a column named population and feed it with some dummy number.

add one more column 'Population' and add dummy population count
Enter fullscreen mode Exit fullscreen mode

dummy population feed

Once executed, you will see the below output.

cities list

Similarly, I asked SQrL to add the following content to my database.

add one more column 'Developers' and add dummy developer count

Enter fullscreen mode Exit fullscreen mode

dummy developers

You can go to your Database dashboard and see the sample data we feeded into the database just now through Notebooks.

database feed

That's an amazing way to feed a lot of data in seconds or minutes to your database.

Retrieve the Data With a Simple Application

We will make use of a simple Node.js application to retrieve/show this data in our local.

Let's start

npm init -y
Enter fullscreen mode Exit fullscreen mode

Install the MySQL2 package which will allow you to connect Node.js and SingleStore, as they are wire-compatible 1. You can do this by running the following command in your terminal:

npm install - save mysql2
Enter fullscreen mode Exit fullscreen mode

To expose this data on a localhost port 3009, you'll need to use something like Express.js, a popular Node.js web application framework. Make sure you install express using npm:

npm install express
Enter fullscreen mode Exit fullscreen mode

At this point, this is how your package.json should look

packagejson

Note: Make sure to add "type":"module", in the package.json file if it is not present.

Create an index.js file where our server code will be present.

import mysql from 'mysql2/promise';
import express from 'express';

// TODO: adjust these connection details to match your SingleStore deployment:
const HOST = '<your host end point>';
const USER = 'admin';
const PASSWORD = '<your password>';
const DATABASE = 'saman';

const app = express();
const port = 3009;

app.get('/', async (req, res) => {
    // Create the connection
    const conn = await mysql.createConnection({
        host: HOST,
        user: USER, 
        password: PASSWORD,
        database: DATABASE
    });

    // Execute the query
    const [rows, fields] = await conn.execute('SELECT * FROM cities');

    // Return the results
    res.send(rows);

    await conn.end();
});

app.listen(port, () => {
    console.log(`App running on http://localhost:${port}`);
});
Enter fullscreen mode Exit fullscreen mode

Make sure to replace 'HOST', 'PASSWORD', and 'DATABASE' with your specific parameters.

Host endpoint can be found through your workspace.

database connection

connect application

The password can be found in the 'Access' tab.
admin password

Let's add all these parameters, save and run the application.

node index.js
Enter fullscreen mode Exit fullscreen mode

You should see your application running on your specified localhost.

app running

This way we can easily automate the flow of creating, updating our databases and retrieving data.

Basic SQL Commands for Beginners!

These are basic SQL commands to get you started.

Data Definition Language (DDL)

  • Creating Databases and Tables
-- Create a new database
CREATE DATABASE database_name;

-- Create a new table
CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);
Enter fullscreen mode Exit fullscreen mode
  • Altering Tables
-- Add a new column
ALTER TABLE table_name ADD column_name datatype;

-- Drop a column
ALTER TABLE table_name DROP COLUMN column_name;
Enter fullscreen mode Exit fullscreen mode
  • Deleting Tables and Databases
-- Delete a table
DROP TABLE table_name;

-- Delete a database
DROP DATABASE database_name;
Enter fullscreen mode Exit fullscreen mode

Data Manipulation Language (DML)

  • Inserting Data
-- Insert data into a table
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Enter fullscreen mode Exit fullscreen mode
  • Updating Data
-- Update data in a table
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Enter fullscreen mode Exit fullscreen mode
  • Deleting Data
-- Delete data from a table
DELETE FROM table_name WHERE condition;
Enter fullscreen mode Exit fullscreen mode

Querying Data

- Selecting Data
-- Select all columns
SELECT * FROM table_name;

-- Select specific columns
SELECT column1, column2, ... FROM table_name;
Enter fullscreen mode Exit fullscreen mode
  • Filtering Data
-- Using WHERE clause for filtering
SELECT * FROM table_name WHERE condition;

-- Using multiple conditions with AND, OR
SELECT * FROM table_name WHERE condition1 AND/OR condition2;
Enter fullscreen mode Exit fullscreen mode

Sorting and Aggregation

  • Sorting Data
-- Sorting data in ascending (ASC) or descending (DESC) order
SELECT * FROM table_name ORDER BY column_name ASC/DESC;
Enter fullscreen mode Exit fullscreen mode
  • Aggregate Functions
-- Count records
SELECT COUNT(column_name) FROM table_name;

-- Average of a column
SELECT AVG(column_name) FROM table_name;

-- Sum of a column
SELECT SUM(column_name) FROM table_name;
Enter fullscreen mode Exit fullscreen mode

Joining Tables

-- INNER JOIN
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

-- LEFT JOIN
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

Below is a basic SQL cheat sheet for beginners.
SQL cheat sheet for beginners

If you are interested to learn more on the concepts such as Data Science, Databases, Python and GenerativeAI, I have been writing simple tutorials you might like to try.

Follow or connect with me on LinkedIn.

AND, Don't forget to claim your $600 worth free computing resources from SingleStore to try these tutorials.
Signup to SingleStore!

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .