Featured resource
2025 Tech Upskilling Playbook
Tech Upskilling Playbook

Build future-ready tech teams and hit key business milestones with seven proven plays from industry leaders.

Check it out
  • Lab
    • Libraries: If you want this lab, consider one of these libraries.
    • Data
Labs

SQL Fundamentals: SQL Functions and Procedures

In this lab, you'll learn how to create and execute SQL stored procedures and perform various data manipulation tasks. You'll practice building stored procedures for inserting and updating records, calling them with dynamic parameters, and using SQL functions such as CONCAT() and TIMESTAMPDIFF() to process and transform data. By the end of this lab, you'll be able to implement stored procedures to automate repetitive operations and manage data efficiently in a MySQL environment.

Lab platform
Lab Info
Level
Intermediate
Last updated
Sep 18, 2025
Duration
1h 0m

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.
Table of Contents
  1. Challenge

    SQL Built-in Functions

    SQL Guide

    For each task, replace or update the existing SQL code in the top pane (main.sql) with your answer to the task and run it to see the results in the bottom pane (SQL Viewer).

    Step 1: Using SQL Built-in Functions

    In this step, you will explore how to use SQL's built-in functions to manipulate and transform data efficiently. SQL functions such as CONCAT(), EXTRACT(), and UPPER() are essential for string manipulation, extracting parts of dates, and modifying text case. The tasks in this step will guide you through writing SQL queries that leverage these functions to concatenate names, extract specific date components, and convert text to uppercase. By the end of this step, you will have a solid understanding of how SQL functions enhance data queries and transformation.

    đź«™ Database Structure

    Tables You Will Be Working With:

    Customers:

    | Column Name | Data Type | |-------------|--------------------------------| | CustomerID | INT PRIMARY KEY AUTO_INCREMENT | | FirstName | VARCHAR(50) | | LastName | VARCHAR(50) | | DateOfBirth | DATE |


    Orders:

    | Column Name | Data Type | |-------------|--------------------------------| | OrderID | INT PRIMARY KEY AUTO_INCREMENT | | CustomerID | INT | | OrderDate | DATE |


    Products:

    | Column Name | Data Type | |--------------|--------------------------------| | ProductID | INT PRIMARY KEY AUTO_INCREMENT | | ProductName | VARCHAR(100) | | Price | DECIMAL(10, 2) |

  2. Challenge

    Creating User-defined Functions

    Step 2: Creating and Using User-Defined Functions

    In this step, you will learn how to create user-defined functions (UDFs) in SQL. UDFs allow you to encapsulate reusable logic within a database, enhancing the flexibility of SQL queries. You will create functions to calculate the age of a customer based on their date of birth and generate an employee's full name by concatenating their first and last names. Additionally, you will apply these functions within SELECT statements to dynamically generate results. By the end of this step, you will be comfortable with creating and using UDFs to improve query efficiency and readability.

    đź«™ Database Structure

    Tables You Will Be Working With:

    Customers:

    | Column Name | Data Type | |-------------|--------------------------------| | CustomerID | INT PRIMARY KEY AUTO_INCREMENT | | FirstName | VARCHAR(50) | | LastName | VARCHAR(50) | | DateOfBirth | DATE |


    Employees:

    | Column Name | Data Type | |--------------|--------------------------------| | EmployeeID | INT PRIMARY KEY AUTO_INCREMENT | | FirstName | VARCHAR(50) | | LastName | VARCHAR(50) | | HireDate | DATE |

  3. Challenge

    Stored Procedures

    Step 3: Creating and Executing Stored Procedures

    In this step, you will delve into creating and executing stored procedures in SQL. Stored procedures are powerful tools for encapsulating complex SQL logic that can be reused and executed with different input parameters. You will create stored procedures to insert new customers into the Customers table and update product prices in the Products table. Additionally, you will learn how to execute these procedures to insert a new customer and modify the price of a product dynamically. This step will solidify your understanding of stored procedures and their role in streamlining database operations.

    đź«™ Database Structure

    Tables You Will Be Working With:

    Customers:

    | Column Name | Data Type | |-------------|--------------------------------| | CustomerID | INT PRIMARY KEY AUTO_INCREMENT | | FirstName | VARCHAR(50) | | LastName | VARCHAR(50) | | DateOfBirth | DATE |


    Products:

    | Column Name | Data Type | |--------------|--------------------------------| | ProductID | INT PRIMARY KEY AUTO_INCREMENT | | ProductName | VARCHAR(100) | | Price | DECIMAL(10, 2) |

About the author

Real skill practice before real-world application

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Learn by doing

Engage hands-on with the tools and technologies you’re learning. You pick the skill, we provide the credentials and environment.

Follow your guide

All labs have detailed instructions and objectives, guiding you through the learning process and ensuring you understand every step.

Turn time into mastery

On average, you retain 75% more of your learning if you take time to practice. Hands-on labs set you up for success to make those skills stick.

Get started with Pluralsight