Sql Mock Questions - Learn DA-DS Course in Hindi

SQL Mock Interview Questions

Top 50+ SQL Interview Questions and Answers (Latest 2024)

1.What is SQL?

SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create a database, to create a table in the database, to retrieve data or update a table in the database, etc. SQL works by allowing users to interact with a database through statements, which are used to perform operations like retrieving and modifying data.

2. What is the difference between SQL and MySQL?

SQL is a standardized language used to interact with relational databases. It defines how you query, update, insert, and manage data in a database.. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that used SQL to interact with data.

3. What is RDBMS? How is it different from DBMS?

RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. Most RDBMS like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift.

Feature	DBMS	RDBMS
Data Structure	Can store data in any format (file-based, hierarchical, etc.)	Data is stored in relational tables (rows and columns).
Data Relationships	No relationships between tables or data	Tables are related through foreign keys and primary keys.
Normalization	Not supported or loosely supported	Normalization is supported to reduce data redundancy.
Support for SQL	May not support SQL or limited support	Fully supports SQL for querying and managing data.
Data Integrity	Limited integrity features	Strong data integrity enforcement (via constraints, keys, etc.).
ACID Properties	Not strictly enforced	Strict enforcement of ACID properties for transactions.
Examples	File systems, XML databases, NoSQL DBs	MySQL, Oracle, PostgreSQL, SQL Server, SQLite.

4. What is a database?

A database is an organized collection of data in the form of tables that can be easily accessed, managed, and updated. Databases are designed to store, retrieve, and manipulate data efficiently

5. Does SQL support programming language features?

SQL (Structured Query Language) is primarily a query language used for interacting with relational databases. However, over time, SQL has evolved to include various programming features that enhance its ability to manage and manipulate data efficiently.

Variables and constants
Control flow (if-else, loops, case statements)
Functions and stored procedures
Error handling
Transactions and ACID properties
Triggers and events

6. What is the difference between CHAR and VARCHAR datatype in SQL?

Feature	CHAR	VARCHAR
Length	Fixed length. Always reserves `n` bytes.	Variable length. Reserves only required space.
Storage	Pads with spaces if the string is shorter than the defined length.	Uses only the amount of storage needed for the actual string.
Space Efficiency	Can waste space if data is shorter than the specified length.	More space-efficient, as it only stores the actual data.
Example	`CHAR(10)` always stores 10 characters, even if only 5 characters are provided.	`VARCHAR(10)` stores up to 10 characters but uses only as much space as required.

7. What do you mean by data definition language?

Data definition language or DDL allows to execution of queries like CREATE, DROP, and ALTER. SQL queries that define the structure of the data.

8. What do you mean by data manipulation language?

Data manipulation Language or DML is used to access or manipulate data in the database. It allows us to perform the queries:

Insert data or rows in a database
Delete data from the database
Retrieve or fetch data
Update data in a database.

9. What are the different types of SQL commands?

10. What is a UNIQUE constraint?

A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table.

Example:

CREATE TABLE Students (   /* Create table with a single field as unique */
   ID INT NOT NULL UNIQUE
   Name VARCHAR(255)
);

CREATE TABLE Students (   /* Create table with multiple fields as unique */
   ID INT NOT NULL
   LastName VARCHAR(255)
   FirstName VARCHAR(255) NOT NULL
   CONSTRAINT PK_Student
   UNIQUE (ID, FirstName)
);

ALTER TABLE Students   /* Set a column as unique */
ADD UNIQUE (ID);
ALTER TABLE Students   /* Set multiple columns as unique */
ADD CONSTRAINT PK_Student   /* Naming a unique constraint */
UNIQUE (ID, FirstName);

11. What is a Query?

A query is a request for data or information from a database table or combination of tables.

SELECT *   FROM students WHERE student_id = 1;    /* select query */

12. What is a primary key in SQL?

It is a unique identifier for each record in a table. It ensures that each row in the table has a distinct and non-null value in the primary key column. Primary keys enforce data integrity and create relationships between tables.

13. What is a foreign key?

It is a field in one table referencing the primary key in another. It establishes a relationship between the two tables, ensuring data consistency and enabling data retrieval across tables.

14. Explain the difference between DELETE and TRUNCATE commands.

The DELETE command is used to remove particular rows from a table based on a condition, allowing you to delete specific records. TRUNCATE, on the other hand, removes all rows from a table. U can not define condition.

-- trucate vs delete;
truncate table emp;-- it will delete all records but not table structure
delete from emp; -- it will all delete all records but not table

15. What do you mean by a NULL value in SQL?

A NULL value in SQL represents the absence of data in a column. It is not the same as an empty string or zero; it signifies that the data is missing or unknown.

16. What is a table and a field in SQL?

In SQL, a table is a structured data collection organized into rows and columns. Each column in a table is called a field or attribute.

17. What is a constraint in SQL? Name some of them.

A constraint in SQL defines rules or restrictions that apply to data in a table, ensuring data integrity.

PRIMARY KEY: A combination of NOT NULL and UNIQUE. It uniquely identifies each record in a table.
FOREIGN KEY: A foreign key is a column or a set of columns used to establish a link between the data in two tables.
UNIQUE: Ensures that all values in a column (or combination of columns) are unique across the table.
CHECK: Ensures that all values in a column meet a specific condition or rule.
NOT NULL: Ensures that a column cannot have a NULL value.
DEFAULT: Provides a default value for a column when no value is specified during an INSERT operation.

18. How to you use the WHERE clause?

The WHERE clause within SQL queries serves the purpose of selectively filtering rows according to specified conditions, thereby enabling you to fetch exclusively those rows that align with the criteria you define. For example:

SELECT * FROM employees WHERE department = 'HR';

19. What are the different operators available in SQL?

Arithmetic Operators
Logical Operators
Comparison Operators

Operator Type	Operator	Description	Example
Arithmetic	`+`	Addition	`Quantity + 10`
	`-`	Subtraction	`Quantity - 5`
	`*`	Multiplication	`Quantity * PricePerUnit`
	`/`	Division	`TotalAmount / Quantity`
	`%`	Modulus (remainder of division)	`Quantity % 2`
Relational	`=`	Equal to	`Quantity = 5`
	`!=` / `<>`	Not equal to	`PricePerUnit != 300`
	`<`	Less than	`Quantity < 5`
	`>`	Greater than	`PricePerUnit > 100`
	`<=`	Less than or equal to	`Quantity <= 10`
	`>=`	Greater than or equal to	`PricePerUnit >= 150`
Logical	`AND`	Both conditions must be true	`Quantity > 2 AND Price < 100`
	`OR`	At least one condition must be true	`Quantity < 3 OR Price > 100`
	`NOT`	Reverses the logical state of condition	`NOT (Quantity < 3)`

20. What is a stored procedure?

A stored procedure in SQL is a set of SQL statements that are stored and executed on the database server. It is a way to group together a set of SQL commands that can be reused, providing a more efficient way to perform operations such as data manipulation, data validation, or other business logic.

21. What are aggregate and scalar functions?

Aggregate functions: Aggregate functions operate on multiple rows of a table and return a single result.
Scalar functions: Scalar functions operate on a single value (scalar) and return a single value. They are typically used to transform, modify, or manipulate individual values.

Function Type	Function	Description	Example
Aggregate	`COUNT()`	Counts rows in a result set.	`COUNT(*)`
	`SUM()`	Adds up the values in a numeric column.	`SUM(Salary)`
	`AVG()`	Calculates the average of a numeric column.	`AVG(Salary)`
	`MIN()`	Returns the minimum value in a column.	`MIN(Salary)`
	`MAX()`	Returns the maximum value in a column.	`MAX(Salary)`
Scalar	`LEN()` / `LENGTH()`	Returns the length of a string.	`LEN(FirstName)`
	`UPPER()`	Converts a string to uppercase.	`UPPER(FirstName)`
	`LOWER()`	Converts a string to lowercase.	`LOWER(FirstName)`
	`ROUND()`	Rounds a number to a specified number of decimal places.	`ROUND(Salary, 2)`
	`CONCAT()`	Concatenates two or more strings into one.	`CONCAT(FirstName, ' ', LastName)`
	`COALESCE()`	Returns the first non-`NULL` value in a list of expressions.	`COALESCE(PhoneNumber, 'Not Available')`

22. What is an ALIAS command?

Aliases are the temporary names given to a table or column for the purpose of a particular SQL query. It is used when the name of a column or table is used other than its original name, but the modified name is only temporary.

23. What is the difference between BETWEEN and IN operators in SQL?

BETWEEN: The BETWEEN operator is used to fetch rows based on a range of values.
For example,

SELECT * FROM Students 
WHERE ROLL_NO BETWEEN 20 AND 30;

IN: The IN operator is used to check for values contained in specific sets.
For example,

SELECT * FROM Students 
WHERE ROLL_NO IN (20,21,23);

24. What is the difference between primary key and unique constraints?

The primary key cannot have NULL values, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constraints. The primary key creates the clustered index automatically but the unique key does not.

Aspect	Primary Key	Unique Key
Uniqueness	Ensures uniqueness for the column(s).	Ensures uniqueness for the column(s).
Nullability	Cannot accept `NULL` values.	Can accept `NULL` values.
Number per Table	A table can only have one primary key.	A table can have multiple unique keys.
Example	`EmployeeID INT PRIMARY KEY`	`Email VARCHAR(100) UNIQUE`

25. What is a join in SQL? What are the types of joins?

An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are:

Join Type	Description	Example Use Case
INNER JOIN	Returns only matching rows from both tables.	Get a list of employees and their departments.
LEFT JOIN	Returns all rows from the left table, and matching rows from the right table (or `NULL` if no match).	Get all employees and their departments, even those without a department.
RIGHT JOIN	Returns all rows from the right table, and matching rows from the left table (or `NULL` if no match).	Get all departments and their employees, even those without employees.
FULL JOIN	Returns all rows when there is a match in either the left or right table.	Get all employees and all departments, including those with no match.
CROSS JOIN	Returns all possible combinations (Cartesian product) of rows from both tables.	Generate all combinations of colors and shapes.
SELF JOIN	Joins a table with itself.	Get employees and their managers from the same `Employees` table.

26. What is an index?

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Data can be stored only in one order on a disk. To support faster access according to different values, a faster search like a binary search for different values is desired. For this purpose, indexes are created on tables. These indexes need extra space on the disk, but they allow faster search according to different frequently searched values.

27. What is the On Delete cascade constraint?

An ‘ON DELETE CASCADE’ constraint is used in MySQL to delete the rows from the child table automatically when the rows from the parent table are deleted.

28. Explain WITH clause in SQL?

The WITH clause is used to define Common Table Expressions (CTEs) in SQL. A CTE is a temporary result set that is defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement.

29. Write down various types of relationships in SQL?

One-to-One Relationship.
One to Many Relationships.
Many to One Relationship.

30. What is a trigger?

A trigger in SQL is a special type of stored procedure that is automatically executed or fired when certain events occur in the database. Triggers are used to enforce business rules, automate system tasks, and maintain data integrity. They are associated with a table or view and are activated by events such as INSERT, UPDATE, or DELETE operations.

31. What is the difference between DELETE and TRUNCATE SQL commands?

SQL DELETE	SQL TRUNCATE
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.	TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
DELETE command is slower than the identityTRUNCATE command.	While the TRUNCATE command is faster than the DELETE command.
To use Delete you need DELETE permission on the table.	To use Truncate on a table we need at least ALTER permission on the table.
The identity of the column retains the identity after using DELETE Statement on the table.	The identity of the column is reset to its seed value if the table contains an identity column.
The delete can be used with indexed views.	Truncate cannot be used with indexed views.

32. What is Case WHEN in SQL?

The CASE statement in SQL is used to add conditional logic to your queries. It allows you to perform IF-THEN-ELSE style operations within a query, enabling conditional transformations of data.

Syntax:

CASE case_value    WHEN when_value THEN statement_list   
[WHEN when_value THEN statement_list] …    [ELSE statement_list]END CASE

33. What is the difference between TRUNCATE and DROP statements?

DROP	TRUNCATE
The DROP command is used to remove the table definition and its contents.	Whereas the TRUNCATE command is used to delete all the rows from the table.
In the DROP command, table space is freed from memory.	While the TRUNCATE command does not free the table space from memory.
DROP is a DDL(Data Definition Language) command.	Whereas the TRUNCATE is also a DDL(Data Definition Language) command.
In the DROP command, a view of the table does not exist.	While in this command, a view of the table exists.
In the DROP command, integrity constraints will be removed.	While in this command, integrity constraints will not be removed.
In the DROP command, undo space is not used.	While in this command, undo space is used but less than DELETE.
The DROP command is quick to perform but gives rise to complications.	While this command is faster than DROP.

34. Which operator is used in queries for pattern matching?

LIKE operator: It is used to fetch filtered data by searching for a particular pattern in the where clause.

Example: Find all employees whose names start with “A”:

SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE 'A%';

35. Define SQL Order by Clause?

The ORDER BY clause in SQL is used to sort the fetched data in either ascending or descending order.

By default ORDER BY sorts the data in ascending order.
We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

SELECT EmployeeName, Salary
FROM Employees
ORDER BY Salary;

36. Explain SQL Having statement?

The HAVING clause in SQL is used to filter records after an aggregation has been performed (i.e., after GROUP BY).

Unlike the WHERE clause, which filters rows before aggregation, the HAVING clause filters groups of rows that are created by the GROUP BY clause. It allows you to apply conditions to the aggregated results, such as counts, sums, averages, or other aggregate functions.

SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID
HAVING SUM(SalesAmount) > 500;

37. Explain SQL AND OR statement with an example?

In SQL, the AND and OR operators are used to combine multiple conditions in a WHERE clause. They help to filter records based on more than one condition, allowing you to refine your queries.

AND: All conditions must be true for a record to be selected.
OR: At least one condition must be true for a record to be selected.

38. Difference between Commit and Rollback commands?

COMMIT	ROLLBACK
COMMIT permanently saves the changes made by the current transaction.	ROLLBACK undo the changes made by the current transaction.
The transaction can not undo changes after COMMIT execution.	Transaction reaches its previous state after ROLLBACK.
When the transaction is successful, COMMIT is applied.	When the transaction is aborted, ROLLBACK occurs.

39. Are NULL values the same as zero or a blank space?

NO, In SQL null means data might not be provided or there is no data.

40. What are ACID properties?

ACID stands for Atomicity, Consistency, Isolation, and Durability.

Consider a transaction in a banking system where you are transferring funds between two accounts:

Atomicity: The transfer is an all-or-nothing operation. If $100 is deducted from Account A, the same $100 will be added to Account B, or nothing happens (if a failure occurs).
Consistency: After the transaction, both accounts must reflect the correct balance according to the rules of the system. The balances must not go negative (if not allowed), and all business rules must be respected.
Isolation: If another transaction tries to read or modify the same accounts while the first one is in progress, the second transaction must either wait or operate on a snapshot of the data to avoid inconsistencies. The changes from the first transaction are invisible to the second until it is completed.
Durability: After the transfer is committed, even if the database crashes, the changes to the account balances will be recovered and will not be lost.

BEGIN TRANSACTION;  -- Start the transaction

-- Deduct money from Account A
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 'A';

-- Add money to Account B
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 'B';

-- Insert the transfer details into the Transfers table
INSERT INTO Transfers (FromAccount, ToAccount, Amount, TransferDate)
VALUES ('A', 'B', 100, GETDATE());

-- If all operations are successful, commit the transaction
COMMIT TRANSACTION;

41. What is the SQL query to display the current date?

Select curdate();

42. How do we avoid getting duplicate entries in a query without using the distinct keyword?

While the DISTINCT keyword is the most common way to eliminate duplicates in SQL, other approaches like GROUP BY, window functions (ROW_NUMBER()), EXISTS, and JOIN can also help you avoid duplicate entries depending on the nature of your query.

43. What is a Cross-Join?

A CROSS JOIN is a type of join that produces a Cartesian product of two tables. This means that every row from the first table is combined with every row from the second table.

44. What is a Self-Join?

A self join is a type of join where a table is joined with itself. It is used when you need to relate rows within the same table.

45. What are UNION, MINUS and INTERSECT commands?

Operation	Description	Example Query	Example Result
UNION	Combines the results of two queries and removes duplicate rows.	`SELECT Name FROM Employees UNION SELECT Name FROM Contractors`	Alice, Bob, Charlie
MINUS	Returns the rows in the first query that are not present in the second query. (Oracle-specific)	`SELECT Name FROM Employees MINUS SELECT Name FROM Contractors`	Alice
INTERSECT	Returns only the rows that are present in both queries.	`SELECT Name FROM Employees INTERSECT SELECT Name FROM Contractors`	Bob
UNION ALL	Similar to `UNION`, but keeps duplicates from both queries.	`SELECT Name FROM Employees UNION ALL SELECT Name FROM Contractors`	Alice, Bob, Bob, Charlie
EXCEPT	SQL Server/PostgreSQL equivalent of `MINUS`. Returns rows in the first query that are not in the second.	`SELECT Name FROM Employees EXCEPT SELECT Name FROM Contractors`	Alice

46. What is Normalization?

Normalization is the process of organizing a relational database to minimize redundancy and dependency by dividing large tables into smaller ones and defining relationships between them.

47. What is Denormalization?

Denormalization is the process of intentionally introducing redundancy into a database by combining tables or adding redundant data. It is the opposite of normalization