SQL Practice Questions | Structured Query Language Questions (2023)

Get your hands dirty while playing with some interesting SQL queries.

PART 1

This covers SQL basic query operations like creating databases forms scratch, creating a table, insert values etc.

It is better to get hands-on in order to have practical experience with SQL queries. A small error/bug will make you feel surprised and next time you will get there!

Let’s get started!

1) Create a Database bank

CREATE DATABASE bank;use bank

2) Create a table with the name “bank_details” with the following columns

— Product with string data type

— Quantity with numerical data type

— Price with real number data type

— purchase_cost with decimal data type

— estimated_sale_price with data type float

Create table bank_details(Product CHAR(10) , quantity INT,price Real ,purchase_cost Decimal(6,2),estimated_sale_price Float); 

3) Display all columns and their datatype and size in Bank_details

Describe bank_details;

4) Insert two records into Bank_details.

— 1st record with values —

— Product: PayCard

— Quantity: 3

— price: 330

— Puchase_cost: 8008

— estimated_sale_price: 9009

— Product: PayPoints —

— Quantity: 4

— price: 200

— Puchase_cost: 8000

— estimated_sale_price: 6800

Insert into Bank_detailsvalues ( 'paycard' , 3 , 330, 8008, 9009);Insert into Bank_detailsvalues ( 'paypoints' , 4 , 200, 8000, 6800);

5) Add a column: Geo_Location to the existing Bank_details table with data type varchar and size 20

Alter table Bank_details add geo_location Varchar(20);

6) What is the value of Geo_location for a product : “PayCard”?

Select geo_location from Bank_details where Product = 'PayCard';

7) How many characters does the Product : “paycard” have in the Bank_details table.

select char_length(Product) from Bank_details where Product = 'PayCard';

8) Alter the Product field from CHAR to VARCHAR in Bank_details

Alter table bank_details modify PRODUCT varchar(10);

9) Reduce the size of the Product field from 10 to 6 and check if it is possible

Alter table bank_details modify product varchar(6);

10) Create a table named as Bank_Holidays with below fields

— a) Holiday field which displays only date

— b) Start_time field which displays hours and minutes

— c) End_time field which also displays hours and minutes and timezone

Create table bank_holidays (Holiday date ,Start_time datetime ,End_time timestamp);

11) Step 1: Insert today’s date details in all fields of Bank_Holidays

— Step 2: After step1, perform the below

— Postpone Holiday to next day by updating the Holiday field

-- Step1: Insert into bank_holidays values ( current_date(), current_date(), current_date() );-- Step 2: Update bank_holidays set holiday = DATE_ADD(Holiday , INTERVAL 1 DAY);

Update the End_time with current European time.

Update Bank_Holidays Set End_time = utc_timestamp();

12) Display output of PRODUCT field as NEW_PRODUCT in Bank_details table

Select PRODUCT as NEW_PRODUCT from bank_details;

13) Display only one record from bank_details

Select * from Bank_details limit 1;

15) Display the first five characters of the Geo_location field of Bank_details.

SELECT substr(Geo_location , 1, 5) FROM `bank_details`;

Also Read: SQL Tutorial for Beginners

PART 2

— ——————————————————–

# Datasets Used: cricket_1.csv, cricket_2.csv

— cricket_1 is the table for cricket test match 1.

— cricket_2 is the table for cricket test match 2.

— ——————————————————–

Find all the players who were present in the test match 1 as well as in the test match 2.

SELECT * FROM cricket_1UNIONSELECT * FROM cricket_2;

Write a MySQl query to find the players from the test match 1 having popularity higher than the average popularity.

select player_name , Popularity from cricket_1 WHERE Popularity > (SELECT AVG(Popularity) FROM cricket_1);

Find player_id and player name that are common in the test match 1 and test match 2.

SELECT player_id , player_name FROM cricket_1WHERE cricket_1.player_id IN (SELECT player_id FROM cricket_2);

Retrieve player_id, runs, and player_name from cricket_1 and cricket_2 table and display the player_id of the players where the runs are more than the average runs.

SELECT player_id , runs , player_name FROM cricket_1 WHERE cricket_1.RUNS > (SELECT AVG(RUNS) FROM cricket_2);

Write a query to extract the player_id, runs and player_name from the table “cricket_1” where the runs are greater than 50.

SELECT player_id , runs , player_name FROM cricket_1 WHERE cricket_1.Runs > 50 ;

Write a query to extract all the columns from cricket_1 where player_name starts with ‘y’ and ends with ‘v’.

SELECT * FROM cricket_1 WHERE player_name LIKE 'y%v';

Write a query to extract all the columns from cricket_1 where player_name does not end with ‘t’.

SELECT * FROM cricket_1 WHERE player_name NOT LIKE '%t'; 

# Dataset Used: cric_combined.csv

Write a MySQL query to create a new column PC_Ratio that contains the popularity to charisma ratio.

ALTER TABLE cric_combinedADD COLUMN PC_Ratio float4;UPDATE cric_combined SET PC_Ratio = (Popularity / Charisma);

Write a MySQL query to find the top 5 players having the highest popularity to charisma ratio

SELECT Player_Name , PC_Ratio FROM cric_combined ORDER BY PC_Ratio DESC LIMIT 5;

Write a MySQL query to find the player_ID and the name of the player that contains the character “D” in it.

SELECT Player_Id , Player_Name FROM cric_combined WHERE Player_Name LIKE '%d%'; 

Dataset Used: new_cricket.csv

Extract the Player_Id and Player_name of the players where the charisma value is null.

SELECT Player_Id , Player_Name FROM new_cricket WHERE Charisma IS NULL;

Write a MySQL query to impute all the NULL values with 0.

SELECT IFNULL(Charisma, 0) FROM new_cricket;

Separate all Player_Id into single numeric ids (example PL1 = 1).

SELECT Player_Id, SUBSTR(Player_Id,3)FROM new_cricket;

Write a MySQL query to extract Player_Id, Player_Name and charisma where the charisma is greater than 25.

SELECT Player_Id , Player_Name , charisma FROM new_cricket WHERE charisma > 25;

# Dataset Used: churn1.csv

Write a query to count all the duplicate values from the column “Agreement” from the table churn1.

SELECT Agreement, COUNT(Agreement) FROM churn1 GROUP BY Agreement HAVING COUNT(Agreement) > 1;

Rename the table churn1 to “Churn_Details”.

RENAME TABLE churn1 TO Churn_Details;

Write a query to create a new column new_Amount that contains the sum of TotalAmount and MonthlyServiceCharges.

ALTER TABLE Churn_DetailsADD COLUMN new_Amount FLOAT;UPDATE Churn_Details SET new_Amount = (TotalAmount + MonthlyServiceCharges);SELECT new_Amount FROM CHURN_DETAILS;

Rename column new_Amount to Amount.

ALTER TABLE Churn_Details CHANGE new_Amount Amount FLOAT;SELECT AMOUNT FROM CHURN_DETAILS;

Drop the column “Amount” from the table “Churn_Details”.

ALTER TABLE Churn_Details DROP COLUMN Amount ;

Write a query to extract the customerID, InternetConnection and gender from the table “Churn_Details ” where the value of the column “InternetConnection” has ‘i’ at the second position.

SELECT customerID, InternetConnection, gender FROM Churn_Details WHERE InternetConnection LIKE '_i%';

Find the records where the tenure is 6x, where x is any number.

SELECT * FROM Churn_Details WHERE tenure LIKE '6_';

Part 3

# DataBase = Property Price Train

Dataset used: Property_Price_Train_new

Write An MySQL Query To Print The First Three Characters Of Exterior1st From Property_Price_Train_new Table.

Select substring(Exterior1st,1,3) from Property_Price_Train_new;

Write An MySQL Query To Print Brick_Veneer_Area Of Property_Price_Train_new Excluding Brick_Veneer_Type, “None” And “BrkCmn” From Property_Price_Train_new Table.

Select Brick_Veneer_Area, Brick_Veneer_Type from Property_Price_Train_new where Brick_Veneer_Type not in ('None','BrkCmn');

Write An MySQL Query to print Remodel_Year , Exterior2nd of the Property_Price_Train_new Whose Exterior2nd Contains ‘H’.

Select Remodel_Year , Exterior2nd from Property_Price_Train_new where Exterior2nd like '%H%' ;

Write MySQL query to print details of the table Property_Price_Train_new whose Remodel_year from 1983 to 2006

select * from Property_Price_Train_new where Remodel_Year between 1983 and 2006;

Write MySQL query to print details of Property_Price_Train_new whose Brick_Veneer_Type ends with e and contains 4 alphabets.

Select * from Property_Price_Train_new where Brick_Veneer_Type like '____e';

Write MySQl query to print nearest largest integer value of column Garage_Area from Property_Price_Train_new

Select ceil(Garage_Area) from Property_Price_Train_new;

Fetch the 3 highest value of column Brick_Veneer_Area from Property_Price_Train_new table

Select Brick_Veneer_Area from Property_Price_Train_new order by Brick_Veneer_Area desc limit 2,1;

Rename column LowQualFinSF to Low_Qual_Fin_SF fom table Property_Price_Train_new

Alter table Property_Price_Train_new change LowQualFinSF Low_Qual_Fin_SF varchar(150);

Convert Underground_Full_Bathroom (1 and 0) values to true or false respectively.

# Eg. 1 – true ; 0 – false

SELECT CASE WHEN Underground_Full_Bathroom = 0 THEN 'false' ELSE 'true' END FROM Property_Price_Train_new;

Extract total Sale_Price for each year_sold column of Property_Price_Train_new table.

Select Year_Sold, sum(Sale_Price) from Property_Price_Train_new group by Year_Sold;

Extract all negative values from W_Deck_Area

Select W_Deck_Area from Property_Price_Train_new where W_Deck_Area < 0;

Write MySQL query to extract Year_Sold, Sale_Price whose price is greater than 100000.

Select Sale_Price , Year_Sold from Property_Price_Train_new group by Year_Sold having Sale_Price > 100000;

Write MySQL query to extract Sale_Price and House_Condition from Property_Price_Train_new and Property_price_train_2 perform inner join. Rename the table as PPTN and PPTN2.

Select Sale_Price , House_Condition from Property_Price_Train_new AS PPTN inner join Property_price_train_2 AS PPT2 on PPTN.ID= PPTN2.ID;

Count all duplicate values of column Brick_Veneer_Type from tbale Property_Price_Train_new

Select Brick_Veneer_Type, count(Brick_Veneer_Type) from Property_Price_Train_new group by Brick_Veneer_Type having count(Brick_Veneer_Type) > 1;

# DATABASE Cricket

Find all the players from both matches.

SELECT * FROM cricket_1UNIONSELECT * FROM cricket_2;

Perform right join on cricket_1 and cricket_2.

SELECT cric2.Player_Id, cric2.Player_Name, cric2.Runs, cric2.Charisma, cric1.PopularityFROM cricket_1 AS cric1 RIGHT JOIN cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

Perform left join on cricket_1 and cricket_2

SELECT cric1.Player_Id, cric1.Player_Name, cric1.Runs, cric1.Popularity, cric2.CharismaFROM cricket_1 AS cric1 LEFT JOIN cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

Perform left join on cricket_1 and cricket_2.

SELECT cric1.Player_Id, cric1.Player_Name, cric1.Runs, cric1.Popularity, cric2.CharismaFROM cricket_1 AS cric1 INNER JOIN cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

Create a new table and insert the result obtained after performing inner join on the two tables cricket_1 and cricket_2.

CREATE TABLE Players1And2 ASSELECT cric1.Player_Id, cric1.Player_Name, cric1.Runs, cric1.Popularity, cric2.CharismaFROM cricket_1 AS cric1 INNER JOIN cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

Write MySQL query to extract maximum runs of players get only top two players

select Player_Name, Runs from cricket_1 group by Player_Name having max(Runs) limit 2;

PART 4

# Pre-Requisites

# Assuming Candidates are familiar with “Group by” and “Grouping functions” because these are used along with JOINS in the questionnaire.

# Create below DB objects

CREATE TABLE BANK_CUSTOMER ( customer_id INT , customer_name VARCHAR(20), Address VARCHAR(20), state_code VARCHAR(3) , Telephone VARCHAR(10));INSERT INTO BANK_CUSTOMER VALUES (123001,"Oliver", "225-5, Emeryville", "CA" , "1897614500");INSERT INTO BANK_CUSTOMER VALUES (123002,"George", "194-6,New brighton","MN" , "1897617000");INSERT INTO BANK_CUSTOMER VALUES (123003,"Harry", "2909-5,walnut creek","CA" , "1897617866");INSERT INTO BANK_CUSTOMER VALUES (123004,"Jack", "229-5, Concord", "CA" , "1897627999");INSERT INTO BANK_CUSTOMER VALUES (123005,"Jacob", "325-7, Mission Dist","SFO", "1897637000");INSERT INTO BANK_CUSTOMER VALUES (123006,"Noah", "275-9, saint-paul" , "MN" , "1897613200");INSERT INTO BANK_CUSTOMER VALUES (123007,"Charlie","125-1,Richfield", "MN" , "1897617666");INSERT INTO BANK_CUSTOMER VALUES (123008,"Robin","3005-1,Heathrow", "NY" , "1897614000");CREATE TABLE BANK_CUSTOMER_EXPORT ( customer_id CHAR(10),customer_name CHAR(20),Address CHAR(20),state_code CHAR(3) , Telephone CHAR(10)); INSERT INTO BANK_CUSTOMER_EXPORT VALUES ("123001 ","Oliver", "225-5, Emeryville", "CA" , "1897614500") ;INSERT INTO BANK_CUSTOMER_EXPORT VALUES ("123002 ","George", "194-6,New brighton","MN" , "189761700");CREATE TABLE Bank_Account_Details(Customer_id INT, Account_Number VARCHAR(19), Account_type VARCHAR(25), Balance_amount INT, Account_status VARCHAR(10), Relationship_type varchar(1) ) ;INSERT INTO Bank_Account_Details VALUES (123001, "4000-1956-3456", "SAVINGS" , 200000 ,"ACTIVE","P");INSERT INTO Bank_Account_Details VALUES (123001, "5000-1700-3456", "RECURRING DEPOSITS" ,9400000 ,"ACTIVE","S"); INSERT INTO Bank_Account_Details VALUES (123002, "4000-1956-2001", "SAVINGS", 400000 ,"ACTIVE","P");INSERT INTO Bank_Account_Details VALUES (123002, "5000-1700-5001", "RECURRING DEPOSITS" ,7500000 ,"ACTIVE","S");INSERT INTO Bank_Account_Details VALUES (123003, "4000-1956-2900", "SAVINGS" ,750000,"INACTIVE","P");INSERT INTO Bank_Account_Details VALUES (123004, "5000-1700-6091", "RECURRING DEPOSITS" ,7500000 ,"ACTIVE","S");INSERT INTO Bank_Account_Details VALUES (123004, "4000-1956-3401", "SAVINGS" , 655000 ,"ACTIVE","P");INSERT INTO Bank_Account_Details VALUES (123005, "4000-1956-5102", "SAVINGS" , 300000 ,"ACTIVE","P");INSERT INTO Bank_Account_Details VALUES (123006, "4000-1956-5698", "SAVINGS" , 455000 ,"ACTIVE" ,"P");INSERT INTO Bank_Account_Details VALUES (123007, "5000-1700-9800", "SAVINGS" , 355000 ,"ACTIVE" ,"P");INSERT INTO Bank_Account_Details VALUES (123007, "4000-1956-9977", "RECURRING DEPOSITS" , 7025000,"ACTIVE" ,"S");INSERT INTO Bank_Account_Details VALUES (123007, "9000-1700-7777-4321", "Credit Card",0 ,"INACTIVE", "P");INSERT INTO Bank_Account_Details VALUES (123007, '5900-1900-9877-5543', "Add-on Credit Card" , 0 ,"ACTIVE", "S");INSERT INTO Bank_Account_Details VALUES (123008, "5000-1700-7755", "SAVINGS" ,0 ,"INACTIVE","P");INSERT INTO Bank_Account_Details VALUES (123006, '5800-1700-9800-7755', "Credit Card" ,0 ,"ACTIVE", "P");INSERT INTO Bank_Account_Details VALUES (123006, '5890-1970-7706-8912', "Add-on Credit Card" ,0 ,"ACTIVE", "S");# CREATE Bank_Account Table:# Create TableCREATE TABLE BANK_ACCOUNT ( Customer_id INT, Account_Number VARCHAR(19), Account_type VARCHAR(25), Balance_amount INT ,Account_status VARCHAR(10), Relation_ship varchar(1) ) ;# Insert records:INSERT INTO BANK_ACCOUNT VALUES (123001, "4000-1956-3456", "SAVINGS" , 200000 ,"ACTIVE","P"); INSERT INTO BANK_ACCOUNT VALUES (123001, "5000-1700-3456", "RECURRING DEPOSITS" ,9400000 ,"ACTIVE","S"); INSERT INTO BANK_ACCOUNT VALUES (123002, "4000-1956-2001", "SAVINGS" , 400000 ,"ACTIVE","P"); INSERT INTO BANK_ACCOUNT VALUES (123002, "5000-1700-5001", "RECURRING DEPOSITS" ,7500000 ,"ACTIVE","S"); INSERT INTO BANK_ACCOUNT VALUES (123003, "4000-1956-2900", "SAVINGS" ,750000,"INACTIVE","P"); INSERT INTO BANK_ACCOUNT VALUES (123004, "5000-1700-6091", "RECURRING DEPOSITS" ,7500000 ,"ACTIVE","S"); INSERT INTO BANK_ACCOUNT VALUES (123004, "4000-1956-3401", "SAVINGS" , 655000 ,"ACTIVE","P"); INSERT INTO BANK_ACCOUNT VALUES (123005, "4000-1956-5102", "SAVINGS" , 300000 ,"ACTIVE","P"); INSERT INTO BANK_ACCOUNT VALUES (123006, "4000-1956-5698", "SAVINGS" , 455000 ,"ACTIVE" ,"P"); INSERT INTO BANK_ACCOUNT VALUES (123007, "5000-1700-9800", "SAVINGS" , 355000 ,"ACTIVE" ,"P"); INSERT INTO BANK_ACCOUNT VALUES (123007, "4000-1956-9977", "RECURRING DEPOSITS" , 7025000,"ACTIVE" ,"S"); INSERT INTO BANK_ACCOUNT VALUES (123007, "9000-1700-7777-4321", "CREDITCARD" ,0 ,"INACTIVE","P"); INSERT INTO BANK_ACCOUNT VALUES (123008, "5000-1700-7755", "SAVINGS" ,NULL ,"INACTIVE","P"); # CREATE TABLE Bank_Account_Relationship_DetailsCREATE TABLE Bank_Account_Relationship_Details ( Customer_id INT,Account_Number VARCHAR(19), Account_type VARCHAR(25), Linking_Account_Number VARCHAR(19));INSERT INTO Bank_Account_Relationship_Details VALUES (123001, "4000-1956-3456", "SAVINGS" , "");INSERT INTO Bank_Account_Relationship_Details VALUES (123001, "5000-1700-3456", "RECURRING DEPOSITS" , "4000-1956-3456"); INSERT INTO Bank_Account_Relationship_Details VALUES (123002, "4000-1956-2001", "SAVINGS" , "" );INSERT INTO Bank_Account_Relationship_Details VALUES (123002, "5000-1700-5001", "RECURRING DEPOSITS" , "4000-1956-2001" );INSERT INTO Bank_Account_Relationship_Details VALUES (123003, "4000-1956-2900", "SAVINGS" , "" );INSERT INTO Bank_Account_Relationship_Details VALUES (123004, "5000-1700-6091", "RECURRING DEPOSITS" , "4000-1956-2900" );INSERT INTO Bank_Account_Relationship_Details VALUES (123004, "5000-1700-7791", "RECURRING DEPOSITS" , "4000-1956-2900" );INSERT INTO Bank_Account_Relationship_Details VALUES (123007, "5000-1700-9800", "SAVINGS" , "" );INSERT INTO Bank_Account_Relationship_Details VALUES (123007, "4000-1956-9977", "RECURRING DEPOSITS" , "5000-1700-9800" );INSERT INTO Bank_Account_Relationship_Details VALUES (NULL, "9000-1700-7777-4321", "Credit Card" , "5000-1700-9800" );INSERT INTO Bank_Account_Relationship_Details VALUES (NULL, '5900-1900-9877-5543', 'Add-on Credit Card', '9000-1700-7777-4321' );INSERT INTO Bank_Account_Relationship_Details VALUES (NULL, '5800-1700-9800-7755', 'Credit Card', '4000-1956-5698' );INSERT INTO Bank_Account_Relationship_Details VALUES (NULL, '5890-1970-7706-8912', 'Add-on Credit Card', '5800-1700-9800-7755' );# CREATE TABLE BANK_ACCOUNT_TRANSACTIONCREATE TABLE BANK_ACCOUNT_TRANSACTION ( Account_Number VARCHAR(19), Transaction_amount Decimal(18,2) , Transcation_channel VARCHAR(18) , Province varchar(3) , Transaction_Date Date) ;INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-3456", -2000, "ATM withdrawl" , "CA", "2020-01-13");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001", -4000, "POS-Walmart" , "MN", "2020-02-14");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001", -1600, "UPI transfer" , "MN", "2020-01-19");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001", -6000, "Bankers cheque", "CA", "2020-03-23");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001", -3000, "Net banking" , "CA", "2020-04-24");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001", 23000, "cheque deposit", "MN", "2020-03-15");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "5000-1700-6091", 40000, "ECS transfer" , "NY", "2020-02-19");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "5000-1700-7791", 40000, "ECS transfer" , "NY", "2020-02-19");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-3401", 8000, "Cash Deposit" , "NY", "2020-01-19");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-5102", -6500, "ATM withdrawal" , "NY", "2020-03-14");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-5698", -9000, "Cash Deposit" , "NY", "2020-03-27");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-9977", 50000, "ECS transfer" , "NY", "2020-01-16");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "9000-1700-7777-4321", -5000, "POS-Walmart", "NY", "2020-02-17");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "9000-1700-7777-4321", -8000, "Shopping Cart", "MN", "2020-03-13");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "9000-1700-7777-4321", -2500, "Shopping Cart", "MN", "2020-04-21");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "5800-1700-9800-7755", -9000, "POS-Walmart","MN", "2020-04-13");INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( '5890-1970-7706-8912', -11000, "Shopping Cart" , "NY" , "2020-03-12") ;# CREATE TABLE BANK_CUSTOMER_MESSAGESCREATE TABLE BANK_CUSTOMER_MESSAGES ( Event VARCHAR(24), Customer_message VARCHAR(75), Notice_delivery_mode VARCHAR(15)) ;INSERT INTO BANK_CUSTOMER_MESSAGES VALUES ( "Adhoc", "All Banks are closed due to announcement of National strike", "mobile" ) ;INSERT INTO BANK_CUSTOMER_MESSAGES VALUES ( "Transaction Limit", "Only limited withdrawals per card are allowed from ATM machines", "mobile" );INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES('4000-1956-9977' , 10000.00 ,'ECS transfer', 'MN' , '2020-02-16' ) ;-- inserted for queries after 17th INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES('4000-1956-9977' , 40000.00 ,'ECS transfer', 'MN' , '2020-03-18' ) ;INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES('4000-1956-9977' , 60000.00 ,'ECS transfer', 'MN' , '2020-04-18' ) ;INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES('4000-1956-9977' , 20000.00 ,'ECS transfer', 'MN' , '2020-03-20' ) ;-- inserted for queries after 24th INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES('4000-1956-9977' , 49000.00 ,'ECS transfer', 'MN' , '2020-06-18' ) ;# CREATE TABLE BANK_INTEREST_RATECREATE TABLE BANK_INTEREST_RATE( account_type varchar(24), interest_rate decimal(4,2), month varchar(2), year varchar(4) );INSERT INTO BANK_INTEREST_RATE VALUES ( "SAVINGS" , 0.04 , '02' , '2020' );INSERT INTO BANK_INTEREST_RATE VALUES ( "RECURRING DEPOSITS" , 0.07, '02' , '2020' );INSERT INTO BANK_INTEREST_RATE VALUES ( "PRIVILEGED_INTEREST_RATE" , 0.08 , '02' , '2020' );# Bank_holidays:Insert into bank_holidays values( '2020-05-20', now(), now() ) ;Insert into bank_holidays values( '2020-03-13' , now(), now() ) ;

Print customer Id, customer name and average account_balance maintained by each customer for all of his/her accounts in the bank.

Select bc.customer_id , customer_name, avg(ba.Balance_amount) as All_account_balance_amountfrom bank_customer bcinner joinBank_Account_Details baon bc.customer_id = ba.Customer_idgroup by bc.customer_id, bc.customer_name;

Print customer_id , account_number and balance_amount ,

#condition that if balance_amount is nil then assign transaction_amount for account_type = “Credit Card”

Select customer_id , ba.account_number,Case when ifnull(balance_amount,0) = 0 then Transaction_amount else balance_amount end as balance_amountfrom Bank_Account_Details ba inner joinbank_account_transaction baton ba.account_number = bat.account_numberand account_type = "Credit Card";

Print customer_id , account_number and balance_amount ,

# conPrint account number, balance_amount, transaction_amount from Bank_Account_Details and bank_account_transaction

# for all the transactions occurred during march,2020 and april, 2020

Selectba.Account_Number, Balance_amount, Transaction_amount, Transaction_Datefrom Bank_Account_Details ba inner joinbank_account_transaction baton ba.account_number = bat.account_numberAnd ( Transaction_Date between "2020-03-01" and "2020-04-30");-- or use below condition -- # (date_format(Transaction_Date , '%Y-%m') between "2020-03" and "2020-04"); 

Print all of the customer id, account number, balance_amount, transaction_amount from bank_customer,

# Bank_Account_Details and bank_account_transaction tables where excluding all of their transactions in march, 2020 month

Selectba.Customer_id,ba.Account_Number, Balance_amount, Transaction_amount, Transaction_Datefrom Bank_Account_Details ba Left join bank_account_transaction baton ba.account_number = bat.account_numberAnd NOT ( date_format(Transaction_Date , '%Y-%m') = "2020-03" );

Print only the customer id, customer name, account_number, balance_amount who did transactions during the first quarter.

# Do not display the accounts if they have not done any transactions in the first quarter.

Selectba.Customer_id,ba.Account_Number, Balance_amount , transaction_amount , transaction_date fromBank_Account_Details ba Inner join bank_account_transaction baton ba.account_number = bat.account_numberAnd ( date_format(Transaction_Date , '%Y-%m') <= "2020-03" );

Print account_number, Event adn Customer_message from BANK_CUSTOMER_MESSAGES and Bank_Account_Details to display an “Adhoc”

# Event for all customers who have “SAVINGS” account_type account.

SELECT Account_Number, Event , Customer_message FROM Bank_Account_Details CROSS JOIN BANK_CUSTOMER_MESSAGES ON Event = "Adhoc" And ACCOUNT_TYPE = "SAVINGS";

Print Customer_id, Account_Number, Account_type, and display deducted balance_amount by

# subtracting only negative transaction_amounts for Relationship_type = “P” ( P – means Primary , S – means Secondary )

SELECTba.Customer_id,ba.Account_Number, (Balance_amount + IFNULL(transaction_amount, 0)) deducted_balance_amount FROM Bank_Account_Details baLEFT JOIN bank_account_transaction bat ON ba.account_number = bat.account_number AND Relationship_type = "P";

Display records of All Accounts, their Account_types, the transaction amount.

# b) Along with the first step, Display other columns with the corresponding linking account number, account types

SELECT br1.Account_Number primary_account , br1.Account_type primary_account_type, br2.Account_Number Seconday_account, br2.Account_type Seconday_account_typeFROM `bank_account_relationship_details` br1 LEFT JOIN `bank_account_relationship_details` br2ON br1.account_number = br2.linking_account_number;

Display records of All Accounts, their Account_types, the transaction amount.

# b) Along with the first step, Display other columns with corresponding linking account number, account types

# c) After retrieving all records of accounts and their linked accounts, display the transaction amount of accounts appeared in another column.

SELECT br1.Account_Number primary_account_number ,br1.Account_type primary_account_type,br2.Account_Number secondary_account_number,br2.Account_type secondary_account_type, bt1.Transaction_amount primary_acct_tran_amountfrom bank_account_relationship_details br1LEFT JOIN bank_account_relationship_details br2on br1.Account_Number = br2.Linking_Account_NumberLEFT JOIN bank_account_transaction bt1on br1.Account_Number = bt1.Account_Number;

Display all saving account holders have “Add-on Credit Cards” and “Credit cards”

SELECT br1.Account_Number primary_account_number ,br1.Account_type primary_account_type,br2.Account_Number secondary_account_number,br2.Account_type secondary_account_typefrom bank_account_relationship_details br1JOIN bank_account_relationship_details br2on br1.Account_Number = br2.Linking_Account_Numberand br2.Account_type like '%Credit%' ;

That covers the most asked or practised SQL questions.
Happy Learning!