In this Article, you will learn about 20 most common SQL query interview questions & answers for freshers & experienced.
- Write a query to get the EmpFname from the EmployeeInfo table in the upper case using the alias name as EmpName?
- Write a query to get the number of employees working in the department ‘HR’?
- 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.
- 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:
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:
Symphony in D Minor
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.
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:
To fetch the current date, you can write this query in MySQL:
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
|Blockchain Technology||React for Beginners||Core Java Basics|
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%’;
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
SELECT TOP 3 salary
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
|Why Learn to Code? How Learn to Code?||How to Install Specific Version of NPM Package?||Types of Inheritance in C++ What Should You Know?|
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.
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.
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:
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-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.
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.
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.