Day 7: Advance SQL For Data Science | by Sunita Rawat | Jan, 2023

So far this is the 7th blog in the journey of basics to advance SQL. you can refer to previous blogs for learning SQL from scratch, This blog contains good knowledge about views, functions, and stored procedures.

A view is actually a virtual table-based approach as a result set for SQL statement. There can be multiple tables i.e. the maximum number of table sources that can be joined in a single query is 256.

A view contains rows and columns just like a table in the database, the fields in a view are fields from one or more tables.

Why do we need views??

Views are used for security purposes because they encapsulate the table’s name. Data is in the virtual table, not stored permanently. Views display only selected data whatever and however, you need your data to be.

We can use Joins/function/SQL clause in views.

Syntax to Create a view:

CREATE VIEW view_name AS
SELECT column1, column2, … FROM table_name WHERE condition;

Create View Salesman_info
As
select s.salesmanid,s.city, count(customerid) no_of_sales from salesman s 
left join customer c on c.salesmanid = s.salesmanid
group by 1;

This works exactly the same as the table and shows the data every time you run a select query on the view and you don’t have to write that complex query again and again.

Syntax to Update a view:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, … FROM table_name WHERE condition;

Syntax to Dropping a view:

DROP VIEW view_name;

This view provides several benefits.

1. Views can hide complexity.
2. Views can be used as a security mechanism.
3. Views can simplify supporting legacy code.

Built-in Functions: There are many inbuilt functions that we can to access or manipulate our data as required. Some of the functions are String functions, Math/Numeric functions, Date functions, Advance functions, or Aggregate functions.

User-defined Function: SQL allows users to create custom functions according to their exact requirements.

There are three types of user-defined functions:

1. Scalar functions i.e. return a single value.
2. Table value functions i.e. return a table set.
3. Multi-table value functions i.e. return a table set.

Note: we can only use the select query in user-defined functions.

Here I have created a function for calculating age using the date of birth in MYSQL.

DELIMITER $$
CREATE FUNCTION Calculate_Age
(
DOB date
)
RETURNS INT DETERMINISTIC
BEGIN
RETURN YEAR(CURRENT_DATE()) - YEAR(DOB);
END$$
DELIMITER ;

We can use functions using select /where/ having clause.

Stored Procedures: A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

Stored procedure syntax:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a Stored Procedure

EXEC procedure_name;

Why we use stored procedure rather than function ?

1. Stored procedure can return zero, single and multiple values.
2. Stored procedure can also return dataset of tables as required using multiple SQL queries.
3. We can call function in stored procedure.
4. Stored procedure can have input/output values and parameters.
5. We can’t use select/where/having statement with SPs.
6. We can use insert/update/delete and select in stored procedure.
7. SQL stored procedure can execute dynamic SQL.

DELIMITER $$
CREATE PROCEDURE Proc_Saleman_Info_Data()
BEGIN
## Type 1
select s.salesmanid,s.city, count(customerid) no_of_sales from salesman s 
left join customer c on c.salesmanid = s.salesmanid
group by 1;
## Type 2
insert into salesman
values(5008,'Shikha rawat','India',12);
END$$
DELIMITER ;

Here I have created a stored procedure using select and insert statements.

call Proc_Saleman_Info_Data();

In MYSQL, we use call to execute stored procedure.

In SQL SERVER, we use exec to execute stored procedure.

Popular Interview Questions for Views/Functions/Stored Procedure.

Ques1: What are your views?

Ques2: How many tables we can use in views?

Ques3: Difference between function and stored procedure?

Thank you for reading it and if you like it, do not forget to give a clap!

Have a nice day!

=============================THE END==========================

GitHub: Day 7 Session

Please give it a star on Git Hub!!

Reference :

1. https://www.w3schools.com/sql/default.asp
2. https://www.geeksforgeeks.org/

Hope you found it helpful! Thanks for reading!

Follow me for more Data Science related posts!

Let’s connect on LinkedIn!