MySQL

Databases with MYSQL


Progress
Submitted: 0%
Reviewed: 0%
8 Tasks


MySQL is one of the most widely used open-source relational database management systems. As a software engineer, it is essential to have a solid understanding of SQL commands and MySQL server monitoring to be able to design, develop, and maintain efficient and robust databases.
In this course, you will learn the fundamentals of SQL commands, including data retrieval, modification, and management. You will also gain practical experience in creating and optimizing MySQL databases. You will get a comprehensive understanding of how to apply this knowledge to build efficient and reliable databases for your web applications.



  • Day 1
    • Introduction to MySQL And Installation

    Concept:



    Resources:



    Assignments:


    Your Tasks

    • Task 1: Install MySQL + Workbench
      Follow these simple steps:
      • Go to https://dev.mysql.com/downloads/
      • Download MySQL Installer (Community Edition)
      • Choose installation type: Developer Default
      • Set username: root
      • Create a password (remember it!)
      • Finish setup → MySQL Workbench is installed 🎉
    • Task 2: Open MySQL Workbench
      Launch MySQL Workbench and log in using:
      Username: root
      Password: your_password


    In Progress Need help Completed
    Write Code! View Solutions

  • Day 3
    • CREATE Statement and Data Types

    Concept:



    Resources:



    Assignments:


    Your Tasks

    • Task 1: Create the Database
      Using the CREATE DATABASE statement, create a database named CompanyDB.

      Example:
      CREATE DATABASE CompanyDB;
    • Task 2: Select the Database
      After creating it, switch to the database using:
      USE CompanyDB;
    • Task 3: Create the Table
      Inside CompanyDB, create a new table named Products with the following columns:

      • product_id – INT, AUTO_INCREMENT, PRIMARY KEY
      • product_name – VARCHAR
      • price – DECIMAL
      • in_stock – BOOLEAN
      • added_date – DATE
      Example starter structure to edit:
      CREATE TABLE Products (
        product_id INT AUTO_INCREMENT PRIMARY KEY,
        product_name VARCHAR(100),
        price DECIMAL(10,2),
        in_stock BOOLEAN,
        added_date DATE
      );

    💡 Tip:
    You must not write the full code from scratch — use the starter code that appears in the editor and edit inside it.

    For Submission

    Go to “Submit via Editor” → select MySQL from the dropdown.
    A starter code will appear automatically — edit inside that code to complete the assignment.
    Your final output should successfully create the CompanyDB database and Products table.


    In Progress Need help Completed
    Write Code! View Solutions

  • Day 4
    • SELECT and WHERE

    Concept:



    Resources:



    Assignments:


    Your Tasks

    • Task 1: Create the Database
      Create a database named StoreDB.

      Example:
      CREATE DATABASE StoreDB;
    • Task 2: Select the Database
      Switch to the database using:
      USE StoreDB;
    • Task 3: Create the Orders Table
      Create a table named Orders with the following columns:

      • order_id – INT, AUTO_INCREMENT, PRIMARY KEY
      • customer_name – VARCHAR(100)
      • amount – DECIMAL(10,2)
      • order_date – DATE
      • is_delivered – BOOLEAN
      Example table structure:
      CREATE TABLE Orders (
        order_id INT AUTO_INCREMENT PRIMARY KEY,
        customer_name VARCHAR(100),
        amount DECIMAL(10,2),
        order_date DATE,
        is_delivered BOOLEAN
      );
    • Task 4: Insert Sample Records
      Insert at least two rows:
      INSERT INTO Orders (customer_name, amount, order_date, is_delivered) VALUES
      ('Rahul Sharma', 650.00, '2025-01-10', TRUE),
      ('Sneha Patel', 400.00, '2025-01-12', FALSE);
    • Task 5: Write SELECT Queries
      Write queries to show:
      • Only delivered orders
      • Only orders with amount > 500

      Example:
      SELECT * FROM Orders WHERE is_delivered = TRUE;

      SELECT * FROM Orders WHERE amount > 500;

    💡 Tip:
    You must use the starter code shown in the editor. Do not delete it—edit inside it to complete the assignment.

    For Submission

    Go to “Submit via Editor” → select MySQL.
    A starter code will automatically appear — edit inside that code to complete all steps.
    Your output must correctly fetch filtered data using the SELECT and WHERE clauses.


    In Progress Need help Completed
    Write Code! View Solutions

  • Day 5
    • Insert, Update and Delete Statements

    Concept:



    Resources:



    Assignments:


    Your Tasks

    • Task 1: Create the Database
      Create a database named LibraryDB.

      Example:
      CREATE DATABASE LibraryDB;
    • Task 2: Select the Database
      Switch to the database:
      USE LibraryDB;
    • Task 3: Create the Books Table
      Create a table named Books with the following columns:

      • book_id – INT, AUTO_INCREMENT, PRIMARY KEY
      • title – VARCHAR(100)
      • author – VARCHAR(100)
      • price – DECIMAL(10,2)
      • is_available – BOOLEAN
      Example:
      CREATE TABLE Books (
        book_id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(100),
        author VARCHAR(100),
        price DECIMAL(10,2),
        is_available BOOLEAN
      );
    • Task 4: Insert 3 Books
      Add three sample books:
      INSERT INTO Books (title, author, price, is_available) VALUES
      ('Atomic Habits', 'James Clear', 450.00, TRUE),
      ('The Alchemist', 'Paulo Coelho', 300.00, TRUE),
      ('Deep Work', 'Cal Newport', 550.00, FALSE);
    • Task 5: Update a Book Price
      Update the price of any one book:
      UPDATE Books SET price = 500.00 WHERE book_id = 2;
    • Task 6: Delete a Book
      Delete a book where is_available = FALSE:
      DELETE FROM Books WHERE is_available = FALSE;

    💡 Tip:
    Edit only inside the starter code that appears in the editor. Do not remove the initial structure.

    For Submission

    Go to “Submit via Editor” → select MySQL.
    A starter file will appear — modify that code to complete the assignment.
    Your final output should correctly perform INSERT, UPDATE, and DELETE operations.


    In Progress Need help Completed
    Write Code! View Solutions

  • Day 6
    • Drop and Truncate

    Concept:



    Resources:



    Assignments:


    Your Tasks

    • Task 1: Create the Database
      Create a database named TrainingDB.

      Example:
      CREATE DATABASE TrainingDB;
    • Task 2: Select the Database
      Switch to the database:
      USE TrainingDB;
    • Task 3: Create Users Table
      Create a table named Users with these columns:

      • user_id – INT, AUTO_INCREMENT, PRIMARY KEY
      • full_name – VARCHAR(100)
      • email – VARCHAR(100)
      • is_active – BOOLEAN
      Example:
      CREATE TABLE Users (
        user_id INT AUTO_INCREMENT PRIMARY KEY,
        full_name VARCHAR(100),
        email VARCHAR(100),
        is_active BOOLEAN
      );
    • Task 4: Insert Sample Users
      Add two sample users:
      INSERT INTO Users (full_name, email, is_active) VALUES
      ('Arjun Singh', 'arjun@example.com', TRUE),
      ('Meera Kapoor', 'meera@example.com', FALSE);
    • Task 5: TRUNCATE the Table
      Remove all rows from the table:
      TRUNCATE TABLE Users;
    • Task 6: DROP the Table
      Delete the entire table:
      DROP TABLE Users;

    💡 Tip:
    TRUNCATE removes data but keeps the table.
    DROP removes the table completely — use both as required in the assignment.

    For Submission

    Go to “Submit via Editor” → select MySQL.
    The starter code will appear — modify it to complete all tasks.
    Your final output must correctly use TRUNCATE and DROP.


    In Progress Need help Completed
    Write Code! View Solutions

  • Day 7
    • Group by, Order by, Having

    Concept:



    Resources:



    Assignments:


    Your Tasks

    • Task 1: Create the Database
      Create a database named SalesDB.

      Example:
      CREATE DATABASE SalesDB;
    • Task 2: Select the Database
      Switch to the database:
      USE SalesDB;
    • Task 3: Create the Sales Table
      Create a table named Sales with these columns:

      • sale_id – INT, AUTO_INCREMENT, PRIMARY KEY
      • product_name – VARCHAR(100)
      • quantity – INT
      • price – DECIMAL(10,2)
      • sale_date – DATE
      Example:
      CREATE TABLE Sales (
        sale_id INT AUTO_INCREMENT PRIMARY KEY,
        product_name VARCHAR(100),
        quantity INT,
        price DECIMAL(10,2),
        sale_date DATE
      );
    • Task 4: Insert Sample Data
      Insert 4 rows of sample sales:
      INSERT INTO Sales (product_name, quantity, price, sale_date) VALUES
      ('Laptop', 3, 50000.00, '2024-01-05'),
      ('Mouse', 10, 500.00, '2024-01-10'),
      ('Keyboard', 4, 1200.00, '2024-01-15'),
      ('Mouse', 6, 500.00, '2024-01-20');
    • Task 5: Group Data
      Group by product and calculate total quantity:
      SELECT product_name, SUM(quantity) AS total_quantity
      FROM Sales
      GROUP BY product_name;
    • Task 6: Apply HAVING
      Show only products where total quantity > 5:
      HAVING total_quantity > 5;
    • Task 7: Apply ORDER BY
      Sort results in descending order of quantity:
      ORDER BY total_quantity DESC;

    💡 Tip:
    Edit only inside the starter code that appears in the editor. Do not remove the initial structure.

    For Submission

    Go to “Submit via Editor” → select MySQL.
    A starter file will appear — modify that code to complete the assignment.
    Your final output should correctly apply GROUP BY, HAVING, and ORDER BY.


    In Progress Need help Completed
    Write Code! View Solutions

  • Day 8
    • Joins - Cross, Inner, Outer

    Concept:



    Resources:



    Assignments:


    Your Tasks

    • Task 1: Create the Database
      Create a database named SchoolDB.

      CREATE DATABASE SchoolDB;
    • Task 2: Select the Database
      Switch to the database:
      USE SchoolDB;
    • Task 3: Create Two Tables
      Table 1: Students
      CREATE TABLE Students (
        student_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        class VARCHAR(20)
      );

      Table 2: Marks
      CREATE TABLE Marks (
        mark_id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT,
        subject VARCHAR(50),
        marks INT
      );
    • Task 4: Insert Students
      INSERT INTO Students (name, class) VALUES
      ('Rohan', '10A'),
      ('Sneha', '10B'),
      ('Arjun', '10A');
    • Task 5: Insert Marks
      INSERT INTO Marks (student_id, subject, marks) VALUES
      (1, 'Math', 85),
      (2, 'Science', 90),
      (1, 'English', 78);
    • Task 6: Perform JOIN Operations
      1️⃣ CROSS JOIN
      SELECT * FROM Students CROSS JOIN Marks;

      2️⃣ INNER JOIN (only students who have marks)
      SELECT Students.name, Marks.subject, Marks.marks
      FROM Students
      INNER JOIN Marks ON Students.student_id = Marks.student_id;

      3️⃣ LEFT JOIN (all students + marks if available)
      SELECT Students.name, Marks.subject, Marks.marks
      FROM Students
      LEFT JOIN Marks ON Students.student_id = Marks.student_id;

    💡 Tip:
    Edit only inside the starter code that appears in the editor. Do not remove the initial structure.

    For Submission

    Go to “Submit via Editor” → select MySQL.
    A starter file will appear — modify that code to complete the assignment.
    Your final output should include CROSS JOIN, INNER JOIN, and LEFT JOIN queries.


    In Progress Need help Completed
    Write Code! View Solutions

  • Day 9
    • Indexing

    Concept:



    Resources:



    Assignments:


    Your Tasks

    • Task 1: Create the Database
      Create a database named IndexDB.

      CREATE DATABASE IndexDB;
    • Task 2: Select the Database
      USE IndexDB;
    • Task 3: Create the Products Table
      CREATE TABLE Products (
        product_id INT PRIMARY KEY,
        product_name VARCHAR(100),
        category VARCHAR(50),
        price DECIMAL(10,2)
      );
    • Task 4: Insert 3 Sample Products
      INSERT INTO Products (product_id, product_name, category, price) VALUES
      (1, 'Laptop', 'Electronics', 55000.00),
      (2, 'Chair', 'Furniture', 3000.00),
      (3, 'Mobile', 'Electronics', 22000.00);
    • Task 5: Create an Index on product_name
      CREATE INDEX idx_product_name ON Products(product_name);
    • Task 6: Create a UNIQUE Index on category
      (This will prevent duplicate categories)
      CREATE UNIQUE INDEX idx_unique_category ON Products(category);
    • Task 7: Drop One Index
      Example: Drop the index on product_name
      DROP INDEX idx_product_name ON Products;

    💡 Tip:
    UNIQUE indexes ensure no duplicate values are entered in that column.

    For Submission

    Go to “Submit via Editor” → select MySQL.
    Edit the starter code to complete the assignment.
    Your final output must include: creating indexes, unique index, and dropping an index.


    In Progress Need help Completed
    Write Code! View Solutions

  • Day 10
    • Transactions and ACID properties

    Concept:



    Resources:



    Assignments:


    Your Tasks

    • Task 1: Create the Database
      Create a database named BankDB.

      CREATE DATABASE BankDB;
    • Task 2: Select the Database
      USE BankDB;
    • Task 3: Create the Accounts Table
      CREATE TABLE Accounts (
        acc_id INT PRIMARY KEY,
        holder_name VARCHAR(100),
        balance DECIMAL(10,2)
      );
    • Task 4: Insert 2 Sample Accounts
      INSERT INTO Accounts (acc_id, holder_name, balance) VALUES
      (1, 'Rohit Sharma', 5000.00),
      (2, 'Virat Kohli', 3000.00);
    • Task 5: Start a Transaction & Transfer Money
      Transfer ₹1000 from Account 1 to Account 2.

      START TRANSACTION;
      UPDATE Accounts SET balance = balance - 1000 WHERE acc_id = 1;
      UPDATE Accounts SET balance = balance + 1000 WHERE acc_id = 2;
      COMMIT;
    • Task 6: Practice ROLLBACK
      Perform another transaction but cancel the changes:

      START TRANSACTION;
      UPDATE Accounts SET balance = balance + 500 WHERE acc_id = 1;
      UPDATE Accounts SET balance = balance - 500 WHERE acc_id = 2;
      ROLLBACK;

    💡 ACID Reminder:
    Atomicity – All steps succeed or none.
    Consistency – Keeps data valid before & after transaction.
    Isolation – Transactions do not affect each other.
    Durability – Once committed, data is permanently saved.

    For Submission

    Go to “Submit via Editor” → select MySQL.
    Modify the starter code to complete the assignment.
    Your output must show correct use of START TRANSACTION, COMMIT, and ROLLBACK.


    In Progress Need help Completed
    Write Code! View Solutions

  • Day 11
    • Cache in MySQL

    Concept:



    Resources:



    Assignments:


    Your Tasks

    • Task 1: Observe Caching
      Understand how MySQL uses caching (buffer pool) to speed up queries.
      Students will run SELECT queries on the same table multiple times to see the effect of cached data.
    • Task 2: Starter Code
      A starter code will appear in the editor.
      You must edit inside the starter code only.
      Do NOT delete starter code lines.
    • Task 3: Sample Steps to Try
      • Create a database and a sample table.
      • Insert some rows.
      • Run a SELECT query multiple times and note the time difference (first run reads from disk, subsequent runs may hit cache).

    💡 Tip:
    Buffer pool caches data pages and indexes in memory to reduce disk access. Observing query timing can demonstrate caching behavior.

    For Submission

    Go to “Submit via Editor” → select MySQL.
    Edit the starter code to complete the assignment.
    Your submission should demonstrate understanding of caching by showing queries accessing cached data.


    In Progress Need help Completed
    Write Code! View Solutions

×

Let's Go!

Congratulations on getting started. Here is a little reward for you...

×

10

Going to the next task in