Learn SQL, From Zero to Hero: The Complete Beginner SQL Guide for Database Mastery

3 min readJun 1, 2023

INTRODUCTION:

Structured Query Language (SQL) is a tool for managing and relational databases.

Whether you’re a software engineer, data analyst, or aspiring database administrator, having a solid understanding of SQL is essential.

In this article, we’ll explore the basics of learning SQL, focusing on MySQL as our database management system.

1. CREATE A DATABASE:

The CREATE command in MySQL is used to create database objects such as:

databases
tables
views
indexes

The CREATE DATABASE statement followed by the name of the database creates a database in MySQL.

CREATE DATABASE mydatabase;

2. CREATE A TABLE

Tables are used to store data in a structured manner within a database.

To create a table use CREATE TABLE and the name of the table and its column definitions.

Below we create a table named users with 3 columns: id, name, and age. The column definitions specify the data type associated with each column:

id integer

age integers

name a string of a maximum of 50 characters

MySQL offers various data types such as:

integers
floats
strings
dates

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    age INT
);

3. INSERT DATA INTO THE TABLE

The INSERT command is used to insert new records or rows into a table.

SYNTAX

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

INSERT 1 ROW

INSERT INTO users (name, age)
VALUES ('John Doe', 25);

INSERT MULTIPLE ROWS

INSERT INTO users (name, age)
VALUES ('John Doe', 25), ('Jane Smith', 30);

4. UPDATE DATA

The UPDATE command is used to modify existing records in a table.

In the table named users with columns id, name, and age to update the age of user with id=1, the UPDATE command would look like this:

UPDATE 1 COLUMN

UPDATE users
SET age = 26
WHERE id = 1;

UPDATE MULTIPLE COLUMNS

UPDATE users
SET age = 26, name = 'John Smith'
WHERE id = 1;

5. SELECT DATA

The SELECT statement is used to get data from one or multiple tables in a database.

SELECT name FROM users;

SELECT age FROM users;

SELECT name, age FROM users;

SELECT all columns using the asterisk character:

SELECT * FROM users;

WORKING
WITH
STRING
DATA

6. SUBSTR — SELECT A SUBSTRING OF THE STRING DATA

The substring function allows you to extract a specific portion of a string based on its position or length.

-- SYNTAX
SUBSTR(string, start, length)

-- Returns: Hello
SELECT SUBSTR('Hello, world!', 1, 5);

The example below selects the first 5 characters of the entries in the name column in the users table and shows the result in a column called substring_name

SELECT SUBSTR(name, 1, 5) as substring_name from users;

7. LENGTH — GET THE LENGTH OF A STRING DATATYPE ENTRY

SELECT name, length(name) FROM users;



/*
  Returns the list of names and their length, for example:

  name       | length(name)
  John Doe   | 8
*/

WORKING
WITH
INTEGER
DATA

8. ROUND — ROUND NUMBERS SPECIFIED TO DECIMAL PLACES

-- Returns: 235.42
SELECT ROUND(235.415, 2);