Machine Learning News Hubb
Advertisement Banner
  • Home
  • Machine Learning
  • Artificial Intelligence
  • Big Data
  • Deep Learning
  • Edge AI
  • Neural Network
  • Contact Us
  • Home
  • Machine Learning
  • Artificial Intelligence
  • Big Data
  • Deep Learning
  • Edge AI
  • Neural Network
  • Contact Us
Machine Learning News Hubb
No Result
View All Result
Home Artificial Intelligence

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

admin by admin
February 4, 2023
in Artificial Intelligence


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 ;
Query :::: SELECT Calculate_Age(‘1988–02–29’) as DOB;

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!



Source link

Previous Post

7 Best HR Communities You Must Join In 2023

Next Post

Analyze and visualize multi-camera events using Amazon SageMaker Studio Lab

Next Post

Analyze and visualize multi-camera events using Amazon SageMaker Studio Lab

How to Spot Fake Bank Statements?

Тестування AI AMD на реальних дзвінках | by Не лише телефонія ☎️ | Feb, 2023

Related Post

Artificial Intelligence

10 Most Common Yet Confusing Machine Learning Model Names | by Angela Shi | Mar, 2023

by admin
March 26, 2023
Machine Learning

How Machine Learning Will Shape The Future of the Hiring Industry | by unnanu | Mar, 2023

by admin
March 26, 2023
Machine Learning

The Pros & Cons of Accounts Payable Outsourcing

by admin
March 26, 2023
Artificial Intelligence

Best practices for viewing and querying Amazon SageMaker service quota usage

by admin
March 26, 2023
Edge AI

March 2023 Edge AI and Vision Innovation Forum Presentation Videos

by admin
March 26, 2023
Artificial Intelligence

Hierarchical text-conditional image generation with CLIP latents

by admin
March 26, 2023

© 2023 Machine Learning News Hubb All rights reserved.

Use of these names, logos, and brands does not imply endorsement unless specified. By using this site, you agree to the Privacy Policy and Terms & Conditions.

Navigate Site

  • Home
  • Machine Learning
  • Artificial Intelligence
  • Big Data
  • Deep Learning
  • Edge AI
  • Neural Network
  • Contact Us

Newsletter Sign Up.

No Result
View All Result
  • Home
  • Machine Learning
  • Artificial Intelligence
  • Big Data
  • Deep Learning
  • Edge AI
  • Neural Network
  • Contact Us

© 2023 JNews - Premium WordPress news & magazine theme by Jegtheme.