🗄️ Database From Scratch

A comprehensive journey into the world of Databases and SQL using MySQL.

---

Welcome to Database From Scratch! This repository is your go-to resource for understanding the fundamentals of Database Management Systems (DBMS), Relational Databases (RDBMS), and mastering SQL commands using MySQL.

Whether you're taking your first steps into data management or brushing up on standard queries, you'll find everything organized into easy-to-follow Theory and Practical guides.

🌟 What's Inside?

📚 Theory

Dive deep into core concepts and terminology:

• Theory 01: DBMS vs. RDBMS, intro to SQL and MySQL, Database Objects, and Data Definition Language (DDL).
• Theory 02: Deep dive into DML (Data Manipulation), DQL (Data Query), TCL (Transaction Control), and DCL (Data Control Language).
• Theory 03: CREATE TABLE AS SELECT (CTAS), INSERT statements, and introduction to Primary Keys.
• Theory 04: Composite Primary Keys, AUTO_INCREMENT property, and default starting values.
• Theory 05: MySQL Advanced Concepts, INSERT IGNORE, ON DUPLICATE KEY UPDATE, and TRUNCATE command.
• Theory 06: DROP vs TRUNCATE, and comprehensive ALTER TABLE operations (Add, Drop, Modify, Change columns).
• Theory 07: Data Types in MySQL (Numeric, Decimal, String, Enum, Set).
• Theory 08: Date and Time Data Types in MySQL.
• Theory 09: SELECT Command in MySQL.
• Theory 10: Query Filtering and Sorting (IS NULL, IS NOT NULL, ORDER BY, Operator Precedence).
• Theory 11: ORDER BY with DISTINCT, LIMIT, OFFSET, and Introduction to UPDATE.
• Theory 12: UPDATE, CASE Statements & UPDATE with LIMIT.
• Theory 13: DELETE command, TRUNCATE vs DELETE, DELETE vs DROP, and TCL (COMMIT, ROLLBACK).
• Theory 14: SAVEPOINT, RELEASE SAVEPOINT, AUTO COMMIT, and difference between AUTOCOMMIT and COMMIT.
• Theory 15: Constraints (Domain Integrity) - NOT NULL, UNIQUE, and CHECK.
• Theory 16: Naming Constraints, DEFAULT Constraint, and DEFAULT with Datetime.
• Theory 17: SQL Functions, Single Row (Scalar) vs Aggregate Functions, and String Functions.

💻 Practical

Get your hands dirty with real SQL queries and commands:

• Practical 01: MySQL basic commands — SELECT, VERSION(), NOW(), user info, math functions, and showing databases.
• Practical 02: Creating databases and tables, dropping schemas, inserting data, Primary Keys, and cloning tables with CTAS.
• Practical 03: Deep dive into creating Primary Keys during table creation and using ALTER.
• Practical 04: Working with Composite Primary Keys, AUTO_INCREMENT, starting values, and SQL_MODE.
• Practical 05: Using INSERT IGNORE, ON DUPLICATE KEY UPDATE, SHOW CREATE TABLE, and TRUNCATE.
• Practical 06: Dropping tables/columns, renaming, and exploring ALTER TABLE capabilities.
• Practical 07: Working with various Data Types, including Numeric (TINYINT, DECIMAL) and String (TEXT, ENUM, SET).
• Practical 08: Working with Date and Time Data Types.
• Practical 09: Fetching data using the SELECT command.
• Practical 10: Query Filtering and Sorting (IS NULL, IS NOT NULL, ORDER BY).
• Practical 11: ORDER BY with DISTINCT, LIMIT, OFFSET, and UPDATE statements.
• Practical 12: Using UPDATE with CASE Statements and LIMIT.
• Practical 13: Practicing DELETE command and TCL (COMMIT, ROLLBACK).
• Practical 14: Practicing SAVEPOINT, RELEASE SAVEPOINT, and AUTO COMMIT.
• Practical 15: Working with Constraints (NOT NULL, UNIQUE, and CHECK).
• Practical 16: Using Named Constraints, DEFAULT constraint, and tracking time with CURRENT_TIMESTAMP.
• Practical 17: Using String Functions such as UPPER, LOWER, LENGTH, CONCAT, and SUBSTRING.

📝 Assignments

Test your knowledge with hands-on practice problems:

• Assignments & Solutions: Practical assignment questions with their corresponding answers and solution screenshots.

🚀 Getting Started

1. Install MySQL: Ensure you have MySQL installed on your system.
2. Connect to MySQL:

   mysql -u root -p

3. Run the Commands: Pick a practical file and start practicing the commands directly in your MySQL monitor.

🧠 Key Takeaways

• DDL: CREATE, ALTER, DROP (Structure matters!)
• DML: INSERT, UPDATE, DELETE (Manipulate your data.)
• DQL: SELECT (Query your data efficiently.)
• TCL: COMMIT, ROLLBACK (Control your transactions.)

🛠️ Tools Used

• MySQL Command Line Client
• VS Code or any text editor

---

Built with 💙 for data enthusiasts everywhere.