Here are 50 commonly asked SQL interview questions for both interviews and written exams:
Basic SQL Questions:
- What is SQL, and what does it stand for?
- Explain the differences between SQL and NoSQL databases.
- What are the key components of an SQL statement?
- What is a database, and what is the role of a database management system (DBMS)?
- How do you retrieve all records from a table in SQL?
- What is the SQL statement to select specific columns from a table?
- How can you filter records using the WHERE clause in SQL?
- Explain the difference between the DISTINCT and GROUP BY clauses.
- How do you limit the number of records returned by a query?
- How do you create a new table in SQL?
- What is the purpose of the PRIMARY KEY constraint?
- Explain the differences between the INSERT, UPDATE, and DELETE statements.
- How can you add a new column to an existing table?
- What is the purpose of the FOREIGN KEY constraint, and how is it used?
Joins and Relationships:
- What is an SQL JOIN, and what are the different types of JOINs?
- How do you retrieve data from multiple tables using a JOIN?
- What is a self-join, and when would you use it?
- Explain the concept of a one-to-many relationship in database design.
- How do you find records with no matching values in another table?
Aggregation and Grouping:
- What are aggregate functions in SQL, and provide examples of a few.
- How do you calculate the total number of records in a table?
- Explain the HAVING clause and its purpose.
- How do you find the average, sum, and maximum values of a column?
- What is the difference between the COUNT(*) and COUNT(column_name) functions?
Subqueries and Nesting:
- What is a subquery, and how is it different from a JOIN?
- How do you use a subquery to filter results in the WHERE clause?
- Provide an example of a correlated subquery.
- Explain the EXISTS and NOT EXISTS operators in subqueries.
- How do you use the IN and NOT IN operators with subqueries?
Indexes and Optimization:
- What is an index in a database, and why is it important?
- How can you improve query performance using indexes?
- Explain the concept of query optimization in SQL.
- What is the purpose of the EXPLAIN statement in SQL?
Transactions and ACID:
- What is a database transaction, and what does ACID stand for?
- How do you start and commit a transaction in SQL?
- Explain the concept of isolation levels in database transactions.
- What is a deadlock, and how can it be resolved in SQL?
Views and Stored Procedures:
- What is an SQL view, and how is it different from a table?
- How do you create a stored procedure in SQL?
- What are the advantages of using views and stored procedures?
Security and Permissions:
- How do you grant and revoke permissions on database objects?
- What is SQL injection, and how can it be prevented?
- Explain the concept of role-based access control in SQL.
Normalization and Database Design:
- What is database normalization, and why is it important?
- Describe the different normal forms in database design.
- How do you denormalize a database, and when is it appropriate to do so?
Backup and Recovery:
- How do you create a backup of a database in SQL?
- Explain the process of restoring a database from a backup.
- What is the purpose of the TRY…CATCH block in SQL for error handling?
- How do you raise custom errors in SQL?
These SQL interview questions cover a wide range of topics and are often used to assess a candidate’s knowledge and skills in working with relational databases