15 SQL Interview Questions TO GET YOU HIRED in 2025 | SQL Interview Questions & Answers |Intellipaat

Summary

This video explains key SQL concepts such as retrieving the second-highest salary from an employee table, using views to create virtual tables, converting text into date format, and using triggers for automating actions like updating stock. The speaker demonstrates how to perform these tasks with practical examples, highlighting the importance of efficient database management and automation. The tutorial covers topics such as subqueries, SQL functions, and the use of triggers to improve data integrity and reduce manual errors.

Takeaways

• 😀 The second highest salary can be found using a subquery that first identifies the highest salary and then uses that result to filter for the second highest salary.
• 😀 SQL views allow you to create virtual tables that combine data from one or more existing tables without storing any data themselves.
• 😀 Views can be customized to display only specific columns or rows, making them useful for limiting access to sensitive data.
• 😀 To convert a string into a date format, SQL provides the `STR_TO_DATE()` function, where you specify the string format and the corresponding date format.
• 😀 Triggers in SQL automate actions based on specific events such as insert, update, or delete, helping ensure database integrity and reducing manual work.
• 😀 A trigger can automatically update related tables, such as adjusting stock counts in a library system after each book is borrowed.
• 😀 Triggers are executed in response to specific changes to the database, and can perform a variety of actions like updating data or logging events.
• 😀 The `CREATE VIEW` SQL command is used to create a view, allowing users to define which data they wish to display or restrict in the view.
• 😀 Using subqueries in SQL, you can perform operations like determining the second highest salary by referencing a previous subquery result.
• 😀 With triggers, actions like updating inventory counts or tracking transactions can be handled automatically without requiring manual intervention.
• 😀 The `AFTER INSERT` trigger event allows actions to be taken after new data is added to a table, making it ideal for automating workflows like updating inventory.

Q & A

What is the purpose of using a subquery to find the second highest salary?

A subquery is used to first identify the maximum salary, and then another subquery is used to find the maximum salary that is less than the first maximum. This effectively identifies the second highest salary in the database.

How does the query handle cases where multiple employees earn the second highest salary?

The outer query ensures that if multiple employees have the same second highest salary, each of them will be included in the result. It retrieves all employees with a salary equal to the second highest salary.

What is a view in SQL and why is it useful?

A view in SQL is a virtual table that provides a specific view of data from one or more tables. It does not store data but presents it in a particular format. It is useful for restricting user access to sensitive data and for simplifying complex queries.

Can views store data like regular tables?

No, views do not store data. They simply display data in a specific way based on the underlying tables.

How do you create a view in SQL?

To create a view, use the 'CREATE VIEW' statement followed by the name of the view, then write a SELECT query to define what data the view should display. For example, 'CREATE VIEW detail_view AS SELECT name, address FROM student WHERE ID < 5;'

What is the significance of the `STR_TO_DATE` function in SQL?

The `STR_TO_DATE` function is used to convert a string into a date format. It allows SQL to interpret a string like '201122' as a valid date, based on the format you specify (e.g., day, month, year).

How do triggers work in SQL?

Triggers in SQL are automatic actions that are executed in response to certain events, like inserting, updating, or deleting data. They can help automate tasks such as updating related data in other tables.

What is an example of a situation where a trigger could be used?

An example is a library system where a trigger could automatically update the available stock of a book every time someone borrows a book. The trigger would decrease the available count by one in the books table.

What are the three types of events that can trigger a SQL trigger?

The three types of events are 'INSERT' (when new data is added), 'UPDATE' (when existing data is modified), and 'DELETE' (when data is removed).

How do you write a trigger to update the stock of books in a library database?

To write a trigger that updates the stock, you would create a trigger that activates after an 'INSERT' event on the borrowed books table. The trigger would decrease the available count in the books table by 1 for the borrowed book using the book's ID.