Introduction to SQL for Dummies

ยท

6 min read

Welcome to a beginner's guide to SQL! I'm Brahma ๐Ÿ‘‹, a passionate software developer. I am documenting my learning journey through a series of blog posts. Stay tuned!!

Introduction

Ahh!! The boring stuff again!! ๐Ÿฅฒ

Brief History and Development

MySQL is an open-source relational database management system (RDBMS) that has been a cornerstone of database management since its inception in 1995 by Swedish company MySQL AB. It was created by Michael "Monty" Widenius, David Axmark, and Allan Larsson. MySQL was designed to provide a reliable, high-performance database solution that was easy to use and available to everyone.

In 2008, MySQL AB was acquired by Sun Microsystems, which was subsequently acquired by Oracle Corporation in 2010. Under Oracleโ€™s stewardship, MySQL has continued to evolve, maintaining its open-source nature while benefiting from Oracle's extensive resources and expertise.

Overview of Relational Databases

Relational databases are a type of database that store data in structured tables, which are organized into rows and columns. Each table represents a collection of related data, and each row in a table corresponds to a unique record, while columns represent the attributes of the data.

The key feature of relational databases is the ability to use Structured Query Language (SQL) to query and manipulate data. SQL is a powerful language that allows for precise data retrieval, updating, and management, making relational databases highly flexible and efficient.

Installation and Setup

The not-so-cool part yet the necessary evil!!๐Ÿซ 

Windows:

  1. Download MySQL Installer: Visit the MySQL website and download the installer for Windows.

  2. Run the Installer: Launch the installer, choose "Developer Default" setup, and follow the prompts.

  3. Configuration: Set a strong root password and configure any additional user accounts.

  4. Complete Installation: Finish the setup. MySQL is now installed.

macOS:

  1. Download MySQL DMG: Download the DMG file from the MySQL website.

  2. Install MySQL: Open the DMG file, run the installer, and set the root password during installation.

  3. Start MySQL Server: Start the server from System Preferences or via command line:

     sudo /usr/local/mysql/support-files/mysql.server start
    

Linux:

  1. Debian/Ubuntu:

    • Update and install MySQL server:

        sudo apt update
        sudo apt install mysql-server
      
    • Secure the installation:

        sudo mysql_secure_installation
      
  2. RHEL/CentOS:

    • Add the MySQL repository and install MySQL server:

        sudo yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
        sudo yum install mysql-server
      
    • Start and secure the server:

        sudo systemctl start mysqld
        sudo mysql_secure_installation
      

Running SQL

Just run the SQL Command Line or the WorkBench if you decide to use MySQL. And that's it!!! Good to go!

Basic Concepts

The usual jargon!! ๐Ÿ˜

  • Database: A collection of organized data stored and accessed electronically. Think of it as a filing cabinet where all your information is stored.

  • Table: A structured format within a database that organizes data into rows and columns. It's similar to a single drawer in the filing cabinet, with labeled folders (columns) and sheets of paper (rows).

  • Record: A single entry or row in a table, representing a set of related data. This is akin to a single sheet of paper in a folder, containing all details for a specific item.

Is that anyway related to IRL? - Yes!! It does!! Read on ๐Ÿ‘‰

  • Database: Imagine a library. Each bookshelf represents a database, containing a variety of books (tables) on different subjects.

  • Table: Within our library, a specific bookshelf might be dedicated to science books. Each book on this shelf is a table with chapters (columns) and pages (rows).

  • Record: A single page within a science book, which has data like the title, author, and content, represents a record.

SQL Basics

SQL is the language used to interact with databases. It allows you to retrieve, update, insert, and delete data. It's the standard language for relational database management systems.

Now let's do some actual grind ๐Ÿ˜!!

Creating a Database and Tables & Inserting Data

To create a new database and a table in MySQL, you can use the following commands:

  1. Creating a Database:

     CREATE DATABASE mydatabase;
    
  2. Creating a Table:

     CREATE TABLE users (
         id INT AUTO_INCREMENT PRIMARY KEY,
         name VARCHAR(100),
         email VARCHAR(100),
         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
     );
    
  3. Inserting Data

    Using the INSERT INTO Statement:

     INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
    

    Inserting Multiple Records:

     INSERT INTO users (name, email) VALUES 
     ('Bob', 'bob@example.com'),
     ('Charlie', 'charlie@example.com'),
     ('Diana', 'diana@example.com');
    

Common SQL Commands

  1. SELECT: Retrieves data from one or more tables.

     SELECT column1, column2 FROM table_name WHERE condition;
    

    Example:

     SELECT name, email FROM users WHERE age > 18;
    
  2. INSERT: Adds new records to a table.

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

    Example:

     INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com');
    
  3. UPDATE: Modifies existing records in a table.

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

    Example:

     UPDATE users SET email = 'john.newemail@example.com' WHERE name = 'John Doe';
    
  4. DELETE: Removes records from a table.

     DELETE FROM table_name WHERE condition;
    

    Example:

     DELETE FROM users WHERE name = 'John Doe';
    

What you just read is also called CRUD operation -> Create Read Update Delete.

Where, Order by & Group by

To make the SELECT clause powerful ๐Ÿ’ช

Let's understand the jargon!!!

Basic SELECT Queries

To retrieve data from a table in MySQL, you use the SELECT statement. Here are some examples:

SELECT * FROM users;

This retrieves all columns from the users table.

SELECT name, email FROM users;

This retrieves only the name and email columns from the users table.

Filtering Results with WHERE Clauses

You can filter results based on specific conditions using the WHERE clause. Examples:

SELECT * FROM users WHERE age > 18;

This retrieves all records from the users table where the age column is greater than 18.

SELECT name, email FROM users WHERE name = 'Alice';

This retrieves records from the users table where the name column is 'Alice'.

Sorting Results with ORDER BY

You can sort the results of your query using the ORDER BY clause. Examples:

SELECT * FROM users ORDER BY name ASC;

This retrieves all records from the users table sorted in ascending order by the name column.

SELECT * FROM users ORDER BY created_at DESC;

This retrieves all records from the users table sorted in descending order by the created_at column.

Grouping Results with GROUP BY

The GROUP BY clause is used to group rows that have the same values into summary rows. Example:

SELECT department, COUNT(*) as employee_count FROM employees GROUP BY department;

This retrieves the count of employees in each department from the employees table, grouped by the department column.

Conclusion

Now I bet you are well equipped to be a SQL Developer on LinkedIn๐Ÿ˜‚.

But jokes aside, this article covers major beginner level jargon. Joins, for those who know, would be covered in a upcoming article coz that deserves one ๐Ÿ˜‚. For those who don't know what is that just stay tuned and subscribe to my newsletter to get updated on that too.

That's all folks. Leave a like and some lovely critics in the comments๐Ÿ˜.

Signing off!!!๐Ÿ‘‹

ย