Scribd
Upload
11 views7 pages

Bank Dataset SQL Creation and Insertion

The document outlines SQL commands for creating and managing a database named 'bank_dataset' and a table called 'bank_details' which includes various attributes related to bank customers. It includes commands for inserting multiple records into the table and querying the data based on specific conditions. The document also demonstrates how to retrieve specific columns and count the total number of entries in the table.

Uploaded by

dallawais543
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
11 views7 pages

Bank Dataset SQL Creation and Insertion

The document outlines SQL commands for creating and managing a database named 'bank_dataset' and a table called 'bank_details' which includes various attributes related to bank customers. It includes commands for inserting multiple records into the table and querying the data based on specific conditions. The document also demonstrates how to retrieve specific columns and count the total number of entries in the table.

Uploaded by

dallawais543
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

show databases

create database if not exists bank_dataset


use bank_dataset

create table if not exists bank_details(


age int,
job varchar(30),
marital varchar(30),
education varchar(30),
`default` varchar(30),
balance int ,
housing varchar(30),
loan varchar(30) ,
contact varchar(30),
`day` int,
`month` varchar(30) ,
duration int ,
campaign int,
pdays int ,
previous int ,
poutcome varchar(30) ,
y varchar(30))

select * from bank_details

insert into bank_details


values(58,"management","married","tertiary","no",2143,"yes","no","unknown",5,"may",
261,1,-1,0,"unknown","no")

insert into bank_details values


(44,"technician","single","secondary","no",29,"yes","no","unknown",5,"may",151,1,-
1,0,"unknown","no"),
(33,"entrepreneur","married","secondary","no",2,"yes","yes","unknown",5,"may",76,1,
-1,0,"unknown","no"),
(47,"blue-collar","married","unknown","no",1506,"yes","no","unknown",5,"may",92,1,-
1,0,"unknown","no"),
(33,"unknown","single","unknown","no",1,"no","no","unknown",5,"may",198,1,-
1,0,"unknown","no"),
(35,"management","married","tertiary","no",231,"yes","no","unknown",5,"may",139,1,-
1,0,"unknown","no"),
(28,"management","single","tertiary","no",447,"yes","yes","unknown",5,"may",217,1,-
1,0,"unknown","no"),
(42,"entrepreneur","divorced","tertiary","yes",2,"yes","no","unknown",5,"may",380,1
,-1,0,"unknown","no"),
(58,"retired","married","primary","no",121,"yes","no","unknown",5,"may",50,1,-
1,0,"unknown","no"),
(43,"technician","single","secondary","no",593,"yes","no","unknown",5,"may",55,1,-
1,0,"unknown","no"),
(41,"admin.","divorced","secondary","no",270,"yes","no","unknown",5,"may",222,1,-
1,0,"unknown","no"),
(29,"admin.","single","secondary","no",390,"yes","no","unknown",5,"may",137,1,-
1,0,"unknown","no"),
(53,"technician","married","secondary","no",6,"yes","no","unknown",5,"may",517,1,-
1,0,"unknown","no"),
(58,"technician","married","unknown","no",71,"yes","no","unknown",5,"may",71,1,-
1,0,"unknown","no"),
(57,"services","married","secondary","no",162,"yes","no","unknown",5,"may",174,1,-
1,0,"unknown","no"),
(51,"retired","married","primary","no",229,"yes","no","unknown",5,"may",353,1,-
1,0,"unknown","no"),
(45,"admin.","single","unknown","no",13,"yes","no","unknown",5,"may",98,1,-
1,0,"unknown","no"),
(57,"blue-collar","married","primary","no",52,"yes","no","unknown",5,"may",38,1,-
1,0,"unknown","no"),
(60,"retired","married","primary","no",60,"yes","no","unknown",5,"may",219,1,-
1,0,"unknown","no"),
(33,"services","married","secondary","no",0,"yes","no","unknown",5,"may",54,1,-
1,0,"unknown","no"),
(28,"blue-
collar","married","secondary","no",723,"yes","yes","unknown",5,"may",262,1,-
1,0,"unknown","no"),
(56,"management","married","tertiary","no",779,"yes","no","unknown",5,"may",164,1,-
1,0,"unknown","no"),
(32,"blue-collar","single","primary","no",23,"yes","yes","unknown",5,"may",160,1,-
1,0,"unknown","no"),
(25,"services","married","secondary","no",50,"yes","no","unknown",5,"may",342,1,-
1,0,"unknown","no"),
(40,"retired","married","primary","no",0,"yes","yes","unknown",5,"may",181,1,-
1,0,"unknown","no"),
(44,"admin.","married","secondary","no",-372,"yes","no","unknown",5,"may",172,1,-
1,0,"unknown","no"),
(39,"management","single","tertiary","no",255,"yes","no","unknown",5,"may",296,1,-
1,0,"unknown","no"),
(52,"entrepreneur","married","secondary","no",113,"yes","yes","unknown",5,"may",127
,1,-1,0,"unknown","no"),
(46,"management","single","secondary","no",-
246,"yes","no","unknown",5,"may",255,2,-1,0,"unknown","no"),
(36,"technician","single","secondary","no",265,"yes","yes","unknown",5,"may",348,1,
-1,0,"unknown","no"),
(57,"technician","married","secondary","no",839,"no","yes","unknown",5,"may",225,1,
-1,0,"unknown","no"),
(49,"management","married","tertiary","no",378,"yes","no","unknown",5,"may",230,1,-
1,0,"unknown","no"),
(60,"admin.","married","secondary","no",39,"yes","yes","unknown",5,"may",208,1,-
1,0,"unknown","no"),
(59,"blue-collar","married","secondary","no",0,"yes","no","unknown",5,"may",226,1,-
1,0,"unknown","no"),
(51,"management","married","tertiary","no",10635,"yes","no","unknown",5,"may",336,1
,-1,0,"unknown","no"),
(57,"technician","divorced","secondary","no",63,"yes","no","unknown",5,"may",242,1,
-1,0,"unknown","no"),
(25,"blue-collar","married","secondary","no",-
7,"yes","no","unknown",5,"may",365,1,-1,0,"unknown","no"),
(53,"technician","married","secondary","no",-3,"no","no","unknown",5,"may",1666,1,-
1,0,"unknown","no"),
(36,"admin.","divorced","secondary","no",506,"yes","no","unknown",5,"may",577,1,-
1,0,"unknown","no"),
(37,"admin.","single","secondary","no",0,"yes","no","unknown",5,"may",137,1,-
1,0,"unknown","no"),
(44,"services","divorced","secondary","no",2586,"yes","no","unknown",5,"may",160,1,
-1,0,"unknown","no"),
(50,"management","married","secondary","no",49,"yes","no","unknown",5,"may",180,2,-
1,0,"unknown","no"),
(60,"blue-collar","married","unknown","no",104,"yes","no","unknown",5,"may",22,1,-
1,0,"unknown","no"),
(54,"retired","married","secondary","no",529,"yes","no","unknown",5,"may",1492,1,-
1,0,"unknown","no"),
(58,"retired","married","unknown","no",96,"yes","no","unknown",5,"may",616,1,-
1,0,"unknown","no"),
(36,"admin.","single","primary","no",-171,"yes","no","unknown",5,"may",242,1,-
1,0,"unknown","no"),
(58,"self-employed","married","tertiary","no",-
364,"yes","no","unknown",5,"may",355,1,-1,0,"unknown","no"),
(44,"technician","married","secondary","no",0,"yes","no","unknown",5,"may",225,2,-
1,0,"unknown","no"),
(55,"technician","divorced","secondary","no",0,"no","no","unknown",5,"may",160,1,-
1,0,"unknown","no"),
(29,"management","single","tertiary","no",0,"yes","no","unknown",5,"may",363,1,-
1,0,"unknown","no"),
(54,"blue-
collar","married","secondary","no",1291,"yes","no","unknown",5,"may",266,1,-
1,0,"unknown","no"),
(48,"management","divorced","tertiary","no",-
244,"yes","no","unknown",5,"may",253,1,-1,0,"unknown","no"),
(32,"management","married","tertiary","no",0,"yes","no","unknown",5,"may",179,1,-
1,0,"unknown","no"),
(42,"admin.","single","secondary","no",-76,"yes","no","unknown",5,"may",787,1,-
1,0,"unknown","no"),
(24,"technician","single","secondary","no",-
103,"yes","yes","unknown",5,"may",145,1,-1,0,"unknown","no"),
(38,"entrepreneur","single","tertiary","no",243,"no","yes","unknown",5,"may",174,1,
-1,0,"unknown","no"),
(38,"management","single","tertiary","no",424,"yes","no","unknown",5,"may",104,1,-
1,0,"unknown","no"),
(47,"blue-collar","married","unknown","no",306,"yes","no","unknown",5,"may",13,1,-
1,0,"unknown","no"),
(40,"blue-collar","single","unknown","no",24,"yes","no","unknown",5,"may",185,1,-
1,0,"unknown","no"),
(46,"services","married","primary","no",179,"yes","no","unknown",5,"may",1778,1,-
1,0,"unknown","no"),
(32,"admin.","married","tertiary","no",0,"yes","no","unknown",5,"may",138,1,-
1,0,"unknown","no"),
(53,"technician","divorced","secondary","no",989,"yes","no","unknown",5,"may",812,1
,-1,0,"unknown","no"),
(57,"blue-collar","married","primary","no",249,"yes","no","unknown",5,"may",164,1,-
1,0,"unknown","no"),
(33,"services","married","secondary","no",790,"yes","no","unknown",5,"may",391,1,-
1,0,"unknown","no"),
(49,"blue-collar","married","unknown","no",154,"yes","no","unknown",5,"may",357,1,-
1,0,"unknown","no"),
(51,"management","married","tertiary","no",6530,"yes","no","unknown",5,"may",91,1,-
1,0,"unknown","no"),
(60,"retired","married","tertiary","no",100,"no","no","unknown",5,"may",528,1,-
1,0,"unknown","no"),
(59,"management","divorced","tertiary","no",59,"yes","no","unknown",5,"may",273,1,-
1,0,"unknown","no"),
(55,"technician","married","secondary","no",1205,"yes","no","unknown",5,"may",158,2
,-1,0,"unknown","no"),
(35,"blue-
collar","single","secondary","no",12223,"yes","yes","unknown",5,"may",177,1,-
1,0,"unknown","no"),
(57,"blue-
collar","married","secondary","no",5935,"yes","yes","unknown",5,"may",258,1,-
1,0,"unknown","no"),
(31,"services","married","secondary","no",25,"yes","yes","unknown",5,"may",172,1,-
1,0,"unknown","no"),
(54,"management","married","secondary","no",282,"yes","yes","unknown",5,"may",154,1
,-1,0,"unknown","no"),
(55,"blue-collar","married","primary","no",23,"yes","no","unknown",5,"may",291,1,-
1,0,"unknown","no"),
(43,"technician","married","secondary","no",1937,"yes","no","unknown",5,"may",181,1
,-1,0,"unknown","no"),
(53,"technician","married","secondary","no",384,"yes","no","unknown",5,"may",176,1,
-1,0,"unknown","no"),
(44,"blue-
collar","married","secondary","no",582,"no","yes","unknown",5,"may",211,1,-
1,0,"unknown","no"),
(55,"services","divorced","secondary","no",91,"no","no","unknown",5,"may",349,1,-
1,0,"unknown","no"),
(49,"services","divorced","secondary","no",0,"yes","yes","unknown",5,"may",272,1,-
1,0,"unknown","no"),
(55,"services","divorced","secondary","yes",1,"yes","no","unknown",5,"may",208,1,-
1,0,"unknown","no"),
(45,"admin.","single","secondary","no",206,"yes","no","unknown",5,"may",193,1,-
1,0,"unknown","no"),
(47,"services","divorced","secondary","no",164,"no","no","unknown",5,"may",212,1,-
1,0,"unknown","no"),
(42,"technician","single","secondary","no",690,"yes","no","unknown",5,"may",20,1,-
1,0,"unknown","no"),
(59,"admin.","married","secondary","no",2343,"yes","no","unknown",5,"may",1042,1,-
1,0,"unknown","yes"),
(46,"self-
employed","married","tertiary","no",137,"yes","yes","unknown",5,"may",246,1,-
1,0,"unknown","no"),
(51,"blue-collar","married","primary","no",173,"yes","no","unknown",5,"may",529,2,-
1,0,"unknown","no"),
(56,"admin.","married","secondary","no",45,"no","no","unknown",5,"may",1467,1,-
1,0,"unknown","yes"),
(41,"technician","married","secondary","no",1270,"yes","no","unknown",5,"may",1389,
1,-1,0,"unknown","yes"),
(46,"management","divorced","secondary","no",16,"yes","yes","unknown",5,"may",188,2
,-1,0,"unknown","no"),
(57,"retired","married","secondary","no",486,"yes","no","unknown",5,"may",180,2,-
1,0,"unknown","no"),
(42,"management","single","secondary","no",50,"no","no","unknown",5,"may",48,1,-
1,0,"unknown","no"),
(30,"technician","married","secondary","no",152,"yes","yes","unknown",5,"may",213,2
,-1,0,"unknown","no"),
(60,"admin.","married","secondary","no",290,"yes","no","unknown",5,"may",583,1,-
1,0,"unknown","no")

select count(*) from bank_details

select * from bank_details

select age , loan , job from bank_details

select `default` from bank_details

select * from bank_details limit 10

select * from bank_details where age = 33

select * from bank_details where age = 60


select * from bank_details where age = 60 and job = 'retired'

select * from bank_details where education = 'unknown' or marital = 'single'

select * from bank_details where (education = 'unknown' or marital = 'single') and


balance < 500

select distinct job from bank_details

select * from bank_details

select * from bank_details order by age


select * from bank_details order by age desc

with this data try to fine out sum of balance


Try to find out avarage of balance
try to find out who is having a min balance
try to find out who is having a mazxmim balance
try to prepare a list of all the person who is having loan
try to find out average balance for all the people whose job role is admin
try to find out a record without job whose age is below 45
try to find out a record where education is primarty and person is jobless
try to find of a record whose bank account is having a negative balance
try to find our a record who is not having house at all along with there balance

select avg(balance) from bank_details

select * from bank_details order by balance limit 1

select * , min(balance) from bank_details

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


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

select * from bank_details where balance in (select max(balance) from bank_details)


select * from bank_details where balance = ( select max(balance) from
bank_details);
select * from bank_details order by balance desc limit 1

select * from bank_details where loan = 'yes'

select avg(balance) from bank_details where job = 'admin.'

select avg(balance) from bank_details where job = 'admin.'

select * from bank_details where job = 'unknown' and age <= 45

select * from bank_details where education = 'primary' and job = 'unknown'

select * from bank_details where balance < 0

select balance , housing from bank_details where housing = 'no'


DELIMITER &&
create procedure sudh()
BEGIN
select * from bank_details;
END &&

call sudh()

DELIMITER &&
create procedure bal_max()
BEGIN
select * from bank_details where balance in (select max(balance) from
bank_details);
END &&

DELIMITER &&
create procedure bal_max()
BEGIN
select * from bank_details where balance in (select max(balance) from
bank_details);
END &&

call bal_max()

DELIMITER &&
create procedure avg_bal_jobrole1(IN sudh varchar(30))
BEGIN
select avg(balance) from bank_details where job = sudh;
END &&

call avg_bal_jobrole1('admin.')

call avg_bal_jobrole1('retired')

call avg_bal_jobrole1('unknown')

DELIMITER &&
create procedure sel_edu_job1(in v1 varchar(30) , in v2 varchar(30) )
BEGIN
select * from bank_details where education = v1 and job = v2;
END &&

call sel_edu_job1('tertiary' , 'retired')


/* View ma hum original dataset ka kuch data leta ha aur ek new dataset keh sakta
ha ka banata ha jis ma hum wo data/column
rakhta ha ju hum analysis ka lea use karta ha kiyo ka original data set ma kuch esa
column ha jiski ham zarorat nahi
aur jab bhi hum querry hit kart ha tu wo time leti ha wo sara dataset ka scan karta
ha jesma wo faltu wala coulumn bhi ata ha
*/

create view bank_view as select age , job , marital , balance , education from
bank_details;

select avg(balance) from bank_view where job = 'admin.'


select * from bank_view

Common questions

Powered by AI

By segmenting average balance data by job roles and marital status, the bank can uncover distinct financial patterns which inform targeted marketing and risk management strategies. For instance, identifying higher average balances among certain job roles could lead to creating specialized wealth management products. Conversely, segments with lower balances might benefit from products designed to encourage saving or offer financial education. This granular insight supports crafting strategies aligned with client needs and potential value extraction from distinct market segments .

Trends or anomalies in client ages can be identified by analyzing distributions in ages concerning key financial indicators like balance, loans, and job roles. For instance, extreme values (either very high or negative balances) associated with particular age groups may indicate anomalies or specific financial trends. SQL queries ordering data by age or using age-specific filters can help surface such patterns .

Having 'unknown' values in critical columns like education and contact can lead to incomplete data analysis and poor decision-making because it hinders the understanding of client profiles. To address these challenges, data cleansing processes such as imputation with educated guesses or consulting additional data sources to fill gaps are needed. Alternatively, these records may be excluded when performing sensitive analyses where precision is essential .

The database schema for bank_details includes a column named 'loan' with a varchar type. This column stores information about whether a client has a loan ('yes' or 'no'). By querying this column, we can identify clients who have loans. For instance, the query 'select * from bank_details where loan = 'yes'' retrieves all records of clients with loans .

Clients with 'unknown' job roles present a challenge for the bank's market segmentation because job roles often correlate with income and risk profiles, critical factors in personal finance management. The presence of numerous records with the job labeled as 'unknown' could limit the bank's ability to accurately assess the market's needs and design targeted financial products. This can lead to inefficiencies in marketing and policy formulation .

Negative balance entries significantly skew the interpretation of the average balance as they lower the overall average, which might not reflect a typical client's financial situation. When calculating average balance across all customers, these negative values can indicate financial distress or errors in data recording, necessitating a closer examination of individual contexts .

The current dataset structure, with its comprehensive columns covering demographics, financial status, and transaction history, is somewhat effective in supporting analytical queries about client behavior. However, the presence of categorical attributes with 'unknown' values, limited differentiation in contact methods, and absence of certain behavioral metrics can restrict deep insights. Further normalization and enrichment of data, such as ensuring categorical completeness and integrating behavioral data, would enhance the dataset's analytical utility .

The database management uses stored procedures such as 'bal_max()' to find maximum balances and a similar query for minimum balances, which select records where the balance equals the maximum or minimum balance found in the database. These procedures are useful as they encapsulate the logic to compute and retrieve the necessary data efficiently and can be reused as needed without rewriting queries, promoting maintainability and reducing errors .

The data in the database can reflect differences in financial behavior among different marital statuses by correlating attributes such as balance, loan status, and job roles with marital status. For example, married individuals might display higher balance due to joint incomes or differences in spending patterns. Using SQL queries to filter and group data by marital status would allow analysis of these behavioral trends .

SQL views play a crucial role in optimizing data queries by allowing the selection of only relevant columns and records necessary for analysis, thereby reducing the query execution time by avoiding full scans of the table. Within the bank dataset, a view like 'bank_view', which selects specific columns such as age, job, marital status, balance, and education, allows more efficient data retrieval by excluding unnecessary data from queries .

You might also like