Day 7: Advance SQL for Data Science | By Sunita Rawat |: January, 2023 -

So far this is the 7th blog in the journey of advancing SQL basics. You can refer to the previous blogs to learn 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 for an SQL statement. There can be multiple tables, that is, the maximum number of table sources that can be joined in a single query 256:.

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

Why do we need views?

Views are used for security purposes because they summarize the name of the table. Data resides in a virtual table, not stored forever. Views display only selected data, whatever it is and how your data should be.

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

Syntax for creating a view.

CREATE VIEW view_name: AS:
CHOOSE column 1, column 2… WHY? table_name: WHERE? state;

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 just like a table and shows the data every time you run a select query on the view, so you don’t have to write that complex query over and over again.

Syntax to update the view:

CREATE OR REPLACE VIEW view_name: AS:
CHOOSE column 1, column 2… WHY? table_name: WHERE? state;

Syntax to skip view.

KEEP WATCHING view_name:;

This view offers several advantages.

Views can hide complexity.
Views can be used as a security mechanism.
Views can simplify the supporting legacy code.

Built-in functionsThere are many built-in functions that we can 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 functionSQL allows users to create custom functions according to their exact requirements.

There are three types of user-defined functions:

Scalar functions, ie return a single value.
Table-valued functions, ie, return a collection of tables.
Multi-table value functions ie return a set of tables.

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

Here I have created a function to calculate age using date of birth in MYSQL.

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

Inquiry :::: SELECT Calculate_Age(‘1988–02–29’) as DOB;

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

Stored ProceduresA stored procedure is prepared SQL code that you can save so that the code can be used again and again. So if you have a SQL query that you write over and over again, save it as a stored procedure and then just call it to run it.

Stored Procedure Syntax:

CREATE ORDER procedure_name:
AS:
sql_statement:
GO;

Execute a Stored Procedure

EXEC: procedure_name:;

Why do we use a stored procedure instead of a function?

A stored procedure can return zero, one, and multiple values.
A stored procedure can also return a database of tables as required using multiple SQL queries.
We can call a function from a stored procedure.
A stored procedure can have input/output values and parameters.
We cannot use select/where/having statement with SPs.
We can use insert/update/delete and select with stored procedure.
An 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 a call to execute a stored procedure.

In SQL SERVER we use exec to execute stored procedure.

Popular Interview Questions for Views/Functions/Stored Procedure.

Question 1: What are your views?

Question 2: How many tables can we use in views?

Question 3. Difference between function and stored procedure.

Thanks for reading it and don’t forget to clap if you liked it.

Nice day!

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

GitHub. Day 7 session

Please give it a star on Git Hub.

Reference.

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

I hope you found it useful! Thanks for reading.

follow me For more data science related posts.

Let’s connect LinkedIn:!

Source link