SQL Query Interview Questions – Practice All Types of SQL Queries

FREE Online Courses: Elevate Your Skills, Zero Cost Attached - Enroll Now!

1. Most Asked SQL Query Interview Questions

Earlier we have discussed the different types of SQL questions asked in the interview. Today, we will focus on a particular type and that is SQL Query Interview Questions. So, in this blog, you will find the interview questions based on complex SQL queries that you can practice online. First, we have provided you with a sample table which you will prepare through the SQL queries. Also, we have given how to prepare these SQL tables through different queries. And finally, you will see the most asked SQL Query interview Questions with Answers which will help you to crack your upcoming SQL interview.

So, let’s start the tutorial by creating a table in SQL.

SQL Query Interview Questions - Practice All Type of SQL Queries

SQL Query Interview Questions – Practice All Type of SQL Queries

2. How to Create Table in SQL?

Below are the tables which you will create through the SQL queries.

WORKER_IDFIRST_NAMELAST_NAMESALARYJOINING_DATEDEPARTMENT
001NIHARIKA ARORA200002013-02-25 09:00:00HR
002AYUSHIGURONDIA50002015-02-10 09:00:00ADMIN
003PRIYANSHA CHOUKSEY250002014-05-16 09:00:00HR
004APARNA DESHPANDE80002016-12-20 09:00:00ADMIN
005SHAFALIJAIN210002015-08-29 09:00:00ADMIN
006SUCHITA JOSHI200002017-02-12 09:00:00ACCOUNT
007SHUBHIMISHRA150002018-03-23 09:00:00ADMIN
008DEVYANI PATIDAR180002014-05-02 09:00:00ACCOUNT

TABLE- BONUS

WORKER_REF_IDBONUS_DATEBONUS_AMOUNT
12015-04-20 00:00:005000
22015-08-11 00:00:003000
32015-04-20 00:00:004000
12015-04-20 00:00:004500
22015-08-11 00:00:003500

TABLE- TITLE

WORKER_REF_IDWORKER_TITLEAFFECTED_FROM
1Manager2016-02-20 00:00:00
2Executive2016-06-11 00:00:00
8Executive2016-06-11 00:00:00
5Manager2016-06-11 00:00:00
4Asst. Manager2016-06-11 00:00:00
7Executive2016-06-11 00:00:00
6Lead2016-06-11 00:00:00
3Lead2016-06-11 00:00:00

i. Query to create the Database

CREATE DATABASE ORG;
SHOW DATABASES;
USE ORG;

Learn more about SQL create Database

ii. Query to create the Table

CREATE TABLE Worker (
WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME CHAR(25),
LAST_NAME CHAR(25),
SALARY INT(15),
JOINING_DATE DATETIME,
DEPARTMENT CHAR(25)
);

iii. Query to insert into the Table Worker

INSERT INTO Worker
(WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
(001, ‘NIHARIKA’, ‘ARORA’, ‘20000’, ‘2013-02-25 09:00:00’, ‘HR’)
(002, ‘AYUSHI’, ‘GURONDIA’, ‘5000’, ‘2015-02-10 09:00:00’, ‘ADMIN’)
(003,’ PRIYANSHA, CHOUKSEY’, ‘25000’, ‘2014-05-16 09:00:00’, ‘HR’)
(004, ‘APARNA’, ’DESHPANDE’, ‘8000’, ‘2016-12-20 09:00:00’, ‘ADMIN’)
(005, ‘SHAFALI’, ‘JAIN’, ‘21000’, ‘2015-08-29 09:00:00’, ADMIN’)
(006, ‘SUCHITA’, ‘JOSHI’, ‘20000’, ‘2017-02-12 09:00:00’, ‘ACCOUNT’)
(007, ‘SHUBHI’, ‘MISHRA’, ‘15000’, ‘2018-03-23 09:00:00’, ‘ADMIN’)
(008, ‘DEVYANI’, ‘PATIDAR’, ‘18000’, ‘2014-05-02 09:00:00’, ‘ACCOUNT’);

iv. Query to create table Bonus

CREATE TABLE Bonus (
WORKER_REF_ID INT,
BONUS_AMOUNT INT(10),
BONUS_DATE DATETIME,
FOREIGN KEY (WORKER_REF_ID)
REFERENCES Worker(WORKER_ID)
       ON DELETE CASCADE
);

Let’s revise the primary and foreign key in SQL

v. Query to insert into table Bonus

INSERT INTO Bonus
(WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES
(001, 5000, '15-04-20'),
(002, 3000, '15-08-11'),
(003, 4000, '15-04-20'),
(001, 4500, '15-04-20'),
(002, 3500, '15-08-11');

vi. Query to create table Title

CREATE TABLE Title (
WORKER_REF_ID INT,
WORKER_TITLE CHAR(25),
AFFECTED_FROM DATETIME,
FOREIGN KEY (WORKER_REF_ID)
REFERENCES Worker(WORKER_ID)
       ON DELETE CASCADE
);

vii. Query to insert into table Title

INSERT INTO Title
(WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
(001, 'Manager', '2016-02-20 00:00:00'),
(002, 'Executive', '2016-06-11 00:00:00'),
(008, 'Executive', '2016-06-11 00:00:00'),
(005, 'Manager', '2016-06-11 00:00:00'),
(004, 'Asst. Manager', '2016-06-11 00:00:00'),
(007, 'Executive', '2016-06-11 00:00:00'),
(006, 'Lead', '2016-06-11 00:00:00'),
(003, 'Lead', '2016-06-11 00:00:00');

In this way, you can create and insert values into the table. So, let’s start SQL Query Interview Questions and Answers.

Recommended Reading – SQL Subquery

3. 30 Complex SQL Queries Interview Questions and Answers

Now you know how to create table and insert values in it through SQL Query.  So, let’s practice the SQL Query through the best and important SQL Query Interview Questions.

Q.1 Write an SQL query for fetching “FIRST_NAME” from the WORKER table using <WORKER_NAME> as alias.

Ans. The query that you can use is:

Select FIRST_NAME AS WORKER_NAME from Worker;

Q.2 What is an SQL Query for fetching the “FIRST_NAME” from WORKER table in upper case?

Ans. The query that you can use is:

Select upper(FIRST_NAME) from Worker;

Q.3 What is an SQL query for fetching the unique values of the column DEPARTMENT from the WORKER table?

Ans. The query that you cam use is:

Select distinct DEPARTMENT from Worker;

Q.4 Write an SQL query for printing the first three characters of the column FIRST_NAME.

Ans. The query that can be used is:

Select substring(FIRST_NAME,1,3) from Worker;

Q.5 What is an SQL query for finding the position of the alphabet (‘A’) in the FIRST_NAME column of Ayushi.

Ans. The query that can be used is:

Select INSTR(FIRST_NAME, BINARY'a') from Worker where FIRST_NAME = 'Ayushi';

Q.6 What is an SQL Query for printing the FIRST_NAME from Worker Table after the removal of white spaces from right side.

Ans. The query that can be used is:

Select RTRIM(FIRST_NAME) from Worker;

You must read about SQL Query Optimization tools

Q.7 Write an SQL Query for printing the DEPARTMENT from Worker Table after the removal of white spaces from the left side.

Ans. The query that you can use is:

Select LTRIM(DEPARTMENT) from Worker;

Q.8 What is an SQL query for fetching the unique values from the DEPARTMENT column and thus printing is the length?

Ans. The query that you can use is:

Select distinct length(DEPARTMENT) from Worker;

Q.9 Write an SQL query for printing the FIRST_NAME after replacing ‘A’ with ‘a’.

The query that can be used is:

Select REPLACE(FIRST_NAME,'a','A') from Worker;

Q.10 What is an SQL query for printing the FIRST_NAME and LAST_NAME into a column named COMPLETE_NAME? (A space char should be used)

Ans. The query that can be used is:

Select CONCAT(FIRST_NAME, ' ', LAST_NAME) AS 'COMPLETE_NAME' from Worker;

Q.11 What is an SQL query for printing all details of the worker table which ordered by FIRST_NAME ascending?

Ans. The query that can be used is:

Select * from Worker order by FIRST_NAME asc;

Q.12 Write an SQL query for printing the all details of the worker table which ordered by FIRST_NAME ascending and the DEPARTMENT in descending

The query that can be used is:

Select * from Worker order by FIRST_NAME asc,DEPARTMENT desc

Q.13 What is an SQL query to print the details of the workers ‘NIHARIKA’ and ‘PRIYANSHA’.

Ans. The query that can be used is:

Select * from Worker where FIRST_NAME in ('NIHARIKA','PRIYANSHA');

Q.14 What is an SQL query printing all details of workers excluding the first names of ‘NIHARIKA’ and ‘PRIYANSHA’

Ans. The query that can be used is:

Select * from Worker where FIRST_NAME not in ('NIHARIKA','PRYANSHA');

Do you know about dynamic SQL tutorial?

Q.15 Write an SQL query for printing the details of DEPARTMENT name as “Admin”.

Ans. The query that can be used is:

Select * from Worker where DEPARTMENT like 'Admin%';

Q.16  What is an SQL query for printing the details of workers whose FIRST_NAME Contains ‘A’?

Ans. The query that can be used is:

Select * from Worker where FIRST_NAME like '%a%';

Q.17 What is an SQL Query for printing the FIRST_NAME of workers whose name ends with ‘A’?

Ans. The query that can be used is:

Select * from Worker where FIRST_NAME like '%a';

Q.18 What is an SQL Query for printing the details of the workers whose FIRST_NAME ends with ‘H’ and contains six alphabets?

Ans. The query that can be used is:

Select * from Worker where FIRST_NAME like '_____h';

Q.19 Write an SQL Query for printing the details of workers whose SALARY lies between 10000 and 20000.

Ans. The query that can be used is:

Select * from Worker where SALARY between 10000 and 20000;

Q.20 Write an SQL Query for printing the details of workers who joined inFeb’2014

Ans. The query that can be used is:

Select * from Worker where year(JOINING_DATE) = 2014 and month(JOINING_DATE) = 2;

Q.21 Write an SQL Query for fetching the count of workers in DEOARTMENT with ‘Admin’.

Ans. The query that can be used is:

SELECT COUNT(*) FROM worker WHERE DEPARTMENT = 'Admin';

Q.22 Write an SQL Query for fetching the details of workers with Salaries >= 5000 and <= 10000.

Ans. The query that can be used is:

SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) As Worker_Name, Salary
FROM worker
WHERE WORKER_ID IN
(SELECT WORKER_ID FROM worker
WHERE Salary BETWEEN 5000 AND 10000);

Q.23 What is an SQL Query for fetching the no. of workers in each department in descending order?

Ans. The query that can be used is:

SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers
FROM worker
GROUP BY DEPARTMENT
ORDER BY No_Of_Workers DESC;

Q.24 What is an SQL Query for printing the details of workers who are also managers?

Ans. The query that can be used is:

SELECT DISTINCT W.FIRST_NAME, T.WORKER_TITLE
FROM Worker W
INNER JOIN Title T
ON W.WORKER_ID = T.WORKER_REF_ID
AND T.WORKER_TITLE in ('Manager');

Q.25 Write an SQL Query for fetching the details of duplicate records in some fields.

Ans. The query that can be used is:

SELECT WORKER_TITLE, AFFECTED_FROM, COUNT(*)
FROM Title
GROUP BY WORKER_TITLE, AFFECTED_FROM
HAVING COUNT(*) > 1;

Q.26 What is an SQL Query for only showing odd rows?

Ans. The query that can be used is:

SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) <> 0;

Q.27 What is an SQL Query for only showing even rows?

Ans. The query that can be used is:

SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) = 0;

Q.28 Write an SQL Query for cloning a new table from another table.

Ans. The general query that can be used to clone a table with data is:

SELECT * INTO WorkerClone FROM Worker;

The general way that can be used to clone a table without information is:

SELECT * INTO WorkerClone FROM Worker WHERE 1 = 0;

Q.29 Write an SQL Query for fetching the intersecting details of two tables.

Ans. The query that can be used is:

(SELECT * FROM Worker)
INTERSECT
(SELECT * FROM WorkerClone);

Q.30 What is an SQL Query for showing the details of one table that another doesn’t have.

Ans. The query that can be used is:

SELECT * FROM Worker
MINUS
SELECT * FROM Title;

So, this was all in SQL Query Interview Questions and Answers. Hope you liked the explanation.

4. Summary – SQL Queries

Hence, you have completed the blog of SQL Query Interview Questions. In this, you discussed SQL interview questions based on complex queries. Along with this, we saw how can you create a table in SQL and add values to it. Moreover, you learned all the type of SQL Queries which could be asked in any technical SQL interview.

Still, if you have any doubt in SQL Query Interview Questions, you can freely ask in the comment tab.

You must check – What are the frequently asked SQL Interview Questions

Did you like our efforts? If Yes, please give DataFlair 5 Stars on Google

follow dataflair on YouTube

10 Responses

  1. MENTA ANUTEJASWY says:

    nice ..post more queries

  2. MENTA ANUTEJASWY says:

    nice ..post more queries 🙂

    • DataFlair Team says:

      Hello Menta,
      Thanks for the appreciation, we have more SQL Interview Questions, please refer them as well. Or you want to know more about SQL Queries, refer our SQL Sub Query article.
      Regards,
      DataFlair

  3. Gopinath says:

    Hi Sir,
    How to move old table records to New tables? can you explain query Also

    Thanks
    Gopinath M

  4. KPK says:

    insert new_table select * from old_table

  5. Praveen kumar S says:

    Hi this praveen…It’s very nice technical stuffs for SQL lerners, Please suggest me I need to learn problem solving sql queries skills …please share me any link for the same..

  6. Vedant Mishra says:

    create table new tablename
    as select columnname1,columnname2,..from old table name;

  7. shubham rai says:

    I hope it will work

    create new_table as select * from old_table

    it will copy all the data from old table to new table.

    Thanks,
    Shubham Rai.

  8. yasabneh dilu says:

    it is very good to now sql
    pleas send all parts of data base question and answer in practical and interview question to my email

  9. J Andrea says:

    Please can someone help me with creating queries for this question:

    Consider the following table
    WORKS(Pname, Cname,salary)

    LIVES(Pname,Street, City)

    LOCATED(Cname, City)

    MANAGER(Pname, Mgrname) where Pname = person name, Cname Company name

    Mgrname Manager name

    a) list the names of the people who work for the company Wipro along with the cities

    b)Find the names of the persons who ,ive and work in the same city

    c)Fnd the names of the persons who do not work for Infosys

    d) Find the persons workd for infosys with the salary more than
    50,000/- along with their city

Leave a Reply

Your email address will not be published. Required fields are marked *