Top 25 SQL Interview Questions - Tawfiq

Jun 27, 2024

SQL Interview Preparation

Introduction

  • Presenter: Tawfiq
  • Objective: Share top 25 SQL interview questions based on 12 years of experience
  • These questions are from Tawfiq's interviews and personal experience
  • Questions mainly cover important SQL concepts
  • Detailed query videos available separately
  • Blog available with questions and answers (link provided in video)

Key Recommendations

  • Watch the entire video for additional information not found in the blog
  • Ensure you can answer all 25 questions to be well-prepared for SQL interviews
  • Blog includes answers and downloadable scripts for practice

SQL Interview Questions

Question 1: DDL and DML Languages

  • DDL (Data Definition Language): Defines database structure
  • DML (Data Manipulation Language): Manipulates data in the database
  • Other important languages: DCL (Data Control Language), TCL (Transaction Control Language), DQL (Data Query Language)

Question 2: Difference between DELETE and TRUNCATE

  • DELETE: DML operation, slower, can use WHERE clause, can be rolled back
  • TRUNCATE: DDL operation, faster, removes all records without using WHERE clause, cannot be rolled back

Question 3: CASE Statement

  • Usage: Similar to if-else statements in other languages
  • Importance: Widely used in SQL queries

Question 4: Difference between LEFT, RIGHT, FULL OUTER, and INNER JOIN

  • INNER JOIN: Returns matched records from both tables
  • LEFT JOIN: Returns all records from left table and matched records from right table
  • RIGHT JOIN: Returns all records from right table and matched records from left table
  • FULL OUTER JOIN: Returns all records when there is a match in either left or right table
  • Other Joins: Self Join, Natural Join, Cross Join

Question 5: Difference between DISTINCT and GROUP BY

  • DISTINCT: Fetches unique records
  • GROUP BY: Groups rows that have the same values into summary rows

Question 6: Rules for Using UNION Operator

  • Combining results from two queries
  • Columns and data types must match

Question 7: Aggregate Functions

  • Types: SUM, AVG, MIN, MAX, COUNT
  • Usage: With or without GROUP BY clause

Question 8: Difference between RANK, DENSE_RANK, and ROW_NUMBER

  • RANK: Provides rank of rows within a partition, gaps may exist
  • DENSE_RANK: Similar to RANK but no gaps
  • ROW_NUMBER: Assigns unique number to rows within partition

Question 9: Using Aggregate Functions as Window Functions

  • Can use functions like SUM, AVG, etc., as window functions using the OVER clause

Question 10: Convert Text to Date Format

  • RDBMS Specific Functions: Example solutions provided for Oracle, MySQL, SQL Server, PostgreSQL

Question 11: Fetching Substring from Full Name

  • Use string functions to extract first name from full name
  • Example solutions for Oracle, MySQL, SQL Server, PostgreSQL

Question 12: Sub Queries

  • Definition: Query within a query
  • Usage: Select, from, where clauses; also in insert, update, delete statements

Question 13: Repeating Sub Queries

  • Advice: Avoid repeating the same subquery; use WITH clause to improve performance

Question 14: Difference between WHERE and HAVING Clause

  • WHERE: Filters rows before grouping
  • HAVING: Filters groups after grouping

Question 15: Indexes

  • Purpose: Improve performance of queries
  • Types: Various types; understanding their appropriate use is crucial

Question 16: Steps to Tune a SQL Query

  1. Review the query for optimization opportunities
  2. Ensure correct indexes are used
  3. Verify and update statistics
  4. Analyze the explain plan

Question 17: Difference between Primary Key, Unique Key, and Foreign Key

  • Primary Key: Unique identifier for records, no NULL values allowed
  • Unique Key: Unique identifier but allows one NULL value
  • Foreign Key: Links two tables, maintains referential integrity

Question 18: Difference between View and Synonym

  • View: Virtual table based on result-set of an SQL statement
  • Synonym: Alias for a table, view, sequence, etc.

Question 19: Calling Functions in Select Query

  • Restriction: Functions using insert, update, delete cannot be used in select statement

Question 20: Triggers

  • Automatically executed based on events
  • Understand types and best practices

Question 21: Difference between View and Materialized View

  • View: Virtual table
  • Materialized View: Physical storage, periodically refreshed

Question 22: Merge Statement

  • Purpose: Combines insert, update in one statement based on conditions

Question 23: Fetching Yesterday’s Date

  • Different RDBMS functions to fetch current date and subtract one day

Question 24: Difference between Function and Procedure

  • Function: Returns a value, used in expressions
  • Procedure: Performs an action, does not return a value

Question 25: PRAGMA Autonomous Transaction

  • Allows a sub-transaction to execute independently
  • Generally used in stored procedures or triggers

Final Tips

  • Familiarize yourself with concepts covered in these questions
  • Practice extensively using provided scripts and blog resources
  • Subscribe to the channel for more SQL tips and tutorials