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.
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)?
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.
- Create a Free SingleStore cloud account worth $600 of free resources
- Node.js installed. Install it here from the official website.
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.
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.
Your workspace gets provisioned with end points and ready to use.
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.
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.
Create a new 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.
Let's say you want to create a Database, you can simply ask the SQrL and it will generate that for you.
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)
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
Once executed, you will see the below output.
Similarly, I asked SQrL to add the following content to my database.
add one more column 'Developers' and add dummy developer count
You can go to your Database dashboard and see the sample data we feeded into the database just now through Notebooks.
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
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
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
At this point, this is how your package.json should look
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}`);
});
Make sure to replace 'HOST', 'PASSWORD', and 'DATABASE' with your specific parameters.
Host endpoint can be found through your workspace.
The password can be found in the 'Access' tab.
Let's add all these parameters, save and run the application.
node index.js
You should see your application running on your specified localhost.
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,
...
);
- 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;
- Deleting Tables and Databases
-- Delete a table
DROP TABLE table_name;
-- Delete a database
DROP DATABASE database_name;
Data Manipulation Language (DML)
- Inserting Data
-- Insert data into a table
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
- Updating Data
-- Update data in a table
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
- Deleting Data
-- Delete data from a table
DELETE FROM table_name WHERE condition;
Querying Data
- Selecting Data
-- Select all columns
SELECT * FROM table_name;
-- Select specific columns
SELECT column1, column2, ... FROM table_name;
- 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;
Sorting and Aggregation
- Sorting Data
-- Sorting data in ascending (ASC) or descending (DESC) order
SELECT * FROM table_name ORDER BY column_name ASC/DESC;
- 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;
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;
Below is a basic 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!
Top comments (0)