What are the basic SQL questions asked in an interview?

In an SQL interview, candidates are often tested on their understanding of fundamental SQL concepts and their ability to apply them in real-world scenarios. Here are some of the basic SQL questions typically asked in interviews:

1. What is SQL?

Purpose: Assess your basic understanding of SQL. Answer: SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It allows users to perform tasks like querying data, updating records, creating and modifying database structures, and managing access control.

2. What are the different types of SQL statements?

Purpose: Test your knowledge of SQL commands. Answer: SQL commands are categorized into five types:

• DDL (Data Definition Language): Commands like CREATE, ALTER, and DROP to define or modify database structures.
• DML (Data Manipulation Language): Commands like SELECT, INSERT, UPDATE, and DELETE to manipulate data.
• DCL (Data Control Language): Commands like GRANT and REVOKE to control access to data.
• TCL (Transaction Control Language): Commands like COMMIT, ROLLBACK, and SAVEPOINT to manage transactions.
• DQL (Data Query Language): The SELECT statement is part of DQL, used to query data from the database.

3. What is the difference between WHERE and HAVING clauses?

Purpose: Test your understanding of filtering data in queries. Answer:

• The WHERE clause is used to filter rows before any groupings are made, typically applied to individual rows.
• The HAVING clause is used to filter groups after aggregation has been performed, usually applied to aggregated data.

4. What are JOIN clauses in SQL, and what are the different types?

Purpose: Evaluate your understanding of how to combine data from multiple tables. Answer:

• INNER JOIN: Returns only the rows where there is a match in both tables.
• LEFT (OUTER) JOIN: Returns all rows from the left table and matched rows from the right table; returns NULL for no matches.
• RIGHT (OUTER) JOIN: Returns all rows from the right table and matched rows from the left table; returns NULL for no matches.
• FULL (OUTER) JOIN: Returns rows when there is a match in either the left or right table, and NULL for no matches.
• CROSS JOIN: Produces a Cartesian product of both tables, returning all possible combinations of rows.

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

Purpose: Test your knowledge of removing data and database objects. Answer:

• DELETE: Removes specific rows from a table based on a condition. It can be rolled back and triggers are activated.
• TRUNCATE: Removes all rows from a table without logging individual row deletions. It’s faster but cannot be rolled back easily, and triggers are not activated.
• DROP: Deletes the entire table, including its structure, making it irreversible.

6. What is a primary key and a foreign key?

Purpose: Test your understanding of database relationships. Answer:

• Primary Key: A column (or set of columns) that uniquely identifies each row in a table. It ensures that no null or duplicate values are allowed.
• Foreign Key: A column that establishes a relationship between two tables, referring to the primary key in another table, ensuring referential integrity.

7. How do you use the GROUP BY clause?

Purpose: Evaluate your understanding of aggregation in SQL. Answer: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It’s often used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to perform operations on groups of data.

Example:

SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department;

8. What is normalization? What are the normal forms?

Purpose: Assess your understanding of database design principles. Answer: Normalization is the process of organizing data in a database to minimize redundancy and improve data integrity. The most common normal forms are:

• 1NF (First Normal Form): Ensures that all data is stored in individual columns and each column contains atomic (indivisible) values.
• 2NF (Second Normal Form): Ensures 1NF and that all non-key attributes are fully functionally dependent on the primary key.
• 3NF (Third Normal Form): Ensures 2NF and that all attributes are only dependent on the primary key, eliminating transitive dependencies.

9. What is a subquery?

Purpose: Test your ability to write more complex SQL queries. Answer: A subquery is a query nested inside another query. It can be used in SELECT, INSERT, UPDATE, or DELETE statements to filter or calculate values.

Example:

SELECT employee_id, employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

In this example, the subquery calculates the average salary, and the outer query returns employees with a salary higher than that average.

10. How would you optimize an SQL query?

Purpose: Evaluate your understanding of performance tuning in SQL. Answer:

• Indexing: Use indexes on columns that are frequently used in WHERE clauses or as part of joins.
• *Avoiding SELECT : Specify only the columns you need instead of selecting all columns.
• Joins over Subqueries: Whenever possible, use joins instead of subqueries, as joins are often more efficient.
• Use EXPLAIN: Run EXPLAIN on your query to understand its execution plan and identify bottlenecks.
• Limit the Use of DISTINCT: Only use DISTINCT when absolutely necessary, as it can slow down query performance.

Conclusion

These basic SQL interview questions help assess your understanding of key concepts, query writing, and performance optimization. Mastering these topics will prepare you for most entry-level to mid-level SQL interviews.