SQL Practice Questions | Structured Query Language Questions (2022)

Table of Contents
PART 1 PART 2 Part 3 PART 4 FAQs Videos

SQL Practice Questions | Structured Query Language Questions (1)

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

(Video) SOLVE 5 SQL QUERIES IN 5 MINUTES (PART 1) | MASTER IN SQL | SQL INTERVIEW QUESTIONS

— 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.

(Video) IQ15: 6 SQL Query Interview Questions

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

(Video) Webinar: Answering Real-World Questions with SQL Queries

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

(Video) Solve SQL Queries for Practice | MASTER IN SQL | SQL INTERVIEW QUESTIONS

# 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!

FAQs

How do I practice SQL Server queries? ›

  1. 4 steps to start practicing SQL at home. Download MySQL and do it yourself. ...
  2. Download the software. Your first task is to download database software. ...
  3. Create your first database and data table. Great — we now have the software we need to get started. ...
  4. Get your hands on some data. ...
  5. Get curious.
19 Nov 2020

What is the best way to practice SQL? ›

Best Ways to Practice SQL Queries

Interview Query — This platform is dedicated to helping data scientists practice their SQL. If you're serious, it's worth looking into, but it's a paid service. TestDome — This is yet another good platform for interview practice.

What are the 5 types of SQL commands? ›

Types of SQL Commands. There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

Can you learn SQL in 2 weeks? ›

Everyone's different, but learning basic SQL statements can take anywhere from a couple of hours to a couple of weeks. It can take months to master them, but once you understand the concepts behind statements like INSERT, UPDATE, and DELETE, you'll be very well placed to use those statements in the real world.

Can you learn SQL in 2 months? ›

Because SQL is a relatively simple language, learners can expect to become familiar with the basics within two to three weeks. That said, if you're planning on using SQL skills at work, you'll probably need a higher level of fluency.

How can I improve my SQL query writing skills? ›

Here's the best way to learn SQL:
  1. Step 1: Determine why you want to learn SQL. ...
  2. Step 2: Learn the basic syntax. ...
  3. Step 3: Start working on guided projects. ...
  4. Step 4: Familiarize yourself with helpful SQL resources. ...
  5. Step 5: Build your own SQL projects. ...
  6. Step 6: Make more advanced projects.
17 Feb 2021

What are the 4 basic commands in SQL? ›

There are four basic SQL Operations or SQL Statements.
  • SELECT – This statement selects data from database tables.
  • UPDATE – This statement updates existing data into database tables.
  • INSERT – This statement inserts new data into database tables.
  • DELETE – This statement deletes existing data from database tables.
13 May 2008

Are SQL queries hard to learn? ›

SQL is one of the easiest languages to learn, and the concepts, syntax, queries, and data formats are not only easy to remember but have name-dependent functions too. That is, you would not be confused in any function, concepts of tables, and picking up the various necessary RDBMS tools makes it even more exciting.

Can we learn SQL in 15 days? ›

It should take an average learner about two to three weeks to master the basic concepts of SQL and start working with SQL databases. But in order to start using them effectively in real-world scenarios, you'll need to become quite fluent; and that takes time.

Can I learn SQL same day? ›

With this book, you can learn SQL in just one day and start coding immediately. SQL for BeginnersComplex topics are broken down into simple steps with clear and carefully chosen examples to ensure that you can easily master SQL even if you have never coded before.

Is SQL the easiest language to learn? ›

Generally speaking, SQL is an easy language to learn. If you understand programming and already know some other languages, you can learn SQL in a few weeks. If you're a beginner, completely new to programming, it can take longer.

What are the 3 components of SQL? ›

SQL has three main components: the Data Manipulation Language (DML), the Data Definition Language (DDL), and the Data Control Language (DCL).

Is Select DDL or DML? ›

The SELECT statement is a limited form of DML statement in that it can only access data in the database. It cannot manipulate data in the database, although it can operate on the accessed data before returning the results of the query.

Is SQL enough to get a job? ›

If you're looking for your first job in data, it turns out knowing SQL is even more critical. For data analyst roles, SQL is again the most in-demand skill, listed in a whopping 61% of job posts. For data analyst roles on Indeed, SQL appears as follows: 1.7 times more than Python.

What is the salary of a SQL developer? ›

An entry-level SQL developer can expect a salary of about Rs. 289,520 per annum. With some experience Rs. 683,480 and as he rises in experience he can expect a salary of about Rs.

Is SQL a coding? ›

YES. SQL is considered a 4th generation programming language, and one of the most successful if not the most installed of that generation on the planet. The language expresses the desired result, and not the implementation details of how to get that result.

Does SQL require coding? ›

Required Education

Learning SQL doesn't require prior programming knowledge, so it's great for beginners. Devote a couple of weeks to these four steps, and you'll be on your way to your dream analytics career.

Can SQL be self taught? ›

It is used by 47% of all developers worldwide and is the perfect language for beginners who wish to learn higher programming languages like python and JavaScript. SQL is easy to manage and anyone can self-learn at home.

How do I master in SQL? ›

7 Tips for How to Finally Get Good at (and Master) SQL
  1. Make SQL Part of Your Work Day. ...
  2. Document Your SQL Learning Experience. ...
  3. Produce Reports using SQL for your business. ...
  4. Share Your SQL Knowledge with Others. ...
  5. Volunteer or Freelance on an SQL or Database Project. ...
  6. Learn SQL Early in Your Career.
1 Aug 2020

What are strong SQL skills? ›

Advanced SQL skills
  • Execution plans. Execution plans are a visual representation of how a database engine executes a query. ...
  • Backup databases. Creating a backup database is crucial in case your first one is corrupted or damaged in some way. ...
  • Using indexes to speed up SQL queries. ...
  • OLAP.
31 Mar 2020

Should I learn Excel or SQL first? ›

If you're not sure, I recommend trying SQL first so you see how easy working with a true relational database is. Start with the SQL Basics course. Excel is useful for many other things, but data synthesis can be done in a much better way on a relational DBMS.

How do I write a SQL script? ›

To create an SQL script in the Script Editor:
  1. On the Workspace home page, click SQL Workshop and then SQL Scripts. The SQL Scripts page appears.
  2. Click the Create button. ...
  3. In Script Name, enter a name for the script. ...
  4. Enter the SQL statements, PL/SQL blocks you want to include in your script. ...
  5. Click Create.

What is the primary key in SQL? ›

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

What are the 5 built in functions in SQL? ›

COUNT, SUM, AVG, MAX and MIN is the built-in functions provided by SQL. DIV and MULT are not available in SQL.

What are the keys in SQL? ›

An SQL key is either a single column (or attribute) or a group of columns that can uniquely identify rows (or tuples) in a table. SQL keys ensure that there are no rows with duplicate information. Not only that, but they also help in establishing a relationship between multiple tables in the database.

Is SQL harder than Excel? ›

Both tools have their own learning curve—although many claim SQL is more difficult than Excel. That said, learning one tool may make it much easier to learn the other. Most data analysts learn both Excel and SQL.

Is SQL easier than coding? ›

However, this comes with a certain benefit. Because of its narrow application domain, SQL is relatively easier to learn than most general-purpose programming languages. We encourage you to follow DataCamp's SQL Fundamentals track, where we take you from being a beginner to a pro in SQL with 5 courses in just 21 hours.

Is Python or SQL easier? ›

SQL is easier than Python, in broad terms, since it has a shorter learning curve. However, for complex data workflows, limiting yourself solely to SQL over Python can make some executions more difficult. SQL is easier if all you need are JOIN, subquery, and aggregate functions.

Can beginner learn SQL? ›

Learning SQL doesn't require prior programming knowledge, so it's great for beginners.

Which SQL course is best? ›

  • Some Brief Info.
  • The Best 6 SQL Courses as of 2022.
  • PostgreSQL for Everybody — Coursera.
  • SQL Fundamentals — Dataquest.
  • Complete SQL Mastery — CodeWithMosh.
  • The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert — Udemy.
  • The Complete Oracle SQL Certification Course — Udemy.

Is SQL good for beginners? ›

Now is a great time to learn SQL. As the top programming language for databases, SQL is used by many companies in almost every industry. If you want to learn an in-demand skill, Sequel language is the way to go! Use this beginner's guide to SQL to find the resources you need and start your journey.

Is SQL good for future? ›

SQL is a massive technology. The future scope of being an SQL Developer it is not just limited to Computer Science, but you can see it revolving around Retail, Finance, Healthcare, Science & Technology, Public Sector, in short everywhere.

How do I memorize SQL commands? ›

So try to memorise the following consecutive statements: SELECT→FROM→WHERE. Next, remember that the SELECT statement refers to the column names, the FROM keyword refers to the table/database used, and the WHERE clause refers to specific conditions that are investigated by the user.

What language is SQL query? ›

Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them.

Which SQL language is most popular? ›

MySQL. MySQL is world's most popular database that is open source and free. MySQL was acquired by Oracle as a part of Sun Microsystems acquisition in 2009. In MySQL, the SQL part of “MySQL” stands for “Structured Query Language”.

Is C++ harder than SQL? ›

C++ is often faster than PL/SQL; though generally harder to write. Again it comes down a lot to what you're doing; for most applications the complexity of using C/C++ over PL/SQL outweighs any performance benefits.

Is SQL a backend language? ›

What does SQL do? SQL is the most common programming language used to interact with databases on the back-end. It is a standard back-end language used to create and maintain relational databases.

What are the four 4 types of database normalization? ›

First Normal Form (1 NF) Second Normal Form (2 NF) Third Normal Form (3 NF) Boyce Codd Normal Form or Fourth Normal Form ( BCNF or 4 NF)

How many types of query are there in SQL? ›

Five types of SQL queries are 1) Data Definition Language (DDL) 2) Data Manipulation Language (DML) 3) Data Control Language(DCL) 4) Transaction Control Language(TCL) and, 5) Data Query Language (DQL)

What are the types of tools in SQL? ›

SQL Server Data Tools (SSDT)

A modern development tool for building SQL Server relational databases, Azure SQL databases, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports.

Why truncate is DDL? ›

Truncate reinitializes the identity by making changes in data definition therefore it is DDL, whereas Delete only delete the records from the table and doesn't make any changes in its Definition that's why it is DML. Like Create a Table Names and Insert Some Initial records.

What is schema in SQL? ›

What is Schema in SQL? In a SQL database, a schema is a list of logical structures of data. A database user owns the schema, which has the same name as the database manager. As of SQL Server 2005, a schema is an individual entity (container of objects) distinct from the user who constructs the object.

Is truncate a DML command? ›

Although TRUNCATE TABLE is similar to DELETE , it is classified as a DDL statement rather than a DML statement.

What is DML and DDL? ›

DDL is the acronym for the data definition language. DML is the acronym for the data manipulation language. DDL is used to create database schema and also define constraints as well. DML is used to delete, update and update data in the database.

Is insert DDL or DML? ›

DML is Data Manipulation Language and is used to manipulate data. Examples of DML are insert, update and delete statements.

What is normalization in SQL? ›

Normalization is the process to eliminate data redundancy and enhance data integrity in the table. Normalization also helps to organize the data in the database. It is a multi-step process that sets the data into tabular form and removes the duplicated data from the relational tables.

What is the best website to practice SQL queries? ›

We'll start with websites that focus on SQL syntax and basic SQL concepts.
  1. W3Schools - "SQL Tutorial" ...
  2. Codecademy - "Learn SQL" ...
  3. LearnSQL.com. ...
  4. Khan Academy - "Intro to SQL" ...
  5. SQLZoo. ...
  6. Tutorialspoint - "Learn SQL" ...
  7. Udacity - "Intro to Relational Databases" ...
  8. SQL Problems and Solutions.
29 Jan 2019

How do I practice SQL queries offline? ›

Make a local (offline) installation of a free relational database management system on your computer and practice SQL.
...
  1. select length(
  2. replace(
  3. replace(
  4. replace(...(
  5. replace(
  6. replace(upper(mystr), 'B', ''),
  7. 'C', ''),
  8. 'D', ''),

Can I practice SQL on my phone? ›

Recently, I bumped into an app called “SQL Playground“, which lets you practice SQL right on your Android device.

How can I improve my SQL query skills? ›

7 Tips for How to Finally Get Good at (and Master) SQL
  1. Make SQL Part of Your Work Day. ...
  2. Document Your SQL Learning Experience. ...
  3. Produce Reports using SQL for your business. ...
  4. Share Your SQL Knowledge with Others. ...
  5. Volunteer or Freelance on an SQL or Database Project. ...
  6. Learn SQL Early in Your Career.
1 Aug 2020

Can I learn SQL in a day? ›

Can I learn SQL by one month? You can learn SQL in a month. You only need a few days to learn the basics of this language. As soon as you start learning, you will see that SQL is based on logical syntax and the English language.

Is Python or SQL better? ›

SQL was not designed for higher-level data manipulation and transformation in terms of field application. In contrast, Python is a well-documented and high-level language with a dedicated data analysis library called 'Pandas,' which is why choosing between SQL and Python is a little complicated.

Are SQL queries hard to learn? ›

SQL is one of the easiest languages to learn, and the concepts, syntax, queries, and data formats are not only easy to remember but have name-dependent functions too. That is, you would not be confused in any function, concepts of tables, and picking up the various necessary RDBMS tools makes it even more exciting.

How many days SQL can learn? ›

It should take an average learner about two to three weeks to master the basic concepts of SQL and start working with SQL databases. But in order to start using them effectively in real-world scenarios, you'll need to become quite fluent; and that takes time.

Is SQL quick to learn? ›

How Quickly Can You Learn SQL? Generally speaking, SQL is an easy language to learn. If you understand programming and already know some other languages, you can learn SQL in a few weeks. If you're a beginner, completely new to programming, it can take longer.

Can beginner learn SQL? ›

Learning SQL doesn't require prior programming knowledge, so it's great for beginners.

What is the easiest SQL to learn? ›

Let's get straight to the point, here's the short answer:
  • SQLite is the easiest database for beginners to learn. ...
  • The easiest NoSQL database for beginners to learn is MongoDB. ...
  • SQLite is a database that should be learned first because of its lightweight and ease of use. ...
  • SQLite database is best for beginners.

Is SQL a good skill to learn? ›

If you're looking for your first job in data, it turns out knowing SQL is even more critical. For data analyst roles, SQL is again the most in-demand skill, listed in a whopping 61% of job posts. For data analyst roles on Indeed, SQL appears as follows: 1.7 times more than Python.

Can you learn SQL without coding? ›

Fortunately, you can start learning SQL even without coding experience. Watching YouTube tutorials and taking online courses are some of the best ways to master SQL. In this article, we'll introduce you to SQL and the benefits of learning it. Then, we'll show you how to learn this language as quickly as possible.

What are strong SQL skills? ›

Advanced SQL skills
  • Execution plans. Execution plans are a visual representation of how a database engine executes a query. ...
  • Backup databases. Creating a backup database is crucial in case your first one is corrupted or damaged in some way. ...
  • Using indexes to speed up SQL queries. ...
  • OLAP.
31 Mar 2020

Does SQL is a programming language? ›

Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them.

Why is SQL so powerful? ›

SQL has many mathematical functions to aid your data analysis. Ability to apply arithmetic functions such as absolute, degree to radian conversion and vice versa, floor, power, and sign. Ability to apply trigonometric and other functions such as exponential, log, square, and square root.

Videos

1. Practice SQL Interview Questions on LeetCode | Solve SQL Interview Questions (Practice SQL Queries)
(techTFQ)
2. Learn how to write SQL Queries(Practice Complex SQL Queries)
(techTFQ)
3. Top 9 SQL queries for interview | SQL Tutorial | Interview Question
(CodeEra)
4. Top 10 SQL interview Questions and Answers | Frequently asked SQL interview questions.
(Ankit Bansal)
5. Top SQL Queries for Interview | Must Do SQL Interviews Queries
(Learn Coding)
6. 25 SQL Query based Multiple Choice Questions with Answer ! 30 Second Challenge
(The Knowledge Adda)

Top Articles

You might also like

Latest Posts

Article information

Author: Frankie Dare

Last Updated: 09/06/2022

Views: 5956

Rating: 4.2 / 5 (53 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Frankie Dare

Birthday: 2000-01-27

Address: Suite 313 45115 Caridad Freeway, Port Barabaraville, MS 66713

Phone: +3769542039359

Job: Sales Manager

Hobby: Baton twirling, Stand-up comedy, Leather crafting, Rugby, tabletop games, Jigsaw puzzles, Air sports

Introduction: My name is Frankie Dare, I am a funny, beautiful, proud, fair, pleasant, cheerful, enthusiastic person who loves writing and wants to share my knowledge and understanding with you.