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_1
UNION
SELECT * 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_1
WHERE 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_combined
ADD 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_Details
ADD 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_1
UNION
SELECT * FROM cricket_2;

Perform right join on cricket_1 and cricket_2.

SELECT
    cric2.Player_Id, cric2.Player_Name, cric2.Runs, cric2.Charisma, cric1.Popularity
FROM
    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.Charisma
FROM
    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.Charisma
FROM
    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 AS
SELECT
    cric1.Player_Id, cric1.Player_Name, cric1.Runs, cric1.Popularity, cric2.Charisma
FROM
    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 Table
CREATE 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_Details

CREATE 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_TRANSACTION

CREATE 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_MESSAGES

CREATE 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_RATE

CREATE 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_amount
from bank_customer bc
inner join
Bank_Account_Details ba
on bc.customer_id = ba.Customer_id
group 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_amount
from Bank_Account_Details ba  
inner join
bank_account_transaction bat
on ba.account_number = bat.account_number
and 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

Select
ba.Account_Number, Balance_amount, Transaction_amount, Transaction_Date
from Bank_Account_Details ba  
inner join
bank_account_transaction bat
on ba.account_number = bat.account_number
And ( 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 

Select
ba.Customer_id,
ba.Account_Number, Balance_amount, Transaction_amount, Transaction_Date
from Bank_Account_Details ba  
Left join bank_account_transaction bat
on ba.account_number = bat.account_number
And 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.

Select
ba.Customer_id,
ba.Account_Number, Balance_amount , transaction_amount , transaction_date from
Bank_Account_Details ba  
Inner join bank_account_transaction bat
on ba.account_number = bat.account_number
And ( 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 )

SELECT
	ba.Customer_id,
	ba.Account_Number,    
	(Balance_amount + IFNULL(transaction_amount, 0)) deducted_balance_amount
 
FROM Bank_Account_Details ba
LEFT 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_type
FROM `bank_account_relationship_details` br1  
LEFT JOIN `bank_account_relationship_details` br2
ON 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_amount
from bank_account_relationship_details br1
LEFT JOIN bank_account_relationship_details br2
on br1.Account_Number = br2.Linking_Account_Number
LEFT JOIN bank_account_transaction bt1
on 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_type
from bank_account_relationship_details br1
JOIN bank_account_relationship_details br2
on br1.Account_Number = br2.Linking_Account_Number
and br2.Account_type like '%Credit%' ;

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

0

LEAVE A REPLY

Please enter your comment!
Please enter your name here

sixteen − 7 =