Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Question: Stored Functions

Understand how to develop a stored function in MySQL that accepts a customer ID, sums delivered orders, and returns the total amount spent. This lesson guides you through creating reusable SQL logic for analytics and reporting, using parameters, SQL data reads, and ordering results for VIP customers.

We'll cover the following...

Question

Given the following structure of the Customers table:

Field

Type

CustomerID

int

CustomerName

varchar(50)

Email

varchar(50)

Phone

varchar(15)

Address

varchar(100)

LastLogin

date

CreatedAt

timestamp

CustomerTier

enum('New','Regular','VIP')

ReferralID

int

LoyaltyPoints

int

LastPurchaseDate

date

IsChurnRisk

tinyint(1)

Where, CustomerID is the primary key and ReferralID is self-referencing to CustomerID. The table contains information about customers. The table includes a Boolean column IsChurnRisk, which indicates whether a customer is considered at risk of churning (TRUE) or not (FALSE).

Also, given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where, OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the ...