Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Most Asked SQL Query Interview Questions
2.1.
1. What is SQL?
2.2.
2. What is a primary key, and why is it important in a database?
2.3.
3. What is a foreign key, and how is it different from a primary key?
2.4.
4. What is the difference between a LEFT JOIN and a RIGHT JOIN in SQL?
2.5.
5. How would you retrieve all the records from a " customers " table in SQL?
2.6.
6. What is a subquery, and how is it used in SQL?
2.7.
7. What is the difference between SQL's WHERE and HAVING clauses?
2.8.
8. What is the difference between a function and a stored procedure in SQL?
2.9.
9. Write a SQL query to find the second-highest salary from an employee table.
2.10.
10. What is the purpose of an index in SQL, and how does it work?
2.11.
11. What is the difference between NOSQL and SQL?
2.12.
12. Write a SQL query to find the names of employees who have not been assigned to any project.
2.13.
13. In the Student table, the marks column contains a list of values separated by commas. How can you determine the number of values in this comma-separated list?
2.14.
14. State the difference between cross-join and natural join.
2.15.
15. List the different types of relationships in SQL.
2.16.
16. What are Tables and Fields?
2.17.
17. What is the difference between the SQL statements DELETE and TRUNCATE?
2.18.
18. How should data be structured to support Join Operations in a one-to-many relationship?
2.19.
19. What is a transaction in SQL, and why is it important?
2.20.
20. What is ETL in SQL?
2.21.
21. What are the types of SQL JOINS?
2.22.
22. What are Aggregate and Scalar functions?
2.23.
23. List different Types of Index in SQL?
2.24.
24. What is the difference between a clustered and a non-clustered index in SQL? 
2.25.
25. What are the different types of normalizations?
2.26.
26. Explain Boyce-Codd Normal Form (BCNF).
2.27.
27. What is the difference between a join and a subquery in SQL? 
2.28.
28. What are some standard clauses used with SELECT queries in SQL? 
2.29.
29. How to get unique records from the table without using distinct keywords.
2.30.
30. Display the monthly Salary of Employees given annual salary.
2.31.
31. Distinguish between nested subquery, correlated subquery, and join operation.
2.32.
32. What is a Non-Equi Join?
2.33.
33. What are OLAP and OLTP?
3.
Frequently Asked Questions
3.1.
How do I prepare for SQL interview?
3.2.
What are the basic SQL queries asked in interview? 
3.3.
How do I practice SQL queries? 
3.4.
What are the 5 types of SQL queries?
3.5.
How do SQL queries execute?
3.6.
How does a query work?
4.
Conclusion
Last Updated: Apr 15, 2024
Easy

SQL Query Interview Questions

Author Kanak Rana
5 upvotes
gp-icon
Interview guide for product based companies
Free guided path
12 chapters
99+ problems
gp-badge
Earn badges and level up

Introduction

SQL is a programming language used to manage relational databases. Structured Query Language is the widely accepted language for communicating with relational databases, and it is utilized in various domains, including data analytics, software development, and business operations.

To effectively work with SQL, it is essential to have a solid understanding of its concepts and practical experience using SQL in real-world scenarios. A good understanding of SQL can demonstrate your technical skills and ability to work with data, making it a valuable asset for many job roles in the tech industry.

sql query interview questions

Most Asked SQL Query Interview Questions

1. What is SQL?

SQL (Structured Query Language) is a programming language used to manipulate and manage data in relational databases. SQL is used to communicate and connect with a database to create, modify, retrieve, and delete data.

Some everyday tasks performed using SQL include creating tables and views, inserting and updating data, selecting specific data from tables, and deleting data from tables. 

2. What is a primary key, and why is it important in a database?

A primary key is a crucial component of a Database as it serves as a unique identifier in a table for each record. The primary key enables efficient data retrieval and manipulation while ensuring data integrity by preventing duplicate entries.

Using a primary key, a database management system can quickly locate and retrieve data from a table without scanning the entire table. This makes data retrieval more efficient, especially in large databases with many records. Additionally, primary keys allow for the easy creation of relationships between tables, simplifying complex queries and making database maintenance easier.

3. What is a foreign key, and how is it different from a primary key?

A foreign key is a database constraint that establishes a link between two tables in a relational database. It is used to maintain referential integrity. It is a field or set of fields in one table that also refers to the primary key of another table. It differs from a primary key in that it does not have to be unique and can be used to establish relationships between tables.

4. What is the difference between a LEFT JOIN and a RIGHT JOIN in SQL?

In SQL, a LEFT JOIN and a RIGHT JOIN are both types of outer join that can be used to combine data from two or more tables. The difference between them lies in which table's data is preserved if there is no matching data in the other table.

  • In a LEFT JOIN, all the rows from the table on the left-hand side of the JOIN keyword (the "left table") are included in the result set, even if there is no matching data in the table on the right-hand side (the "right table"). 
  • In the Right JOIN, all the rows from the table on the right-hand side of the JOIN keyword (the "right table") are included In the result set, even if there is no matching data in the left table. 

In summary, the difference between a LEFT JOIN and a RIGHT JOIN is the table whose data is preserved when there is no match in the other table.

5. How would you retrieve all the records from a " customers " table in SQL?

Ans: To retrieve all the records from a table called "customers" in SQL, you would use the following query:

SELECT * FROM customers;

6. What is a subquery, and how is it used in SQL?

A subquery is a query that is embedded within another query. It retrieves data used in the main query as a filter or a condition.

Syntax:

SELECT column1, column2
FROM table1
WHERE column3 IN (SELECT column3 FROM table2 WHERE condition);

7. What is the difference between SQL's WHERE and HAVING clauses?

In SQL, the WHERE clause is used to filter rows based on a condition on a column, while the HAVING clause is used to filter groups based on an aggregate function.

The WHERE clause is applied before any grouping takes place and filters individual rows based on a condition. On the other hand, the HAVING clause is applied after the grouping and filter groups based on the results of aggregate functions such as COUNT, SUM, AVG, etc.

8. What is the difference between a function and a stored procedure in SQL?

In SQL, a function returns a value, while a stored procedure does not necessarily return a value and may execute a series of operations or tasks. Functions can be used as part of a SQL statement or expression to return a value. In contrast, stored procedures can be used to encapsulate a series of SQL statements and can be executed as a single unit. Additionally, functions can be used within stored procedures, but stored procedures cannot be used within functions.

9. Write a SQL query to find the second-highest salary from an employee table.

Assuming we have the following "employees" table:

CREATE TABLE employees (
	id INT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	salary INT
);

INSERT INTO employees (id, name, salary) VALUES (1, 'Sangeeta', 100000);
INSERT INTO employees (id, name, salary) VALUES (2, 'Ranjita', 150000);
INSERT INTO employees (id, name, salary) VALUES (3, 'Anita', 70000);
INSERT INTO employees (id, name, salary) VALUES (4, 'Sunita', 50000);
INSERT INTO employees (id, name, salary) VALUES (5, 'Anjeeta', 90000);
employees table
SELECT MAX(salary) as second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)


Output:

Output

10. What is the purpose of an index in SQL, and how does it work?

An index is used to improve the performance of queries by allowing for faster data retrieval. It creates a separate data structure that stores the values of one or more columns and allows faster access to the data based on those values.

11. What is the difference between NOSQL and SQL?

SQLNOSQL
It is a relational data model.It is a non-relational data model.
Vertical scaling is more common.Supports horizontal scaling.
Handles structured data.Handles large and unstructured data.
Fixed schema structure.Flexible structure.
Schema-based.Schema-less.

12. Write a SQL query to find the names of employees who have not been assigned to any project.

We have created an Employee table and a Project table

CREATE TABLE employees (
	employee_id INT PRIMARY KEY,
	name VARCHAR(50)
);

INSERT INTO employees (employee_id, name)
VALUES
	(1, 'Raghav'),
	(2, 'Raashi'),
	(3, 'Rohan'),
	(4, 'Mohan');
SQL table

 

CREATE TABLE projects (
	project_id INT PRIMARY KEY,
	name VARCHAR(50),
	employee_id INT,
	FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

INSERT INTO projects (project_id, name, employee_id)
VALUES
	(1, 'Project A', 1),
	(2, 'Project B', 2),
	(3, 'Project C', 1),
	(4, 'Project D', 3);
SQL Project table
SELECT employees.name
FROM employees
LEFT JOIN projects
ON employees.employee_id = projects.employee_id
WHERE projects.employee_id IS NULL;


Output:

Output

Explanation:

This is because the employee Mohan has not been assigned to any project, as there are no corresponding rows in the "projects" table with their respective employee IDs. The rest have been assigned to at least one project, so they are not included in the output.

13. In the Student table, the marks column contains a list of values separated by commas. How can you determine the number of values in this comma-separated list?

 

CREATE TABLE Student (
	id INT NOT NULL,
	name VARCHAR(50) NOT NULL,
	marks VARCHAR(255) NOT NULL,
	PRIMARY KEY (id)
);
INSERT INTO Student (id, name, marks)
VALUES (1, 'Rohit', '87,92,76,89');
Student table
SELECT id, name, marks, LENGTH(marks) - LENGTH(REPLACE(marks, ',', '')) + 1 AS num_marks
FROM Student
WHERE id = 1;


Output:

Output

14. State the difference between cross-join and natural join.

FeaturesCROSS JOINNATURAL JOIN
DefinitionReturns the Cartesian product of the two tables, meaning every row from the first table is combined with every row from the second table.Joins two tables based on columns with the same name, producing a result set with only one column for each pair of same-named columns.
SyntaxSELECT * FROM table1 CROSS JOIN table2;SELECT * FROM table1 NATURAL JOIN table2;
Join ConditionJoins every row of the first table to every row of the second table.Joins the two tables based on columns with the same name.
Resulting RowsTotal rows in table1 multiplied by total rows in table2.Rows where the values in columns with the same name are equal.
PerformanceIt can be slow for large tables, generates Cartesian product.More efficient than CROSS JOIN but may require specifying columns explicitly.
UsageUsed when no columns exist to join tables or when you want all possible combinations of rows from the two tables.Used when two tables have at least one column with the same name, and you want to join them based on that column.

15. List the different types of relationships in SQL.

In SQL, there are four main types of relationships:
 

  • One-to-One (1:1) Relationship: In this type of relationship, each record in the first table is associated with only one record in the second table, and vice versa. This is typically used when the two tables have a common attribute or key, and the second table contains additional information about the first table.
     

 

  • One-to-Many (1:N) Relationship: In this type of relationship, each record in the first table can be associated with multiple records in the second table, but each record in the second table is associated with only one record in the first table. This is used when one record in the first table can have multiple related records in the second table.

 

  • Many-to-One (N:1) Relationship: This is the inverse of the one-to-many relationship. In this type of relationship, each record in the second table can be associated with multiple records in the first table, but each record in the first table is associated with only one record in the second table. This is used when multiple records in the second table are related to one record in the first table.

 

  • Many-to-Many (N:N) Relationship: In this type of relationship, each record in the first table can be associated with multiple records in the second table and vice versa. This requires the use of an intermediary table, often called a junction table, which contains foreign keys to both the first and second tables. This is used when multiple records in each table can be related to multiple records in the other table.

 

16. What are Tables and Fields?

In a relational database, a table is a collection of data organized in rows and columns. Tables are used to store and manage data in a structured way, with each row representing a unique record and each column representing specific information about the record. Tables are often named according to the type of data they contain, such as "customers", "orders", or "employees".

A field, also known as a column or an attribute, is a single information stored in a table. Each field is named and has a specific data type, such as text, number, date, or Boolean, that determines the type of data that can be stored in the field. For example, a "customers" table might have fields for the customer's name, address, phone number, and email address.

Fields can also have other properties, such as a maximum length or a default value, that define how the data is stored and how it can be used. In addition, fields can be used to define relationships between tables by referencing the primary key of another table or by creating foreign keys that link related records across different tables.

TablesFields
customer'scustomer_id, name, email, phone, and address.
ordersorder_id, customer_id, order_date, total_amount.
productsproduct_id, name, description, price, quantity.
employeesemployee_id, first_name, last_name, job_title, hire_date.

Together, tables and fields form the basic building blocks of a relational database, providing a flexible and powerful way to store, manage, and query large amounts of data in a structured and organized way.

17. What is the difference between the SQL statements DELETE and TRUNCATE?

StatementDELETETRUNCATE
SyntaxDELETE FROM table_name WHERE condition;TRUNCATE TABLE table_name;
FunctionalityDeletes specific rows that match the WHERE condition.Removes all rows from the table.
Auto-incrementDoes not reset auto-increment values.Resets auto-increment values to their starting value.
TransactionIt can be rolled back within a transaction.It cannot be rolled back within a transaction.
LoggingLogs, each row deletion, can be slower for large tables.It does not log individual row deletions but can be faster for large tables.
AccessRequires to DELETE privileges on the table.Requires to DROP and CREATE privileges on the table.

18. How should data be structured to support Join Operations in a one-to-many relationship?

In a one-to-many relationship, where one record in the primary table is related to many records in the related table, the data should be structured in a way that supports efficient join operations.

A practical method to establish a relationship between two tables is to utilize a foreign key column within the related table that points to the primary key column in the primary table. This allows the database to quickly locate all the related records for a given primary record.

Consider two tables

CREATE TABLE customers_Table (
	customer_id INT PRIMARY KEY,
	name VARCHAR(50),
	email VARCHAR(100)
);
INSERT INTO customers_Table (customer_id, name, email)
VALUES (1, 'Pallavi Tiwari', 'Pallo@gmail.com');

INSERT INTO customers_Table (customer_id, name, email)
VALUES (2, 'Muskan Sharma', 'Muskan@gmail.com');

INSERT INTO customers_Table (customer_id, name, email)
VALUES (3, 'Raashi Batla', 'Raashi@gmail.com');

 

sql Customers table
CREATE TABLE orders_Table (
	order_id INT PRIMARY KEY,
	customer_id INT,
	order_date DATE,
	total_amount DECIMAL(10, 2),
	FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO orders_Table(order_id, customer_id, order_date, total_amount)
VALUES (101, 1, '2023-01-05', 100.00);

INSERT INTO orders_Table (order_id, customer_id, order_date, total_amount)
VALUES (102, 1, '2023-02-12', 50.00);

INSERT INTO orders_Table (order_id, customer_id, order_date, total_amount)
VALUES (103, 2, '2023-02-18', 200.00);

INSERT INTO orders_Table (order_id, customer_id, order_date, total_amount)
VALUES (104, 3, '2023-01-25', 75.00);
orders table

 

In this example, the table name customer_Table has a primary key column called "customer_id", a unique identifier for each customer. The orders_Table has a foreign key column called "customer_id", which references the customer_id column in the customers_Table. This establishes a one-to-many relationship between customers and orders: each can have multiple orders, but each order is associated with a single customer.

To retrieve a list of all customers and their associated orders, you could use a SQL statement like this:

SELECT customers_Table.customer_id, customers_Table.name, orders_Table.order_id, orders_Table.order_date, orders_Table.total_amount
FROM customers_Table
LEFT JOIN orders_Table ON customers_Table.customer_id = orders_Table.customer_id;


This statement uses a LEFT JOIN operation to include all records from the customers_Table, and any matching records from the orders_Table. The ON clause specifies the join condition, which matches records in the orders table with the corresponding customer records based on the customer_id column. 

Output:

output

As you can see, this query combines the data from both tables, showing the customer and order information together in a single result set.

19. What is a transaction in SQL, and why is it important?

In SQL, a transaction is a logical unit of work consisting of one or more SQL statements executed as a single atomic operation. The primary purpose of a transaction is to ensure that a group of SQL statements is executed as a single, consistent, and reliable unit.
When a transaction is initiated, a set of SQL statements are executed, and the changes made to the database are temporarily stored in a buffer area called a transaction log. If all the SQL statements in the transaction are executed successfully, the changes made to the database are committed, which means they are made permanent. 
 

  • If any error occurs during the transaction, all changes made to the database are rolled back, and the database is restored to its original state before the transaction starts.
     
  • The importance of transactions in SQL is maintaining the consistency, reliability, and integrity of the data in the database. Transactions help to ensure that the database is always in a valid state and that data is not lost or corrupted due to errors or system failures. 
     
  • Transactions also provide a mechanism for concurrent access to the database, allowing multiple users to access the database simultaneously without interfering with each other's work.
     
  • Transactions form the backbone of many business-critical applications and systems by ensuring that database changes are processed reliably and consistently.


20. What is ETL in SQL?

ETL (Extract, Transform, Load) is a common process used in data warehousing and business intelligence to move data from various sources into a data warehouse or database. The process involves three steps:
 

  • Extract: In this step, data is extracted from various sources such as databases, files, or web services. This may involve using SQL queries to extract data from databases, APIs, or web scraping tools to extract data from web services or files.
     
  • Transform: Once the data has been extracted, it is transformed or cleaned to make it suitable for storage and analysis. This may involve applying filters, aggregating data, or converting data types. SQL is commonly used to transform data as part of the ETL process.
     
  • Load: The final step is to load the transformed data into a data warehouse or database. This may involve loading data into tables, creating indexes, or performing other database operations.
     

The ETL process is critical for data integration, as it allows organizations to collect data from various sources, transform it into a consistent format, and store it in a central location for analysis and reporting. ETL tools such as Microsoft SQL Server Integration Services (SSIS) or Talend can automate much of the ETL process and provide a visual interface for designing and managing data flows.

21. What are the types of SQL JOINS?

In SQL, there are four types of JOINs:

  • INNER JOIN: It returns only the matching rows between two tables. It combines rows from two or more tables where the values in the common columns match.
     
  • LEFT JOIN: It returns all the left and the matching rows from the right table. The result will contain null values for the right table's columns if there are no matches.
     
  • RIGHT JOIN: It returns all the rows from the right table and the matching ones from the left table. The result will contain null values for the left table's columns if there are no matches.
     
  • FULL OUTER JOIN: It returns all the rows from both tables, including unmatched ones. If there are no matches, the result will contain null values for the table's columns that don't have a matching row.

 

There is also a CROSS JOIN, which returns the Cartesian product of the two tables, which combines every row from the first table with all rows from the second table. However, unlike the other JOIN types, it doesn't use a join condition to match the rows between the tables.

22. What are Aggregate and Scalar functions?

In database management systems, there are two main types of functions used to manipulate data: aggregate functions and scalar functions.

Aggregate functions perform calculations on a set of values and return a single value representing a summary of that set. These functions are typically used with a GROUP BY clause to group data by one or more columns and then perform calculations on each group. Examples of aggregate functions include SUM, AVG, COUNT, MAX, and MIN.

 

Scalar functions, on the other hand, operate on a single value and also return a single value. They can manipulate data in various ways, such as performing string operations, date calculations, or mathematical computations. Examples of scalar functions include CONCAT (to concatenate two strings), DATEADD (to add a specified amount of time to date), and ABS (to return the absolute value of a number).

 

Aggregate functions are used to perform calculations on data sets and return a single value representing some summary of that set. In contrast, scalar functions operate on a single value and return a single value. Both functions are commonly used in database management systems to manipulate and analyze data.

23. List different Types of Index in SQL?

In SQL, different indexes can be created to improve query performance. Here are some of the most common types of indexes:

  • Clustered Index: A clustered index in SQL organizes and stores the data rows in a table based on the values of one or more columns. This index determines the physical order of the data within the table, making it highly efficient for range queries and sorting operations. By sorting and storing the data in the table based on the values of the clustered index, queries that filter or sort by those columns can be performed faster since they can utilize the physical order of the data.
     
  • Non-Clustered Index: A non-clustered index creates a separate structure that stores a copy of the indexed columns and a pointer to the corresponding data row in the table. It allows for faster retrieval of specific rows or ranges of rows but can be less efficient than a clustered index for sorting operations.
     
  • Unique Index: A unique index enforces the constraint that the values in the indexed column(s) must be unique across all rows in the table. Depending on the table's primary key, it can be either a clustered or non-clustered index.
     
  • Composite Index: A composite index is an index that is created on two or more columns in a table. It can improve the performance of queries that filter on multiple columns, allowing more efficient sorting and matching of the indexed values.
     
  • Full-Text Index: A full-text index searches for text-based data in a table, such as articles, documents, or web pages. It allows for fast and efficient searching of large amounts of text using algorithms that analyze the data's words, phrases, and context.
     
  • Spatial Index: A spatial index is used to optimize the querying of geographic or location-based data in a table, such as maps, GPS coordinates, or boundaries. It uses specialized data structures and algorithms to store and search for spatial data efficiently.
     

24. What is the difference between a clustered and a non-clustered index in SQL? 

FeaturesClustered IndexNon-Clustered Index
DefinitionDetermines the physical order of the data.A separate structure that contains the index key and a pointer to the data.
ImplementationOnly one clustered index per table.Multiple non-clustered indexes per table are possible.
Data RetrievalFaster data retrieval for large data sets.Slower data retrieval for large data sets.
StorageContains the actual data.Does not contain the actual data.
KeyDetermines the order of data on the table.It helps in searching for the data.
Unique IndexClustered indexes are unique by default.Non-clustered indexes can be unique or non-unique.
Data ModificationThis may cause more overhead for updates.It May cause less overhead for updates.
Tables with Clustered IndexIt may require defragmentation.Does not require defragmentation.

25. What are the different types of normalizations?

In database design, several types of normalization are used to reduce data redundancy, improve data integrity, and ensure efficient data retrieval. The different types of normalizations are 

  • First Normal Form (1NF): This normalization ensures that the data in each table's column is atomic, meaning that it cannot be further broken down into smaller pieces. It also eliminates duplicate rows from the table.
     
  • Second Normal Form (2NF): This normalization eliminates partial dependencies by ensuring that each non-key column in a table is dependent on the entire primary key rather than on its part of it.
     
  • Third Normal Form (3NF): This normalization eliminates transitive dependencies by ensuring that each non-key column in a table is dependent only on the primary key and not on any other non-key columns.
     
  • Boyce-Codd Normal Form (BCNF): This normalization is an extension of 3NF and ensures that each determinant in a table is a candidate key.
     
  • Fourth Normal Form (4NF): This normalization eliminates multivalued dependencies by ensuring that each non-key column in a table is dependent on the entire primary key and not on any subsets.
     
  • Fifth Normal Form (5NF): This normalization is known as the Project-Join Normal Form and ensures that each table in a database has a single theme or topic.
     

The normalization process is iterative, and it may be necessary to apply multiple normalizations to achieve the desired data organization and efficiency level.

26. Explain Boyce-Codd Normal Form (BCNF).

Boyce-Codd Normal Form (BCNF) is a normalization technique used in database design to eliminate redundancy and improve data integrity. In BCNF, each determinant (i.e., attribute or set of attributes that uniquely determine another attribute) in a table must be a candidate key (i.e., a unique identifier for each row).

In simpler terms, BCNF ensures that each attribute in a table depends on the entire primary key rather than on just a part of it. This helps to reduce data redundancy and improve data integrity by ensuring that each piece of data in the table is only stored once and can be accessed efficiently.

BCNF is an extension of the Third Normal Form (3NF) and is helpful in situations where 3NF is insufficient to eliminate all forms of redundancy in a table. 

However, it is essential to note that achieving BCNF can sometimes result in a higher number of tables and more complex relationships between them. Hence, it is important to balance the benefits of normalization with the practical considerations of database design.

27. What is the difference between a join and a subquery in SQL? 

Here's a table format to help illustrate the differences between a join and a subquery in SQL:

FeaturesJoinSubquery
SyntaxSELECT ... FROM table1 JOIN table2 ...SELECT ... FROM table1 WHERE condition IN (...)
PurposeCombine columns from multiple tables.Retrieve data to use as a condition in the query.
UsageWhen querying data from multiple tables.When querying data based on a condition.
PerformanceTypically faster than a subquery.It can be slower than a join for large datasets.
Result setReturns columns from multiple tables.Returns a result set for a single table.
ComplexityMore complex syntax and usage.Less complex syntax and usage
FlexibilityIt can be used with various types of joins.It can be used with various types of subqueries.
Code readabilityIt can be less readable for complex joinsIt can be easier to read for simple queries

28. What are some standard clauses used with SELECT queries in SQL? 

In SQL, the SELECT statement retrieves data from a database. Along with the introductory SELECT statement, you can use clauses to specify additional details about how the data should be retrieved. Here are some standard clauses used with the SELECT statement example:

Here, we consider a table name List_1 and perform the following SELECT queries.

CREATE TABLE `List_1` (
	order_id int(11) NOT NULL,
	customer_id int(11) NOT NULL,
	order_date date NOT NULL,
	total_amount decimal(10,2) NOT NULL,
	PRIMARY KEY (`order_id`)
);

INSERT INTO `List_1` (`order_id`, `customer_id`, `order_date`, `total_amount`) VALUES
(1, 101, '2022-01-01', 50.00),
(2, 102, '2022-01-02', 100.00),
(3, 103, '2022-01-03', 75.00),
(4, 102, '2022-01-04', 25.00),
(5, 101, '2022-01-05', 80.00);

 

list 1 table

 

  • WHERE: This clause filters data based on a specific condition. For example, you can use WHERE to retrieve all records where a particular column is equal to a specific value.
     
SELECT * FROM List_1
WHERE customer_id = 101;


Output:

Output

 

  • ORDER BY: This clause is used to sort the results in descending or ascending order based on one or more columns. For example, you can use ORDER BY to sort a list of customers by their last name.
     
SELECT * FROM List_1
ORDER BY total_amount DESC;


Output:

output

 

  • GROUP BY: This clause group the results by one or more columns. For example, you can use GROUP BY to group a list of sales by month or by product.
     
SELECT customer_id, COUNT(*) as order_count
FROM List_1
GROUP BY customer_id;


Output:

Output

 

  • HAVING: It filters the groups created by the GROUP BY clause based on a specific condition. For example, you can use HAVING to retrieve only those groups where the total sales are greater than a certain amount.
     
SELECT customer_id, AVG(total_amount) as avg_total
FROM List_1
GROUP BY customer_id
HAVING AVG(total_amount) > 60;


Output:

Output

 

  • LIMIT: This clause limits the number of rows returned by the query. For example, you can use LIMIT to retrieve the top 10 customers based on their sales.
     
SELECT * FROM List_1
LIMIT 3;


Output:

Output

29. How to get unique records from the table without using distinct keywords.

To get unique records from a table without using the DISTINCT keyword in SQL, you can use the GROUP BY clause with aggregate functions like COUNT, SUM, or AVG.

Create a Table Sales

CREATE TABLE sales (
	product VARCHAR(50),
	quantity INT
);

INSERT INTO sales (product, quantity) VALUES ('apple', 20);
INSERT INTO sales (product, quantity) VALUES ('orange', 15);
INSERT INTO sales (product, quantity) VALUES ('apple', 30);
INSERT INTO sales (product, quantity) VALUES ('banana', 35);
SQL sales table

To get unique products from the sales table, you can group the rows by the product column and use the COUNT aggregate function to count the number of occurrences of each product:

SELECT product
FROM sales
GROUP BY product;


Output:

Output

If you want to include additional columns in the output, you can also use aggregate functions for those columns. For example, to get the sum of quantities sold for each product, you can use the SUM aggregate function:

SELECT product, SUM(quantity) as total_quantity
FROM sales
GROUP BY product;


Output:

Output

By grouping the rows by the product column and using the aggregate functions, you can effectively get unique records from the table without using the DISTINCT keyword.

30. Display the monthly Salary of Employees given annual salary.

CREATE TABLE employees (
	id INT NOT NULL,
	name VARCHAR(50) NOT NULL,
	annual_salary DECIMAL(10, 2) NOT NULL,
	PRIMARY KEY (id)
);
INSERT INTO employees (id, name, annual_salary)
VALUES (1, 'Muskan', 60000);

INSERT INTO employees (id, name, annual_salary)
VALUES (2, 'Pallavi', 75000);

INSERT INTO employees (id, name, annual_salary)
VALUES (3, 'Raashi', 90000);
Employees table
SELECT id, name, annual_salary / 12 as monthly_salary
FROM employees;


Output:

Output

31. Distinguish between nested subquery, correlated subquery, and join operation.

For distinguishing between these three, we have taken some example through which it would be better for you to understand the terms.

CREATE TABLE customerss (
	customer_id INT,
	customer_name VARCHAR(50),
	customer_location VARCHAR(50)
);

INSERT INTO customerss (customer_id, customer_name, customer_location)
VALUES 
	(1, 'Shila', 'New York'),
	(2, 'Palak', 'India'),
	(3, 'Rajesh', 'New York');


CREATE TABLE orderss (
	order_id INT,
	customer_id INT,
	total_amount INT
);

INSERT INTO orderss (order_id, customer_id, total_amount)
VALUES 
	(1, 1, 100),
	(2, 1, 150),
	(3, 2, 75),
	(4, 3, 200),
	(5, 3, 175);

 

customerss table

 

orderss table

Nested Subquery:

A nested subquery is a query that is embedded within another query. The inner query is performed first, and the outer query uses its results. The inner query is enclosed within parentheses and is usually placed in the WHERE or HAVING clause of the outer query. A nested subquery returns a single value or a list of values.

Example:

SELECT order_id, customer_id, total_amount
FROM orderss
WHERE customer_id IN (
	SELECT customer_id
	FROM customerss
	WHERE customer_location = 'New York'
);


Output:

output

Correlated Subquery:

It uses a value from the outer query. The inner query is performed once for each row of the outer query, and the result depends on the outer query's current row. A correlated subquery filters or joins data from two or more tables.

Example:

SELECT customer_name, (
	SELECT SUM(total_amount)
	FROM orderss
	WHERE orderss.customer_id = customerss.customer_id
) AS total_spent
FROM customerss;

 

Output:

Output

Join Operation:

When you have information about a single object or entity spread across multiple tables, you can use a join operation to combine this information into a single table. Join operations work by matching the data in a specific column between two or more tables and then combining the rows from these tables into a new table. Different types of join operations determine how data is matched and combined. You can retrieve a piece of complete information about an object or entity using join operations from multiple tables.

Example:

CREATE TABLE employees (
	employee_id INT,
	employee_name VARCHAR(50),
	department_id INT
);

INSERT INTO employees (employee_id, employee_name, department_id)
VALUES 
	(1, 'Juhi', 1),
	(2, 'Ruhi', 2),
	(3, 'Bharat', 2);

CREATE TABLE departments (
	department_id INT,
	department_name VARCHAR(50)
);

INSERT INTO departments (department_id, department_name)
VALUES 
	(1, 'Sales'),
	(2, 'Marketing');

 

Tables
SELECT employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;


Output:

output

32. What is a Non-Equi Join?

A non-equi join is a type of join operation in SQL where the join condition is based on a comparison operator other than equality, such as '>' (greater than),  '>=' (greater than or equal to), '<' (less than), '<=' (less than or equal to), or '<>' (not equal to).

Example:

CREATE TABLE table1 (
	id INT,
	value INT
);

INSERT INTO table1 (id, value) VALUES (1, 10);
INSERT INTO table1 (id, value) VALUES (2, 20);
INSERT INTO table1 (id, value) VALUES (3, 30);

CREATE TABLE table2 (
	id INT,
	value INT
);

INSERT INTO table2 (id, value) VALUES (1, 15);
INSERT INTO table2 (id, value) VALUES (2, 25);
INSERT INTO table2 (id, value) VALUES (3, 35);
table1 and table2
SELECT *
FROM table1
JOIN table2
ON table1.value > table2.value;


Output:

Output

As you can see, this query returns all possible combinations of rows where the value in table1 is greater than the corresponding value in table2.

33. What are OLAP and OLTP?

OLAP(Online analytical processing) and OLTP(Online Transaction Processing) are two different types of database systems that are used for different purposes in the data processing. OLTP systems are mainly used for recording and processing transactions in real-time. These transactions are usually brief and involve updating, inserting, or deleting data in the database. OLTP systems are built to handle a high volume of concurrent transactions while ensuring data consistency, accuracy, and availability.

In contrast, OLAP systems are used for analytical processing, which involves complex queries and data aggregation for decision-making and business intelligence. OLAP systems typically store a large amount of historical data and allow for sophisticated data analysis, such as trend analysis, forecasting, and data mining. OLAP systems are designed for quick query performance and data retrieval, even when handling large amounts of data.

To sum up, OLTP systems are primarily used for real-time transaction processing, while OLAP systems are used for complex analysis of historical data to support decision-making and business intelligence.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Frequently Asked Questions

How do I prepare for SQL interview?

To prepare for your SQL interview, first cover the basics of SQL, including concepts such as tables, queries, and relational databases. Practice writing SQL queries to retrieve, insert, update, and delete data. Understand how to use different types of joins, aggregate functions, and subqueries. Study common SQL interview questions and practice solving them. Familiarize yourself with database normalization and indexing techniques.  Finally, stay abreast of the latest advances in SQL and database management systems.

What are the basic SQL queries asked in interview? 

Basic SQL queries commonly asked in interviews include SELECT, INSERT, UPDATE, DELETE, and JOIN queries. Interviewers may also ask about filtering data using WHERE clause, sorting using ORDER BY, aggregating data with GROUP BY, and performing calculations using functions like COUNT, SUM, AVG.

How do I practice SQL queries? 

To practice SQL queries, you can use online SQL sandboxes or practise exercises and questions on websites like Coding Ninjas, Leetcode, Hackerrank, etc. You can work on sample projects and also join coding communities where you can find new problems.

What are the 5 types of SQL queries?

The five basic SQL commands are select, insert, update, delete and create. SELECT isused to retrieve data from one or more tables. INSERT is used to add new records or rows into a table. UPDATE is used to modify existing records in a table. DELETE is used to remove one or more records from a table. CREATE is used to create a new database, table, view, or other database objects.

How do SQL queries execute?

A database management system (DBMS) performs multiple steps in the execution of SQL queries, including parsing, optimization, and execution.

How does a query work?

The database management system parses, validates, and optimizes a query. Execution uses the best possible method to retrieve data. After generating results and managing transactions, cleaning comes next. Resource management and optimization are necessary for effective query execution.

Conclusion

In this blog, we have thoroughly discussed SQL interview questions. In this, we have discussed all the critical interview questions related to SQL from beginner to advanced level, which are asked in the interviews. I hope you all found these SQL interview questions interesting and helpful. Please refer below articles for more information. 

Guided path
Free
gridgp-icon
Interview guide for product based companies
12 chapters
123+ Problems
gp-badge
Earn badges and level up
Live masterclass