15 Popular SQL Interview Questions for Freshers

Nail your SQL interview with these 30 handpicked questions for freshers. Boost your confidence and stand out in the crowd.

Here are some answers to the SQL interview questions

What is SQL and why is it important?

SQL (Structured Query Language) is a standard programming language designed for managing and manipulating relational databases. It is important because it provides a standardized way for applications to communicate with and manage databases. SQL is used to perform tasks such as querying data, updating data, inserting data, and creating and modifying database structures.

Differentiate between SQL and MySQL

SQL is a language used for managing and manipulating relational databases, while MySQL is a relational database management system (RDBMS) that uses SQL as its query language. MySQL is just one of many database systems that use SQL.

Explain the key differences between INNER JOIN and LEFT JOIN.

• INNER JOIN returns only the matching rows from both tables.
• LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

What is the purpose of the SELECT statement in SQL?

The SELECT statement is used to query the database and retrieve data from one or more tables. It allows you to specify the columns you want to retrieve, apply conditions to filter the rows, and can include various clauses such as JOIN, WHERE, GROUP BY, HAVING, ORDER BY, etc.

Explain the concept of normalization in databases.

Normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves breaking down large tables into smaller, related tables and defining relationships between them to eliminate data duplication and improve data integrity.

What is a primary key? How is it different from a unique key?

A primary key is a column or a set of columns that uniquely identifies each record in a table. It must contain unique values and cannot have NULL values. A unique key, on the other hand, is a constraint that ensures that all values in a column (or a set of columns) are unique, but it allows NULL values.

Explain the difference between DELETE and TRUNCATE commands.

• DELETE is used to remove rows from a table based on a condition. It can be rolled back and has WHERE clause for conditional deletion.
• TRUNCATE is used to remove all the rows from a table, and it cannot be rolled back. It is faster than DELETE, but it does not support a WHERE clause.

What is an Index in SQL? Why is it used?

An index in SQL is a data structure that improves the speed of data retrieval operations on a database table. It works like an index in a book, allowing the database engine to quickly locate the rows matching a column or set of columns specified in the query's WHERE clause. Indexes are used to optimize query performance.

Describe the ACID properties in the context of database transactions.

ACID stands for Atomicity, Consistency, Isolation, and Durability, which are properties that guarantee the reliability of database transactions:

• Atomicity: Ensures that a transaction is treated as a single, indivisible unit.
• Consistency: Ensures that a transaction brings the database from one valid state to another.
• Isolation: Ensures that the execution of transactions is isolated from each other.
• Durability: Ensures that once a transaction is committed, its changes are permanent.

What is a foreign key, and how is it used in SQL?

A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a link between the two tables, enforcing referential integrity. The foreign key constraint ensures that values in the foreign key column(s) match the values in the primary key column(s) of the referenced table.

Explain the difference between CHAR and VARCHAR data types.

• CHAR is a fixed-length character data type that stores a specified number of characters. It pads shorter strings with spaces.
• VARCHAR is a variable-length character data type that stores a varying number of characters. It does not pad shorter strings.

What is the purpose of the GROUP BY clause in SQL?

The GROUP BY clause is used in conjunction with aggregate functions (like SUM, COUNT, AVG) to group rows based on the values in one or more columns. It is often used to perform operations on each group of rows, rather than on the entire result set.

What is the difference between UNION and UNION ALL in SQL?

• UNION combines the results of two or more SELECT statements and removes duplicate rows.
• UNION ALL also combines the results of two or more SELECT statements but includes all rows, including duplicates.

Write a SQL query to retrieve the second highest salary from an Employee table

Explain the concept of stored procedures and functions in SQL

Stored Procedure: A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. It is stored in the database and can be called by name, with or without parameters.

Function: A function is similar to a stored procedure but typically returns a value. It can be used in SQL queries and is invoked as part of an expression.

Conclusion

In summary, MySQL is important for the discipline of data management. It has become an essential tool for everyone who works with relational databases due to its consistency and flexibility. Acquiring an excellent understanding of SQL helps you to interact with databases and benefit from their potential regardless of your work in database administration, software development, data analysis, or any other related industry.