- Lab
- 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.
Path 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
SELECTstatements 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
Customerstable and update product prices in theProductstable. 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) |
What's a lab?
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.
Provided environment for hands-on practice
We will provide the credentials and environment necessary for you to practice right within your browser.
Guided walkthrough
Follow along with the author’s guided walkthrough and build something new in your provided environment!
Did you know?
On average, you retain 75% more of your learning if you get time for practice.