- Lab
-
Libraries: If you want this lab, consider one of these libraries.
- Data

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 Info
Table of Contents
-
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()
, andUPPER()
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) |
-
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 |
-
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 theProducts
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.