SQL (Structured Query Language) manages data in relational database management systems (RDBMS). It supports inserting, querying, updating, deleting data, creating and modifying schemas, and controlling access. A relational DBMS stores data in tables composed of rows and columns. Each table represent...
Database Fundamentals Why Learn Databases? Previously, to store data permanently, we used I/O streams to write data to local files. However, tasks like changing a specific user's age in a text file were cumbersome, requiring reading lines, creating objects, modifying them, and writing back. Database...
Understanding Relational Databases with MySQL A relational database organizes data into structured tables composed of rows and columns. MySQL is a widely used open-source relational database management system (RDBMS) that implements this model. Core Concepts Database: A container holding related tab...
PyMySQL is a library for connecting to MySQL servers from Python 3.x, replacing MySQLdb used in Python 2. Install it using pip: pip install pymysql Core Methods in PyMySQL pymysql.connect() parameters: host (str): MySQL server address port (int): MySQL server port user (str): database usernmae passw...
Core Concepts of Structured Query Language Structured Query Language (SQL) serves as the universal interface for interacting with relational databases. Its capabilities extend beyond simple queries to encompass schema definition, data manipulation, security enforcement, and integrity control. This m...
Sample Tables Table X CREATE TABLE X ( user_id INT, username VARCHAR(20), profile VARCHAR(20) ); INSERT INTO X VALUES(1, 'Alice', 'Dev'); INSERT INTO X VALUES(2, 'Bob', 'QA'); Table Y CREATE TABLE Y ( user_id INT, username VARCHAR(20), score INT ); INSERT INTO Y VALUES(1, 'Alice', 85); INSERT INTO Y...
Problem Statement When performing time-based data analysis, there's often a need to align timestamps to predefineed interval boundaries. For instance, given a timestamp like 2024-03-26 14:25:59, you might want to group it into 30-minute buckets, resulting in 2024-03-26 14:00:00. This operation is co...
Creating a full‑text search system inside a database involves setting up a lightweight inverted index using metadata tables, word lookup tables, and row mapping structures. The core idea is to tokenize text, map tokens to indexed rows, and query the result through prebuilt relationships without rely...
Table Definition Syntax CREATE TABLE table_name ( column1 data_type constraint, column2 data_type constraint ); Column Naming Convensions Avoid using numeric-only identifiers. Special characters are prohibited. Recommended practice: Use descriptive, snake_case names like employee_id or department_na...
Data Definition Language (DDL) serves as the foundation for managing the structural framework of a relational database. These SQL commands are responsible for defining, modifying, and removing schema objects such as databases, tables, indexes, and views. The CREATE Command The CREATE keyword facilit...