Top 25 SQL Interview Questions and Answers(The BEST SQL Interview Questions)

Summary

In this video, Tawfiq shares his top 25 SQL interview questions based on his 12-year career experience. He covers a range of SQL concepts from basic to advanced, including DDL and DML, joins, aggregate functions, and window functions. Tawfiq also discusses performance tuning, indexes, and the differences between views, synonyms, and triggers. Each question is accompanied by an explanation and examples, preparing viewers for various SQL interview scenarios.

Takeaways

• 😀 The video shares 25 top SQL interview questions based on the presenter's 12 years of SQL experience.
• 📚 The presenter has both taken and given SQL interviews, lending credibility to the questions' relevance.
• 🔗 Links to additional resources, including videos and a blog post, are provided in the video description for further study.
• 📈 The video emphasizes the importance of understanding SQL concepts rather than just memorizing queries.
• 🏢 The first question differentiates between DDL, DML, DCL, TCL, and DQL, which are fundamental SQL languages.
• 🗑️ The difference between 'DELETE' and 'TRUNCATE' statements is highlighted, noting that 'TRUNCATE' is faster for large datasets.
• 🔄 The video explains the use of 'CASE' statements, comparing them to 'IF ELSE' statements in other programming languages.
• 🔗 The presenter discusses the differences between various JOIN types, such as LEFT, RIGHT, FULL OUTER, and INNER JOIN.
• 📊 The script covers the use of 'DISTINCT' and 'GROUP BY' clauses, clarifying their differences and use cases.
• 📈 Aggregate functions like SUM, AVG, MIN, MAX, and COUNT are explained, along with their application with and without 'GROUP BY'.
• 🖥️ The video guarantees that understanding WINDOW functions like RANK, DENSE_RANK, and ROW_NUMBER is crucial for SQL interviews.

Q & A

What are the five languages available in SQL?

DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), TCL (Transaction Control Language), and DQL (Data Query Language).

What is the difference between DELETE and TRUNCATE statements in SQL?

DELETE is a DML statement used to remove specific rows from a table. TRUNCATE, a DDL statement, is used to remove all rows from a table and is faster because it does not generate individual row delete actions and does not log the deletion of each row.

Why is the CASE statement important in SQL?

The CASE statement is used for conditional logic in SQL queries, similar to an IF-ELSE statement in other programming languages, and is widely used for decision-making within SQL queries.

What is the difference between LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and INNER JOIN in SQL?

INNER JOIN returns rows when there is a match in both tables. LEFT JOIN returns all records from the left table and matched records from the right table. RIGHT JOIN returns all records from the right table and matched records from the left table. FULL OUTER JOIN returns all records when there is a match in one of the tables.

What is the difference between DISTINCT and GROUP BY in SQL?

DISTINCT is used to return only distinct (unique) values. GROUP BY is used to group rows that have the same values in specified columns into summary rows, like 'find the number of customers in each country'.

What are the rules to follow when using the UNION operator in SQL?

When using UNION, both the SELECT statements must have the same number of columns; the columns must have similar data types; and the column order must be the same in both SELECT statements.

What are aggregate functions and name a few types?

Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include SUM, AVG, MIN, MAX, and COUNT.

What is the difference between RANK, DENSE_RANK, and ROW_NUMBER window functions in SQL?

RANK assigns a rank to each row with gaps in the ranking sequence when there are ties. DENSE_RANK also assigns a rank to each row but does not leave gaps between the ranks. ROW_NUMBER assigns a unique number to each row without considering rank.

Can aggregate functions be used as window functions in SQL?

Yes, aggregate functions can be used as window functions by using the OVER clause, which allows the function to operate on a set of rows specified by the clause.

How can you convert a text into a date format in SQL?

Conversion from text to date format depends on the RDBMS. Functions like TO_DATE in Oracle, STR_TO_DATE in MySQL, CONVERT in SQL Server, and TO_DATE in PostgreSQL are commonly used.

What are subqueries and where can they be used in SQL?

Subqueries are SQL queries nested within other SQL queries. They can be used in the SELECT, FROM, WHERE, and HAVING clauses, and in various SQL statements like INSERT, UPDATE, and DELETE.

Is it good to have the same subquery multiple times in your SQL query?

No, it's not efficient to repeat the same subquery multiple times because it forces the SQL engine to execute the subquery multiple times. It's better to use common table expressions (CTEs) or temporary tables.

What is the difference between WHERE and HAVING clauses in SQL?

WHERE is used to filter rows before the groups are formed, while HAVING is used to filter groups after they are formed by the GROUP BY clause.

What are indexes and why are they used in SQL?

Indexes are used to speed up the retrieval of rows from a database table. They are a special lookup table that the database search engine can use to speed up data retrieval.

What steps would you take to tune a SQL query?

Tuning a SQL query involves checking the query for efficiency, ensuring proper indexes are in place, checking that statistics are generated and up-to-date, and reviewing the execution plan.

What is the difference between a primary key, unique key, and foreign key in SQL?

A primary key uniquely identifies each record in a table. A unique key also ensures uniqueness but can be null. A foreign key is a field or a set of fields in one table that uniquely identifies a row of another table.

What is the difference between a view and a synonym in SQL?

A view is a virtual table based on the result set of an SQL statement. A synonym is an alias for a table, view, or another synonym and is particularly useful for providing a different name for an object.

When can a function not be called from a SELECT query in SQL?

A function cannot be called from a SELECT query if it modifies the database state, such as using INSERT, UPDATE, or DELETE statements within the function.

What is a trigger in SQL?

A trigger is a database object that is associated with a particular table and is automatically executed or fired when certain events occur on that table.

What is the difference between a view and a materialized view in SQL?

A view is a virtual table defined by a query, whereas a materialized view is a physical copy of the data retrieved from one or more tables, stored on disk, and can be indexed.

What is a MERGE statement in SQL?

MERGE is a DML statement that allows you to insert a row into a table or update an existing row based on a condition, effectively combining INSERT and UPDATE operations.

Which functions can be used to fetch yesterday's date in SQL?

Functions to fetch yesterday's date vary by RDBMS. For example, GETDATE() in SQL Server can be used with DATEADD to subtract one day, while CURRENT_DATE in PostgreSQL can be used with INTERVAL to subtract one day.

What is the difference between a function and a procedure in SQL?

A function is a database object that returns a value and can be used in a SELECT statement, while a procedure is a set of precompiled SQL statements saved in the database that can be executed multiple times.

What is PRAGMA AUTONOMOUS_TRANSACTION in SQL?

PRAGMA AUTONOMOUS_TRANSACTION is a mode in stored procedures that allows the procedure to have its own independent transaction, separate from the calling transaction.