DEV Community

Cover image for 🚀 Day 21 of My Data Analytics Journey !
Ramya .C
Ramya .C

Posted on

🚀 Day 21 of My Data Analytics Journey !

Today, I focused on SQL concepts and explored important commands in MySQL Workbench.

📌 What I Learned

  1. Table Operations CREATE TABLE IF NOT EXISTS → Creates a table only if it doesn’t exist. DROP TABLE IF EXISTS → Removes a table safely (avoids error if it doesn’t exist). SHOW TABLES; → Lists all tables in the database. DESC table_name; → Displays table schema (structure).
  2. Difference Between Commands TRUNCATE → Removes all rows but keeps the table structure. DROP → Deletes the entire table structure. DELETE → Removes specific rows using a condition (WHERE).
  3. Querying Data SELECT * FROM employees; → Fetch all rows. SELECT first_name, last_name FROM employees; → Fetch specific columns. SELECT * FROM employees WHERE emp_id=5; → Filter rows with condition. SELECT * FROM employees WHERE salary > 50000 AND salary < 60000; → Using logical operators.

  4. Using LIKE for Pattern Matching
    LIKE 'John' → Exact match.
    LIKE 'Jo%' → Names starting with "Jo".
    LIKE '_o%' → Second character "o".
    LIKE 'Raj%Gupta' → Matches with prefix and suffix.
    Uploading image

  5. Date Functions
    DAY(hire_date), MONTH(hire_date), YEAR(hire_date) → Extract date parts.
    DAYNAME(hire_date) → Returns weekday.
    FORMAT(hire_date, 'dd') → Custom date format.
    📚 Concepts I Revised
    🔹 DBMS
    Database Management System – A software to store and manage data (example: MS Access).

🔹 RDBMS
Relational DBMS – Stores data in tables with relationships (example: MySQL, Oracle, SQL Server).

🔹 ORDBMS
Object-Relational DBMS – Supports both relational tables and object-oriented features like inheritance (example: PostgreSQL).

🔹 SQL
Structured Query Language – Standard language for managing and querying databases.

🔹 MySQL
An open-source RDBMS widely used in applications (websites, ERP, etc.). Uses SQL syntax.

🔹 PostgreSQL (psql)
An advanced ORDBMS, open-source, supports complex queries, JSON, advanced indexing.

🔗 How They Connect
SQL → The language.
MySQL → A software (RDBMS) that uses SQL.
PostgreSQL (psql) → A more advanced ORDBMS that also uses SQL (with extra features).
DBMS, RDBMS, ORDBMS → Categories of database systems where SQL is used.
👉 In simple words:

SQL = Language
MySQL = Tool (RDBMS)
PostgreSQL = Advanced Tool (ORDBMS)

Top comments (0)