20 Most Common SQL Query Interview Questions & Answers [For Freshers & Experienced]

Summary:

In this Article, you will learn about 20 most common SQL query interview questions & answers for freshers & experienced.

1. Write a query to get the EmpFname from the EmployeeInfo table in the upper case using the alias name as EmpName?
2. Write a query to get the number of employees working in the department ‘HR’?
3. What query will you write to fetch the current date?……….

Read the full blog to know all the 20 Questions & Answers in detail.

Attending an SQL query interview questions and wondering what are all the questions and discussions you will go through? Before attending an SQL interview, it’s better to have an idea about the types of SQL interview questions will be asked so that you can mentally prepare answers for them.

To help you out, I have created the top SQL interview question and answers guide to understand the depth and real-intend of SQL interview questions. Let’s get started.

Structured Query Language (SQL) is a popular and extensively used programming language for managing, manipulating, and querying data in relational databases. While SQL is not exactly the fastest language for communicating with relational databases, it is undoubtedly the most efficient means of handling structured data. 

Check out our free courses to get an edge over the competition.

Compared to traditional read-write APIs like ISAM or VSAM, SQL extends two core benefits: 

• It allows you to access multiple records using a single command. Here is the SQL query examples with answers.
• It eliminates the need to specify how to access a record (whether or not to use an index) from a database.

An SQL query is a question or request for accessing data stored in a database. A database stores information in a tabular format. Thus, it contains rows and columns. While the database fields form the columns that direct the database regarding what information it can store, the rows contain the data. Here’s a sample dataset called “tblAlbum” to put things into perspective:

Source

Let’s create an SQL query using this sample data. We’ll focus on choosing only those albums with a 9 or above rating. So, we’re essentially asking the query to produce a list of album titles from the database having a rating equal to or greater than 9. You will use relevant keywords to get the desired answer.

The keywords in this scenario are – SELECT (dictating to fetch information from the database), FROM (the specific table containing the said data), and WHERE (dictating the criteria for accessing the information). When you use these keywords, your SQL query will look like this:

SELECT albumTitle FROM tblAlbums

WHERE rating >= 9;

Thus, you are choosing (SELECT) the data from (FROM) the table “tblAlbums” where (WHERE) the album rating is 9 or above. The WHERE statement can either be very simple or overly complex if there’s a substantial amount of nested logic involved. On running the query, you’ll get the following result:

albumTitle

Symphony in D Minor

Poet’s Heart

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.

EmployeeInfo table:

EmpID	EmpFname	EmpLname	Department	Project	Address	DOB	Gender
1	Rohit	Gupta	Admin	P1	Delhi	02/12/1979	Male
2	Rahul	Mahajan	Admin	P2	Mumbai	10/10/1986	Male
3	Sonia	Banerjee	HR	P3	Pune	05/06/1983	Female
4	Ankita	Kapoor	HR	P4	Chennai	28/11/1983	Female
5	Swati	Garg	HR	P5	Delhi	06/04/1991	Female

EmployeePosition table:

EmpID	EmpPosition	DateOfJoining	Salary
1	Executive	01/04/2020	75000
2	Manager	03/04/2020	500000
3	Manager	02/04/2020	150000
2	Officer	02/04/2020	90000
1	Manager	03/04/2020	300000

Check out upGrad’s Full Stack Development Bootcamp

upGrad’s Exclusive Software and Tech Webinar for you –

SAAS Business – What is So Different?

 

SQL Query Interview Questions and Answers

1. Write a query to get the EmpFname from the EmployeeInfo table in the upper case using the alias name as EmpName.

The query for this condition is:

SELECT UPPER(EmpFname) AS EmpName FROM EmployeeInfo;

Check out Java Bootcamp from upGrad

2. Write a query to get the number of employees working in the department ‘HR’.

Here’s the query for this demand:

SELECT COUNT(*) FROM EmployeeInfo WHERE Department = ‘HR’;

3. What query will you write to fetch the current date?

To fetch the current date, you can write this query in the SQL server:

SELECT GETDATE();

To fetch the current date, you can write this query in MySQL:

SELECT SYSTDATE();

4. Write a query to fetch only the place name(string before brackets) from the Address column of the EmployeeInfo table.

You can use the MID function in MySQL to create the following query:

SELECT MID(Address, 0, LOCATE(‘(‘,Address)) FROM EmployeeInfo;

You can use SUBSTRING to create the following query:

SELECT SUBSTRING(Address, 1, CHARINDEX(‘(‘,Address)) FROM EmployeeInfo;

Read More: SQL for Data Science: Why SQL

5. Write a query to create a new table whose data and structure are copied from another table.

You can use the SELECT INTO command to create the following query:

SELECT * INTO NewTable FROM EmployeeInfo WHERE 1 = 0;

You can use the CREATE command in MySQL to create the following query:

CREATE TABLE NewTable AS SELECT * FROM EmployeeInfo;

Explore Our Software Development Free Courses

6. Write a query to display the names of employees that begin with ‘S’.

This is the query you need to write to get the names of the employees whose names start with’S’:

SELECT * FROM EmployeeInfo WHERE EmpFname LIKE ‘S%’;

7. Write a query to retrieve the top N records.

You can write a query using the TOP command in SQL Server:

SELECT TOP N * FROM EmployeePosition ORDER BY Salary DESC;

You can also create a query using the LIMIT command in MySQL:

SELECT * FROM EmpPosition ORDER BY Salary DESC LIMIT N;

8. Write a query to obtain relevant records from the EmployeeInfo table ordered by Department in ascending order and EmpLname in descending order.

You need to use the ORDER BY statement in SQL for this purpose;

SELECT * FROM EmployeeInfo ORDER BY Department asc, EmpFname desc;

9. Write a query to get the details of employees whose EmpFname ends with ‘A’.

You can use the LIKE operator in SQL to create a query for this issue:

SELECT * FROM EmployeeInfo WHERE EmpFname LIKE ‘____a’;

10. Create a query to fetch details of employees having “DELHI” as their address.

SELECT * FROM EmployeeInfo WHERE Address LIKE ‘DELHI%’;

Read: Top 9 Data Science Tools in 2020

11. Write a query to fetch all employees who also hold the managerial position.

Any SQL Interview Question and Answers guide won’t complete without this question.

Here’s the query to get the employees who hold the managerial position:

SELECT E.EmpFname, E.EmpLname, P.EmpPosition

FROM EmployeeInfo E INNER JOIN EmployeePosition P ON

E.EmpID = P.EmpID AND P.EmpPosition IN (‘Manager’);

12. Create a query to generate the first and last records from the EmployeeInfo table.

One of the most common SQL interview question.

Here’s the query to fetch the first record from the EmployeeInfo table:

SELECT * FROM EmployeeInfo WHERE EmpID = (SELECT MIN(EmpID) FROM EmployeeInfo);

Here’s the query to fetch the last record from the EmployeeInfo table:

SELECT * FROM EmployeeInfo WHERE EmpID = (SELECT MAX(EmpID) FROM EmployeeInfo);

13. Create a query to check if the passed value to the query follows the EmployeeInfo and EmployeePosition tables’ date format.  

You can use the IsDate() function in SQL to check whether the passed value follows the specified format or not. It returns 1(true) or 0(false) accordingly. Here’s how the query will look:

SELECT ISDATE(’01/04/2020′) AS “MM/DD/YY”;

Running this query will return 0 since the passed value does not match the specified format. 

Explore our Popular Software Engineering Courses

14. Create a query to obtain display employees having salaries equal to or greater than 150000.

The query for this request will be:

SELECT EmpName FROM Employees WHERE Salary>=150000;

15. Write a query to fetch the year using a date.

You can get the year from a date in an SQL server by running the following query:

SELECT YEAR(GETDATE()) as “Year”;

16. Create an SQL query to fetch EmpPostion and the total salary paid for each employee position.

The query for this request is:

SELECT EmpPosition, SUM(Salary) from EmployeePosition GROUP BY EmpPosition;

17. Write a query to find duplicate records from a table.

One of the most common question in any SQL query interview questions for experienced professionals guide with SQL query examples with answers.

SELECT EmpID, EmpFname, Department COUNT(*) 

FROM EmployeeInfo GROUP BY EmpID, EmpFname, Department 

HAVING COUNT(*) > 1;

18. Create a query to fetch the third-highest salary from the EmpPosition table.

SELECT TOP 1 salary

FROM(

SELECT TOP 3 salary

FROM employee_table

ORDER BY salary DESC) AS emp

ORDER BY salary ASC;

19. Write an SQL query to find even and odd records in the EmployeeInfo table.

You must use the MOD() function to fetch the even and odd records from a table. For even records, the query will be:

SELECT EmpID FROM (SELECT rowno, EmpID from EmployeeInfo) WHERE MOD(rowno,2)=0;

For odd records, the query will be:

SELECT EmpID FROM (SELECT rowno, EmpID from EmployeeInfo) WHERE MOD(rowno,2)=1;

20. Create a query to fetch the list of employees of the same department.

Here’s the query for this request:

Select DISTINCT E.EmpID, E.EmpFname, E.DepartmentFROM EmployeeInfo E, Employee E1 

WHERE E.Department = E1.Department AND E.EmpID != E1.EmpID;

What is a Database?

This question can be most probably asked among various other SQL interview questions. A database refers to a structured collection of data that can be stored, maintained, and accessed digitally from a local or remote computer network. A fixed design and modeling technique is used to build databases, which can be large and complex. Large databases are housed on multiple computers or cloud services, whereas smaller databases can be stored on a file system.

Get Software Engineering degrees from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

 What are RDBMS and DBMS? Describe the variations between them.

System software that really can create, retrieve, update, and administer a database is known as a database management system or DBMS. DBMA serves the role of an interface between the database and its users or application software, it maintains data consistency, ensures it is structured, and assures that it is easily available. DBMSs can be divided into four categories:

• Hierarchical database: Data is kept in a hierarchical style in a database with a tree-like structure. A parent in a database can have several children, but a child can only have one parent.
• Network databases: These databases are displayed as graphs with many-to-many relationships, enabling parents to have more than one kid.
• Relational database: The most popular and user-friendly database is the relational one. Stored values in the columns and rows are arranged in a table and are connected by relationships.
• Object-oriented database: In this sort of database, the data values and operations are kept as objects, and these objects have many relationships among them.

What is SQL?

SQL is known as Structured Query Language. It is the preferred language for RDBMS and can be used to manage structured data with variables or entities with relationships. For interacting with databases, SQL is employed.

As stated by ANSI, SQL is used to manage RDBMS and to carry out various data manipulation operations on various sorts of data. In essence, it is a database language that is used to create and delete databases. It can also be used, along with a few other things, to retrieve and change the rows of the table. This is amid one of the majorly asked SQL interview questions or even SQL query interview questions and answers.

What are Joins in SQL?

Among the many SQL query interview questions and answers, this is a definite one.

The join function in SQL merges rows from two or more tables based on a shared column. Depending on how the tables are related to one another, different forms of joins can be utilized to get data.

Four different forms of joins exist:

• Inner join
• Left join
• Right join
• Full join

What are the applications of SQL?

The major applications of SQL are:

• writing scripts for data integration
• Configuring and executing analytical queries
• retrieving specific data from a database to be used in transaction processing and analytics
• adding, changing, and removing rows and columns of data in a database

What are the usages of SQL?

This is one of the top SQL coding interview questions asked by the interviewer. Here are a few operations performed by SQL:-

• Creating new databases
• Inserting new data
• Deleting existing data
• Updating records
• Retrieving the data
• Creating and dropping tables
• Creating functions and views
• Converting data types

In-Demand Software Development Skills

What is an index?

Indexing enables a faster database search. The SQL server tends to scan the entire database and verify each row to retrieve matches if a column of the WHERE clause contains no index. This could slow down operations in huge data sets.

Indexes locate all rows matching a specific set of columns, which enables users to easily go through only those parts of the data for matches.

Read our Popular Articles related to Software Development

What are the Constraints in SQL?

Constraints in SQL are rules or conditions that you can apply to the columns of a table to add data integrity and support the accuracy and consistency of the data stored in the database. Moreover, it help ensure that the data meets certain criteria and prevent incorrect or inconsistent data from being inserted, updated, or deleted.

Here are some common types of constraints in SQL:

• Primary Key Constraint

It ensures that a column uniquely identifies every row in a table. It enforces the uniqueness and not-null property of the specified column(s), making them suitable for identifying records. A table can have only one primary key.

• Unique Constraint

It enforces the uniqueness of values in a column or a set of columns. Unlike a primary key, a unique constraint allows null values, but only one row can have a null value in the constrained column(s).

• Foreign Key Constraint

It create a relationship by joining two tables by ensuring that values in one table’s column (the foreign key) match the values in another table’s primary key column (the referenced key). This helps maintain data integrity and enforce referential integrity.

• Check Constraint

A check constraint allows you to define a condition that must be true for the data in a column. It restricts the values inserted or updated in the column based on the specified condition.

• Not Null Constraint

It ensures that each column cannot have null values. It enforces that a value must be provided for the column when inserting a new row.

What are the differences between OLTP and OLAP?

OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) are distinct database architectures with different purposes. OLTP focuses on managing day-to-day operational data and handling transactional workloads in real time. It involves frequent small updates, insertions, and deletions. On the other hand, OLAP is designed for complex analytical queries involving large volumes of historical data. It supports decision-making processes by enabling users to perform advanced data analysis, aggregation, and reporting.

OLTP databases are optimized for high-speed data manipulation, ensuring data integrity and enforcing constraints, such as primary and foreign keys. OLAP databases are optimized for read-heavy tasks and use techniques like data cubes and indexing to retrieve and analyze data efficiently.

In summary, OLTP is tailored for operational efficiency and real-time transactional processing, while OLAP caters to analytical tasks, enabling users to gain insights from historical data. The differences lie in usage, data volumes, query complexity, and optimization strategies.

What is Collation? What are the different types of Collation Sensitivity?

Collation in a database context refers to the rules governing how string comparison and sorting operations are performed on character data. It determines how characters are compared, whether they are treated as case-sensitive or case-insensitive, and how accented or special characters are treated.

Collation sensitivity refers to how a collation treats different aspects of character comparison:

• Case Sensitivity

In case-sensitive collations, uppercase and lowercase letters are considered distinct. For example, in a case-sensitive collation, ‘A’ and ‘a’ are treated as different characters. In case-insensitive collations, the distinction between uppercase and lowercase letters is ignored in comparisons.

• Accent Sensitivity

Accent-sensitive collations consider accented characters as distinct from their non-accented counterparts. For instance, ‘é’ and ‘e’ would be treated as different characters in an accent-sensitive collation. In accent-insensitive collations, accents are ignored during comparison.

• Kana Sensitivity (for Japanese)

This is relevant for Japanese collations. Kana-sensitive collations treat different Japanese kana characters as distinct, while kana-insensitive collations consider them equivalent.

• Width Sensitivity

This aspect applies to the width of characters, particularly relevant in East Asian languages where characters can have full-width and half-width forms. Width-sensitive collations differentiate between full-width and half-width characters, while width-insensitive collations do not.

Conclusion

With that, we come to the end of our SQL query interview questions for experienced professionals list. We hope that these queries give you a fair idea of the standard SQL query pattern and how to create SQL queries. The more you practice, the better you will get at writing SQL queries to access and manipulate data in a data table.

If you are curious to improve your SQL knowledge, and more about full stack development, check out IIIT-B & upGrad’s PG Diploma in Full Stack Software Development which is created for working professionals and offers 10+ case studies & projects, practical hands-on workshops, mentorship with industry experts, 1-on-1 with industry mentors, 400+ hours of learning and job assistance with top firms. It also includes various SQL coding interview questions that will help you practice for the interview.