SQL interview questions Archives

Top 20 SQL Interview Questions and Answers

SQL (Structured Query Language) remains one of the most in-demand skills for data analysts, database administrators, and backend developers. Whether you’re preparing for your first technical interview or brushing up on fundamentals, these 20 questions cover the essential concepts you’re likely to encounter. 1. What is SQL, and what are its different types? SQL is a standardized programming language used for managing and manipulating relational databases. There are several types of SQL commands: 2. What is the difference between DELETE and TRUNCATE? DELETE is a DML command that removes rows one at a time and logs each deletion, allowing you to use a WHERE clause to delete specific rows. It can be rolled back, and triggers are activated. TRUNCATE is a DDL command that removes all rows from a table at once without logging individual row deletions. It’s faster, cannot be rolled back (in most databases), doesn’t activate triggers, and resets identity columns. 3. Explain the different types of JOINs in SQL 4. What are a Primary Key and a Foreign Key? A Primary Key uniquely identifies each record in a table. It cannot contain NULL values, and each table can have only one primary key (which can consist of single or multiple columns). A Foreign Key is a column or set of columns in one table that references the primary key in another table. It establishes relationships between tables and helps maintain referential integrity. 5. What is the difference between WHERE and HAVING clauses? WHERE is used to filter rows before grouping occurs and cannot be used with aggregate functions. It works with individual rows. HAVING is used to filter groups after the GROUP BY clause has been applied and can be used with aggregate functions. For example, WHERE filters employees before calculating department averages, while HAVING filters departments after calculating those averages. 6. Explain SQL indexes and their types Indexes are database objects that improve query performance by providing faster data retrieval. Types include: 7. What is normalization, and what are its types? Normalization is the process of organizing data to reduce redundancy and improve data integrity. The normal forms are: 8. What are aggregate functions in SQL? Aggregate functions perform calculations on a set of values and return a single value. Common ones include COUNT (counts rows), SUM (adds numeric values), AVG (calculates average), MAX (finds maximum value), MIN (finds minimum value), and GROUP_CONCAT or STRING_AGG (concatenates strings from multiple rows). 9. What is a subquery, and what are its types? A subquery is a query nested inside another query. Types include: 10. Explain the difference between UNION and UNION ALL UNION combines result sets from multiple SELECT statements and removes duplicate rows, requiring additional processing. UNION ALL also combines result sets but keeps all rows, including duplicates, making it faster. Both require the same number of columns with compatible data types in the same order. 11. What are constraints in SQL? Constraints enforce rules on data in tables. Common ones include: 12. What is the difference between RANK, DENSE_RANK, and ROW_NUMBER? These are window functions used for ranking. ROW_NUMBER assigns unique sequential numbers (1, 2, 3, 4…) regardless of duplicates. RANK assigns the same rank to ties but skips subsequent ranks (1, 2, 2, 4…). DENSE_RANK assigns the same rank to ties without skipping ranks (1, 2, 2, 3…). 13. Explain transactions and ACID properties A transaction is a logical unit of work containing one or more SQL statements. ACID properties ensure reliable processing: 14. What is the difference between CHAR and VARCHAR? CHAR is a fixed-length data type that always uses the specified amount of storage, padding with spaces if necessary. It’s faster for fixed-length data. VARCHAR is a variable-length data type that uses only the space needed for the actual data plus overhead bytes. It’s more storage-efficient for varying lengths. 15. What are views in SQL? A view is a virtual table based on a SQL query. It doesn’t store data itself but displays data from one or more tables. Views simplify complex queries, provide security by restricting access to specific data, present data in different formats, and maintain logical data independence. Views can be either updatable or read-only, depending on their complexity. 16. Explain the GROUP BY clause GROUP BY groups rows with the same values in specified columns into summary rows. It’s typically used with aggregate functions to perform calculations on each group. For example, grouping sales by region to calculate total sales per region, or grouping employees by department to count employees per department. 17. What is a stored procedure, and what are its advantages? A stored procedure is a prepared SQL code that you can save and reuse. Advantages include improved performance through precompilation, reduced network traffic, enhanced security through access control, code reusability, easier maintenance, and the ability to encapsulate complex business logic. 18. What are triggers in SQL? Triggers are special stored procedures that automatically execute when specific events occur in a database. Types include BEFORE triggers (execute before an operation), AFTER triggers (execute after an operation), and INSTEAD OF triggers (replace the operation). They’re used for enforcing business rules, maintaining audit trails, validating data, and synchronizing tables. 19. Explain the difference between clustered and non-clustered indexes A clustered index determines the physical order of data storage in the table, meaning the table data is sorted according to the clustered index key. Only one clustered index can exist per table. A non-clustered index creates a separate structure that contains the indexed columns and a pointer to the actual data row. Multiple non-clustered indexes can exist on a table. 20. What is a CTE (Common Table Expression)? A CTE is a temporary named result set that exists within the scope of a single statement. Defined using the WITH clause, CTEs improve query readability, can be referenced multiple times in the same query, and support recursion. They’re useful for breaking down complex queries, performing recursive operations such as those found in organizational hierarchies, and making code more maintainable. Final

Top 20 SQL Interview Questions and Answers Read More »

Top Django, SQL, and AWS Interview Questions and Answers

Leave a Comment / Interview / Tarun

If you are preparing for Python/Django backend interviews, then you’ll not only face questions on Django itself but also on SQL, APIs, caching, and cloud (AWS). Therefore, in this blog, I’ve compiled some of the most frequently asked questions along with clear and concise answers. However, these examples will help you understand the concepts better and prepare more effectively for your next interview. My advice is to prepare more questions in SQL because, for Django developers, it is often difficult to learn SQL due to relying heavily on the ORM. Therefore, practicing SQL separately will strengthen your backend fundamentals and give you more confidence in interviews. 1. What is the Longest Substring Problem? The longest substring problem usually refers to finding the longest substring without repeating characters. def longest_substring(s): seen = set() left = 0 start = 0 max_len = 0 for right in range(len(s)): while s[right] in seen: # shrink window if duplicate found seen.remove(s[left]) left += 1 seen.add(s[right]) if (right – left + 1) > max_len: max_len = right – left + 1 start = left # track start index of longest substring return s[start:start + max_len] # Example print(longest_substring(“abcabcbb”)) # Output: “abc” 2. What are the Types of Indexing in Databases? A database technique that speeds up query performance by creating data pointers: 3. How Do I Write the Longest SQL Query? This is a trick question — instead of “long queries,” interviewers expect optimized queries. 4. How Do You Make Fast APIs in Django Rest Framework (DRF)? Ways to optimize DRF APIs: 5. Can an Anonymous User Store Cart Items? An anonymous user’s cart items can be stored using sessions or cookies. The cart data (like product IDs and quantities) is kept in the session or browser storage until the user logs in. Once they log in, the session/cart data is merged into the user’s permanent cart stored in the database. 6. How Do You Store Frequently Accessed Product Images Efficiently? 7. What is Middleware in Django? Middleware is a layer between the request and the response. These are predefined in django: Authentication, Session, CSRF, and Security headers. Custom Middleware Example: class SimpleMiddleware: def __init__(self, get_response): self.get_response = get_response def __call__(self, request): print(“Before View:”, request.path) response = self.get_response(request) print(“After View:”, response.status_code) return response register in settings like settings.py → MIDDLEWARE = [‘myapp.middleware.SimpleMiddleware’, …] 8. How Do You Optimize Django ORM Queries? 9. How Do You Write Raw SQL Queries in Django? Two main ways of user raw or user cursor connection: users = UserProfile.objects.raw(“SELECT * FROM user WHERE active = %s”, [True]) from django.db import connection with connection.cursor() as cursor: cursor.execute(“SELECT id, name FROM user WHERE active = %s”, [True]) rows = cursor.fetchall() 10. What AWS Services Have You Used? 11. What is Load Balancing in AWS? Load balancing distributes incoming traffic across multiple servers. AWS provides Elastic Load Balancer (ELB): 12. What are the Types of JOIN Queries in SQL? In SQL, JOINs combine rows from two or more tables based on related columns. They allow you to fetch meaningful data by linking multiple tables together. Different types of JOINs define how records from one table are matched with records from another. 13. How Does LEFT JOIN Work? LEFT JOIN returns all rows from the left table and matching rows from the right. Non-matching → NULL. Example: SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id = d.id; 14. How Do You Implement Push Notifications in Django? 15. What is Redis, and How Did You Use It? Redis serves as a high-performance caching and message broker.Use cases in Django: Example (Django cache): from django.core.cache import cache def get_product(pid): key = f”product:{pid}” product = cache.get(key) if not product: product = Product.objects.get(id=pid) cache.set(key, product, 3600) return product Preparing for Python/Django backend interviews requires a balance of theory and practical knowledge. While Django and its ORM simplify a lot of work, interviewers often test your understanding of SQL, system design, caching, and cloud services like AWS. Therefore, practice coding problems, revisit core SQL queries, and explore concepts like load balancing, middleware, and caching. With consistent preparation, you’ll not only perform well in interviews but also become a more confident backend developer. See my previous Python Interview question.

Top Django, SQL, and AWS Interview Questions and Answers Read More »