In todays article I’m going to be talking about views and procedures in SQL.
SQL Views
A view is a virtual table that is based on the result of a SELECT query. Views don’t store data in the database, but makes complex queries easier to use. Views also help with security as users can query from views without accessing the underlying tables in the database. For this article I’m going to be using the employees table that we’ve created in our previous articles. Now lets see how to create a view,
create view high_salary_employees as
select name, salary, department
from employees
where salary > 2000;
Here I have created a view to see employees who are earning more than 2000. Now lets see how we can call a view,
select * from high_salary_employees;
You can also modify a view,
create or replace view high_salary_employees as
select name, salary, department
from employees
where salary > 100000;
You can also delete a view,
DROP VIEW high_salary_employees;
Stored Procedures
A Stored Procedure is a predefined SQL script that runs when called. It helps to reuse code which makes queries more efficient. Similar to views, it helps with security by limiting access to underlying tables.
Lets see how to create a stored procedure. Here I’m trying to find out the employees that are earning more than 100000,
delimiter $$
create procedure get_high_salary_employees()
begin
select name, salary, department
from employees
where salary > 100000;
end $$
delimiter ;
Lets see how to call a stored procedure,
call get_high_salary_employees();
This will return all the employees earning more than 100000. Now lets see how to create a stored procedure with parameters,
delimiter $$
create procedure get_employees_salary(IN dpt_name varchar(50))
begin
select name, salary
from employees
where department = dpt_name;
end $$
delimiter ;
/*calling a stored procedure with parameter*/
call get_employees_salary('Mechanic');
You can delete a stored procedure by using the below query,
DROP procedure get_high_salary_employees;
SQL Functions
There are two types of functions in SQL. One is in-built functions which we already discussed in our previous articles. Some of these are AVG(),COUNT(),SUM() and we already discussed how to use these functions in detail. Additionally, you can create your own functions in SQL. Lets see how to create a function,
Delimiter $$
create function get_salary_by_department(dept_name varchar(50))
returns int
deterministic
begin
declare total_salary int;
select sum(salary) into total_salary
from employees where department = dept_name;
return total_salary;
end $$
delimiter ;
/* calling a function */
select get_salary_by_department('Mechanic');
Lets do some practice tasks to summarize what we’ve learned in this article,
Create a view that shows employees from the "Marketing" department.
create view marketing_emp as select name,salary from employees where department = 'Marketing'; select * from marketing_emp;Modify the view to show only employees earning more than 4000.
create or replace view marketing_emp as select name,salary from employees where department = 'Marketing' AND salary > 4000; select * from marketing_emp;Create a stored procedure to get employees who were hired after a given date.
Delimiter $$ create procedure emp_hired_date() begin select name from employees where added_date > '2025:02:10:23:20:15'; end $$ Delimiter ; call emp_hired_date();Create a function that returns the average salary in a given department.
Delimiter $$
create function avg_sal_by_department(dpt_name varchar(50))
returns float
deterministic
begin
declare avg_salary float;
select avg(salary) into avg_salary from employees where department = dpt_name;
return avg_salary;
end $$
Delimiter ;
select avg_sal_by_department('Mechanic');