DEV Community

Vigneshwaralingam
Vigneshwaralingam

Posted on

DAY 1 OF MY SQL LEARNING JOURNEY

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
Enter fullscreen mode Exit fullscreen mode

2. Install PostgreSQL 

sudo apt install postgresql postgresql-contrib -y
Enter fullscreen mode Exit fullscreen mode

3. Switch to PostgreSQL User 

sudo -i -u postgres
Enter fullscreen mode Exit fullscreen mode

4. Enter PostgreSQL Shell 

psql
Enter fullscreen mode Exit fullscreen mode

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

6. Creating a Database & Tables

Create a Database 

CREATE DATABASE my_database;
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

7. Basic SQL Queries

How to retrieve all columns from the Actors table? 

SELECT * FROM Actors;
Enter fullscreen mode Exit fullscreen mode

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

SELECT name, birth_year FROM Actors;
Enter fullscreen mode Exit fullscreen mode

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

SELECT title, release_year FROM Movies;
Enter fullscreen mode Exit fullscreen mode

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

SELECT name FROM Directors WHERE debut_year > 2010;
Enter fullscreen mode Exit fullscreen mode

How do you get a list of all male actors? 

SELECT name FROM Actors WHERE gender = 'Male';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  • OR Clause: Returns records where at least one condition is true. 
SELECT * FROM Movies WHERE genre = 'Action' OR genre = 'Thriller';
Enter fullscreen mode Exit fullscreen mode
  • AND Clause: Returns records where all conditions are true. 
SELECT * FROM Movies WHERE release_year > 2010 AND box_office > 200;
Enter fullscreen mode Exit fullscreen mode
  • BETWEEN Clause: Retrieves values within a specific range. 
SELECT * FROM Movies WHERE release_year BETWEEN 2000 AND 2020;
Enter fullscreen mode Exit fullscreen mode

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. 🚀

Top comments (0)