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 –
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;
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?
Select FIRST_NAME AS TRADER_NAME From Trader;
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?
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?
Select RTRIM(FIRST_NAME) From Trader;
The RTRIM() function of SQL, removes trailing (ending) spaces from a string.
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;
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’?
Select INSTR(FIRST_NAME, 'A') as Matched_Position From Trader Where FIRST_NAME = 'SHUBHAM';
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?
Select LTRIM(AREA_ZONE) as TRIMMED_ZONE_NAME From TRADER;
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’?
SELECT Trader_Id, REPLACE(FIRST_NAME,'A','a') AS Replaced_a_WITH_A FROM Trader;
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).
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?
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS 'FULL_NAME' FROM Trader;
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?
SELECT * FROM Trader ORDER BY Income ASC;
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?
SELECT Trader_id, First_name, Last_name, Area_zone, Income FROM Trader ORDER BY Area_zone DESC, Income ASC;
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.
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?
SELECT * FROM trader WHERE first_name IN ( 'Shubham', 'Peter' );
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”?
SELECT * FROM trader WHERE first_name NOT IN ( 'Parker', 'Peter' );
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?
SELECT trader_id AS ID, first_name, income, area_zone FROM trader WHERE area_zone LIKE 'West_%';
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’?
SELECT * FROM trader WHERE first_name LIKE '%a%'; SELECT trader_id AS ID, first_name, income FROM trader WHERE first_name LIKE '%a%';
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%) :
1) MySQL : Go from SQL Beginner to Expert
2) Oracle : 11g/12c version – Become Junior DBA
3) Microsoft SQL for Beginners (Free till Feb, 21)
4) PostgreSQL : Complete SQL from Zero to Hero
6) MongoDB : The Developer’s Guide 2121
7) ElasticSearch : Learn from Scratch
SQL Interview (Read Next) :
1) SQL Query Interview Questions For Practice (PDF)
2) SQL Query Interview Questions For Practice (Part 2)
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!!!