SQL Learning Journey - Day 1: Introduction & Basic Queries

Introduction

Hello everyone, welcome to my SQL learning vlog! 🚀 This is Day 1 of my journey, and today, I'll cover the basics of SQL, databases, and some fundamental queries using PostgreSQL. Let's dive in!

1. What is Data, Information, Database, and SQL?

What is Data?

Data is a collection of raw facts, numbers, or symbols without context. It can be anything like a name, number, or date. Without processing, data alone has no real meaning.

What is Information?

When data is processed, organized, and structured to make sense, it becomes information. For example, "2003" is data, but "My birth year is 2003" is information.

What is a Database?

A database is an organized collection of data that allows easy access, management, and updating. Examples include SQL databases like MySQL and PostgreSQL.

What is SQL?

SQL (Structured Query Language) is a language used to interact with relational databases. It allows us to store, retrieve, update, and delete data efficiently.

2. Understanding DBMS, RDBMS, ORDBMS, ODBMS

  • DBMS (Database Management System): A software to create and manage databases (e.g., MS Access).
  • RDBMS (Relational DBMS): Stores data in tables with relationships (e.g., MySQL, PostgreSQL).
  • ORDBMS (Object-Relational DBMS): Combines RDBMS with object-oriented features (e.g., PostgreSQL).
  • ODBMS (Object Database Management System): Stores data as objects, used in complex applications (e.g., MongoDB).

3. MySQL vs PostgreSQL

MySQL

  • Simple, fast, and easy to use.
  • Best for read-heavy operations.
  • Lacks advanced features like full ACID compliance.

PostgreSQL

  • Fully ACID-compliant and supports complex queries.
  • Has JSON support and extensible features.
  • More secure and stable for enterprise applications.

Why Choose PostgreSQL? It is open-source, scalable, supports custom data types, and is best for handling large databases.

4. SQL Origins

SQL was developed in the early 1970s by IBM researchers Donald D. Chamberlin and Raymond F. Boyce. It was initially called SEQUEL (Structured English Query Language) and later became SQL. It became a standard for database management and is widely used today.

5. To **install PostgreSQL on Linux Mint and enter the psql shell, follow these steps:

1. Update Your System

Open the terminal and run:

sudo apt update && sudo apt upgrade -y

2. Install PostgreSQL 

sudo apt install postgresql postgresql-contrib -y

3. Switch to PostgreSQL User 

sudo -i -u postgres

4. Enter PostgreSQL Shell 

psql

Now you’re inside the psql shell! 🎯 To exit, type \q. 🚀

6. Creating a Database & Tables

Create a Database 

CREATE DATABASE my_database;

Creating Tables & Inserting Data

Table: Actors 

CREATE TABLE Actors (
    actor_id INT PRIMARY KEY,
    name VARCHAR(100),
    birth_year INT,
    debut_year INT,
    gender VARCHAR(10)
);

Insert Data:

INSERT INTO Actors VALUES 
(1, 'Rajinikanth', 1950, 1975, 'Male'),
(2, 'Kamal Haasan', 1954, 1960, 'Male'),
(3, 'Vijay', 1974, 1992, 'Male'),
(4, 'Ajith Kumar', 1971, 1992, 'Male'),
(5, 'Nayanthara', 1984, 2003, 'Female');

7. Basic SQL Queries

How to retrieve all columns from the Actors table? 

SELECT * FROM Actors;

How to fetch only the name and birth_year from the Actors table? 

SELECT name, birth_year FROM Actors;

How do you list the titles and release years of all movies? 

SELECT title, release_year FROM Movies;

How do you find all directors who started their career after 2010? 

SELECT name FROM Directors WHERE debut_year > 2010;

How do you get a list of all male actors? 

SELECT name FROM Actors WHERE gender = 'Male';

8. SQL Syntax & Special Characters

  • Why use ' instead of "?

    • 'Value' → Used for string literals.
    • "ColumnName" → Used for column names.

  • Escape Characters in SQL

    • \' → Escape a single quote inside a string.
    • \\ → Escape backslash.

9. Special SQL Queries

Using WHERE, OR, AND, BETWEEN

  • WHERE Clause: Used to filter records. 
SELECT * FROM Actors WHERE birth_year > 1970;
  • OR Clause: Returns records where at least one condition is true. 
SELECT * FROM Movies WHERE genre = 'Action' OR genre = 'Thriller';
  • AND Clause: Returns records where all conditions are true. 
SELECT * FROM Movies WHERE release_year > 2010 AND box_office > 200;
  • BETWEEN Clause: Retrieves values within a specific range. 
SELECT * FROM Movies WHERE release_year BETWEEN 2000 AND 2020;

Using \d and \c in PostgreSQL

  • \d → Show table structure.
  • \c database_name → Connect to a specific database.

That’s it for today! If you found this useful, like, share, and follow my journey as I become an SQL pro! See you tomorrow with new learnings. 🚀