SQL Practice Questions | Structured Query Language Questions (2022)

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) Learn how to write SQL Queries(Practice Complex SQL Queries)

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) Practice SQL Interview Questions on LeetCode | Solve SQL Interview Questions (Practice 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) 25 SQL Query based Multiple Choice Questions with Answer ! 30 Second Challenge

# 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 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.
Nov 19, 2020

How do you solve a query question? ›

Solving SQL Interview Queries - YouTube

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.
Jan 29, 2019

How can I improve MySQL 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.
Feb 17, 2021

Is Hackerrank good for SQL? ›

I've recently started the SQL problems on hackerrank and I've been happy with them so far. They've been a great way to get more practice in with SQL and learn a bit more too!

How long does it take to learn SQL? ›

How Long Does it Take to Learn SQL? 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 practice PL SQL at home? ›

Text Editor
  1. Type your code in a text editor, like Notepad, Notepad+, or EditPlus, etc.
  2. Save the file with the . sql extension in the home directory.
  3. Launch the SQL*Plus command prompt from the directory where you created your PL/SQL file.
  4. Type @file_name at the SQL*Plus command prompt to execute your program.

Where can I practice SQL offline? ›

You can practice SQL on HackerRank.
...
Here are some excellent resources to learn SQL online, hope can help you too:
  • Stanford - Introduction to Databases (Coursera)
  • SQL exercises.
  • SQLZOO.
  • Database eLearning.
  • PostgreSQL tutorial.
  • PostgreSQL exercises.
  • The Schemaverse is a game, I never played it so I can't tell much.
  • SQLite.

What are the 5 basic SQL commands? ›

Some of The Most Important SQL Commands
  • SELECT - extracts data from a database.
  • UPDATE - updates data in a database.
  • DELETE - deletes data from a database.
  • INSERT INTO - inserts new data into a database.
  • CREATE DATABASE - creates a new database.
  • ALTER DATABASE - modifies a database.
  • CREATE TABLE - creates a new table.

What are the basic SQL queries asked in interview? ›

SQL Interview Questions
  • What is Database? ...
  • What is DBMS? ...
  • What is RDBMS? ...
  • What is SQL? ...
  • What is the difference between SQL and MySQL? ...
  • What are Tables and Fields? ...
  • What are Constraints in SQL? ...
  • What is a Primary Key?
Jun 14, 2022

How can get second highest salary in SQL Server? ›

The SQL query to calculate second highest salary in database table name as Emp
  1. SQL> select min(salary) from.
  2. (select distinct salary from emp order by salary desc)
  3. where rownum < 3;
  4. In order to calculate the second highest salary use rownum < 3.
  5. In order to calculate the third highest salary use rownum < 4.

Can I learn SQL in a 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.

Where can I study SQL for free? ›

TAKE THE QUIZ!
  • Intro to SQL: Querying and managing data via Khan Academy. ...
  • Codecademy's free SQL Class. ...
  • Developing SQL Databases via EdX. ...
  • SQLcourse.com. ...
  • W3Schools SQL Class. ...
  • FreeCodeCamp's SQL Course. ...
  • SQLZoo. ...
  • Introduction to Computer Science via EdX.

Can I learn SQL without any programming knowledge? ›

SQL is easy to learn

Because SQL query syntax relies on common English words, even if you have no programming experience you can easily understand how to use it. But it may take somewhat longer to become proficient than if you had come in with some programming experience.

Read 50 most frequently asked SQL query questions and improve your SQL skills. To get you started, we've answered with appropriate SQL queries.

50 SQL Query Questions Sample Table – Worker WORKER_IDFIRST_NAMELAST_NAMESALARYJOINING_DATEDEPARTMENT 001MonikaArora1000002014-02-20 09:00:00HR002NiharikaVerma800002014-06-11 09:00:00Admin003VishalSinghal3000002014-02-20 09:00:00HR004AmitabhSingh5000002014-02-20 09:00:00Admin005VivekBhati5000002014-06-11 09:00:00Admin006VipulDiwan2000002014-06-11 09:00:00Account007SatishKumar750002014-01-20 09:00:00Account008GeetikaChauhan900002014-04-11 09:00:00Admin Sample Table – Bonus WORKER_REF_IDBONUS_DATEBONUS_AMOUNT 12016-02-20 00:00:00500022016-06-11 00:00:00300032016-02-20 00:00:00400012016-02-20 00:00:00450022016-06-11 00:00:003500 Sample Table – Title WORKER_REF_IDWORKER_TITLEAFFECTED_FROM 1Manager2016-02-20 00:00:002Executive2016-06-11 00:00:008Executive2016-06-11 00:00:005Manager2016-06-11 00:00:004Asst.. Write an SQL query to fetch “FIRST_NAME” from Worker table using the alias name as .. Write an SQL query to print the first three characters of FIRST_NAME from Worker table.. Write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length.. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending and DEPARTMENT Descending.. Write an SQL query to print details for Workers with the first name as “Vipul” and “Satish” from Worker table.. Write an SQL query to print details of workers excluding first names, “Vipul” and “Satish” from Worker table.. Write an SQL query to clone a new table from another table.. Write an SQL query to show records from one table that another table does not have.

These questions and example solutions will keep your skills sharp.

To complement SQL training resources ( PGExercises , LeetCode , HackerRank , Mode ) available on the web, I’ve compiled a list of my favorite questions that you can tackle by hand or solve with a PostgreSQL instance.. The questions outlined below include example solutions confirmed to work in PostgreSQL.. Write a query to return the start and end dates of each project, and the number of days it took to complete.. Note that there is no MEDIAN() function in SQL!. From the following user activity table, write a query to return the fraction of users who are retained (show some activity) a given number of days after joining.

Using SQL effectively is a critical skill for any data scientist & one of the most tested in interviews. In our own attempts at improving…

Given two tables below, write a query to display the comparison result (higher/lower/same) of the average salary of employees in a department to the company’s average salary.. Now that we have the two tables joined, consider the intermediary columns needed for our final table.. Let’s create a temporary table and join the necessary columns from the orders and items tables and test with the 1st day of the week, Monday.. Write an SQL query to compute moving average of how much customer paid in a 7 days window (current day + 6 days before) .. First let’s use a CROSS JOIN, which is a type of join that generates a paired combination of each row of the first table with each row of the second table.

SQL Practice Exercises with Solutions : In my previous article i have given the different examples of SQL as well as most important complex sql queries for interview purpose.I would like to combine all those examples and want to make one best article on SQL Practice Exercises with solutions.My main purpose writing this article on

In my previous article i have given the different examples of SQL as well as most important complex sql queries for interview purpose .I would like to combine all those examples and want to make one best article on SQL Practice Exercises with solutions.My main purpose writing this article on SQL Practice Exercises with solution is to get idea about different SQL real world examples as well as user can easily implement it in day to day life.These are the scenarios which are useful for real world industry.. Create table Student_Replica as Select * from Student;. Create table Student_Replica as Select * from Student where 1=2;. There are some situations where user needs to fetch some last records from the table.The following query will fetch the last records from the table.. There are so many examples where user needs to fetch the highest values from the specified table.Following query will fetch the first 5 highest marks from student table.. Select * from Student where rowid = select max(rowid) from Student;. So User needs to fetch the maximum of row id record from Student table.. Example 14 : What is query to display odd records from Student table.. This query is also most important and most used SQL Practice Exercises with Solutions to display odd as well as display Even records from the specific table,. Example 15 : What is query to display even records from Student table.. This query is also most important and most used SQL Practice Exercises with Solutions to display even as well as display odd records from the specific table,. These are some most important SQL Practice Exercises with Solution.I will update the examples of SQL Practice Exercises with Solutions on weekly basis so that user will get the best SQL queries for practice.Hope you like this article on SQL Practice Exercises with Solutions.If you like this article or if you have any questions or queries regarding the same kindly comment in to comment section.

Are you looking for places to practice SQL online? This list contains many different websites that have SQL practice functionality.

Some of them are a simple textbox where you enter an SQL SELECT query on a predefined database, and others allow you to create tables and data to go along with the queries.. SQL Fiddle is a popular site for quickly generating sample databases and writing SQL code on them.. Another great thing about SQL Fiddle is that it supports different versions of SQL.. So while it doesn’t support Oracle or SQL Server, the versions of MySQL and PostgreSQL are newer than SQL Fiddle.. It has a sample database feature, which shows AdventureWorks for SQL Server, and there are many other SQL versions supported:. There are many different SQL challenges on the site, and each of them has a sample data set, a question to answer with SQL, a list of the submissions, a leaderboard, and discussions.. Exercises are broken down by difficulty and you’re able to write the SQL in DB2, MySQL, Oracle, and SQL Server.. SQL Bolt is a site that teaches SQL as well as including several exercises on each concept.. Oracle Live SQL is a tool created by Oracle that lets you write and run SQL code against an online Oracle database .. It’s not as fully-featured as some other examples here, as it doesn’t have an SQL editor, but it can help you improve your SQL by completing queries.. They include tutorials on SQL and many SQL exercises.. It’s similar to SQL Fiddle and DB Fiddle, where it has an SQL editor and a results panel.. Each exercise includes a sample database with ERD, a question to solve, and an editor that lets you enter SQL and run it to see the results.. Once you’re in, you can see the exercise, sample data, and write SQL queries to solve the questions.. SQLTest is an online SQL practice tool that lets you create tables, populate tables, write SQL queries on them and see the results.

This article is a guide on the Top 30 SQL Query Interview Questions in 2022 which will help you ace interviews for the role of Database Administrator.

In this article on SQL Query Interview Questions, I will discuss a few queries which you must practice to become a Database Administrator and will also help you ace your interviews.. Top SQL Query Interview Questions. For, your better understanding, I will be considering the following tables to write queries.. You can write a query as follows in SQL Server:. You can write a query as follows in MySQL :. By using the LIMIT command in MySQL:. To retrieve the even records from a table, you have to use the MOD() function as follows:. Similarly, to retrieve the odd records from a table, you can write a query as follows:. To retrieve two minimum salaries, you can write a query as below:. So this brings us to the end of the SQL Query Interview Questions article.. Check out this MySQL DBA Certification Training by Edureka, a trusted online learning company with a network o f more than 250,000 satisfied learners spread across the globe.This course trains you on the core concepts & advanced tools and techniques to manage data and administer the MySQL Database.. Read Article

Looking for SQL Interview Questions & Answers to prepare? We have an ultimate guide of knowledge-based SQL Interview Questions and Answers.

Structured Query Language (SQL) is a popular and extensively used programming language for managing, manipulating, and querying data in relational databases.. Now that you have a basic understanding of what SQL queries are and how they function let’s check out twenty SQL query interview questions!. Before we get started with the SQL query interview questions, check out these reference tables – our queries will be based on these tables.. One of the most common question in any SQL query interview questions for experienced professionals guide with SQL query examples with answers.. We hope that these queries give you a fair idea of the standard SQL query pattern and how to create SQL queries.

Here's a selection of the most useful SQL queries every beginner must practice. This article will not only provide you with the answers but also the explanation to each query.

Calculating average price using AVG() function and storing it in a new column named average_price .. Calculating average price using AVG() function and storing it in a new column named average_price .. Arranging the result w.r.t AVG(price) in descending order using ORDER BY and DESC functions.. Calculating average price using AVG() function and storing it in a new column named average_price .. Calculating average price using AVG() function and storing it in a new column named average_price .. Calculating average price using AVG() function and storing it in a new column named average_price .. Calculating average price using AVG() function and storing it in a new column named average_price .

Hello friends! in this post, we will see some of the most common SQL queries asked in interviews. Whether you are a DBA, developer, tester, or data analyst, these SQL query interview questions and answers are going to help you. In fact, I have been asked most of these questions during interviews in the different phases of my career. If you

SQL Query to fetch records that are present in one table but not in another table.. Write an SQL query to fetch only odd rows from the table.. Write an SQL query to fetch the different projects available from the EmployeeSalary table.. Write an SQL query to fetch the count of employees working in project ‘P1’.. Write an SQL query to fetch all those employees who work on Project other than P1.. Write an SQL query to fetch all the EmpIds which are present in either of the tables – ‘EmployeeDetails’ and ‘EmployeeSalary’.. Write an SQL query to fetch common records between two tables.. Write an SQL query to fetch records that are present in one table but not in another table.. Write an SQL query to fetch the EmpIds that are present in both the tables – ‘EmployeeDetails’ and ‘EmployeeSalary.. Write an SQL query to fetch employee names having a salary greater than or equal to 5000 and less than or equal to 10000.. Write an SQL query to fetch all the Employees details from EmployeeDetails table who joined in the Year 2020.. Write an SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.. These questions cover SQL queries on advanced SQL JOIN concepts, fetching duplicate rows, odd and even rows, nth highest salary, etc.. Write an SQL query to fetch all the Employees who are also managers from the EmployeeDetails table.. Write an SQL query to fetch only even rows from the table.

You are already done with the basics of SQL on Day 10. No matter how good you are at it, there are some tricky questions which might confuse you in its trap. Below are some not so difficult but confusing questions. Try to solve it on your own before hoping on the solution. For the [&hellip;]

The ranking result will have no meaning, as the rank will be done according to Salary values per each partition, and the data will be partitioned according to the Salary values.. And due to the fact that each partition will have rows with the same Salary values, the rows with the same Salary values in the same partition will be ranked with the value equal to 1.. SELECT *, RANK() OVER (PARTITION BY Salary ORDER BY Salary) as Row_Num. Select MAX(Salary) from Employee Where Salary NOT IN (SELECT MAX(Salary) from Employee). -SELECT * FROM employee WHERE salary = (select MIN(salary) from employee);. – SELECT *FROM employeeWHERE salary = (SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee). – SELECT *FROM employeeWHERE salary = (SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee). – SELECT DISTINCT (salary) FROM emp e1 where 3 = (SELECT COUNT(DISTINCT salary) FROM emp e2 WHERE e1.sal >= e2.sal);. -SELECT DISTINCT salary FROM emp a WHERE 3 >= (SELECT COUNT(DISTINCT salary) FROM emp b WHERE a.salary >= b.salary);. SELECT CURDATE(); à It returns the current date(MySQL) SELECT NOW(); à It returns the current date and time(MySQL) SELECT getdate(); à It returns the current date and time(SQL Server Query) SELECT SYSDATE FROM DUAL à This Oracle query returns the current date and time

Here's a selection of the most useful SQL queries every beginner must practice. This article will not only provide you with the answers but also the explanation to each query.

We get the above result w.r.t some conditions using the WHERE() clause, i.e.:-. We extract the number of ids using count(id) , condition and paint_color .. Fixing paint_color not equal to " ". Fixing condition not equal to " ". Setting price between 1 to 655000. We want the above result w.r.t the following conditions using the WHERE clause:. Fixing condition not equal to " ". Fixing type not equal to " ". Setting price between 1 to 655000. We want the above result w.r.t the following conditions using the WHERE clause:. We get the above result w.r.t some conditions using the WHERE() clause, i.e.:-. Fixing type not equal to " ". Fixing description not equal to " ". Setting price between 1 to 655000. Fixing condition not equal to " ". Fixing description not equal to " ". Setting price between 1 to 655000. In the end, we group the results w.r.t condition using the GROUP BY command.. Extracting average_price and corresponding condition , w.r.t.. Fixing condition not equal to " ". Fixing type equal to "sedan". Setting price between 1 to 655000

Learn the kinds of interview questions you can expect for an SQL-related job. Discover our courses to help you prepare for the technical interview.

Keep reading to learn the kinds of questions to expect during a SQL interview, how to answer them, and how our courses can help you prepare.. During the interview process, you’ll likely have two different interviews — a technical interview and a behavioral interview.. Describe a time when you had to learn a new programming language.. Talk about a time when you had to incorporate feedback.. Something you learned from your team member Something you learned from the experience, in general A way in which your thinking shifted based on what you learned from the interaction. To answer this question, you’ll want to specifically outline indexes such as:. Keep in mind that the majority of SQL databases will make the best index decision for you — based on your use case.. The shared columns between two tables will be eligible for this kind of join.. Here’s a way to answer it.

A lot of the SQL interview questions you'll find on the web are generic: "What is SQL?" You'll never be asked that. We've got real questions to help you prep.

If you’re looking for a job in data, chances are you’re going to have to answer some SQL interview questions, or complete some kind of SQL test.. We dug into the data in depth in this post about why you should learn SQL , but the short version is this: more than half of all data analyst, data scientist, and data engineer jobs in 2021 list SQL as a requirement.. In-person (or video) interview where you’re asked SQL questions or given SQL problems to solve.. Much more likely: the SQL interview questions you’ll face will be asking you to solve real problems with SQL, or asking you to answer trickier questions that test your working knowledge.. The SQL engine fetches the data from the tables ( FROM and INNER JOIN ) Filters it ( WHERE ) Aggregates the data ( GROUP BY ) Filters the aggregated data ( HAVING ) Selects the columns and expressions to display ( SELECT ) Orders the remaining data ( ORDER BY ) Limits the results ( LIMIT )

Learn Sql joins interview for preparing your next coding interviews, Register for our Free Webinar to know how to crack FAANG companies Interviews. Signup Today.

SQL Joins interview questions are among the most commonly asked in SQL Developer interviews.. Topics to Prepare for Your SQL Joins Interview Top SQL Joins Interview Questions and Answers SQL Query Interview Questions on Joins and Subqueries SQL Joins Interview Questions for Experienced Developers FAQs on SQL Interviews. SQL Joins questions asked in FAANG interviews are based on the types of SQL Joins and their applications.. An SQL Join is a clause or statement used to combine two or more tables based on common fields present in the tables.. A Cross join returns all possible combinations of all rows of both tables, resulting in a cartesian product between two tables.. How will you join more than two tables?. Here are some SQL Join interview questions on Joins and Subqueries:. FROM tbl_samplejoin1 a INNER JOIN tbl_samplejoin2 b. Questions asked in SQL interviews are based on SQL Join topics - Joining Tables & Queries, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, Cross Join, Union, and Union All.. The most commonly asked SQL Joins interview questions are: What are SQL Joins?

Want to learn SQL or practice SQL online? We’ve rounded up the top ways to practice SQL so you can go from learning to earning!

Learning SQL, of course, is no different.. LearnSQL.com Practice Course Wanting to get some hands-on experience with SQL is great, but first you have to know your way around a database and some basic SQL queries .. When you’re learning a language, learning to drive, or learning to dance, it’s always better, faster, and more fun when you start applying your knowledge in practice.. It is very much the same with practicing SQL .. A free site that allows you to test and save queries in several different kinds of SQL, SQL Fiddle is a perfect way to play around with the queries you have learned to date.. For SQL rookies, the best feature of the site is the stacks of exercises , undeniably making this a good place to practice SQL online.. Be warned, though: Stack Overflow is not a place for SQL beginners to ask basic questions.. If you’re looking to practice what you’ve learned in the LearnSQL.com practice course , the DB Fiddle “Load Example” feature is a great place to start.. GitHub is a great (albeit massive and somewhat overwhelming) place to host your own projects as you get started with SQL, and just like Stack Overflow, there are plenty of opportunities to make the most of the SQL hive mind.. Testdome helps you practice SQL skills online with a huge range of practice interview questions .. There are a million ways to find sample databases to use for practicing SQL (that’s a whole other blog post for another day), but if you’re going to take a look at one, make it Data.gov .. Now, Grasshopper–it’s time to test your SQL skills in the real world!

Here are ten common questions you might hear during an advanced SQL interview. We also have the answers for you.

There can be only one clustered index per table, since the table can only be ordered in one way.. A non-clustered index simply points to the data, with the order of the data in the index not being the same as the physical order of the actual data.. Question 2: What’s the code for creating an index?. This will create the clustered index with the name CL_nin , on the table employee and the column nin .. Run this code and you will create a non-clustered index named NCL_surname on the column surname in the table employee .. The code uses the RANK() window function.. The basic meaning behind JOIN is that it will return data from one table when that data is equal to the data in a second table.. (Uses data from the table production_year .). This table is joined with the table production_year using the column year_id , which is a connection between the cars and the production_year tables.. Then, using the RIGHT() function, we’ve instructed the query to take the strings in the column product and return the last four characters starting from the right.

SQL interview questions for Freshers and Experienced: Here is a list of the most popular SQL Server interview questions and their answers likely to be asked during the SQL interview basic to advance level SQL questions depending on their experience.

RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table.. There can be many unique constraint defined per table, but only one Primary key constraint defined per table.. A foreign key is one table which can be related to the primary key of another table.. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.. This is a keyword used to query data from more tables based on the relationship between the fields of the tables.. Right join return rows which are common between the tables and all rows of Right hand side table.. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.. Left join return rows which are common between the tables and all rows of Left hand side table.. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.. It is also process of introducing redundancy into a table by incorporating data from the related tables.. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.. A view is a virtual table which consists of a subset of data contained in a table.. Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.. Cross join defines as Cartesian product where number of rows in the first table multiplied by number of rows in the second table.

A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

1.What is SQL?. It is a language used to interact with the database, i.e to create a database, to create a table in the database, to retrieve data or update a table in the database, etc.. For example – we can execute queries, we can insert records into a table, we can update records, we can create a database, we can create a table, we can delete a table, etc.. data in the database.. What is the difference between BETWEEN and IN operators in SQL?BETWEEN The BETWEEN operator is used to fetch rows based on a range of values.. Insert data or rows in a database Delete data from the database Retrieve or fetch data Update data in a database.. O_IDORDER_NOC_ID122533233253345212485321C_IDNAMEADDRESS1RAMESHDELHI2SURESHNOIDA3DHARMESHGURGAONAs we can see clearly, that the field C_ID in the Orders table is the primary key in the Customers’ table, i.e. it uniquely identifies each row in the Customers table.. What are the different operators available in SQL?. There are three operators available in SQL namely:. Within the create function command you must define the table structure that is being returned.. Aggregate functions: These functions are used to do operations from the values of the column and a single value is returned.. Define SQL Order by the statement?. Define BETWEEN statements in SQL?. What is the need for group functions in SQL?

Complex SQL Queries,Complex SQL queries for Practice,SQL Queries for Practice,Complex Queries in SQL,complex sql queries,Complex SQL,Complex sql interview

Select distinct Salary from Employee e1 where 2=Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary ;. select min(salary)from(select distinct salary from emp order by salary desc)where rownum<=2;. Select * from Employee a where rowid <>( select max(rowid) from Employee b where a.Employee_num=b.Employee_num);. Select * from Employee where Rowid= select min(Rowid) from Employee;. Select * from Employee where Rowid= select max(Rowid) from Employee;. Complex SQL Queries 6.What is Query to display first 5 Records from Employee table?. select * from (Select * from Employee e order by rowid desc) where rownum <=5;. select distinct salary from employee a where 3 >= (select count(distinct salary) from employee b where a.salary <= b.salary) order by a.salary desc;. select min(salary)from(select distinct salary from emp order by salary desc)where rownum<=3;. * from Employee E where rownum<=(Select count(*)/2) from Employee);. Select * from Employee where Dept_no Not in(Select Department_no from Department);. select * from Employee a where rowid = (select max(rowid) from Employee b where a.Employee_no=b.Employee_no);. Select e.employee_name,m.employee name from Employee e,Employee m where e.Employee_id=m.Manager_id;

Explore easy, medium and hard SQL questions for data analysts. Learn more about the type of questions and what gets asked in data analyst SQL interviews.

To [answer this question], you need the name of the department to be associated with each employee in the employees table, to understand which department each employee is a part of.. The “department_id” field in the employees table is associated with the “id” field in the departments table.. You can call the “department_id” a foreign key because it is a column that references the primary key of another table, which in this case is the “id” field in the departments table.. Based on this shared field you can join both tables using INNER JOIN to associate the department name to their employees.. Since our goal in this problem is to pull the last transaction from each day, you want to group the transactions by the day they occurred and create a chronological ordering within each day from which you can retrieve the latest transaction.. To accomplish the task of grouping and ordering, create a modified version of the bank_transactions table with an added column denoting the chronological ordering of transactions within each day.. In this problem, you are given two tables: An employees table and a departments table.. ColumnsType id INTEGER first_name VARCHAR last_name VARCHAR salary INTEGER department_id INTEGERColumnsType id INTEGER name VARCHARColumnType percentage_over_100k FLOAT department_name VARCHAR number of employees INTEGERFirst, break down the question to understand what it’s asking.. From here, think about how you would associate employees with their department, calculate and display the percentage of employees making over $100,000 a year, and order those results to provide an answer to the original question.. Given that this problem is referencing one table with only two columns, you have to self-reference different creations of the same table.. ColumnsType id INTEGER user_id INTEGER created_at DATETIME product_id INTEGER quantity INTEGERColumnsType user_id INTEGER created_at DATETIME product_id INTEGER quantity INTEGERHere is a helpful hint for this question: You need an indicator of which purchase was the third by a specific user.. For this problem, you are given an employees and a departments table.. You can do an inner join on all 3 tables since the question states that the flight_purchases table does not have entries of flights or seats that do not exist.. Here’s a hint for this question to get you started: If you are sampling from this table and you want to specifically sample every fourth value, you will probably have to use a window function.. More Context: You are given two tables: the first is a users table with demographic information and the neighborhoods they live in, and the second is a neighborhoods table.

Videos

1. Solve SQL Queries for Practice | MASTER IN SQL | SQL INTERVIEW QUESTIONS
(Crack Concepts)
2. Top 9 SQL queries for interview | SQL Tutorial | Interview Question
(CodeEra)
3. SQL Interview Preparation :- Real Time Scenario Question (Structured Query Language)
(The Scholar)
4. Practice Writing SQL Queries using Real Dataset(Practice Complex SQL Queries)
(techTFQ)
5. Top 65 SQL Interview Questions and Answers | SQL Interview Preparation | SQL Training | Edureka
(edureka!)
6. TOP 23 SQL INTERVIEW QUESTIONS & ANSWERS! (SQL Interview Tips + How to PASS an SQL interview!)
(CareerVidz)

You might also like

Latest Posts

Article information

Author: Dean Jakubowski Ret

Last Updated: 08/30/2022

Views: 5405

Rating: 5 / 5 (50 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Dean Jakubowski Ret

Birthday: 1996-05-10

Address: Apt. 425 4346 Santiago Islands, Shariside, AK 38830-1874

Phone: +96313309894162

Job: Legacy Sales Designer

Hobby: Baseball, Wood carving, Candle making, Jigsaw puzzles, Lacemaking, Parkour, Drawing

Introduction: My name is Dean Jakubowski Ret, I am a enthusiastic, friendly, homely, handsome, zealous, brainy, elegant person who loves writing and wants to share my knowledge and understanding with you.