Basic SQL Interview Questions & Answers

The Database Gatekeeper: Mastering the SQL Interview

Imagine you’re in the middle of a high-stakes interview for a Data Analyst role. The hiring manager shares their screen, shows you two messy tables of customer orders and shipping details, and says, “Write me a query to find the total revenue from users in Ahmedabad who haven’t returned an item in thirty days.” If your palms start to sweat, you aren’t alone. SQL is the universal language of data, yet even experienced devs occasionally trip over a simple JOIN or a GROUP BY clause under pressure.

Whether you’re a fresher looking for your first break or an experienced professional brushing up on the fundamentals, this guide is for you. We’ll move past the robotic definitions and dive into how SQL actually works in production environments. By the end of this post, you’ll be able to articulate your logic clearly and write clean, efficient queries that prove you know your way around a database.

Quick Answer

To ace basic SQL interview questions, you must demonstrate a rock-solid understanding of the SELECT statement, filtering with WHERE, joining tables, and aggregating data with GROUP BY. Interviewers look for your ability to translate a business problem into a logical sequence of data retrieval steps.

Top 5 Basic SQL Interview Questions

1. What is the difference between DELETE, TRUNCATE, and DROP?
2. How do you explain the difference between an INNER JOIN and a LEFT JOIN?
3. What is the purpose of the GROUP BY clause?
4. Can you explain what a Primary Key and a Foreign Key are?
5. How do you filter results using the HAVING clause versus the WHERE clause?

---

QUICK OVERVIEW TABLE

Topic	No. of Questions	Difficulty Level	Best For
Basic Querying	5	🟢 Beginner	Freshers
Joins & Relationships	5	🟡 Intermediate	All Levels
Aggregations	5	🟡 Intermediate	Data Analysts
DDL/DML Operations	5	🟢 Beginner	Database Admins

---

MAIN Q&A SECTION

1. What is SQL and why is it called a “Declarative” language?

🟢 Beginner

Here’s the thing: in languages like Python or Java, you tell the computer how to do something step-by-step. In SQL (Structured Query Language), you simply tell the database what you want, and the database’s query optimizer figures out the most efficient way to get it. In my experience, this is why SQL is so powerful; you don’t need to worry about memory management or loop logic. You just focus on the data relationships. It’s the standard language for interacting with Relational Database Management Systems (RDBMS) like MySQL, PostgreSQL, and SQL Server.

2. What is the difference between CHAR and VARCHAR?

🟢 Beginner

Honestly, this one trips people up during technical screenings. CHAR is a fixed-length data type. If you define CHAR(10) and store the word “SQL,” it still takes up 10 bytes of space by padding it with blanks. VARCHAR is variable-length; it only uses the space needed for the actual characters plus a little overhead. In my experience, you should use CHAR for data with a consistent length, like a state code (e.g., ‘GJ’ or ‘MH’), and VARCHAR for everything else, like names or addresses, to save storage space.

3. How do you explain the difference between INNER JOIN and LEFT JOIN?

🟡 Intermediate

In my experience, visualizing a Venn diagram is the easiest way to keep this straight. An INNER JOIN only returns rows where there’s a match in both tables. If you join “Customers” and “Orders,” you only get customers who have actually placed an order. A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there’s no match, the result is NULL on the right side. This is actually really important when you want to find “orphan” data—like customers who haven’t placed an order yet.

4. What is the difference between WHERE and HAVING?

🟡 Intermediate

A lot of candidates miss this distinction. The WHERE clause is used to filter individual rows before any groupings are made. The HAVING clause is used to filter groups after the GROUP BY clause has been applied. For example, if you want to find all sales in 2026, use WHERE. If you want to find regions where the total sales exceeded 10 Lakhs, you must use HAVING. Honestly, if you try to use an aggregate function like SUM() in a WHERE clause, the database will throw an error immediately.

5. What is a Primary Key and a Foreign Key?

🟢 Beginner

A Primary Key is a unique identifier for a record in a table—think of it like your Aadhar card or Social Security number. It cannot be NULL and must be unique. A Foreign Key is a column in one table that points to the Primary Key in another table. In my experience, this is the “glue” that creates relationships between tables. For instance, in an “Orders” table, the customer_id is a Foreign Key that links back to the id in the “Customers” table. It ensures “Referential Integrity,” so you don’t have orders for customers who don’t exist.

6. What is the difference between DELETE and TRUNCATE?

🟡 Intermediate

DELETE is a Data Manipulation Language (DML) command. It removes rows one by one and logs each deletion, which makes it slower but allows you to use a WHERE clause to target specific rows. TRUNCATE is a Data Definition Language (DDL) command. It deallocates the data pages and is much faster, but it’s an “all or nothing” deal—you can’t filter which rows to remove. Honestly, this is actually really important: DELETE can be rolled back if you’re in a transaction, but in many databases, TRUNCATE is harder to undo.

7. What are the different types of Constraints in SQL?

🟢 Beginner

Constraints are the rules we apply to columns to maintain data quality. The most common ones you’ll see are NOT NULL (prevents empty values), UNIQUE (ensures all values in a column are different), PRIMARY KEY, FOREIGN KEY, and CHECK (ensures values meet a specific condition, like age >= 18). There’s also DEFAULT, which provides a value if none is specified. In my experience, a database without constraints quickly turns into a mess of “garbage data” that is impossible to analyze.

8. What is the UNION operator and how does it differ from UNION ALL?

🟡 Intermediate

Both are used to combine the result sets of two or more SELECT statements. The catch is that UNION removes duplicate rows from the final result, while UNION ALL keeps them. Because UNION has to perform a distinct check, it is slower than UNION ALL. In my experience, you should use UNION ALL by default unless you specifically need to hide duplicates. A lot of candidates miss this performance tip during coding rounds. Just remember: the columns must have the same data types in the same order.

9. How do you find the second-highest salary in an Employee table?

🟡 Intermediate

This is a classic interview question that tests your knowledge of subqueries or specific clauses. One common way is to use a subquery: SELECT MAX(salary) FROM Employees WHERE salary < (SELECT MAX(salary) FROM Employees). Another way is using the OFFSET clause in some databases: SELECT salary FROM Employees ORDER BY salary DESC LIMIT 1 OFFSET 1. In my experience, interviewers love to see if you can handle the case where there are duplicate salaries by using the DISTINCT keyword.

10. What is an Aggregate Function?

🟢 Beginner

Aggregate functions perform a calculation on a set of values and return a single value. The “Big Five” you absolutely must know are COUNT(), SUM(), AVG(), MIN(), and MAX(). You almost always use these in conjunction with the GROUP BY clause. For example, if you want to know the average order value per city, you’d use AVG(order_amount) and GROUP BY city. Honestly, this is the bread and butter of any data reporting task you’ll face on the job.

---

COMPARISON TABLE: SQL Commands

Choosing the right command is about understanding the scope of your action on the database.

Feature	DELETE	TRUNCATE	DROP
Command Category	DML (Data Manipulation)	DDL (Data Definition)	DDL (Data Definition)
Scope	Specific rows (via WHERE)	All rows in the table	Entire table structure
Speed	Slower (logs per row)	Faster (deallocates pages)	Very Fast
Rollback	Possible	Depends on RDBMS	Impossible (usually)
Resets Identity?	No	Yes	N/A

---

INTERVIEW TIPS SECTION

• Clarify the Schema: Before writing a single line of SQL, ask the interviewer about the table structure. Are there NULL values allowed? What are the Primary Keys? This shows you think like a developer.
• Talk Through Your Logic: Don’t just sit in silence. Explain why you are choosing a LEFT JOIN over an INNER JOIN. Interviewers care more about your thought process than a perfect syntax.
• Formatting Matters: Use uppercase for keywords (SELECT, FROM, WHERE) and lowercase for table names. It makes your code much easier to read on a whiteboard or a shared screen.
• Think About Edge Cases: What happens if a table is empty? What if there are duplicate values? Mentioning these possibilities shows that you have “Experienced” level foresight.
• Master the “Null” Trap: Remember that NULL is not the same as zero or an empty string. You can’t use = to find it; you must use IS NULL. This is the most common mistake freshers make.

---

WHAT INTERVIEWERS REALLY LOOK FOR

When we interview for SQL roles, we’re looking for Logical Clarity. We want to see if you can take a complex business request—like “Find me users who haven’t logged in for a month but have an active subscription”—and break it down into a series of filters and joins. We also look for Performance Awareness. A query that works on 10 rows might crash a database with 10 million rows. If you mention “Indexing” or “Query Cost,” you’re instantly ahead of the pack.

Another big factor is Attention to Detail. Did you remember to use DISTINCT? Did you alias your tables properly to avoid ambiguity? Sounding like an “Insider” means acknowledging that SQL is a tool for communication between you and the data. We want someone who writes code that is maintainable and easy for the next person to read.

---

FAQ : Basic SQL Interview Questions

CONCLUSION

SQL is more than just a technical skill; it’s the primary way businesses turn raw data into decisions. Preparing for basic SQL interview questions is about proving you have the logical foundation to handle data responsibly. Don’t just memorize the commands—understand the “Why” behind the joins and the filters. If you can explain the flow of data through a query, you’ll stand out as a candidate who is ready to add value from Day 1.

Ready to level up your data journey? Check out our other expert guides:

• [Advanced SQL Joins: A Deep Dive]
• [Top 30 Database Design Interview Questions]
• [How to Optimize Your SQL Queries for Performance]

Stay curious, keep querying, and good luck with your interview!