Gain insights into SQL by exploring database creation, data manipulation, and advanced queries. Learn about practical applications and practice for SQL interviews to build confidence and readiness.

sql.tar.gz

The ability to work SQL is becoming an increasingly in-demand skill, both for software developers and people in less technical roles. If you’re interested in learning SQL and have no prior experience with it, then this course will be your light in a dark tunnel.

You’ll start by covering the basics of SQL such as how to create a database, how to insert, query, and update data. You’ll also learn fundamental concepts that developers and data scientists use everyday such as multi-table operations, nested queries, and how to set up views. 

Throughout, you’ll get to execute SQL queries in your browser and see results in real-time - you won’t need to worry about set-up.

At the end of this course, you’ll also get some hands-on practice with common SQL interview questions, so when the time comes, you’ll be ready and confident to answer any question that comes your way. Let’s get started!

An Introductory Guide to SQL

# Stored Functions

A stored function is a kind of a stored program that can only return one value. It can be used in SQL statements in place of an expression. Common formulas or expressions that are used over and over again can be written in stored functions to make the code reusable. However, if a stored function that queries data from tables is used in a SQL statement, then it may slow down the speed of the query.



There are a number of differences when we compare stored functions to stored procedures. Stored procedures can call stored functions but the opposite is not possible. Stored functions can be used in SQL statements but stored procedures can only be called with the __CALL__ keyword. That is why stored procedures are stored in compiled form where as stored functions are parsed and compiled at runtime. Return value is optional in stored procedures but a must in stored functions. Moreover, stored functions can only return one value but there is no such restriction on the number of return values in stored procedures. Stored functions only support __IN__ parameter type while stored procedures can have __IN__, __OUT__ and __INOUT__ parameters. Error handling is not possible in stored functions.


The __CREATE FUNCTION__ statement is used to create a stored function. The parameter list contains all the parameters of the function. Unlike stored procedures where the parameters could be __IN__, __OUT__ or __INOUT__ type, a stored function only takes __IN__ parameters so there is no need to specify the type of parameters in the parameter list. Since the stored function can return only one value, the data type of the return value is specified after the __RETURN__ keyword.


A stored function can be deterministic or non deterministic meaning that for the same input parameters, the result will either be the same or different. This can be specified by using the keywords __DETERMINISTIC__ or __NOT DETERMINISTIC__. If this keyword is not specified, the type is set to __NOT DETERMINISTIC__ by default. The function body must have at least one __RETURN__ statement. When control reaches it, the stored function exits. 


To view all functions in a database, __SHOW FUNCTION STATUS__ statement is used. This results returned can be narrowed down based on the __LIKE__ operator or any other condition specified in the optional __WHERE__ clause. To delete a stored function, __DROP FUNCTION__ keywords with the optional __IF EXISTS__ clause is used.

## Syntax

> DELIMITER **
>
> CREATE FUNCTION **function_name**(**parameter_list**)
>
> RETURNS **datatype**
>
> [NOT] DETERMINISTIC
>
> BEGIN
>
> **function body**
>
> END **
>
> DELIMITER ;

> SHOW FUNCTION STATUS 
[LIKE **'pattern'** | WHERE **condition**];

> DROP FUNCTION [IF EXISTS] **function_name**;

>Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy-paste the command **./DataJek/Lessons/59lesson.sh** and wait for the mysql prompt to start-up.

This lesson is about creating, viewing and deleting stored functions. We also discuss the key differences between stored procedures and stored functions.

Introduction

Basic SQL

Joins

Nested Queries

Multi Table Operations

Views

Stored Procedures

Triggers

Miscellaneous

Practice & Prep

Epilogue

Stored Functions

Stored Functions