SQL interview questions with answers
1. How would you find duplicate records in SQL?
You can find duplicate records using the GROUP BY clause along with the HAVING clause. For example:
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
2. What are various types of SQL joins?
INNER JOIN: Returns only the rows where there is a match in both tables.
LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table; if no match, returns NULL from the right table.
RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table; if no match, returns NULL from the left table.
FULL JOIN (FULL OUTER JOIN): Returns all records when there is a match in either the left or right table. Non-matching rows are filled with NULL.
CROSS JOIN: Returns the Cartesian product of the two tables, i.e., all possible combinations of rows.
SELF JOIN: Joins a table with itself.
3. What is a trigger in SQL?
A trigger is a stored procedure that automatically executes or "fires" when an event (such as an insert, update, or delete) occurs on a table or view. Triggers are used to enforce business rules, validations, and logging.
4. What are different DDL, DML commands in SQL?
DDL (Data Definition Language): Includes commands like CREATE, ALTER, DROP, and TRUNCATE that define or modify database structures.
DML (Data Manipulation Language): Includes commands like INSERT, UPDATE, DELETE, and SELECT that manage data within schema objects.
5. What is the difference between Delete, Drop and Truncate?
DELETE: Removes rows from a table based on a condition. It is a DML command and can be rolled back.
DROP: Deletes a table (or other database object) and all its data. It is a DDL command and cannot be rolled back.
TRUNCATE: Removes all rows from a table, but the table structure remains. It is faster than DELETE and is a DDL command. It cannot be rolled back in many systems.
6. What is the difference between Union and Union all?
UNION: Combines the result sets of two or more queries and removes duplicates.
UNION ALL: Combines the result sets of two or more queries without removing duplicates.
7. Which command gives unique values?
The DISTINCT keyword is used to return unique values in a result set. Example:
SELECT DISTINCT column_name FROM table_name;
8. What is the difference between Where and Having Clause?
WHERE: Filters rows before any groupings are made (used with SELECT, UPDATE, DELETE).
HAVING: Filters groups after the GROUP BY operation (used with GROUP BY).
9. Give the execution order of keywords in SQL?
The typical execution order is:
1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. DISTINCT
8. ORDER BY
9. LIMIT/OFFSET
10. What is the difference between IN and BETWEEN Operator?
IN: Used to check if a value is within a set of values. Example:
SELECT * FROM table WHERE column_name IN (value1, value2, value3);
BETWEEN: Used to filter the result set within a specific range. Example:
SELECT * FROM table WHERE column_name BETWEEN value1 AND value2;
11. What is primary and Foreign key?
Primary Key: A column (or set of columns) in a table that uniquely identifies each row in the table. It cannot have NULL values.
Foreign Key: A column (or set of columns) in one table that refers to the primary key in another table, establishing a relationship between the two tables.
12. What is an aggregate function?
Aggregate functions perform a calculation on a set of values and return a single value. Examples include:
COUNT(): Counts the number of rows.
SUM(): Returns the sum of a numeric column.
AVG(): Returns the average value.
MIN() and MAX(): Return the minimum and maximum values.
13. What is the difference between Rank and Dense Rank?
RANK(): Assigns a rank to each row, with gaps in the ranking where there are ties.
DENSE_RANK(): Similar to RANK(), but does not leave gaps in the ranking when there are ties.
1. How would you find duplicate records in SQL?
You can find duplicate records using the GROUP BY clause along with the HAVING clause. For example:
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
2. What are various types of SQL joins?
INNER JOIN: Returns only the rows where there is a match in both tables.
LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table; if no match, returns NULL from the right table.
RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table; if no match, returns NULL from the left table.
FULL JOIN (FULL OUTER JOIN): Returns all records when there is a match in either the left or right table. Non-matching rows are filled with NULL.
CROSS JOIN: Returns the Cartesian product of the two tables, i.e., all possible combinations of rows.
SELF JOIN: Joins a table with itself.
3. What is a trigger in SQL?
A trigger is a stored procedure that automatically executes or "fires" when an event (such as an insert, update, or delete) occurs on a table or view. Triggers are used to enforce business rules, validations, and logging.
4. What are different DDL, DML commands in SQL?
DDL (Data Definition Language): Includes commands like CREATE, ALTER, DROP, and TRUNCATE that define or modify database structures.
DML (Data Manipulation Language): Includes commands like INSERT, UPDATE, DELETE, and SELECT that manage data within schema objects.
5. What is the difference between Delete, Drop and Truncate?
DELETE: Removes rows from a table based on a condition. It is a DML command and can be rolled back.
DROP: Deletes a table (or other database object) and all its data. It is a DDL command and cannot be rolled back.
TRUNCATE: Removes all rows from a table, but the table structure remains. It is faster than DELETE and is a DDL command. It cannot be rolled back in many systems.
6. What is the difference between Union and Union all?
UNION: Combines the result sets of two or more queries and removes duplicates.
UNION ALL: Combines the result sets of two or more queries without removing duplicates.
7. Which command gives unique values?
The DISTINCT keyword is used to return unique values in a result set. Example:
SELECT DISTINCT column_name FROM table_name;
8. What is the difference between Where and Having Clause?
WHERE: Filters rows before any groupings are made (used with SELECT, UPDATE, DELETE).
HAVING: Filters groups after the GROUP BY operation (used with GROUP BY).
9. Give the execution order of keywords in SQL?
The typical execution order is:
1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. DISTINCT
8. ORDER BY
9. LIMIT/OFFSET
10. What is the difference between IN and BETWEEN Operator?
IN: Used to check if a value is within a set of values. Example:
SELECT * FROM table WHERE column_name IN (value1, value2, value3);
BETWEEN: Used to filter the result set within a specific range. Example:
SELECT * FROM table WHERE column_name BETWEEN value1 AND value2;
11. What is primary and Foreign key?
Primary Key: A column (or set of columns) in a table that uniquely identifies each row in the table. It cannot have NULL values.
Foreign Key: A column (or set of columns) in one table that refers to the primary key in another table, establishing a relationship between the two tables.
12. What is an aggregate function?
Aggregate functions perform a calculation on a set of values and return a single value. Examples include:
COUNT(): Counts the number of rows.
SUM(): Returns the sum of a numeric column.
AVG(): Returns the average value.
MIN() and MAX(): Return the minimum and maximum values.
13. What is the difference between Rank and Dense Rank?
RANK(): Assigns a rank to each row, with gaps in the ranking where there are ties.
DENSE_RANK(): Similar to RANK(), but does not leave gaps in the ranking when there are ties.
❤1👍1
14. List the ACID Properties and explain what they are?
Atomicity: Ensures that a transaction is all-or-nothing. Either all operations are performed, or none are.
Consistency: Ensures that a transaction takes the database from one valid state to another.
Isolation: Ensures that operations of one transaction are isolated from others.
Durability: Ensures that once a transaction is committed, it will remain in the database, even in case of a system crash.
15. What is the difference between % and _ in the LIKE operator?
%: Represents zero or more characters.
_: Represents exactly one character.
16. What does CTE stand for?
CTE stands for Common Table Expression. It is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
17. What is a database? What is DBMS? What is RDBMS?
Database: A collection of structured data stored and accessed electronically.
DBMS (Database Management System): Software that provides an interface for interacting with databases, allowing for data creation, modification, and querying.
RDBMS (Relational Database Management System): A type of DBMS that stores data in tables with relationships between them (e.g., MySQL, PostgreSQL).
18. What is Alias in SQL?
An Alias is a temporary name given to a table or column for the duration of a query. It makes the query easier to read. Example:
SELECT column_name AS alias_name FROM table_name;
19. What is Normalisation? Describe various forms.
Normalization is the process of organizing a database to reduce redundancy and dependency.
1NF (First Normal Form): Ensures no repeating groups or arrays in a table.
2NF (Second Normal Form): Ensures that all non-key attributes are fully dependent on the primary key.
3NF (Third Normal Form): Ensures that no non-key attribute is transitively dependent on the primary key.
BCNF (Boyce-Codd Normal Form): A stricter version of 3NF.
20. How do you sort the results of a query?
Use the ORDER BY clause to sort the result set. Example:
SELECT * FROM table_name ORDER BY column_name ASC|DESC;
21. Explain the types of Window functions?
ROW_NUMBER(): Assigns a unique row number to each row.
RANK(): Assigns a rank, with gaps in case of ties.
DENSE_RANK(): Assigns a rank without gaps.
NTILE(): Divides the result set into a specified number of buckets.
LEAD() and LAG(): Accesses data from subsequent or previous rows.
22. What is limit and offset?
LIMIT: Restricts the number of rows returned by a query. Example: LIMIT 10.
OFFSET: Skips the specified number of rows before beginning to return rows. Example: OFFSET 5 LIMIT 10.
23. What is candidate key?
A candidate key is a set of one or more columns that can uniquely identify a row in a table. There can be multiple candidate keys in a table, but one is chosen as the primary key.
24. Describe various types of Alter command?
ALTER TABLE: Modifies an existing table (e.g., adding/removing columns, changing data types).
Example: ALTER TABLE table_name ADD column_name datatype;
ALTER COLUMN: Modifies an existing column's properties.
Example: ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE new_type;
25. What is Cartesian product?
A Cartesian Product occurs when a query uses a CROSS JOIN. It returns all possible combinations of rows from the two tables, resulting in a multiplication of the number of rows from both tables.
Example:
SELECT * FROM table1 CROSS JOIN table2;
React with ❤️ for more Interview Resources
Atomicity: Ensures that a transaction is all-or-nothing. Either all operations are performed, or none are.
Consistency: Ensures that a transaction takes the database from one valid state to another.
Isolation: Ensures that operations of one transaction are isolated from others.
Durability: Ensures that once a transaction is committed, it will remain in the database, even in case of a system crash.
15. What is the difference between % and _ in the LIKE operator?
%: Represents zero or more characters.
_: Represents exactly one character.
16. What does CTE stand for?
CTE stands for Common Table Expression. It is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
17. What is a database? What is DBMS? What is RDBMS?
Database: A collection of structured data stored and accessed electronically.
DBMS (Database Management System): Software that provides an interface for interacting with databases, allowing for data creation, modification, and querying.
RDBMS (Relational Database Management System): A type of DBMS that stores data in tables with relationships between them (e.g., MySQL, PostgreSQL).
18. What is Alias in SQL?
An Alias is a temporary name given to a table or column for the duration of a query. It makes the query easier to read. Example:
SELECT column_name AS alias_name FROM table_name;
19. What is Normalisation? Describe various forms.
Normalization is the process of organizing a database to reduce redundancy and dependency.
1NF (First Normal Form): Ensures no repeating groups or arrays in a table.
2NF (Second Normal Form): Ensures that all non-key attributes are fully dependent on the primary key.
3NF (Third Normal Form): Ensures that no non-key attribute is transitively dependent on the primary key.
BCNF (Boyce-Codd Normal Form): A stricter version of 3NF.
20. How do you sort the results of a query?
Use the ORDER BY clause to sort the result set. Example:
SELECT * FROM table_name ORDER BY column_name ASC|DESC;
21. Explain the types of Window functions?
ROW_NUMBER(): Assigns a unique row number to each row.
RANK(): Assigns a rank, with gaps in case of ties.
DENSE_RANK(): Assigns a rank without gaps.
NTILE(): Divides the result set into a specified number of buckets.
LEAD() and LAG(): Accesses data from subsequent or previous rows.
22. What is limit and offset?
LIMIT: Restricts the number of rows returned by a query. Example: LIMIT 10.
OFFSET: Skips the specified number of rows before beginning to return rows. Example: OFFSET 5 LIMIT 10.
23. What is candidate key?
A candidate key is a set of one or more columns that can uniquely identify a row in a table. There can be multiple candidate keys in a table, but one is chosen as the primary key.
24. Describe various types of Alter command?
ALTER TABLE: Modifies an existing table (e.g., adding/removing columns, changing data types).
Example: ALTER TABLE table_name ADD column_name datatype;
ALTER COLUMN: Modifies an existing column's properties.
Example: ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE new_type;
25. What is Cartesian product?
A Cartesian Product occurs when a query uses a CROSS JOIN. It returns all possible combinations of rows from the two tables, resulting in a multiplication of the number of rows from both tables.
Example:
SELECT * FROM table1 CROSS JOIN table2;
React with ❤️ for more Interview Resources
❤7👍2
15 essential SQL interview questions
1️⃣ Explain Order of Execution of SQL query
2️⃣ Provide a use case for each of the functions Rank, Dense_Rank & Row_Number ( 💡 majority struggle )
3️⃣ Write a query to find the cumulative sum/Running Total
4️⃣ Find the Most selling product by sales/ highest Salary of employees
5️⃣ Write a query to find the 2nd/nth highest Salary of employees
6️⃣ Difference between union vs union all
7️⃣ Identify if there any duplicates in a table
8️⃣ Scenario based Joins question, understanding of Inner, Left and Outer Joins via simple yet tricky question
9️⃣ LAG, write a query to find all those records where the transaction value is greater then previous transaction value
1️⃣ 0️⃣ Rank vs Dense Rank, query to find the 2nd highest Salary of employee
( Ideal soln should handle ties)
1️⃣ 1️⃣ Write a query to find the Running Difference (Ideal sol'n using windows function)
1️⃣ 2️⃣ Write a query to display year on year/month on month growth
1️⃣ 3️⃣ Write a query to find rolling average of daily sign-ups
1️⃣ 4️⃣ Write a query to find the running difference using self join (helps in understanding the logical approach, ideally this question is solved via windows function)
1️⃣ 5️⃣ Write a query to find the cumulative sum using self join
(helps in understanding the logical approach, ideally this question is solved via windows function
React with ❤️ for detailed answers
1️⃣ Explain Order of Execution of SQL query
2️⃣ Provide a use case for each of the functions Rank, Dense_Rank & Row_Number ( 💡 majority struggle )
3️⃣ Write a query to find the cumulative sum/Running Total
4️⃣ Find the Most selling product by sales/ highest Salary of employees
5️⃣ Write a query to find the 2nd/nth highest Salary of employees
6️⃣ Difference between union vs union all
7️⃣ Identify if there any duplicates in a table
8️⃣ Scenario based Joins question, understanding of Inner, Left and Outer Joins via simple yet tricky question
9️⃣ LAG, write a query to find all those records where the transaction value is greater then previous transaction value
1️⃣ 0️⃣ Rank vs Dense Rank, query to find the 2nd highest Salary of employee
( Ideal soln should handle ties)
1️⃣ 1️⃣ Write a query to find the Running Difference (Ideal sol'n using windows function)
1️⃣ 2️⃣ Write a query to display year on year/month on month growth
1️⃣ 3️⃣ Write a query to find rolling average of daily sign-ups
1️⃣ 4️⃣ Write a query to find the running difference using self join (helps in understanding the logical approach, ideally this question is solved via windows function)
1️⃣ 5️⃣ Write a query to find the cumulative sum using self join
(helps in understanding the logical approach, ideally this question is solved via windows function
React with ❤️ for detailed answers
❤3👍2🎉1
15 Essential SQL Interview Questions (With Detailed Answers)
1. Explain Order of Execution of SQL Query
Answer: The order in which SQL queries are executed is:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT/OFFSET
This order is essential to understand how SQL processes data, especially when using aggregates and filtering.
2. Use Case for RANK, DENSE_RANK & ROW_NUMBER
RANK: Assigns the same rank to ties, but skips the next rank(s).
DENSE_RANK: Same rank for ties, but does not skip any ranks.
ROW_NUMBER: Assigns unique numbers to each row, even if values are same.
Use case example: Find top 3 employees by salary:
SELECT *, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees;
3. Query to Find Cumulative Sum/Running Total
SELECT name, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;
4. Most Selling Product / Highest Salary
Most Selling Product:
SELECT product_id, SUM(quantity) AS total_sold
FROM sales
GROUP BY product_id
ORDER BY total_sold DESC
LIMIT 1;
Highest Salary:
SELECT MAX(salary) FROM employees;
5. Nth Highest Salary
SELECT * FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
) sub
WHERE rnk = N;
Replace N with desired rank.
6. Difference Between UNION vs UNION ALL
UNION removes duplicates.
UNION ALL keeps all records including duplicates.
UNION is slower due to deduplication.
7. Identify Duplicates
SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;
8. Joins Scenario
Inner Join: Only matching records
Left Join: All from left + matches from right
Full Outer Join: All records from both with NULL where no match
Example:
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
9. LAG - Find records with increased transaction
SELECT *,
LAG(transaction_value) OVER (ORDER BY transaction_date) as prev_txn
FROM transactions
WHERE transaction_value > prev_txn;
10. Rank vs Dense Rank - 2nd Highest Salary
SELECT * FROM (
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
) sub
WHERE rnk = 2;
11. Running Difference Using Window Function
SELECT name, transaction_value,
transaction_value - LAG(transaction_value) OVER (ORDER BY id) as difference
FROM transactions;
12. YoY or MoM Growth
SELECT year, month, total_sales,
total_sales - LAG(total_sales) OVER (ORDER BY year, month) AS growth
FROM sales_summary;
13. Rolling Average of Daily Sign-ups
SELECT signup_date, COUNT(*) AS daily_signups,
AVG(COUNT(*)) OVER (ORDER BY signup_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_avg_7_days
FROM signups
GROUP BY signup_date;
14. Running Difference Using Self Join
SELECT a.id, a.value - b.value AS running_difference
FROM transactions a
JOIN transactions b ON a.id = b.id + 1;
15. Cumulative Sum Using Self Join
SELECT a.id, a.name, SUM(b.salary) AS cumulative_salary
FROM employees a
JOIN employees b ON b.id <= a.id
GROUP BY a.id, a.name;
Like for more free resources
1. Explain Order of Execution of SQL Query
Answer: The order in which SQL queries are executed is:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT/OFFSET
This order is essential to understand how SQL processes data, especially when using aggregates and filtering.
2. Use Case for RANK, DENSE_RANK & ROW_NUMBER
RANK: Assigns the same rank to ties, but skips the next rank(s).
DENSE_RANK: Same rank for ties, but does not skip any ranks.
ROW_NUMBER: Assigns unique numbers to each row, even if values are same.
Use case example: Find top 3 employees by salary:
SELECT *, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees;
3. Query to Find Cumulative Sum/Running Total
SELECT name, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;
4. Most Selling Product / Highest Salary
Most Selling Product:
SELECT product_id, SUM(quantity) AS total_sold
FROM sales
GROUP BY product_id
ORDER BY total_sold DESC
LIMIT 1;
Highest Salary:
SELECT MAX(salary) FROM employees;
5. Nth Highest Salary
SELECT * FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
) sub
WHERE rnk = N;
Replace N with desired rank.
6. Difference Between UNION vs UNION ALL
UNION removes duplicates.
UNION ALL keeps all records including duplicates.
UNION is slower due to deduplication.
7. Identify Duplicates
SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;
8. Joins Scenario
Inner Join: Only matching records
Left Join: All from left + matches from right
Full Outer Join: All records from both with NULL where no match
Example:
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
9. LAG - Find records with increased transaction
SELECT *,
LAG(transaction_value) OVER (ORDER BY transaction_date) as prev_txn
FROM transactions
WHERE transaction_value > prev_txn;
10. Rank vs Dense Rank - 2nd Highest Salary
SELECT * FROM (
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
) sub
WHERE rnk = 2;
11. Running Difference Using Window Function
SELECT name, transaction_value,
transaction_value - LAG(transaction_value) OVER (ORDER BY id) as difference
FROM transactions;
12. YoY or MoM Growth
SELECT year, month, total_sales,
total_sales - LAG(total_sales) OVER (ORDER BY year, month) AS growth
FROM sales_summary;
13. Rolling Average of Daily Sign-ups
SELECT signup_date, COUNT(*) AS daily_signups,
AVG(COUNT(*)) OVER (ORDER BY signup_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_avg_7_days
FROM signups
GROUP BY signup_date;
14. Running Difference Using Self Join
SELECT a.id, a.value - b.value AS running_difference
FROM transactions a
JOIN transactions b ON a.id = b.id + 1;
15. Cumulative Sum Using Self Join
SELECT a.id, a.name, SUM(b.salary) AS cumulative_salary
FROM employees a
JOIN employees b ON b.id <= a.id
GROUP BY a.id, a.name;
Like for more free resources
👍5❤4🎉1
Key SQL Commands:
➡️ SELECT: Retrieves data from one or more tables.
➡️ FROM: Specifies the table(s) to query.
➡️ WHERE: Filters results based on conditions.
➡️ GROUP BY: Groups rows that share a value in specified columns.
➡️ ORDER BY: Sorts results in ascending or descending order.
➡️ JOIN: Combines rows from multiple tables based on related columns.
➡️ UNION: Merges the results of two or more SELECT statements.
➡️ LIMIT: Restricts the number of rows returned.
➡️ INSERT INTO: Adds new records to a table.
➡️ UPDATE: Modifies existing records.
➡️ DELETE: Removes records from a table.
Understanding SQL Command Types:
Data Definition Language (DDL):
➡️ CREATE: Generates new database objects like tables, indexes, and views.
➡️ ALTER: Changes the structure of existing database objects.
➡️ DROP: Deletes database objects permanently.
➡️ TRUNCATE: Erases all records from a table but keeps its structure intact.
➡️ RENAME: Changes the name of a database object.
Data Manipulation Language (DML):
➡️ INSERT: Adds new data into a table.
➡️ UPDATE: Updates existing data within a table.
➡️ DELETE: Deletes existing data from a table.
➡️ MERGE: Conditionally inserts or updates data.
Data Control Language (DCL):
➡️ GRANT: Assigns access privileges to users.
➡️ REVOKE: Removes access privileges from users.
Transaction Control Language (TCL):
➡️ COMMIT: Saves the changes made by a transaction.
➡️ ROLLBACK: Reverses the changes made by a transaction.
➡️ SAVEPOINT: Sets a point within a transaction to which you can rollback.
Data Query Language (DQL):
➡️ SELECT: Fetches data from the database.
Hope it helps :)
➡️ SELECT: Retrieves data from one or more tables.
➡️ FROM: Specifies the table(s) to query.
➡️ WHERE: Filters results based on conditions.
➡️ GROUP BY: Groups rows that share a value in specified columns.
➡️ ORDER BY: Sorts results in ascending or descending order.
➡️ JOIN: Combines rows from multiple tables based on related columns.
➡️ UNION: Merges the results of two or more SELECT statements.
➡️ LIMIT: Restricts the number of rows returned.
➡️ INSERT INTO: Adds new records to a table.
➡️ UPDATE: Modifies existing records.
➡️ DELETE: Removes records from a table.
Understanding SQL Command Types:
Data Definition Language (DDL):
➡️ CREATE: Generates new database objects like tables, indexes, and views.
➡️ ALTER: Changes the structure of existing database objects.
➡️ DROP: Deletes database objects permanently.
➡️ TRUNCATE: Erases all records from a table but keeps its structure intact.
➡️ RENAME: Changes the name of a database object.
Data Manipulation Language (DML):
➡️ INSERT: Adds new data into a table.
➡️ UPDATE: Updates existing data within a table.
➡️ DELETE: Deletes existing data from a table.
➡️ MERGE: Conditionally inserts or updates data.
Data Control Language (DCL):
➡️ GRANT: Assigns access privileges to users.
➡️ REVOKE: Removes access privileges from users.
Transaction Control Language (TCL):
➡️ COMMIT: Saves the changes made by a transaction.
➡️ ROLLBACK: Reverses the changes made by a transaction.
➡️ SAVEPOINT: Sets a point within a transaction to which you can rollback.
Data Query Language (DQL):
➡️ SELECT: Fetches data from the database.
Hope it helps :)
👍6❤5👏1
20 medium-level SQL interview questions:
1. Write a SQL query to find the second-highest salary.
2. How would you optimize a slow SQL query?
3. What is the difference between INNER JOIN and OUTER JOIN?
4. Write a SQL query to find the top 3 departments with the highest average salary.
5. How do you handle duplicate rows in a SQL query?
6. Write a SQL query to find the employees who have the same name and work in the same department.
7. What is the difference between UNION and UNION ALL?
8. Write a SQL query to find the departments with no employees.
9. How do you use indexing to improve SQL query performance?
10. Write a SQL query to find the employees who have worked for more than 5 years.
11. What is the difference between SUBQUERY and JOIN?
12. Write a SQL query to find the top 2 products with the highest sales.
13. How do you use stored procedures to improve SQL query performance?
14. Write a SQL query to find the customers who have placed an order but have not made a payment.
15. What is the difference between GROUP BY and HAVING?
16. Write a SQL query to find the employees who work in the same department as their manager.
17. How do you use window functions to solve complex queries?
18. Write a SQL query to find the top 3 products with the highest average price.
19. What is the difference between TRUNCATE and DELETE?
20. Write a SQL query to find the employees who have not taken any leave in the last 6 months.
Like for detailed answers ❤️
1. Write a SQL query to find the second-highest salary.
2. How would you optimize a slow SQL query?
3. What is the difference between INNER JOIN and OUTER JOIN?
4. Write a SQL query to find the top 3 departments with the highest average salary.
5. How do you handle duplicate rows in a SQL query?
6. Write a SQL query to find the employees who have the same name and work in the same department.
7. What is the difference between UNION and UNION ALL?
8. Write a SQL query to find the departments with no employees.
9. How do you use indexing to improve SQL query performance?
10. Write a SQL query to find the employees who have worked for more than 5 years.
11. What is the difference between SUBQUERY and JOIN?
12. Write a SQL query to find the top 2 products with the highest sales.
13. How do you use stored procedures to improve SQL query performance?
14. Write a SQL query to find the customers who have placed an order but have not made a payment.
15. What is the difference between GROUP BY and HAVING?
16. Write a SQL query to find the employees who work in the same department as their manager.
17. How do you use window functions to solve complex queries?
18. Write a SQL query to find the top 3 products with the highest average price.
19. What is the difference between TRUNCATE and DELETE?
20. Write a SQL query to find the employees who have not taken any leave in the last 6 months.
Like for detailed answers ❤️
❤18👍2🎉1
Important SQL Interview Questions
1. How do you find duplicate records in SQL?
2. What are the different types of SQL Joins?
3. What is a Trigger in SQL?
4. Differentiate between DDL and DML commands in SQL.
5. Difference between DELETE, DROP, and TRUNCATE.
6. UNION vs UNION ALL – What’s the difference?
7. Which command gives unique values in SQL?
8. WHERE vs HAVING – Key differences?
9. Explain the execution order of SQL keywords.
10. IN vs BETWEEN operators – When to use what?
11. Define Primary Key and Foreign Key.
12. What are Aggregate Functions in SQL?
13. Difference between RANK and DENSE_RANK?
14. List and explain ACID Properties.
15. What is the difference between % and _ in LIKE operator?
16. What is CTE (Common Table Expression)?
17. What is a Database, DBMS, and RDBMS?
18. What is an Alias in SQL?
19. What is Normalization? Explain its types.
20. How to sort query results in SQL?
21. Explain types of Window Functions.
22. What is LIMIT and OFFSET in SQL?
23. What is a Candidate Key?
24. Different types of ALTER commands.
25. What is a Cartesian Product in SQL?
React with ❤️ for detailed answers
1. How do you find duplicate records in SQL?
2. What are the different types of SQL Joins?
3. What is a Trigger in SQL?
4. Differentiate between DDL and DML commands in SQL.
5. Difference between DELETE, DROP, and TRUNCATE.
6. UNION vs UNION ALL – What’s the difference?
7. Which command gives unique values in SQL?
8. WHERE vs HAVING – Key differences?
9. Explain the execution order of SQL keywords.
10. IN vs BETWEEN operators – When to use what?
11. Define Primary Key and Foreign Key.
12. What are Aggregate Functions in SQL?
13. Difference between RANK and DENSE_RANK?
14. List and explain ACID Properties.
15. What is the difference between % and _ in LIKE operator?
16. What is CTE (Common Table Expression)?
17. What is a Database, DBMS, and RDBMS?
18. What is an Alias in SQL?
19. What is Normalization? Explain its types.
20. How to sort query results in SQL?
21. Explain types of Window Functions.
22. What is LIMIT and OFFSET in SQL?
23. What is a Candidate Key?
24. Different types of ALTER commands.
25. What is a Cartesian Product in SQL?
React with ❤️ for detailed answers
❤10👍2
SQL Programming Resources
20 medium-level SQL interview questions: 1. Write a SQL query to find the second-highest salary. 2. How would you optimize a slow SQL query? 3. What is the difference between INNER JOIN and OUTER JOIN? 4. Write a SQL query to find the top 3 departments with…
Answers posted on our WhatsApp channel: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/966
👍1
Database concepts explained in banking terms 👇
Database = Bank
Table = Account
Data = Money
DDL = Account Opening/Updating/Closing
DML = Debit and Credit
ACID = Secure Transaction
Index = Credit/Debit card
Constraints = Bank Policies
Primary Key = Account Number
Foreign Key = Joint Account
Triggers = SMS Alerts
Stored Procedure = Customer Care
Hope this helps you 😊
Database = Bank
Table = Account
Data = Money
DDL = Account Opening/Updating/Closing
DML = Debit and Credit
ACID = Secure Transaction
Index = Credit/Debit card
Constraints = Bank Policies
Primary Key = Account Number
Foreign Key = Joint Account
Triggers = SMS Alerts
Stored Procedure = Customer Care
Hope this helps you 😊
👍16👏6🎉5❤1
Important SQL Interview Questions and Answers (2025)
1. How do you find duplicate records in SQL?
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
This query groups records by a specific column and filters groups that have more than one occurrence.
2. What are the different types of SQL Joins?
- INNER JOIN: Returns records that have matching values in both tables.
- LEFT JOIN: Returns all records from the left table and the matched records from the right table.
- RIGHT JOIN: Returns all records from the right table and the matched records from the left table.
- FULL OUTER JOIN: Returns all records when there is a match in either left or right table.
- SELF JOIN: A regular join but the table is joined with itself.
3. What is a Trigger in SQL? A trigger is a stored procedure that runs automatically in response to specific events on a particular table or view (e.g., INSERT, UPDATE, DELETE).
4. Differentiate between DDL and DML commands in SQL.
DDL (Data Definition Language): Defines database schema (CREATE, ALTER, DROP).
DML (Data Manipulation Language): Manipulates data (INSERT, UPDATE, DELETE).
5. Difference between DELETE, DROP, and TRUNCATE.
DELETE: Removes specific rows, can use WHERE clause, can be rolled back.
TRUNCATE: Removes all rows, faster than DELETE, cannot be rolled back in most DBMS.
DROP: Deletes the entire table structure and data.
6. UNION vs UNION ALL – What’s the difference?
UNION: Removes duplicate records.
UNION ALL: Includes duplicates.
7. Which command gives unique values in SQL?
SELECT DISTINCT column_name FROM table_name;
8. WHERE vs HAVING – Key differences?
WHERE: Filters rows before grouping.
HAVING: Filters groups after aggregation.
9. Explain the execution order of SQL keywords.
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT
10. IN vs BETWEEN operators – When to use what?
IN: For checking values within a list.
BETWEEN: For checking values within a range.
11. Define Primary Key and Foreign Key.
Primary Key: Uniquely identifies each record in a table.
Foreign Key: Links records between two tables.
12. What are Aggregate Functions in SQL?
Functions like COUNT(), SUM(), AVG(), MAX(), MIN() used for calculations on data.
13. Difference between RANK and DENSE_RANK?
RANK(): Leaves gaps in ranking when there are ties.
DENSE_RANK(): No gaps in ranks.
14. List and explain ACID Properties.
Atomicity: All or nothing.
Consistency: Valid state before and after a transaction.
Isolation: Transactions don't affect each other.
Durability: Results are permanent after commit.
15. What is the difference between % and _ in LIKE operator?
%: Matches any number of characters.
_: Matches a single character.
16. What is CTE (Common Table Expression)? A temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
WITH CTE AS (
SELECT column FROM table
)
SELECT * FROM CTE;
17. What is a Database, DBMS, and RDBMS?
Database: Organized data collection.
DBMS: Software to manage databases.
RDBMS: DBMS based on relational model (tables, keys).
18. What is an Alias in SQL?
Temporary name for a table or column.
SELECT column_name AS alias_name FROM table_name;
19. What is Normalization? Explain its types. Process of organizing data to reduce redundancy:
1NF: Eliminate repeating groups.
2NF: Remove partial dependency.
3NF: Remove transitive dependency.
20. How to sort query results in SQL?
Use ORDER BY clause.
SELECT * FROM table_name ORDER BY column_name ASC|DESC;
21. Explain types of Window Functions.
ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), LAG(), etc., work over a partition of data.
22. What is LIMIT and OFFSET in SQL?
LIMIT: Specifies the number of records to return.
OFFSET: Specifies how many rows to skip before starting to return rows.
Credits: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
1. How do you find duplicate records in SQL?
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
This query groups records by a specific column and filters groups that have more than one occurrence.
2. What are the different types of SQL Joins?
- INNER JOIN: Returns records that have matching values in both tables.
- LEFT JOIN: Returns all records from the left table and the matched records from the right table.
- RIGHT JOIN: Returns all records from the right table and the matched records from the left table.
- FULL OUTER JOIN: Returns all records when there is a match in either left or right table.
- SELF JOIN: A regular join but the table is joined with itself.
3. What is a Trigger in SQL? A trigger is a stored procedure that runs automatically in response to specific events on a particular table or view (e.g., INSERT, UPDATE, DELETE).
4. Differentiate between DDL and DML commands in SQL.
DDL (Data Definition Language): Defines database schema (CREATE, ALTER, DROP).
DML (Data Manipulation Language): Manipulates data (INSERT, UPDATE, DELETE).
5. Difference between DELETE, DROP, and TRUNCATE.
DELETE: Removes specific rows, can use WHERE clause, can be rolled back.
TRUNCATE: Removes all rows, faster than DELETE, cannot be rolled back in most DBMS.
DROP: Deletes the entire table structure and data.
6. UNION vs UNION ALL – What’s the difference?
UNION: Removes duplicate records.
UNION ALL: Includes duplicates.
7. Which command gives unique values in SQL?
SELECT DISTINCT column_name FROM table_name;
8. WHERE vs HAVING – Key differences?
WHERE: Filters rows before grouping.
HAVING: Filters groups after aggregation.
9. Explain the execution order of SQL keywords.
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT
10. IN vs BETWEEN operators – When to use what?
IN: For checking values within a list.
BETWEEN: For checking values within a range.
11. Define Primary Key and Foreign Key.
Primary Key: Uniquely identifies each record in a table.
Foreign Key: Links records between two tables.
12. What are Aggregate Functions in SQL?
Functions like COUNT(), SUM(), AVG(), MAX(), MIN() used for calculations on data.
13. Difference between RANK and DENSE_RANK?
RANK(): Leaves gaps in ranking when there are ties.
DENSE_RANK(): No gaps in ranks.
14. List and explain ACID Properties.
Atomicity: All or nothing.
Consistency: Valid state before and after a transaction.
Isolation: Transactions don't affect each other.
Durability: Results are permanent after commit.
15. What is the difference between % and _ in LIKE operator?
%: Matches any number of characters.
_: Matches a single character.
16. What is CTE (Common Table Expression)? A temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
WITH CTE AS (
SELECT column FROM table
)
SELECT * FROM CTE;
17. What is a Database, DBMS, and RDBMS?
Database: Organized data collection.
DBMS: Software to manage databases.
RDBMS: DBMS based on relational model (tables, keys).
18. What is an Alias in SQL?
Temporary name for a table or column.
SELECT column_name AS alias_name FROM table_name;
19. What is Normalization? Explain its types. Process of organizing data to reduce redundancy:
1NF: Eliminate repeating groups.
2NF: Remove partial dependency.
3NF: Remove transitive dependency.
20. How to sort query results in SQL?
Use ORDER BY clause.
SELECT * FROM table_name ORDER BY column_name ASC|DESC;
21. Explain types of Window Functions.
ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), LAG(), etc., work over a partition of data.
22. What is LIMIT and OFFSET in SQL?
LIMIT: Specifies the number of records to return.
OFFSET: Specifies how many rows to skip before starting to return rows.
Credits: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
👍7❤4🎉1
SQL Interview Questions which can be asked in a Data Analyst Interview.
1️⃣ What is difference between Primary key and Unique key?
◼Primary key- A column or set of columns which uniquely identifies each record in a table. It can't contain null values and only one primary key
can exist in a table.
◼Unique key-Similar to primary key it also uniquely identifies each record in a table and can contain null values.Multiple Unique key can exist in a table.
2️⃣ What is a Candidate key?
◼A key or set of keys that uniquely identifies each record in a table.It is a combination of Primary and Alternate key.
3️⃣ What is a Constraint?
◼Specific rule or limit that we define in our table. E.g - NOT NULL,AUTO INCREMENT
4️⃣ Can you differentiate between TRUNCATE and DELETE?
◼TRUNCATE is a DDL command. It deletes the entire data from a table but preserves the structure of table.It doesn't deletes the data row by row hence faster than DELETE command, while DELETE is a DML command and it deletes the entire data based on specified condition else deletes the entire data,also it deletes the data row by row hence slower than TRUNCATE command.
5️⃣ What is difference between 'View' and 'Stored Procedure'?
◼A View is a virtual table that gets data from the base table .It is basically a Select statement,while Stored Procedure is a sql statement or set of sql statement stored on database server.
6️⃣ What is difference between a Common Table Expression and temporary table?
◼CTE is a temporary result set that is defined within execution scope of a single SELECT ,DELETE,UPDATE statement while temporary table is stored in TempDB and gets deleted once the session expires.
7️⃣ Differentiate between a clustered index and a non-clustered index?
◼ A clustered index determines physical ordering of data in a table and a table can have only one clustered index while a non-clustered index is analogous to index of a book where index is stored at one place and data at other place and index will have pointers to storage location of the data,a table can have more than one non-clustered index.
8️⃣ Explain triggers ?
◼They are sql codes which are automatically executed in response to certain events on a table.They are used to maintain integrity of data.
1️⃣ What is difference between Primary key and Unique key?
◼Primary key- A column or set of columns which uniquely identifies each record in a table. It can't contain null values and only one primary key
can exist in a table.
◼Unique key-Similar to primary key it also uniquely identifies each record in a table and can contain null values.Multiple Unique key can exist in a table.
2️⃣ What is a Candidate key?
◼A key or set of keys that uniquely identifies each record in a table.It is a combination of Primary and Alternate key.
3️⃣ What is a Constraint?
◼Specific rule or limit that we define in our table. E.g - NOT NULL,AUTO INCREMENT
4️⃣ Can you differentiate between TRUNCATE and DELETE?
◼TRUNCATE is a DDL command. It deletes the entire data from a table but preserves the structure of table.It doesn't deletes the data row by row hence faster than DELETE command, while DELETE is a DML command and it deletes the entire data based on specified condition else deletes the entire data,also it deletes the data row by row hence slower than TRUNCATE command.
5️⃣ What is difference between 'View' and 'Stored Procedure'?
◼A View is a virtual table that gets data from the base table .It is basically a Select statement,while Stored Procedure is a sql statement or set of sql statement stored on database server.
6️⃣ What is difference between a Common Table Expression and temporary table?
◼CTE is a temporary result set that is defined within execution scope of a single SELECT ,DELETE,UPDATE statement while temporary table is stored in TempDB and gets deleted once the session expires.
7️⃣ Differentiate between a clustered index and a non-clustered index?
◼ A clustered index determines physical ordering of data in a table and a table can have only one clustered index while a non-clustered index is analogous to index of a book where index is stored at one place and data at other place and index will have pointers to storage location of the data,a table can have more than one non-clustered index.
8️⃣ Explain triggers ?
◼They are sql codes which are automatically executed in response to certain events on a table.They are used to maintain integrity of data.
👍4🎉1
Preparing for a SQL interview?
Focus on mastering these essential topics:
1. Joins: Get comfortable with inner, left, right, and outer joins.
Knowing when to use what kind of join is important!
2. Window Functions: Understand when to use
ROW_NUMBER, RANK(), DENSE_RANK(), LAG, and LEAD for complex analytical queries.
3. Query Execution Order: Know the sequence from FROM to
ORDER BY. This is crucial for writing efficient, error-free queries.
4. Common Table Expressions (CTEs): Use CTEs to simplify and structure complex queries for better readability.
5. Aggregations & Window Functions: Combine aggregate functions with window functions for in-depth data analysis.
6. Subqueries: Learn how to use subqueries effectively within main SQL statements for complex data manipulations.
7. Handling NULLs: Be adept at managing NULL values to ensure accurate data processing and avoid potential pitfalls.
8. Indexing: Understand how proper indexing can significantly boost query performance.
9. GROUP BY & HAVING: Master grouping data and filtering groups with HAVING to refine your query results.
10. String Manipulation Functions: Get familiar with string functions like CONCAT, SUBSTRING, and REPLACE to handle text data efficiently.
11. Set Operations: Know how to use UNION, INTERSECT, and EXCEPT to combine or compare result sets.
12. Optimizing Queries: Learn techniques to optimize your queries for performance, especially with large datasets.
If we master/ Practice in these topics we can track any SQL interviews..
Like this post if you need more 👍❤️
Hope it helps :)
Focus on mastering these essential topics:
1. Joins: Get comfortable with inner, left, right, and outer joins.
Knowing when to use what kind of join is important!
2. Window Functions: Understand when to use
ROW_NUMBER, RANK(), DENSE_RANK(), LAG, and LEAD for complex analytical queries.
3. Query Execution Order: Know the sequence from FROM to
ORDER BY. This is crucial for writing efficient, error-free queries.
4. Common Table Expressions (CTEs): Use CTEs to simplify and structure complex queries for better readability.
5. Aggregations & Window Functions: Combine aggregate functions with window functions for in-depth data analysis.
6. Subqueries: Learn how to use subqueries effectively within main SQL statements for complex data manipulations.
7. Handling NULLs: Be adept at managing NULL values to ensure accurate data processing and avoid potential pitfalls.
8. Indexing: Understand how proper indexing can significantly boost query performance.
9. GROUP BY & HAVING: Master grouping data and filtering groups with HAVING to refine your query results.
10. String Manipulation Functions: Get familiar with string functions like CONCAT, SUBSTRING, and REPLACE to handle text data efficiently.
11. Set Operations: Know how to use UNION, INTERSECT, and EXCEPT to combine or compare result sets.
12. Optimizing Queries: Learn techniques to optimize your queries for performance, especially with large datasets.
If we master/ Practice in these topics we can track any SQL interviews..
Like this post if you need more 👍❤️
Hope it helps :)
❤4👍2👏1🎉1
35 Most Common SQL Interview Questions 👇👇
1.) Explain order of execution of SQL.
2.) What is difference between where and having?
3.) What is the use of group by?
4.) Explain all types of joins in SQL?
5.) What are triggers in SQL?
6.) What is stored procedure in SQL
7.) Explain all types of window functions?
(Mainly rank, row_num, dense_rank, lead & lag)
8.) What is difference between Delete and Truncate?
9.) What is difference between DML, DDL and DCL?
10.) What are aggregate function and when do we use them? explain with few example.
11.) Which is faster between CTE and Subquery?
12.) What are constraints and types of Constraints?
13.) Types of Keys?
14.) Different types of Operators ?
15.) Difference between Group By and Where?
16.) What are Views?
17.) What are different types of constraints?
18.) What is difference between varchar and nvarchar?
19.) Similar for char and nchar?
20.) What are index and their types?
21.) What is an index? Explain its different types.
22.) List the different types of relationships in SQL.
23.) Differentiate between UNION and UNION ALL.
24.) How many types of clauses in SQL?
25.) What is the difference between UNION and UNION ALL in SQL?
26.) What are the various types of relationships in SQL?
27.) Difference between Primary Key and Secondary Key?
28.) What is the difference between where and having?
29.) Find the second highest salary of an employee?
30.) Write retention query in SQL?
31.) Write year-on-year growth in SQL?
32.) Write a query for cummulative sum in SQL?
33.) Difference between Function and Store procedure ?
34.) Do we use variable in views?
35.) What are the limitations of views?
React with ❤️ for the detailed answers
1.) Explain order of execution of SQL.
2.) What is difference between where and having?
3.) What is the use of group by?
4.) Explain all types of joins in SQL?
5.) What are triggers in SQL?
6.) What is stored procedure in SQL
7.) Explain all types of window functions?
(Mainly rank, row_num, dense_rank, lead & lag)
8.) What is difference between Delete and Truncate?
9.) What is difference between DML, DDL and DCL?
10.) What are aggregate function and when do we use them? explain with few example.
11.) Which is faster between CTE and Subquery?
12.) What are constraints and types of Constraints?
13.) Types of Keys?
14.) Different types of Operators ?
15.) Difference between Group By and Where?
16.) What are Views?
17.) What are different types of constraints?
18.) What is difference between varchar and nvarchar?
19.) Similar for char and nchar?
20.) What are index and their types?
21.) What is an index? Explain its different types.
22.) List the different types of relationships in SQL.
23.) Differentiate between UNION and UNION ALL.
24.) How many types of clauses in SQL?
25.) What is the difference between UNION and UNION ALL in SQL?
26.) What are the various types of relationships in SQL?
27.) Difference between Primary Key and Secondary Key?
28.) What is the difference between where and having?
29.) Find the second highest salary of an employee?
30.) Write retention query in SQL?
31.) Write year-on-year growth in SQL?
32.) Write a query for cummulative sum in SQL?
33.) Difference between Function and Store procedure ?
34.) Do we use variable in views?
35.) What are the limitations of views?
React with ❤️ for the detailed answers
❤2👍2🎉1
35 Important SQL Interview Questions with Detailed Answers:
1. Explain order of execution of SQL.
Order: FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. SQL queries are processed in this logical sequence, not the way they are written.
2. What is difference between WHERE and HAVING?
WHERE filters rows before aggregation, while HAVING filters groups after aggregation.
3. What is the use of GROUP BY?
GROUP BY aggregates data across rows with the same values in specified columns, commonly used with aggregate functions.
4. Explain all types of joins in SQL?
INNER JOIN: Returns matching rows from both tables.
LEFT JOIN: All rows from the left, matched rows from right.
RIGHT JOIN: All rows from the right, matched rows from left.
FULL JOIN: All rows from both, with NULLs where no match.
SELF JOIN: Joins table to itself.
CROSS JOIN: Cartesian product of both tables.
5. What are triggers in SQL?
Triggers are procedural code executed automatically in response to certain events on a table or view (INSERT, UPDATE, DELETE).
6. What is stored procedure in SQL?
A stored procedure is a set of SQL statements saved and executed on demand, useful for modularizing code.
7. Explain all types of window functions?
RANK(): Gives rank with gaps.
DENSE_RANK(): Ranks without gaps.
ROW_NUMBER(): Unique row index.
LEAD(): Access next row.
LAG(): Access previous row.
8. What is difference between DELETE and TRUNCATE?
DELETE: Row-wise deletion, can have WHERE clause, logs each row.
TRUNCATE: Deletes all rows, faster, minimal logging, cannot rollback easily.
9. What is difference between DML, DDL and DCL?
DML: Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE).
DDL: Data Definition Language (CREATE, ALTER, DROP).
DCL: Data Control Language (GRANT, REVOKE).
10. What are aggregate functions?
Functions that return a single value: SUM(), AVG(), COUNT(), MIN(), MAX().
11. Which is faster: CTE or Subquery?
Performance depends on context, but subqueries are sometimes faster as CTEs may be materialized.
12. What are constraints and types?
Rules to maintain data integrity. Types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
13. Types of Keys?
Primary Key
Foreign Key
Unique Key
Composite Key
Candidate Key
14. Different types of Operators?
Arithmetic: +, -, *, /
Comparison: =, <>, >, <, >=, <=
Logical: AND, OR, NOT
Bitwise, LIKE, IN, BETWEEN
15. Difference between GROUP BY and WHERE?
WHERE filters before aggregation. GROUP BY groups after filtering.
16. What are Views?
Virtual tables based on SQL queries. They store only query definition.
17. What are different types of constraints?
Same as Q12: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
18. What is difference between VARCHAR and NVARCHAR?
VARCHAR: ASCII, 1 byte per char.
NVARCHAR: Unicode, 2 bytes per char, supports multiple languages.
19. Similarity for CHAR and NCHAR?
CHAR: Fixed-length ASCII.
NCHAR: Fixed-length Unicode.
20. What are indexes and their types?
Used for faster retrieval.
Types:
- Clustered
- Non-clustered
- Unique
- Composite
- Full-text
21. What is an index? Explain its types.
Same as above. Indexes speed up queries by creating pointers to data.
22. List different types of relationships in SQL.
One-to-One
One-to-Many
Many-to-Many
23. Differentiate between UNION and UNION ALL.
UNION: Removes duplicates.
UNION ALL: Includes duplicates.
24. How many types of clauses in SQL?
Common clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET, JOIN, ON.
25. What is the difference between UNION and UNION ALL in SQL?
Same as Q23.
26. What are various types of relationships in SQL?
Same as Q22.
27. Difference between Primary Key and Secondary Key?
Primary Key: Uniquely identifies rows.
Secondary Key: May not be unique, used for lookup.
Credits: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1000
1. Explain order of execution of SQL.
Order: FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. SQL queries are processed in this logical sequence, not the way they are written.
2. What is difference between WHERE and HAVING?
WHERE filters rows before aggregation, while HAVING filters groups after aggregation.
3. What is the use of GROUP BY?
GROUP BY aggregates data across rows with the same values in specified columns, commonly used with aggregate functions.
4. Explain all types of joins in SQL?
INNER JOIN: Returns matching rows from both tables.
LEFT JOIN: All rows from the left, matched rows from right.
RIGHT JOIN: All rows from the right, matched rows from left.
FULL JOIN: All rows from both, with NULLs where no match.
SELF JOIN: Joins table to itself.
CROSS JOIN: Cartesian product of both tables.
5. What are triggers in SQL?
Triggers are procedural code executed automatically in response to certain events on a table or view (INSERT, UPDATE, DELETE).
6. What is stored procedure in SQL?
A stored procedure is a set of SQL statements saved and executed on demand, useful for modularizing code.
7. Explain all types of window functions?
RANK(): Gives rank with gaps.
DENSE_RANK(): Ranks without gaps.
ROW_NUMBER(): Unique row index.
LEAD(): Access next row.
LAG(): Access previous row.
8. What is difference between DELETE and TRUNCATE?
DELETE: Row-wise deletion, can have WHERE clause, logs each row.
TRUNCATE: Deletes all rows, faster, minimal logging, cannot rollback easily.
9. What is difference between DML, DDL and DCL?
DML: Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE).
DDL: Data Definition Language (CREATE, ALTER, DROP).
DCL: Data Control Language (GRANT, REVOKE).
10. What are aggregate functions?
Functions that return a single value: SUM(), AVG(), COUNT(), MIN(), MAX().
11. Which is faster: CTE or Subquery?
Performance depends on context, but subqueries are sometimes faster as CTEs may be materialized.
12. What are constraints and types?
Rules to maintain data integrity. Types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
13. Types of Keys?
Primary Key
Foreign Key
Unique Key
Composite Key
Candidate Key
14. Different types of Operators?
Arithmetic: +, -, *, /
Comparison: =, <>, >, <, >=, <=
Logical: AND, OR, NOT
Bitwise, LIKE, IN, BETWEEN
15. Difference between GROUP BY and WHERE?
WHERE filters before aggregation. GROUP BY groups after filtering.
16. What are Views?
Virtual tables based on SQL queries. They store only query definition.
17. What are different types of constraints?
Same as Q12: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
18. What is difference between VARCHAR and NVARCHAR?
VARCHAR: ASCII, 1 byte per char.
NVARCHAR: Unicode, 2 bytes per char, supports multiple languages.
19. Similarity for CHAR and NCHAR?
CHAR: Fixed-length ASCII.
NCHAR: Fixed-length Unicode.
20. What are indexes and their types?
Used for faster retrieval.
Types:
- Clustered
- Non-clustered
- Unique
- Composite
- Full-text
21. What is an index? Explain its types.
Same as above. Indexes speed up queries by creating pointers to data.
22. List different types of relationships in SQL.
One-to-One
One-to-Many
Many-to-Many
23. Differentiate between UNION and UNION ALL.
UNION: Removes duplicates.
UNION ALL: Includes duplicates.
24. How many types of clauses in SQL?
Common clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET, JOIN, ON.
25. What is the difference between UNION and UNION ALL in SQL?
Same as Q23.
26. What are various types of relationships in SQL?
Same as Q22.
27. Difference between Primary Key and Secondary Key?
Primary Key: Uniquely identifies rows.
Secondary Key: May not be unique, used for lookup.
Credits: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1000
❤8👍5🎉1
Here are some interview questions for both freshers and experienced applying for a data analyst #SQL
Analyst role:
#ForFreshers:
1. What is SQL, and why is it important in data analysis?
2. Explain the difference between a database and a table.
3. What are the basic SQL commands for data retrieval?
4. How do you retrieve all records from a table named "Employees"?
5. What is a primary key, and why is it important in a database?
6. What is a foreign key, and how is it used in SQL?
7. Describe the difference between SQL JOIN and SQL UNION.
8. How do you write a SQL query to find the second-highest salary in a table?
9. What is the purpose of the GROUP BY clause in SQL?
10. Can you explain the concept of normalization in SQL databases?
11. What are the common aggregate functions in SQL, and how are they used?
ForExperiencedCandidates:
1. Describe a scenario where you had to optimize a slow-running SQL query. How did you approach it?
2. Explain the differences between SQL Server, MySQL, and Oracle databases.
3. Can you describe the process of creating an index in a SQL database and its impact on query performance?
4. How do you handle data quality issues when performing data analysis with SQL?
5. What is a subquery, and when would you use it in SQL? Give an example of a complex SQL query you've written to extract specific insights from a database.
6. How do you handle NULL values in SQL, and what are the challenges associated with them?
7. Explain the ACID properties of a database and their importance.
8. What are stored procedures and triggers in SQL, and when would you use them?
9. Describe your experience with ETL (Extract, Transform, Load) processes using SQL.
10. Can you explain the concept of query optimization in SQL, and what techniques have you used for optimization?
Enjoy Learning 👍👍
Analyst role:
#ForFreshers:
1. What is SQL, and why is it important in data analysis?
2. Explain the difference between a database and a table.
3. What are the basic SQL commands for data retrieval?
4. How do you retrieve all records from a table named "Employees"?
5. What is a primary key, and why is it important in a database?
6. What is a foreign key, and how is it used in SQL?
7. Describe the difference between SQL JOIN and SQL UNION.
8. How do you write a SQL query to find the second-highest salary in a table?
9. What is the purpose of the GROUP BY clause in SQL?
10. Can you explain the concept of normalization in SQL databases?
11. What are the common aggregate functions in SQL, and how are they used?
ForExperiencedCandidates:
1. Describe a scenario where you had to optimize a slow-running SQL query. How did you approach it?
2. Explain the differences between SQL Server, MySQL, and Oracle databases.
3. Can you describe the process of creating an index in a SQL database and its impact on query performance?
4. How do you handle data quality issues when performing data analysis with SQL?
5. What is a subquery, and when would you use it in SQL? Give an example of a complex SQL query you've written to extract specific insights from a database.
6. How do you handle NULL values in SQL, and what are the challenges associated with them?
7. Explain the ACID properties of a database and their importance.
8. What are stored procedures and triggers in SQL, and when would you use them?
9. Describe your experience with ETL (Extract, Transform, Load) processes using SQL.
10. Can you explain the concept of query optimization in SQL, and what techniques have you used for optimization?
Enjoy Learning 👍👍
👍7❤1🎉1