To date that is the seventh weblog within the journey of fundamentals to advance SQL. you’ll be able to consult with earlier blogs for studying SQL from scratch, This weblog incorporates good data about views, capabilities, and saved procedures.
A view is definitely a digital table-based strategy consequently set for SQL assertion. There could be a number of tables i.e. the utmost variety of desk sources that may be joined in a single question is 256.
A view incorporates rows and columns identical to a desk within the database, the fields in a view are fields from a number of tables.
Why do we want views??
Views are used for safety functions as a result of they encapsulate the desk’s identify. Knowledge is within the digital desk, not saved completely. Views show solely chosen information no matter and nevertheless, you want your information to be.
We are able to use Joins/operate/SQL clause in views.
Syntax to Create a view:
CREATE VIEW view_name ASSELECT column1, column2, … FROM table_name WHERE situation;
Create View Salesman_infoAsselect s.salesmanid,s.metropolis, rely(customerid) no_of_sales from salesman s left be a part of buyer c on c.salesmanid = s.salesmanidgroup by 1;
This works precisely the identical because the desk and reveals the information each time you run a choose question on the view and also you don’t have to put in writing that complicated question time and again.
Syntax to Replace a view:
CREATE OR REPLACE VIEW view_name ASSELECT column1, column2, … FROM table_name WHERE situation;
Syntax to Dropping a view:
DROP VIEW view_name;
This view supplies a number of advantages.
Views can disguise complexity.Views can be utilized as a safety mechanism.Views can simplify supporting legacy code.
Constructed-in Features: There are various inbuilt capabilities that we are able to to entry or manipulate our information as required. A number of the capabilities are String capabilities, Math/Numeric capabilities, Date capabilities, Advance capabilities, or Mixture capabilities.
Person-defined Perform: SQL permits customers to create customized capabilities in accordance with their actual necessities.
There are three kinds of user-defined capabilities:
Scalar capabilities i.e. return a single worth.Desk worth capabilities i.e. return a desk set.Multi-table worth capabilities i.e. return a desk set.
Notice: we are able to solely use the choose question in user-defined capabilities.
Right here I’ve created a operate for calculating age utilizing the date of beginning in MYSQL.
DELIMITER $$CREATE FUNCTION Calculate_Age(DOB date)RETURNS INT DETERMINISTICBEGINRETURN YEAR(CURRENT_DATE()) – YEAR(DOB);END$$DELIMITER ;
We are able to use capabilities utilizing choose /the place/ having clause.
Saved Procedures: A saved process is a ready SQL code that you could save, so the code could be reused again and again. So if in case you have an SQL question that you just write again and again, put it aside as a saved process, after which simply name it to execute it.
Saved process syntax:
CREATE PROCEDURE procedure_nameASsql_statementGO;
Execute a Saved Process
EXEC procedure_name;
Why we use saved process quite than operate ?
Saved process can return zero, single and a number of values.Saved process may return dataset of tables as required utilizing a number of SQL queries.We are able to name operate in saved process.Saved process can have enter/output values and parameters.We are able to’t use choose/the place/having assertion with SPs.We are able to use insert/replace/delete and choose in saved process.SQL saved process can execute dynamic SQL.DELIMITER $$CREATE PROCEDURE Proc_Saleman_Info_Data()BEGIN## Sort 1select s.salesmanid,s.metropolis, rely(customerid) no_of_sales from salesman s left be a part of buyer c on c.salesmanid = s.salesmanidgroup by 1;
## Sort 2insert into salesmanvalues(5008,’Shikha rawat’,’India’,12);
END$$DELIMITER ;
Right here I’ve created a saved process utilizing choose and insert statements.
name Proc_Saleman_Info_Data();
In MYSQL, we use name to execute saved process.
In SQL SERVER, we use exec to execute saved process.
Standard Interview Questions for Views/Features/Saved Process.
Ques1: What are your views?
Ques2: What number of tables we are able to use in views?
Ques3: Distinction between operate and saved process?
Thanks for studying it and if you happen to prefer it, don’t forget to present a clap!
Have a pleasant day!
=============================THE END==========================
GitHub: Day 7 Session
Please give it a star on Git Hub!!
Reference :
https://www.w3schools.com/sql/default.asphttps://www.geeksforgeeks.org/
Hope you discovered it useful! Thanks for studying!
Comply with me for extra Knowledge Science associated posts!
Let’s join on LinkedIn!
