Basic SQL Questions and Answers

1: What is SQL?

Relational databases are managed and altered using a domain-specific language called SQL (Structured Query Language).? In addition to creating and modifying database structures, it is used to query, insert, update, and delete data.

2: What are the different types of SQL commands?

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

3: What is the difference between DELETE and TRUNCATE?

• DELETE: Eliminates rows one at a time and allows for WHERE clause filtering. It reports every row deletion and is slower.
• TRUNCATE: Eliminates every row at once and is unable to employ a WHERE clause. It resets the identity of the table (auto-increment) and is faster.

4: What is a Primary Key?

A column, or group of columns, that uniquely identifies every table row is called a primary key. It must be unique and cannot contain NULL data.

5: What is a Foreign Key?

A column or group of columns in one table that point to the primary key of another table is called a foreign key. It creates a connection between two tables.

6: What is the difference between INNER JOIN and OUTER JOIN?

• INNER JOIN: Only gives back rows from both tables that match.
• OUTER JOIN: Includes rows that don't match as well. LEFT JOIN, RIGHT JOIN, and FULL JOIN are its three varieties.

7: What is a Subquery?

A query nested inside another query is called a subquery. It is employed to retrieve information that will be a condition in the main query.

8: What is an Index?

A database object that speeds up data retrieval operations on a table is called an index. It functions similarly to a pointer to information in a certain column.

9: What is the difference between WHERE and HAVING?

• WHERE: Before grouping, rows are filtered.
• HAVING: After aggregation, it filters groupings

10: What is Normalization?

The practice of arranging data in a database to minimize dependencies and redundancies is called normalization.? It entails breaking up big tables into more manageable, relevant tables.

Intermediate SQL Questions

11. What is a View?

A view is a query-generated virtual table. It dynamically pulls data from underlying tables rather than storing it itself.

12: What is the difference between UNION and UNION ALL?

• UNION: Merges and eliminates duplicate results from two queries.
• UNION ALL: Incorporates duplicates and combines the results of two queries.

13: What is a Self-Join?

When a table is joined with itself, it's called a self-join. When comparing rows inside the same database or dealing with hierarchical data, it is helpful.

14: What is the purpose of GROUP BY?

The GROUP BY clause, which is frequently used with aggregate functions like SUM, COUNT, AVG, etc., collects rows that share values in designated columns into summary rows.

15: What is a Stored Procedure?

A precompiled set of SQL statements that may be run as a single unit is called a stored procedure. It enhances reusability and performance.

16: What is a Trigger?

A trigger is a unique kind of stored procedure that starts running automatically when specific events (like INSERT, UPDATE, or DELETE) on a table occur.

17: What is the difference between CHAR and VARCHAR?

• CHAR: String with a set length. Uses blanks to fill in empty spaces.
• VARCHAR: Variable-length string. doesn’t fill in empty space.

18: What is a Cursor?

A database object called a cursor is used to access, modify, and move through the rows that a query returns.

19: What is the difference between DROP and TRUNCATE?

• DROP: Removes all of the data and the table structure.
• TRUNCATE: Eliminates every row while maintaining the table's structure.

20: What is ACID in databases?

Atomicity, Consistency, Isolation, and Durability are the acronyms for ACID. It guarantees dependable database transaction processing.

Advanced SQL Questions

21. What is a CTE (Common Table Expression)?

A CTE is a temporary result set that is defined while a SELECT, INSERT, UPDATE, or DELETE statement is being executed. It makes complicated queries easier to understand and more readable.

22: What is Window Function?

Without collapsing the result set, a window function does computations across a group of rows associated with the current row. RANK(), DENSE_RANK(), and ROW_NUMBER() are a few examples.

23: What is the difference between RANK() and DENSE_RANK()?

• RANK(): Duplicate rows are given the same rank, but further ranks are skipped.
• DENSE_RANK(): Duplicate rows are given the same rank, but succeeding ranks are not skipped.

24: What is a Recursive Query?

Organizational charts and other hierarchical data are queried using a recursive query. Until a base case is satisfied, it repeatedly runs using a Common Table Expression (CTE).

25: What is Database Sharding?

The technique of dividing a big database into smaller, easier-to-manage sections known as shards is known as sharding. A subset of the data is contained in each shard.

26: What is the difference between OLTP and OLAP?

• OLTP (Online Transaction Processing): Concentrates on quick, regular transactions (banking systems, for example).
• OLAP (Online Analytical Processing): Prioritizes intricate reporting and analysis questions.

27: What is Denormalization?

Adding duplicate data to a normalized database in order to enhance read efficiency is known as denormalization. It decreases the requirement for joins while increasing storage.

28: What is a Deadlock?

When two or more transactions lock down resources that the other transactions require, it's called a deadlock and causes a halt.

29: What is the difference between Clustered and Non-Clustered Index?

• Clustered Index: Establishes the data's physical arrangement in a table. Each table can only have one.
• Non-Clustered Index: Separates the conceptual order from the actual data. Each table has several.

30: What is a Materialized View?

A database entry that physically stores a query's result is called a materialized view. It enhances query performance and is updated on a regular basis.

Scenario-Based SQL Questions

31. How do you find duplicate rows in a table?

Using SQL, you can perform the following procedure to find the duplicate rows in a table:

• SELECT column1, column2, COUNT(*)
• FROM table_name
• GROUP BY column1, column2
• HAVING COUNT(*) > 1;

32: How do you delete duplicate rows?

Through SQL, you can execute the process of deleting duplicate rows via the following procedure:

• DELETE FROM my_table
• WHERE id NOT IN (
• SELECT MIN(id)
• FROM my_table
• GROUP BY column1, column2
• );

33: How do you calculate running totals?

Through SQL, you can implement the following process to calculate running totals:

• SELECT Date, Sales,
• ???????? SUM(Sales) OVER (ORDER BY Date) AS Running_Total
• FROM SalesTable;

34: How do you find the second highest salary?

Via SQL, you can follow the below-mentioned process to find the second-highest salary:

• SELECT DISTINCT salary
• FROM employees
• ORDER BY salary DESC
• LIMIT 1 OFFSET 1;

35. What are DDL, DML, DCL, and TCL?

• DDL (Data Definition Language)

• DML (Data Manipulation Language)

• DCL (Data Control Language)

• TCL (Transaction Control Language)

These SQL command types help in efficiently managing databases, ensuring data integrity, and maintaining security.

36: What is the difference between a Left Join and a Right Join?

• LEFT JOIN: Only returns matched records from the right table and all records from the left table. Columns from the right table return NULL values if there is no match.
• RIGHT JOIN: Only returns matched records from the left table and all records from the right table. Columns from the left table return NULL values if there is no match.

37: How do you handle NULL values in SQL?

• Use the IS NULL or IS NOT NULL condition to check for NULL values.
• Use COALESCE() to replace NULL with a default value.
• Use IFNULL() (MySQL) or NVL() (Oracle) for default replacements.

38: What is the purpose of the COALESCE function?

The COALESCE() function returns the first non-NULL value from a list of expressions. E.g.

• SELECT COALESCE(NULL, NULL, 'Default Value', 'Another Value');

39: How do you optimize SQL queries?

1. Use Indexes: Index columns that are frequently sought and primary keys.
2. Avoid SELECT *: Choose only the columns that are required.
3. Use EXPLAIN Plan: Examine the execution of the query.
4. Normalize Data: Cut down on redundancy.
5. Use Joins Efficiently: Steer clear of pointless joins.
6. Optimize WHERE Conditions: Start by putting limiting conditions in place.
7. Use LIMIT for Large Queries: Only retrieve the necessary rows.
8. Partition Large Tables: Aids in effective large data management.
9. Use Caching: Save the results of frequently asked questions.

40: What is the difference between a correlated and a non-correlated subquery?

• Correlated Subquery: Runs once for every row in the outer query; dependent on the outer query.
• Non-Correlated Subquery: Runs only once, regardless of the outer query.

41. What is a Cartesian Product?

A Cartesian Product occurs when a JOIN condition is missing, causing every row from one table to combine with every row from another.

42: How do you implement pagination in SQL?

A subset of records can be retrieved for display through pagination.

MySQL / PostgreSQL (Using LIMIT OFFSET)

• SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

43: What is the difference between a Unique Key and a Primary Key?

The prime difference between a Unique Key and a Primary Key is described in the following table:

FeaturePrimary KeyUnique KeyUniquenessEnsures uniquenessEnsures uniquenessNULL valuesNot allowedAllowed (one NULL)Number per TableOnly oneMultiple allowedIndexClustered IndexNon-clustered Index

E.g. using SQL,

• CREATE TABLE employees (
• id INT PRIMARY KEY,?? ??????? -- Only one per table, cannot be NULL
• email VARCHAR(100) UNIQUE -- Can have multiple unique constraints
• );

44. What are magic tables in SQL?

Magic Tables in SQL are virtual tables that temporarily store data during INSERT, UPDATE, and DELETE operations. They are mainly used in triggers to hold the before and after values of the affected rows.

• Types of Magic Tables:

1. INSERTED Table – Stores the newly inserted or updated data.
2. DELETED Table – Stores the old data before an update or delete operation.

45: How do you find the Nth highest salary?

Using SQL, we can find the Nth highest salary through the following procedure:

• SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

A good starting point for being ready for SQL interviews is this list. To succeed in technical rounds, practice crafting questions and comprehending the underlying ideas.

46: What is a Composite Key?

Combining two or more columns to uniquely identify each table row is known as a composite key.? It is employed when rows cannot be individually identified by a single column.

47: What is the difference between a Primary Key and a Unique Key?

• Primary Key: Enforces uniqueness and is unable to include NULL data.
• Unique Key: Maintains uniqueness even if it contains a single NULL value.

48: What is the purpose of the WITH clause in SQL?

CTEs, or common table expressions, are defined using the WITH clause. By dividing complicated searches into smaller, reusable components, it makes them simpler.

49: What is a Temporary Table?

A table that only exists momentarily on the database server is known as a temporary table. It is automatically dropped at the end of a session after being created during it.

50: How do you handle hierarchical data in SQL?

One way to manage hierarchical data is by using:

• Recursive queries with CTEs.
• Adjacency list model.
• Nested set model.

51. What is the difference between IN and EXISTS?

• IN: Finds out whether a value is present in a predefined list or subquery.
• EXISTS: Determines whether any rows are returned by a subquery. For large datasets, it is typically faster.

52: What is the purpose of the CASE statement?

Conditional logic in SQL queries is implemented using the CASE statement.? It assesses conditions and, depending on the outcome, returns various values.

53: How do you calculate the median in SQL?

By using the following SQL formula, we can sincerely calculate the median in SQ:

• SELECT PERCENTILE_CONT(0:5) WITHIN GROUP (ORDER BY column_name) AS median
• FROM table_name;

54: What is the difference between COUNT(*) and COUNT(column_name)?

• COUNT(*): Counts every row, even those that contain NULL values.
• COUNT(column_name): Only counts values in the designated column that are not NULL.

55: What is a Self-Referencing Table?

When a foreign key in the same table references the primary key, the table is said to be self-referencing.? Organizational charts and other hierarchical data frequently use it.

Performance Optimization Questions

56: How do you optimize SQL queries?

By using the following methodology, I can sincerely optimize the SQL queries:

• Make proper use of indexes.
• In SELECT, stay away from superfluous columns.
• WHERE clauses can be used to filter data in advance.
• Steer clear of linked subqueries.
• To examine query execution plans, use EXPLAIN or EXPLAIN ANALYZE.

57: What is Query Execution Plan?

A detailed description of how a database engine runs a query is called a query execution plan. It displays actions such as index utilization, joins, and scans.

58: What is Index Fragmentation?

When an index's logical and physical page orders diverge, it is said to be fragmented. The performance of queries is deteriorated.

59: How do you reduce index fragmentation?

• Rebuild the index (ALTER INDEX ... REBUILD).
• Reorganize the index (ALTER INDEX ... REORGANIZE).

60: What is the difference between a Clustered and a Non-Clustered Index?

• Clustered Index: Establishes a table's row physical order. Each table can only have one.
• Non-Clustered Index: Makes a distinct structure that points to the data itself. Each table has several.

61. What is a Covering Index?

A covering index removes the need to contact the underlying table by including all the columns required by a query.

62: What is the difference between UNION and JOIN?

• UNION: Vertically combines the results of several inquiries.
• JOIN: Combines rows according to relevant columns in a horizontal fashion.

63: How do you handle large datasets in SQL?

• Divide the table.
• Make good use of indexing.
• Use aggregations and filters to optimize queries.
• If parallel processing is available, use it.

64: What is Database Partitioning?

A huge table can be partitioned into smaller, easier-to-manage sections known as partitions. It is possible to store and query each partition separately.

65: What is the difference between INNER JOIN and OUTER JOIN?

• INNER JOIN: Only gives back rows that match.
• OUTER JOIN: Includes the LEFT, RIGHT, and FULL non-matching rows as well.

Scenario-Based SQL Questions (Continued)

66: How do you find employees who earn more than their managers?

By using the following procedure, we can nicely find the desired employees who earn more than their managers in SQL:

• SELECT e.name AS employee, e.salary AS employee_salary, m.name AS manager, m.salary AS manager_salary
• FROM employees e
• JOIN employees m ON e.manager_id = m.id
• WHERE e.salary > m.salary;

67: How do you find customers who have not placed any orders?

• SELECT c.customer_id, c.name
• FROM customers c
• LEFT JOIN orders o ON c.customer_id = o.customer_id
• WHERE o.order_id IS NULL;

68: How do you calculate year-over-year growth?

• SELECT year, revenue,
• LAG(revenue) OVER (ORDER BY year) AS previous_year_revenue,
• (revenue - LAG(revenue) OVER (ORDER BY year)) / LAG(revenue) OVER (ORDER BY year) * 100 AS growth_percentage
• FROM sales;

69: How do you pivot data without using the PIVOT keyword?

• SELECT category,
• SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan_Sales,
• SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb_Sales
• FROM sales_data
• GROUP BY category;

70: How do you find the top N records in each group?

• SELECT id, category, value
• FROM (
• SELECT id, category, value,
• ROW_NUMBER() OVER (PARTITION BY category ORDER BY value DESC) AS rn
• FROM table_name
• ) AS ranked
• WHERE rn <= N;

Database Design and Theory Questions

71. What are the different normal forms?

• 1NF: Remove any duplicate columns.
• 2NF: Eliminate partial dependencies.
• 3NF: Get rid of transitive dependencies.
• BCNF: A more robust form of 3NF is the Boyce-Codd Normal Form.

72: What is a Surrogate Key?

An artificial key (such as an auto-incremented ID) used as a primary key in place of a natural key is called a surrogate key.

73: What is Referential Integrity?

Consistent relationships between tables are guaranteed via referential integrity. A legitimate primary key must be referenced by a foreign key, for instance.

74: What is a Star Schema?

One database design used in data warehousing is called a star schema. It is composed of dimension tables linked to a core fact table.

75: What is a Snowflake Schema?

A normalized star schema with dimension tables further subdivided into sub-dimensions is called a snowflake schema.

76: What is Data Warehousing?

The process of gathering, keeping, and organizing vast volumes of both structured and unstructured data for reporting and analysis is known as data warehousing.

77: What is ETL?

The process of taking data from source systems, converting it into a format that can be used, and then feeding it into a data warehouse is known as ETL (Extract, Transform, Load).

78: What is the difference between a Fact Table and a Dimension Table?

• Fact Table: includes numerical data, such as revenue and sales.
• Dimension Table: includes qualities that are descriptive (e.g., product, time, location).

79: What is a Slowly Changing Dimension (SCD)?

SCD describes how dimension data in a data warehouse evolves over time. Type 1 (overwrite), Type 2 (add new row), and Type 3 (add new column) are examples of types.

80: What is a Materialized View?

A precomputed view that physically stores the query result is called a materialized view. At the expense of storage, it enhances query performance.

Miscellaneous SQL Questions

81. What is the difference between DROP, DELETE, and TRUNCATE?

The prime difference between DROP, DELETE, and TRUNCATE are as follows:

• DROP: Eliminates the table's whole structure.
• DELETE: Eliminates particular rows (WHERE may be used).
• TRUNCATE: Retains the framework but eliminates all rows.

82: What is the difference between ROW_NUMBER() and RANK()?

• ROW_NUMBER(): Gives each row a unique number.
• RANK(): Duplicates are given the same rank, but later ranks are skipped.

83: What is the difference between COALESCE and ISNULL?

• COALESCE: Returns the first non-NULL value after accepting a number of arguments.
• ISNULL: Accepts two arguments and substitutes a given value for NULL.

84: What is the purpose of the OVER clause?

A window for window functions such as ROW_NUMBER(), RANK(), and aggregate functions is defined by the OVER clause.

85: What is the difference between UNION and UNION ALL?

• UNION: Eliminates duplicates.
• UNION ALL: Accepts duplicates.

86: What is the difference between DISTINCT and GROUP BY?

• DISTINCT: Eliminates redundant rows.
• GROUP BY: Groups rows for aggregation according to column values.

87: What is the difference between CHARINDEX and PATINDEX?

• CHARINDEX: Determines a substring's location.
• PATINDEX: Uses wildcards to determine a pattern's location.

88: What is the difference between CAST and CONVERT?

• CAST: Uses a common method to convert data kinds.
• CONVERT: Provides more formatting options while converting data types.

89: What is the difference between LIKE and =?

• LIKE: Used with wildcards to match patterns.
• =: For precise matching, use this.

90: What is the difference between INNER JOIN and CROSS JOIN?

• INNER JOIN: Gives back rows that match.
• CROSS JOIN: Delivers back the two tables' Cartesian product.

91. What is a Deadlock?

When two transactions lock down resources that the other requires, it's called a deadlock, and both transactions are forced to wait indefinitely.

92: What is a Transaction?

A series of actions carried out as a single logical unit of work is called a transaction. It has ACID characteristics.

93: What is the difference between COMMIT and ROLLBACK?

• COMMIT: Saves modifications for all time.
• ROLLBACK: Reverses any modifications made during the transaction.

94: What is a Savepoint?

You can roll back a transaction to a savepoint without affecting the transaction as a whole.

95: What is a Stored Procedure?

A precompiled set of SQL statements that may be run as a single unit is called a stored procedure.

96: What is a Trigger?

A trigger is a unique kind of stored procedure that starts up automatically when specific events (like INSERT, UPDATE, or DELETE) are triggered.

97: What is a Cursor?

A database object called a cursor is used to access, modify, and move through the rows that a query returns.

98: What is the difference between HAVING and WHERE?

• WHERE: Before grouping, rows are filtered.
• HAVING: After aggregation, it filters groupings.

99: What is a Subquery?

A query nested inside another query is called a subquery. Data utilized in the primary query is retrieved.

100: What is a View?

A view is a query-generated virtual table. It dynamically pulls data from underlying tables rather than storing it itself.

?