SQL Interview Questions

Preparing for an SQL interview requires a deep understanding of database management and SQL queries. It involves familiarizing oneself with a range of topics from basic commands to complex query optimization. These questions and answers are designed to cover fundamental concepts, practical applications, and advanced features of SQL.

What is SQL?

SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases. It allows for creating, reading, updating, and deleting (CRUD) database records.

Explain the difference between DELETE and TRUNCATE commands.

• The DELETE command is used to remove rows from a table based on a WHERE condition and it can be rolled back. TRUNCATE, on the other hand, removes all rows from a table, resetting the table to empty, and cannot be rolled back.
• know more: Interview Questions and Answers for MYSQL

What are JOINs in SQL and explain different types of JOINs?

• JOINs are used to combine rows from two or more tables based on a related column between them. The main types are INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN.

What is a Primary Key?

• A Primary Key is a field in a table that uniquely identifies each row/record in that table. It cannot accept null values and there must be a unique value for each row.

What is a Foreign Key?

• A Foreign Key is a field in a table that is a primary key in another table. It is used to establish and enforce a link between the data in two tables.

Explain Normalization and its types.

• Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The normal forms, primarily first (1NF), second (2NF), and third (3NF) normal forms, each resolve specific types of redundancy and dependency.

What is an Index?

• An index is a performance-tuning method of allowing faster retrieval of records from the table. It creates an entry for each value and it will be faster to retrieve data.

What are the different types of SQL commands?

• The types of SQL commands are DDL (Data Definition Language) like CREATE, ALTER, and DROP; DML (Data Manipulation Language) like INSERT, UPDATE, and DELETE; DQL (Data Query Language) like SELECT; DCL (Data Control Language) like GRANT, REVOKE; and TCL (Transaction Control Language) like COMMIT, ROLLBACK.

What is a Stored Procedure?

• A Stored Procedure is a set of SQL queries that can take input and send back output. It is used to encapsulate a set of operations or queries to execute on a database server.

What is a View in SQL?

• A view is a virtual table based on the result set of an SQL statement. It contains rows and columns, just like a real table, but the fields in a view are fields from one or more real tables in the database.

Explain the difference between a clustered and a non-clustered index.

• A clustered index alters the way records are physically stored in the database, sorting and storing the data rows in the table based on the index key. There can be only one clustered index per table. A non-clustered index, on the other hand, creates a separate structure to hold the index values and a pointer to the location of the data in the heap or clustered index. A table can have multiple non-clustered indexes.

What is a subquery and explain its types.

• A subquery is a query nested within another query. It is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be classified as single-row, multiple-row, or correlated subqueries.

What is a self-join and when would you use it?

• A self-join is a regular join, but the table is joined with itself. This is useful for comparing rows within the same table.

Explain ACID properties in databases.

• ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the properties that guarantee database transactions are processed reliably. Atomicity ensures the complete success or failure of a transaction. Consistency ensures the database remains in a consistent state before and after a transaction. Isolation ensures transactions are securely and independently processed at the same time without interference. Durability ensures completed transactions are saved to the database even in the event of a system failure.

What is SQL injection and how can it be prevented?

• SQL injection is a security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. It can be prevented by using prepared statements with parameterized queries, employing stored procedures, and validating user input.

What is a cursor and what are its types?

• A cursor in SQL is a control structure that allows for the traversal of records in a database. Cursors are used to process each row returned by a query individually. Cursors can be classified as static, dynamic, forward-only, and keyset-driven.

What is a transaction in SQL?

• A transaction in SQL is a sequence of operations performed as a single logical unit of work. A transaction must be completed in its entirety or not executed at all.

What are aggregate functions in SQL?

• Aggregate functions perform a calculation on a set of values and return a single value. Examples include SUM(), COUNT(), AVG(), MIN(), and MAX().

Explain the GROUP BY and HAVING clauses.

• The GROUP BY clause groups rows that have the same values in specified columns into summary rows. The HAVING clause is used to filter records that work on summarized GROUP BY results.

What are the limitations of a view in SQL?

• Views in SQL have several limitations such as not being able to create an index on a view, not being able to use DDL statements on a view, and limitations on the use of certain clauses like ORDER BY in a view definition.

What is a “WITH” clause in SQL and how is it used?

• The WITH clause, also known as Common Table Expressions (CTE), is used to create a temporary named result set that can be used within a SELECT, INSERT, UPDATE, or DELETE statement. It simplifies complex queries by breaking them down into simpler parts.

How do you handle NULL values in SQL?

• NULL values can be handled using functions like ISNULL(), COALESCE(), NULLIF(), and using conditions like IS NULL or IS NOT NULL in WHERE clauses.

What is an SQL constraint and what are the different types of constraints?

• An SQL constraint is a rule applied to the data in a table. It is used to limit the type of data that can go into a table. Common types include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT.

What is the difference between UNION and UNION ALL?

• UNION combines the result set of two or more SELECT statements (eliminating duplicate rows), while UNION ALL also combines the results, but includes duplicates.

How do you optimize SQL queries?

• SQL query optimization can involve indexing, avoiding unnecessary columns in SELECT statements, using proper join and where clause order, avoiding complex subqueries, and using query execution plans for analysis.

Explain the concept of a database schema.

• A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated.

What is the difference between HAVING and WHERE clauses?

• The WHERE clause is used to filter rows before any groupings are made, while the HAVING clause is used to filter values after they have been grouped.

Explain what SQL Injection is and how it can be prevented.

• SQL Injection is a code injection technique used to attack data-driven applications, where malicious SQL statements are inserted into an entry field for execution. It can be prevented by using parameterized queries, stored procedures, and input validation.

What are SQL triggers and how are they used?

• SQL triggers are a set of statements that automatically execute or fire when a specified database event occurs. They are used for maintaining the integrity of the information on the database.

What is Database Normalization and what are its benefits?

• Database Normalization is a process used to organize a database into tables and columns. The main idea with this is that a table should be about a specific topic and only supporting topics included. Benefits include reduced data redundancy, improved data integrity, and optimization of query performance.

Describe the difference between a full join and an inner join.

• A full join returns all records when there is a match in either left or right table records. An inner join, however, returns records that have matching values in both tables.

How can you prevent SQL injections in stored procedures?

• SQL injections in stored procedures can be prevented by avoiding dynamic SQL when possible, using parameterized queries, and validating all input data.

What is database denormalization and when would you use it?

• Database denormalization is the process of adding redundant data to a database to improve read performance at the expense of write performance and data integrity. It’s used in scenarios where read operations are significantly more frequent than write operations.

Explain the difference between a correlated and a non-correlated subquery.

• A correlated subquery depends on data from the outer query and is executed repeatedly, once for each row that is evaluated by the outer query. A non-correlated subquery is independent of the outer query and can be executed on its own.

What is a covering index and how does it improve query performance?

• A covering index is an index that contains all, and possibly more, the columns you need for your query. It improves performance by allowing the database engine to fetch all necessary data from the index without having to access the main table data.

How would you explain the concept of a transaction log?

• A transaction log is a history of actions executed by a database management system to guarantee ACID properties over crashes or hardware failures. It records all transactions and the database modifications made by each transaction.

What is the difference between CHAR and VARCHAR data types?

• CHAR is a fixed-length character type while VARCHAR is a variable-length character type. CHAR uses static memory allocation, and VARCHAR uses dynamic memory allocation.

How do you implement one-to-one, one-to-many, and many-to-many relationships while designing tables?

• One-to-one relationship: Use a primary key-foreign key relationship with a unique constraint on the foreign key.
• One-to-many relationship: Implement a primary key in one table and a foreign key in the other table.
• Many-to-many relationship: Create a third table, also known as a junction or join table, where each record is a combination of the primary keys of the two tables it joins.

What are indexed views and how are they different from regular views?

• Indexed views are views with a unique clustered index. They physically store the result set of the view, which can significantly improve performance for complex queries. Regular views do not store the result set physically.

How can you handle errors in SQL stored procedures?

Errors in SQL stored procedures can be handled using TRY…CATCH blocks. These allow the capturing and handling of errors by executing a set of statements in the TRY block and defining error handling in the CATCH block.

Conclusion:

Mastering SQL for an interview demands a thorough grasp of both theory and practice. The discussed questions encompass various aspects of SQL, from data manipulation and querying to optimization and security. Being well-prepared in these areas can significantly enhance one’s readiness for a challenging and successful SQL interview.