SQL(Basics2)(Procedures &Views)

 show databases

create database dev

use dev

select database()

select count(*) from bank_details

Stored Proceudre

----------------:-

Note:-

----->In SQL indentation is important in the case of functions and procedures only.

A stored procedure is a group of statements that can be executed.

-------How to Create the Procedure-----------------------

DELIMITER &&

create procedure select_rec()

BEGIN

 select * from bank_details;

END && 

How to Call the procedure

---------------------------------

syntax:-

call <procedure name>

call select_rec()

DELIMITER &&

create procedure bal_min()

BEGIN

 select * from bank_details where balance in(select min(balance) from bank_details);

END && 

call bal_min()

select * from bank_details

select distinct job from bank_details

select count(distinct job) from bank_details

------How to create parameterized  procedure-----------------------

DELIMITER &&

create procedure job_all(IN galla varchar(30))

BEGIN

 select * from bank_details where job=galla;

END && 

------How to call the Parameterized procedure-------------

call job_all('admin.')

call job_all('management')

call job_all('technician')

select * from bank_details

------How to use distinct(),count() at the same time --------------

select count(distinct job) from bank_details

select count(distinct age) from bank_details

select count(distinct marital) from bank_details

------How to create parameterized   procedure-----------------------

EX:-1

DELIMITER &&

create procedure job_marry(in v1 varchar(30),in v2 varchar(30))

BEGIN

 select * from bank_details where job=v1 and marital=v2;

END && 

call job_marry('admin.','single')

call job_marry('admin.','married')

call job_marry('admin.','unmarried')

select * from bank_details where job='admin.' and marital='Unmarried'

select distinct marital from bank_details

EX:-2

DELIMITER &&

create procedure job_age(in v1 varchar(30),in v2 varchar(30))

BEGIN

 select * from bank_details where job=v1 and age=v2;

END && 

call job_age('admin.','60')

call job_age('unknown','33')

EX:-3

DELIMITER &&

create procedure job_age_housing(in v1 varchar(30),in v2 varchar(30),in v3 varchar(30))

BEGIN

 select * from bank_details where job=v1 and (age=v2 or housing=v3);

END && 

call job_age_housing('admin.','60','yes')

call job_age_housing('unknown','36','no')

View:-

-----

   

---->View is simple showcasing data stored in the database tables.

Syntax:-

create view <view name> as <query we can put here what columns are u trying to fetch

create view bank_view1 as select job,marital,education,contact from bank_details;

----------How to execute view---------------

select * from bank_view1

select count(*) from bank_view1