SQL Query Interview Questions For Practice – Part 1

In this SQL Tutorial series, we will learn all the Top SQL Query Interview Questions for Practice.

SQL Query Interview Questions are really difficult. That’s a tough fact. Because, not only you need to have a solid grasp of Database fundamentals, but also need to have a clear-cut understanding of things like- CRUD Operations, SQL Security, SQL Injections, SQL Transactions and Rollbacks (with PDF at the end) as well.

These SQL Query Interview Questions and Answers series will definitely help you and any Fresher or Intermediate level programmer to practice some Fundamental and Advanced level queries. It will help to crack a Technical Interview of Top MNCs like Amazon, Flipkart, Uber, and others.

Let’s begin : Step by Step –

Start Practicing Latest SQL Query Interview Questions and Answers


Steps : Practice SQL Query Interview Questions

For a quick start, follow the below steps –

[Step 1] To practice these SQL Interview Queries along with me, you need a database for eg: MySQL, Oracle or similar. Here is a direct link to MySQL Free Installer For Windows users. If you are using Ubuntu or macOS, just click on ‘Select Operating System’ > Choose the correct OS > and Download the database application on your machine.

[Step 2] Once your downloading gets finished > Install it with default settings > Provide the Login username and password, and verify it. Once you have provided the right credentials and connected with the database, you’ll get a notification of successfully connected.

[Step 3] Run the below SQL queries to create a new database, tables and insert the dummy data in it. You can copy the below code and run it in your SQL Editor / Command prompt or terminal as well-

Note*** We are using MySQL database version 8.0.18 on a Windows 10 machine, but all the below SQL Query will work fine on other databases with no or minimal changes. So, Let’s start running our SQL-


Initial : Structure & Data for Practice

We will take An analogy of Traders and their Profit till a particular date. I’ve assigned a Trader_Level according to their performance of selling products in their Zones.

So, copy the code below, paste and execute it, to generate the structure and dummy data, and start SQL query Interview questions for practice –

CREATE DATABASE SQL_INTERVIEW_DB;

SHOW DATABASES;

USE SQL_INTERVIEW_DB;


CREATE TABLE TRADER( 
   TRADER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    FIRST_NAME CHAR(25),
    LAST_NAME CHAR(25),
    INCOME INT(15),
    STARTING_DATE DATETIME,
    AREA_ZONE CHAR(25)
);


INSERT INTO TRADER (TRADER_ID, FIRST_NAME, LAST_NAME, INCOME,
STARTING_DATE, AREA_ZONE)  
VALUES 
    (001, 'DAVID', 'DHAWAN', 100000, '14-02-20 09.00.00', 'EAST_ZONE'),
    (002, 'SHUBHAM', 'SRIVASTAVA', 80000, '14-06-11 09.00.00', 'WEST_ZONE'),
    (003, 'ALEX', 'JOHNSON', 300000, '14-02-20 09.00.00', 'EAST_ZONE'),
    (004, 'JUSTIN', 'BEIBER', 500000, '14-02-20 09.00.00', 'WEST_ZONE'),
    (005, 'SHASHI', 'GUPTA', 500000, '14-06-11 09.00.00', 'WEST_ZONE'),
    (006, 'PARKER', 'MAX', 200000, '14-06-11 09.00.00', 'NORTH_ZONE'),
    (007, 'PETER', 'KHAN', 75000, '14-01-20 09.00.00', 'NORTH_ZONE'),
    (008, 'POOJA', 'CHAUHAN', 90000, '14-04-11 09.00.00', 'WEST_ZONE');

	
CREATE TABLE PROFIT (
   TRADER_REF_ID INT, 
   PROFIT_AMOUNT INT(10),
   PROFIT_DATE DATETIME,
   FOREIGN KEY (TRADER_REF_ID) REFERENCES TRADER(TRADER_ID) 
   ON DELETE CASCADE
);


INSERT INTO PROFIT (TRADER_REF_ID, PROFIT_AMOUNT, PROFIT_DATE)
VALUES
       (001, 5000, '16-02-20'),
       (002, 3000, '16-06-11'),
       (003, 4000, '16-02-20'),
       (001, 4500, '16-02-20'),
       (002, 3500, '16-06-11');


CREATE TABLE TRADER_LEVEL (
   TRADER_REF_ID INT, 
   THE_LEVEL CHAR(25),
   AFFECTED_FROM DATETIME,
   FOREIGN KEY (TRADER_REF_ID) REFERENCES TRADER(TRADER_ID)
   ON DELETE CASCADE
);


INSERT INTO TRADER_LEVEL (TRADER_REF_ID, THE_LEVEL, AFFECTED_FROM)
VALUES 
       (001, 'TOP_LEVEL', '2016-02-20 00:00:00'),
       (002, 'BEGINNER_LEVEL', '2016-06-11 00:00:00'),
       (008, 'BEGINNER_LEVEL', '2016-06-11 00:00:00'),
       (005, 'TOP_LEVEL', '2016-06-11 00:00:00'),
       (004, 'SECOND_TOP_LEVEL', '2016-06-11 00:00:00'),
       (007, 'BEGINNER_LEVEL', '2016-06-11 00:00:00'),
       (006, 'MID_LEVEL', '2016-06-11 00:00:00'),
       (003, 'MID_LEVEL', '2016-06-11 00:00:00');

 

After executing the above SQL Query, you’ll see the below results-

  • PROFIT‘s Table:
TRADER_REF_ID PROFIT_AMOUNT PROFIT_DATE
1 5000 2016-02-20 00:00:00
2 3000 2016-06-11 00:00:00
3 4000 2016-02-20 00:00:00
1 4500 2016-02-20 00:00:00
2 3500 2016-06-11 00:00:00
  • TRADER‘s Table
TRADER_ID FIRST_NAME LAST_NAME INCOME STARTING_DATE AREA_ZONE
1 DAVID DHAWAN 100000 2014-02-20 09:00:00 EAST_ZONE
2 SHUBHAM SRIVASTAVA 80000 2014-06-11 09:00:00 WEST_ZONE
3 ALEX JOHNSON 300000 2014-02-20 09:00:00 EAST_ZONE
4 JUSTIN BEIBER 500000 2014-02-20 09:00:00 WEST_ZONE
5 SHASHI GUPTA 500000 2014-06-11 09:00:00 WEST_ZONE
6 PARKER MAX 200000 2014-06-11 09:00:00 NORTH_ZONE
7 PETER KHAN 75000 2014-01-20 09:00:00 NORTH_ZONE
8 POOJA CHAUHAN 90000 2014-04-11 09:00:00 WEST_ZONE
  • TRADER_LEVEL‘s Table :
TRADER_REF_ID THE_LEVEL AFFECTED_FROM
1 TOP_LEVEL 2016-02-20 0:00:00
2 BEGINNER_LEVEL 2016-06-11 0:00:00
8 BEGINNER_LEVEL 2016-06-11 0:00:00
5 TOP_LEVEL 2016-06-11 0:00:00
4 SECOND_TOP_LEVEL 2016-06-11 0:00:00
7 BEGINNER_LEVEL 2016-06-11 0:00:00
6 MID_LEVEL 2016-06-11 0:00:00
3 MID_LEVEL 2016-06-11 0:00:00

SQL Query Interview Questions For Practice


Q1. Write an SQL query to fetch unique values of AREA_ZONE from TRADER’s table?

SELECT distinct AREA_ZONE 
FROM Trader;

 

Key Notes***
Inside a table, a column may contain duplicate values; but if we only want unique values from the list, the SELECT DISTINCT statement can be used to return only distinct (different) values.

The SQL SELECT command is one of the core commands used in CRUD (Create, Read, Update & Delete) operations. If you really want to be an expert in SQL, here’s Go from SQL Beginner to Expert course on Udemy using MySQL database, that you must try to be a lot better Database Analyst or Developer in this domain.

Q2. Write an SQL query to fetch FIRST_NAME from TRADER’s table using the alias TRADER_NAME?

A possible solution for this question-:
Select FIRST_NAME AS TRADER_NAME 
From Trader;

 

Key Notes***
SQL aliases are used to give temporary names to tables, columns in a table or other search results. Aliases are majorly used to give better names to columns for our better understanding. An alias is a temporary name that only exists until the query execution.

Q3. Write an SQL query to fetch FIRST_NAME from the TRADER table in upper case?

A possible solution for this question-:
SELECT upper(FIRST_NAME) 
FROM Trader;

 

Q4. Write an SQL query to print the FIRST_NAME from Trader table after removing white spaces from the right side?

A possible solution for this question-:
Select RTRIM(FIRST_NAME) 
From Trader;

 

Key Notes***
The RTRIM() function of SQL, removes trailing (ending) spaces from a string.

SQL Functions such as RTRIM() help to solve database tasks so easily.

You will hear about a number of useful functions such as RTRIM( ), TRUNC( ), EXTRACT( datatime ) and others from any Experienced Database Engineer. To learn more about these SQL functions, Triggers and other concepts, I would recommend to visit the official documentation or you can also try MySQL, Oracle, Microsoft SQL Server OR PostgreSQL databases related Top performing courses on Udemy. And, I’m sure, You will definitely learn a lot from them.

Q5. Write an SQL query that fetches the unique values of AREA_ZONE from the TRADER’s table and prints the length of each value?

SELECT DISTINCT length(Area_Zone) Length, Area_Zone 
FROM Trader;

 

Key Notes***
We can make use of the LENGTH() function, which returns the number of characters or say, the length of a string (in bytes).

Q6. Write a query that finds the position of the alphabet ‘a’ in the FIRST_NAME from the TRADER’s table where value is ‘Shubham’?

A possible solution for this question-:
Select INSTR(FIRST_NAME, 'A') as Matched_Position 
From Trader 
Where FIRST_NAME = 'SHUBHAM';

 

Key Notes***
The INSTR() function performs its operations in case-sensitive mode. Here, we have also used an alias ‘Matched_Position’ for readable results.

Q7. Write an SQL query to print the AREA_ZONE from the TRADER’s table after removing white spaces from the left side?

A possible solution for this SQL query interview question-
Select LTRIM(AREA_ZONE) as TRIMMED_ZONE_NAME 
From TRADER;

 

Key Notes***
The LTRIM() function removes spaces from a string that come in the beginning of a string (or text).

Q8. Write an SQL query to print the FIRST_NAME from the TRADER table after replacing ‘a’ with ‘A’?

A possible solution for this question-:
SELECT Trader_Id, 
REPLACE(FIRST_NAME,'A','a') AS Replaced_a_WITH_A 
FROM Trader;

 

Key Notes***
The REPLACE() function replaces all the one or multiple occurrences of a character or substring within a string, with a new substring that you provide as a third argument.Here in the above query, we have provided ‘a’ as the third argument which replaced the second argument ‘A’ in all the values of the specified column name ‘FIRST_NAME’ (that was the First Argument).

Remember, the REPLACE(_,‘_’,‘_’) function is a case-sensitive replacement function. The above SQL Query will update your TRADER’s table, so to make it’s data as before this query, first drop the TRADER’s table and re-insert the records same as when entering the sample data (provided upper in Sample data).

Q9. Write an SQL query to print the FIRST_NAME and LAST_NAME from TRADER table into a single column FULL_NAME using a space as a separator?

A possible solution for this SQL query interview question-:
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS 'FULL_NAME' 
FROM Trader;

 

Key Notes***
The CONCAT() function is used to combine (or concatenate) two or more expressions together.

Q10. Write an SQL query to print all Trader details from the Trader table order by their INCOME Ascending?

A possible solution for this question-:
SELECT * 
FROM Trader 
ORDER BY Income ASC;

 

Key Notes***
If we need to sort the data returned in ascending order, we make use of the ASC command in almost all the databases.

Q11. Write an SQL query to print all Trader details from the Trader table order by AREA_ZONE descending, INCOME Ascending?

A possible solution for this question-:
SELECT Trader_id, 
       First_name, 
       Last_name, 
       Area_zone, 
       Income 
FROM Trader 
ORDER  BY Area_zone DESC, 
       Income ASC;

 

Key Notes***
By this query, you will get records with descending AREA_ZONE(s), but if 2 or more records have the same AREA_ZONE(s) , the records will display in a lower to higher INCOME manner.

SQL ORDER BY command sorts the records in Ascending OR Descending Order.
ORDER BY is a mostly used and super powerful SQL command for sorting thousands of records. You must know, How it is used with the ASC and DESC keywords to control the sorting order. ORDER BY command is supported by all the Top performing databases including MySQL, Oracle, Microsoft SQL, PostgreSQL and SQLite as well, and I would recommend you to learn more about it from these courses or their Official documentation to be better in SQL.

Q12. Write an SQL query to print details for Traders with the first name as “Shubham” and “Peter” from TRADER’s table?

A possible solution for this question-:
SELECT * 
FROM   trader 
WHERE  first_name IN ( 'Shubham', 'Peter' ); 

 

Key Notes***
The IN operator allows us to provide multiple values in it (similar to a WHERE clause). You can think of it as, the IN operator is a shorthand for a couple of OR conditions.

Q13. Write an SQL query to print details of Traders excluding those whose first names are “Parker” and “Peter”?

A possible solution for this question-:
SELECT * 
FROM   trader 
WHERE  first_name NOT IN ( 'Parker', 'Peter' );

 

Key Notes***
The NOT keyword or command is used when we only want to include rows where a condition is not true.

Q14. Write an SQL query to print details of Traders who belong to “West” AREA_ZONE?

A possible solution for this question-:
SELECT trader_id AS ID, 
       first_name, 
       income, 
       area_zone 
FROM   trader 
WHERE  area_zone LIKE 'West_%';

 

Key Notes***
I’ve used the LIKE operator in a WHERE clause to search for a specified pattern in the column ‘AREA_ZONE’.

  • % : The percent sign indicates zero, one, or multiple characters.
  • _ : The underscore symbol indicates a single character.

Q15. Write an SQL query to print details of the Traders whose FIRST_NAME contains ‘a’?

A possible solution for this question-:
SELECT * 
FROM   trader 
WHERE  first_name LIKE '%a%';


SELECT trader_id AS ID,
       first_name,
       income
FROM   trader
WHERE  first_name LIKE '%a%';

 

Key Notes***
We can make use of wildcard characters such as percentage sign ‘%’, or underscore ‘_’. We put these wildcards before and after a character or substring to search according to a pattern.

What Next? 

So far, we have learned and practiced a number of Fundamental SQL Query Interview Questions with useful Key notes. I had also provided some useful Official documentation resources and Direct links for further in-depth learnings.

A lot of other concepts such as Inner-Outer Joins, Triggers, Primary Keys – Foriegn Keys covered in the Next part of this series. But, to understand all those SQL Queries, you must have a better command in SQL with any Database.

Here are some better resources for you on Udemy, that I’ve picked up based on important factors such as – Latest concepts, Reviews of happy students, Hours of lectures and more. So, go ahead, choose anyone and start Learning in-depth :

Better Resources (100%) :

 

SQL Interview (Read Next) :

 

Found Helpful? Do Share It!

I’ve put so much effort into writing this article to provide value to the programmers. If you found this helpful? Smash the Yellow icon, Copy its Link, and share with your best Social media friends, It’ll be very helpful for all. Thanks!!!

Sharing is Caring conclusion-and-sharing-request

Leave a Comment