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) |