Are you getting ready for a SQL developer interview and need guidance? We are here to encourage and stimulate your confidence and help you to have a remarkable interview experience. The SQL interview questions we have prepared for you will help you become a job-ready and desirable candidate.
This blog will cover the collection of real-world sql interview questions which many big companies prefer to ask. Also, this blog will help you brush up on your skills. The SQL interview questions are very well written to prepare you for the big day. All SQL query questions cover a well-written answer, saving much of your interview preparation time.
We have made sure that the SQL interview questions are helpful for the fresher’s and experienced candidates, thus making them comfortable with the actual interview experience. This blog will prepare you with the popular sql interview questions preferred by companies to ask and help you clear the concept of SQL with examples.
An interview is a necessary process of conveying the message to the interviewer about how well you are comfortable and precise with the concept of the subject. This blog will make you familiarize with the practical SQL interview questions you might confront during the interview.
- It also covers SQL Scenario-Based sql questions to help you understand the general concepts of SQL.
We've divided this article into the following sections:
- SQL Basic interview questions
- SQL Intermediate interview questions
- SQL Advanced interview questions
- SQL Scenario-Based interview questions
Basic SQL Interview Questions
This section covers the basic sql interview questions that help you explain the primary concept of the subject with ease.
1. What is a Database?
This is the basic sql interview question often asked by the companies. A database is a collection of data that is stored and organized so that it can be easily accessed, managed, and updated. It is used to store and manage data that is needed by an organization or application. It allows users to store, retrieve, and manipulate data in an efficient and organized manner.
2. What is DBMS?
With confidence and ease you can answer this sql question by saying that, a database management system (DBMS) is a software program that is used to create, modify, and manage a database. It provides users with a way to store, retrieve, and manipulate data stored in a database. A DBMS is designed to allow users to interact with a database in a way that is easy to use and understand.
Examples: MySQL, Oracle, Microsoft SQL Server
3. What is RDBMS? How is it different from DBMS?
This sql question can be answered in simple words by explaining that a relational database management system (RDBMS) is a type of database management system (DBMS) that is based on the relational model of data. It is designed to store and manage data in a structured format that consists of tables with rows and columns.
RDBMSs are different from other types of DBMSs in that they allow users to create relationships between the data stored in different tables. This allows users to store and manage data more flexibly and efficiently.
4. What is SQL?
Answer this basic sql interview question by saying that Structured Query Language (SQL) is a standard programming language used to create, modify, and query relational databases. It is a declarative language, which allows users to specify what they want to do, rather than how to do it. It consists of a set of commands that can be used to perform various operations on a database, such as creating tables and indices, inserting and updating data, and querying the data for specific information.
5. What is the difference between SQL and MySQL?
This is the most frequently asked sql question. Structured Query Language (SQL) is a programming language is used to manage and organize data stored in a relational database management system (RDBMS).
MySQL is a specific RDBMS that uses SQL as its primary programming language. It is an open-source RDBMS that is popular for web applications and is widely used in the development of websites and applications
6. What is the difference between SQL and NoSQL?
SQL stands for Structured Query Language used with relational databases and is designed to store structured data, while NoSQL stands for not only SQL is used with non-relational databases and is designed to store unstructured or semi-structured data.
7. What are entities and relationships?
This sql question has simple way to explain that an entity is a person, place, thing, or event that is represented in the database. An entity can be an object, such as a customer or an employee, or it can be an abstract concept, such as an order or a payment.
A relationship is a connection or link between two or more entities. For example, in a database of customers and orders, there is a relationship between a customer and the orders that they have placed.
8. What are the types of relationships in SQL?
Relationships between entities are defined using foreign keys. A foreign key is a field in a table that is used to link that table to another table. Three types of relationships can be defined using foreign keys in SQL:
a. One-to-one
b. One-to-many
c. Many-to-many
9. What is the ACID property in a database?
ACID is a set of properties that are used to describe the behavior of a database. ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties are used to ensure that database transactions are processed reliably and that the integrity of the data is maintained.
10. What do you mean by data integrity?
Data integrity refers to the accuracy and consistency of data stored in a database. It is the quality of data that ensures that it is complete, accurate, and trustworthy.
11. What is an ALIAS command?
An alias is a temporary name that is given to a table or column in SQL. Aliases are used to make it easier to write and read SQL queries, especially when working with large and complex queries.
Get introduced to the basics of SQL. Understand in detail what is the database management and manipulation-video https://www.youtube.com/watch?v=kLEQ__thWWI
Intermediate SQL Interview Questions
The intermediate sql interview questions will guide you further in your interview with detail query description.
What are Joins and their types in SQL?
This is an interesting sql interview question which can be simply explained as,
In SQL, a join is used to combine rows from two or more tables based on a related column between them. There are several types of joins in SQL:
1. INNER JOIN: An inner join returns only the rows that match between the two tables.
2. OUTER JOIN: An outer join returns all rows from both tables, and fills in NULL values for any missing matches on either side.
There are three types of outer joins:
a. LEFT JOIN: Returns all rows from the left table, and any matching rows from the right table.
b. RIGHT JOIN: Returns all rows from the right table, and any matching rows from the left table.
c. FULL JOIN: Returns all rows from both tables, and fills in NULL values for any missing matches on either side.
d. CROSS JOIN: A cross join returns the Cartesian product of the two tables, which is a combination of every row from the left table with every row from the right table.
e. NATURAL JOIN: A natural join is similar to an inner join, but it automatically matches rows based on columns with the same name and data type in both tables.
1. Explain the difference between a left join and a right join.
This is the popular sql question you can answer with confidence that, in a LEFT JOIN, all rows from the left table are returned, and any matching rows from the right table are included. If there is no match, NULL values are returned for the right table's columns.
In a RIGHT JOIN, all rows from the right table are returned, and any matching rows from the left table are included. If there is no match, NULL values are returned for the left table's columns.
2. What is the difference between cross-join and natural join?
This sql question can be answered in simple words by starting that CROSS JOIN returns the Cartesian product of the two tables. This means that it combines every row from the left table with every row from the right table.
NATURAL JOIN automatically matches rows from the two tables based on columns with the same name and data type. It is similar to an INNER JOIN, but it does not require an ON clause or a USING clause to specify the join condition.
3. How many Aggregate functions are available in SQL?
Aggregate functions perform calculations on a set of values and return a single result.
Some aggregate functions include:
a. AVG: Calculates the average of a set of values.
b. COUNT: Counts the number of rows in a table.
c. MAX: Finds the maximum value in a set of values.
d. MIN: Finds the minimum value in a set of values.
e. SUM: Calculates the sum of a set of values.
Other common aggregate functions include STDEV, VARIANCE, and GROUPING.
4. What are constraints and their types in SQL?
Explain this basic sql interview question with ease begin by saying that a constraint is a rule that specifies how data is allowed to be stored in a database. Constraints can be used to enforce data integrity, prevent data inconsistencies, and ensure that data is accurate and valid.
There are several types of constraints:
a. NOT NULL: Ensures that a column cannot contain a NULL value.
b. UNIQUE: Ensures that all values in a column are unique.
c. PRIMARY KEY: A primary key is a column or combination of columns that uniquely identifies each row in a table. A primary key cannot contain NULL values and must be unique.
d. FOREIGN KEY: A foreign key is a column or combination of columns that reference the primary key of another table. A foreign key is used to enforce relationships between tables.
e. CHECK: Ensures that the values in a column meet a specified condition.
f. DEFAULT: Specifies a default value for a column. If no value is specified when a new row is inserted, the default value will be used.
5. What are the different types of keys in SQL?
This sql query needs explanation that a key is a column or combination of columns used to uniquely identify a row in a table. There are several types of keys in SQL:
a. PRIMARY KEY: A primary key is a column or combination of columns that uniquely identifies each row in a table. A primary key cannot contain NULL values and must be unique.
b. FOREIGN KEY: A foreign key is a column or combination of columns that reference the primary key of another table. A foreign key is used to enforce relationships between tables.
c. UNIQUE KEY: A unique key is a column or combination of columns that ensures that all values in a table are unique. A unique key can contain NULL values, but only if there is only one NULL value in the column.
d. CANDIDATE KEY: A candidate key is a column or combination of columns that could potentially be used as a primary key for a table. A table can have multiple candidate keys, but it must have only one primary key.
6. Explain the different types of SQL commands.
This sql interview question can be described in detail by mentioning that there are several types of SQL commands to interact with a database:
a. Data Definition Language (DDL): These commands are used to define the database structure or schema. Examples include CREATE, ALTER, and DROP.
b. Data Manipulation Language (DML): These commands are used to manipulate data in the database. Examples include SELECT, INSERT, UPDATE, and DELETE.
c. Data Control Language (DCL): These commands are used to control access to the data in the database. Examples include GRANT and REVOKE.
d. Transaction Control Language (TCL): These commands are used to manage transactions in the database. Examples include COMMIT and ROLLBACK.
7. What is a query? What are the types of SQL Queries?
This sql query question is directly related to the query. A query is a request for data or information from a database. A query is used to select, insert, update, or delete data, or to perform calculations on the data.
There are several types of queries in SQL:
a. SELECT: Retrieves data from a database.
b. INSERT: Adds new data to a database.
c. UPDATE: Modifies existing data in a database.
d. DELETE: Deletes data from a database.
e. CREATE: Creates a new database object, such as a table or index.
f. DROP: Deletes a database object.
g. ALTER: Modifies the structure of a database object.
h. TRUNCATE: Deletes all data from a table.
i. GRANT: Grants permissions to users.
j. REVOKE: Revokes permissions from users.
8. What is a subquery in SQL? What are the different types of subquery?
A subquery is a SELECT statement that is nested within another SELECT, INSERT, UPDATE, DELETE, or SET statement. It is used to return data that will be used in the outer query as a condition or as a value.
There are several types of subqueries in SQL:
a. Scalar subquery: Returns a single value from the inner query to the outer query.
b. Row subquery: Returns one or more rows from the inner query to the outer query.
c. Column subquery: Returns one or more columns from the inner query to the outer query.
d. Correlated subquery: Uses values from the outer query in the inner query.
9. What is the difference between DELETE, DROP and TRUNCATE commands?
The DELETE command is used to delete rows from a table. We can use a WHERE clause to specify which rows to delete or can delete all rows if you omit the WHERE clause.
DROP command is used to delete a database object, such as a table, index, or view. When we drop an object, it is permanently removed from the database, and we cannot recover it.
The TRUNCATE command is used to delete all rows from a table. Unlike DELETE, TRUNCATE does not require a WHERE clause, and is faster than DELETE, because it does not generate rollback.
10. What is CLAUSE?
A clause in SQL statement performs a specific task. It is used to specify conditions, filter data, group data, and perform calculations.
Some common clauses in SQL include:
a. SELECT: Specifies which columns to retrieve from the database.
b. FROM: Specifies the table or tables from which to retrieve data.
c. WHERE: Filters the rows that are returned by the query based on a condition.
d. GROUP BY: Groups the rows based on the values in one or more columns.
e. HAVING: Filters the groups based on a condition.
f. ORDER BY: Sorts the rows based on the values in one or more columns.
g. LIMIT: Limits the number of rows that are returned by the query.
11. What are some common clauses used with SELECT queries in SQL?
Here are some clauses used with SELECT queries in SQL:
a. WHERE: Filters the rows that are returned by the query based on a condition.
b. GROUP BY: Groups the rows based on the values in one or more columns.
c. HAVING: Filters the groups based on a condition.
d. ORDER BY: Sorts the rows based on the values in one or more columns.
e. LIMIT: Limits the number of rows that are returned by the query.
6. OFFSET: Skips a specified number of rows before returning the remaining rows.
12. What is the difference between HAVING and WHERE clauses?
WHERE and HAVING clauses filter rows from a query based on a condition.
There are some differences between these clauses:
WHERE filter rows before the GROUP BY clause is applied, while HAVING filter groups after the GROUP BY clause is applied.
WHERE is used with columns that are part of the SELECT list or that are used in the GROUP BY clause, while HAVING can be used with aggregate functions.
13. What are SQL operators?
Operators are used to specifying conditions in a query, or to perform calculations on data. Some operators in SQL include:
a. Arithmetic operators: Perform arithmetic calculations, such as addition (+), subtraction (-), multiplication (*), and division (/).
b. Comparison operators: Compare values, such as equal to ( = ), not equal to ( <> ), greater than ( > ), less than ( < ), greater than or equal to ( >= ), and less than or equal to ( <= ).
c. Logical operators: Combine conditions, such as AND, OR, and NOT.
d. String operators: Perform operations on strings, such as concatenation (||).
14. What is the difference between Union and Union All operators?
UNION and UNION ALL operators combine the results of two or more SELECT queries. There are some key differences between these operators:
UNION combines the results of the SELECT queries and removes duplicate rows, while UNION ALL includes all rows, including duplicates.
UNION requires that the SELECT queries have the same number of columns and data types, while UNION ALL does not have this requirement.
15. What are UNION, MINUS, and INTERSECT commands?
UNION, MINUS, and INTERSECT commands combine or compare the results of two or more SELECT queries.
a. UNION combines the results of the SELECT queries and removes duplicate rows.
b. MINUS returns the rows from the first SELECT query that are not in the second SELECT query.
c. INTERSECT returns the rows that are common to both SELECT queries.
16. What is the difference between BETWEEN and IN operators in SQL?
BETWEEN and IN operators are used to specify a range of values or a list of values in a query.
There are some differences between these operators:
a. BETWEEN specifies a range of values, while IN specifies a list of values.
b. BETWEEN requires two values to define the range, while IN can take multiple values in the list.
17. What are normalization and denormalization in SQL?
Normalization is the process of organizing a database that minimizes redundancy and dependency and helps to ensure the integrity of the data. Normalization is usually done by dividing a large table into smaller tables and creating relationships between them using foreign keys.
Denormalization is the process of deliberately adding redundancy to a database by introducing redundant copies of data, to improve performance. It is done to speed up queries that join multiple tables or to improve the performance of queries that retrieve large amounts of data.
18. What are all the different normalizations?
There are several different normal forms used to normalize a database are:
1. First Normal Form (1NF): A table is in 1NF if all of the following conditions are met:
a. The table has a primary key.
b. There are no repeating groups of data in the table.
c. All of the columns in the table are atomic, meaning that each column contains a single value.
2. Second Normal Form (2NF): A table is in 2NF if it is in 1NF, and all of the following conditions are met:
a. All non-key columns are dependent on the entire primary key.
b. There are no partial dependencies in the table.
3. Third Normal Form (3NF): A table is in 3NF if it is in 2NF, and all of the following conditions are met:
a. There are no transitive dependencies in the table.
b. Boyce-Codd Normal Form (BCNF): A table is in BCNF if it is in 3NF, and all of the following conditions are met:
c. Every determinant is a candidate key.
4. Fourth Normal Form (4NF): A table is in 4NF if it is in BCNF, and all of the following conditions are met:
a. There are no multi-valued dependencies in the table.
5. Fifth Normal Form (5NF): A table is in 5NF if it is in 4NF, and all of the following conditions are met:
a. There are no join dependencies in the table.
Normal forms are hierarchical, which means if a table is in a higher normal form is also in all the lower normal forms.
19. What is ETL in SQL?
This is a important sql interview question frequently asked, so answer precisely stating that ETL stands for Extract, Transform, Load. It is a process used to extract data from different sources, transform the data into a format that is suitable for analysis, and load the data into a target database or data warehouse.
The process of ETL involves the following steps:
a. Extract: Data is extracted from various sources, such as databases, flat files, or APIs.
b. Transform: The extracted data is transformed and cleaned up, such as by removing duplicates, formatting data types, or applying calculations.
c. Load: The transformed data is loaded into the target database or data warehouse.
ETL tools are used to automate the process of ETL and can be used to schedule and run the ETL process regularly. ETL is an important component of data integration and data warehousing and is used to make data from different sources available for analysis and reporting.
20. What is an index? Explain the types of indexes.
The most common sql questions asked needs an simple explanation. An index is a data structure that improves the performance of queries on a table. It allows the database to quickly locate rows without having to scan the entire table. This can improve the performance of queries.
Types of indexes in SQL:
a. Clustered Index: A clustered index physically rearranges the rows in the table based on the indexed columns. There can only be one clustered index per table.
b. Non-clustered Index: A non-clustered index does not rearrange the rows in the table, but creates a separate data structure that stores the indexed columns and pointers to the rows. A table can have multiple non-clustered indexes.
c. Unique Index: A unique index enforces uniqueness on the indexed columns, meaning that no two rows in the table can have the same values in the indexed columns.
21. How clustered indexes are different from non-clustered indexes.
This particular sql interview question needs mindful explanation which states that the clustered index is an index that physically rearranges the rows in the table based on the indexed columns.
A non-clustered index is an index that does not rearrange the rows in the table but creates a separate data structure that stores the indexed columns and pointers to the rows.
Difference between clustered and non-clustered indexes:
1. Structure: A clustered index rearranges the rows in the table based on the indexed columns, while a non-clustered index creates a separate data structure that stores the indexed columns and pointers to the rows.
2. The number of indexes: A table can have only one clustered index because the rows are physically rearranged based on the indexed columns. However, a table can have multiple non-clustered indexes, because each non-clustered index creates a separate data structure.
3. Performance: A clustered index can improve the performance of queries that use the indexed columns because the data is stored in sorted order. However, inserting or updating rows in a table with a clustered index can be slower, because the rows have to be physically rearranged. Non-clustered indexes can improve the performance of queries that use the indexed columns, but the improvement may not be as significant as with a clustered index. Inserting or updating rows in a table with a non-clustered index is usually faster because the rows are not physically rearranged.
4. Disk space: A clustered index uses more disk space than a non-clustered index because it stores the actual data rows in the index. A non-clustered index uses less disk space because it only stores the indexed columns and pointers to the rows.
Advanced SQL Interview Questions
Advanced sql interview questions cover a set of most important sql query questions that has proved very helpful for the candidate to have a remarkable interview experience.
1. What is a TRIGGER in SQL?
This sql interview question can be explained as a trigger is a special type of stored procedure that is executed automatically by the database in response to certain events. These are used to enforce business rules, maintain data integrity, and perform other tasks when data is modified in a table and are defined using the CREATE TRIGGER statement.
Triggers can be activated by one of the following events:
a. INSERT
b. UPDATE
c. DELETE
Triggers can be defined to execute before or after the event that activates them, and can also be defined to execute once for each row affected by the event, or once for the entire event.
Triggers are often used to enforce constraints on data, such as ensuring that a value is within a certain range or that a value is unique. They can also be used to update related tables when data is modified, or to perform other tasks such as logging or sending notifications.
2. What are views? What are the types of views in SQL?
Make sure you keep your reply specific while answering the sql interview questions. In SQL, a view is a virtual table that is based on the result set of a SELECT statement. It does not physically store data, but rather displays data that is stored in other tables.
Views can be used for several purposes, such as:
a. Simplifying complex queries
b. Providing restricted access to data
c. Combining data from multiple tables
There are two types of views in SQL:
Regular views: It is based on a SELECT statement that defines the structure and data of the view. A regular view can be queried like a table, but the data is not stored in the database. It is created using the CREATE VIEW statement.
Materialized views: A materialized view is similar to a regular view, but the data is physically stored in the database. This can improve the performance of queries on the view, as the data does not need to be re-evaluated each time the view is queried. However, the data in a materialized view may not always be up to date, as it is not automatically refreshed when the data in the underlying tables is modified.
3. State the differences between views and tables.
This sql question can be specifically explained by mentioning that a view is a virtual table that is based on the result set of a SELECT statement. It does not physically store data, but rather displays data that is stored in other tables.
A table is a physical storage location for data within a database. Tables store data that is entered into a database, as well as the structure of the data.
Here are some key differences between views and tables:
a. Structure: A view does not have its own structure, as it is based on the structure of the underlying tables. A table has its own structure, defined by the columns and data types of the table.
b. Storage: A view does not physically store data, it only displays data from the underlying tables. A table stores data in the database.
c. Modification: Data in a view cannot be directly modified, as it is only a display of data from the underlying tables. Data in a table can be modified, either by updating existing rows or inserting new rows into the table.
4. Indexing: A view cannot be indexed, as it does not physically store data. A table can be indexed to improve the performance of queries that access the table.
5. Security: A view can be used to provide restricted access to data in the underlying tables. For example, a view can be created that only displays a subset of the columns in a table, or that filters the rows based on certain criteria. Tables can also be secured, but this is typically done at the database level rather than at the individual table level.
4. What is a stored procedure? Give an example.
A stored procedure is a precompiled SQL code that can be called by the database management system (DBMS) or by applications to perform a specific task. A Stored procedures can accept input parameters and return multiple output parameters, making them very flexible and efficient for many types of tasks.
Example: A Stored procedure that accepts an input parameter (customer's ID) and returns multiple output parameters like the customer's name, address, and phone number:
CREATE PROCEDURE CustomerInfo (IN cust_id INT, OUT cust_name VARCHAR(255), OUT cust_address VARCHAR(255), OUT cust_phone VARCHAR(255))
BEGIN
SELECT name, address, phone
INTO cust_name, cust_address, cust_phone
FROM customers
WHERE id = cust_id;
END
To call this stored procedure, we would use a SQL statement like:
CALL CustomerInfo(12345, @name, @address, @phone);
This would execute the stored procedure and return the name, address, and phone number of the customer with an ID of 12345.
5. What are some advantages and Disadvantages of a Stored Procedure?
The most popular sql interview question can be answered with ease by describing the advantages of using stored procedures:
a. Improved performance: Stored procedures are precompiled, which means that the database server can execute them more efficiently than if the SQL statements were sent directly from the application.
b. Reduced network traffic: Stored procedures are executed on the database server, which means that the results can be returned to the client application without sending the entire SQL statement over the network.
c. Improved security: Stored procedures can be granted specific permissions, which means that you can control which users or applications are allowed to execute them.
d. Reusability: Stored procedures can be used by multiple applications and users, which means that you can write a stored procedure once and use it in many different contexts.
Disadvantages of using stored procedures:
a. Complexity: Stored procedures can be complex to develop and maintain, especially if they contain multiple nested loops or conditional statements.
b. Lack of flexibility: Stored procedures are more difficult to modify than regular SQL statements, because you have to modify the stored procedure itself rather than just sending a new SQL statement to the database.
c. Portability: Stored procedures are specific to a particular database management system (DBMS), which means that they may not be portable to other DBMSs.
d. Lack of visibility: Stored procedures are executed on the server which means that it can be difficult to debug problems or understand exactly what is happening inside the stored procedure.
6. What do you mean by recursive stored procedure?
Keep your approach to the point while answering the sql interview questions. Explain by indicating that stored procedure is a precompiled SQL code that can be called by the database management system (DBMS) or by applications to perform a specific task. A stored procedure can be recursive, which means that it can call itself to repeat a task. This can be useful for tasks that require repeated execution, such as querying a database for data or processing data in a specific way. Recursive stored procedures can be useful for tasks that require multiple iterations or for tasks that have a complex set of steps that need to be performed in a specific order. However, it is important to use recursive stored procedures carefully, as they can consume a lot of resources and can potentially cause performance issues if not used properly.
7. What is a cursor? How to use a cursor?
A cursor is a temporary work area created in the system memory when a SELECT statement is executed. It is used to retrieve data from a result set one row at a time. It is particularly useful when you need to perform an operation on each row in a table, such as updating the values in certain columns based on the values in other columns.
To use a cursor, we must declare it first.
The basic syntax for declaring a cursor is:
DECLARE cursor_name CURSOR FOR
SELECT column1, column2, ...
FROM table
WHERE condition;
Once the cursor is declared, we can open it and fetch rows from it using the following statements:
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO variable1, variable2, ...;
8. What is the difference between the RANK() and DENSE_RANK() functions?
Answer this sql question with simple explanation and related example.
The RANK() and DENSE_RANK() functions are both used to rank rows in a result set based on the values in one or more columns. However, there is a difference in the way they assign ranks to rows:
a. The RANK() function assigns a unique rank to each row and leaves a gap in the ranking when there are ties. For example, if there are three rows with the same value in the ranking column, the RANK() function will assign the same rank to all three rows, and the next rank will be skipped.
b. The DENSE_RANK() function also assigns a unique rank to each row, but it does not leave a gap in the ranking for tied rows. For example, if there are three rows with the same value in the ranking column, the DENSE_RANK() function will assign the same rank to all three rows, and the next rank will be assigned to the next row in the result set, regardless of whether it is tied or not.
Image-case when in SQL
9. What is the case when in SQL?
The CASE statement in SQL allows you to perform different actions based on different conditions. It is often used in the SELECT statement to return a value based on a set of conditions.
The basic syntax for a CASE statement:
CASE
WHEN condition THEN result
WHEN condition THEN result
ELSE result
END
10. What is SQL Injection?
SQL injection is a type of security vulnerability that allows an attacker to insert malicious code into a SQL statement, via user input. This can be used to gain unauthorized access to a database or to retrieve sensitive data, such as passwords and credit card numbers.
SQL injection attacks are a common method used by hackers to gain access to databases. They can be difficult to prevent, as they often involve injecting code into user input fields, such as search boxes or login forms.
To prevent SQL injection attacks, it is important to use prepared statements and parameterized queries and to validate and sanitize all users’ input. This can help to ensure that only valid and safe data is inserted into the database.
11. What are CRUD operations in SQL?
Explain this sql question by stating that in SQL, CRUD stands for Create, Read, Update, and Delete. These are the four basic functions of persistent storage.
a. Create - Inserting data into a database
b. Read - Reading data from a database
c. Update - Updating existing data in a database
d. Delete - Deleting data from a database
These CRUD operations are used to manage the data in a database. For example, we can use the "Create" operation to add new records to a database, the "Read" operation to retrieve data from a database, the "Update" operation to modify existing data in a database, and the "Delete" operation to delete data from a database.
Scenario-Based SQL Interview Questions
|
Emp_ID |
First_name |
Last_name |
Dept |
Project |
Add |
DOB |
Gender |
|
1 |
Ranjit |
Kapoor |
HR |
P1 |
Hyderabad |
01/11/1976 |
M |
|
2 |
Divya |
Mishra |
Admin |
P2 |
Delhi |
04/05/1968 |
F |
|
3 |
Rohit |
Mehra |
Account |
P3 |
Mumbai |
06/01/1980 |
M |
|
4 |
Sonal |
Jain |
HR |
P1 |
Jaipur |
08/05/1992 |
F |
|
5 |
Deepak |
Roy |
Admin |
P2 |
Mumbai |
03/10/1994 |
M |
|
Emp_ID |
Position |
DOJ |
Salary |
|
1 |
Manager |
02/01/2023 |
400000 |
|
2 |
Executive |
01/01/2023 |
85000 |
|
3 |
Executive |
02/01/2023 |
90000 |
|
2 |
Lead |
01/01/2023 |
100000 |
|
1 |
Manager |
02/01/2023 |
500000 |
Write SQL queries based on the above two tables:
1. To fetch the First_name from the Emp_Info table in upper case and use the ALIAS name as
Emp_Name.
SELECT UPPER(First_name) AS Emp_Name FROM Emp_Info;
2. To fetch the number of employees working in the Dept “Admin”.
SELECT COUNT(*) FROM Emp_Info WHERE Dept = ' Admin ';
3. To get the current date.SELECT GETDATE();
4. To get the first three characters of Last_name from the Emp_Info table.
SELECT SUBSTRING(Last_name, 1, 3) FROM Emp_Info;
5. Create a new table which is a copy of another table.
CREATE TABLE New AS SELECT * FROM Emp_Info;
6. Find all the employees whose salary is between 80000 to 280000.
SELECT * FROM Emp_Position WHERE Salary BETWEEN 80000 AND 280000;
7. Find the names of employees that begin with ‘R’
SELECT * FROM Emp_Info WHERE First_name LIKE 'R%';
8. Write a query to fetch the top N records.
SELECT * FROM Emp_Position ORDER BY Salary DESC LIMIT N;
9. Get First_name and Last_name in a single column as “Full_Name”. The first name and the last name must be separated by a space.
SELECT CONCAT(First_name, ‘ ‘, Last_name) AS ‘Full_Name’ FROM Emp_Info;
10. Find number of employees whose DOB is between 01/01/1970 to 31/12/1990 and are grouped according to gender
SELECT COUNT(*), Gender FROM Emp_Info WHERE DOB BETWEEN ‘01/1970’ AND ‘31/12/1990’ GROUP BY Gender;
11. Fetch all records from the Emp_Info table order by Last_name in descending order and Dept in ascending order.
SELECT *FROM Emp_Info ORDER BY Last_name desc, Dept asc;
12. Fetch details of employees whose Last_name ends with an alphabet ‘y’ and contains three alphabets.
SELECT * FROM Emp_Info WHERE Last_name LIKE ‘____a’;
13. Fetch details of all employees excluding the employees with first names, “Divya” and “Rohit” from the Emp_Info table.SELECT * FROM Emp_Info WHERE First_name NOT IN (‘Divya’, ‘Rohit’);
14. Fetch details of employees with the address as “Jaipur”.
SELECT * FROM Emp_Info WHERE Add=”Jaipur”;
15. Write a query to fetch all employees who hold the lead position.
SELECT E.First_name, E.Last_name, P.Position FROM Emp_Info E INNER JOIN Emp_Position P ONE.Emp_ID = P.Emp_ID AND P.Position IN (‘Lead’);
16. Fetch the department-wise count of employees sorted by department’s count in ascending order.
SELECT Dept, count(Emp_ID) AS Emp_Dept_Count FROM Emp_Info GROUP BY Dept ORDER BY Emp_Dept_Count ASC;
17. Write a query to calculate the even and odd records from a table.Even records from a table:SELECT Emp_ID FROM (SELECT rowno, Emp_ID from Emp_Info) WHERE MOD(rowno,2)=0;
Odd records from a table:SELECT Emp_ID FROM (SELECT rowno, Emp_ID from Emp_Info) WHERE MOD(rowno,2)=1;
18. Get employee details from the Emp_Info table who have a date of joining(DOJ) in the Emp_Position table.
SELECT * FROM Emp_Info E WHERE EXISTS (SELECT * FROM Emp_Position P WHERE E.Emp_Id = P.Emp_Id);
19. Get two minimum and maximum salaries from the Emp_Position table.Two minimum salaries:
SELECT DISTINCT Salary FROM Emp_Position E1 WHERE 2 >= (SELECT COUNT(DISTINCT Salary) FROM Emp_Position E2 WHERE E1.Salary >= E2.Salary) ORDER BY E1.Salary DESC;
Two maximum salaries: SELECT DISTINCT Salary FROM Emp_Position E1 WHERE 2 >= (SELECT COUNT(DISTINCT Salary) FROM Emp_Position E2 WHERE E1.Salary <= E2.Salary) ORDER BY E1.Salary DESC;
20. Find the Nth highest salary without using limit keyword.
SELECT Salary FROM Emp_Position E1 WHERE N-1 = ( SELECT COUNT( DISTINCT ( E2.Salary ) )FROM Emp_Position E2 WHERE E2.Salary > E1.Salary );
21. Get duplicate records from a table.
SELECT Emp_ID, First_name, Dept COUNT(*) FROM Emp_Info GROUP BY Emp_ID, First_name,
DeptHAVING COUNT(*) > 1;
22. Get the last 4 records from the Emp_Info table.
SELECT * FROM Emp_Info WHERE Emp_ID <=4 UNION SELECT * FROM (SELECT * FROM Emp_Info E ORDER BY E.Emp_ID DESC) AS E1 WHERE E1.Emp_ID <=4;
23. Display the first and the last record from the Emp_Info table.Display first record:
SELECT * FROM Emp_Info WHERE Emp_ID = (SELECT MIN(Emp_ID) FROM Emp_Info);
Display last record:SELECT * FROM Emp_Info WHERE Emp_ID = (SELECT MAX(Emp_ID) FROM Emp_Info);
24. Get Departments that have less than 2 employees working in them.SELECT Dept, COUNT(Emp_ID) as ‘Emp_No’ FROM Emp_Info GROUP BY DeptHAVING COUNT(Emp_ID) < 2;
25. Get Postion with the total salaries paid for each of them.
SELECT Position, SUM(Salary) FROM Emp_Position GROUP BY Position;
26. Fetch 50% of records from the Emp_Info table.
SELECT * FROM Emp_Info WHERE Emp_ID <= (SELECT COUNT(Emp_ID)/2 from Emp_Info);
