SQL Quick Notes to refresh your concepts

If you are trying to transition into the data analytics domain and getting started with SQL, focus on the most useful concept that will help you solve the majority of the problems, and then try to learn the rest of the topics:

👉🏻 Basic Aggregation function:
1️⃣ AVG
2️⃣ COUNT
3️⃣ SUM
4️⃣ MIN
5️⃣ MAX

👉🏻 JOINS
1️⃣ Left
2️⃣ Inner
3️⃣ Self (Important, Practice questions on self join)

👉🏻 Windows Function (Important)
1️⃣ Learn how partitioning works
2️⃣ Learn the different use cases where Ranking/Numbering Functions are used? ( ROW_NUMBER,RANK, DENSE_RANK, NTILE)
3️⃣ Use Cases of LEAD & LAG functions
4️⃣ Use cases of Aggregate window functions

👉🏻 GROUP BY
👉🏻 WHERE vs HAVING
👉🏻 CASE STATEMENT
👉🏻 UNION vs Union ALL
👉🏻 LOGICAL OPERATORS

Other Commonly used functions:
👉🏻 IFNULL
👉🏻 COALESCE
👉🏻 ROUND
👉🏻 Working with Date Functions
1️⃣ EXTRACTING YEAR/MONTH/WEEK/DAY
2️⃣ Calculating date differences

👉🏻CTE
👉🏻Views & Triggers (optional)

Here is an amazing resources to learn & practice SQL: https://bit.ly/3FxxKPz

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL is one of the core languages used in data science, powering everything from quick data retrieval to complex deep dive analysis. Whether you're a seasoned data scientist or just starting out, mastering SQL can boost your ability to analyze data, create robust pipelines, and deliver actionable insights.

Let’s dive into a comprehensive guide on SQL for Data Science!

I have broken it down into three key sections to help you:

𝟭. 𝗦𝗤𝗟 𝗖𝗼𝗻𝗰𝗲𝗽𝘁𝘀:
Get a handle on the essentials -> SELECT statements, filtering, aggregations, joins, window functions, and more.

𝟮. 𝗦𝗤𝗟 𝗶𝗻 𝗗𝗮𝘆-𝘁𝗼-𝗗𝗮𝘆 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲:
See how SQL fits into the daily data science workflow. From quick data queries and deep-dive analysis to building pipelines and dashboards, SQL is really useful for data scientists, especially for product data scientists.

𝟯. 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗦𝗤𝗟 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝘀:
Learn what interviewers look for in terms of technical skills, design and engineering expertise, communication abilities, and the importance of speed and accuracy.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

#sql

Most Asked SQL Interview Questions at MAANG Companies🔥🔥

Preparing for an SQL Interview at MAANG Companies? Here are some crucial SQL Questions you should be ready to tackle:

1. How do you retrieve all columns from a table?

SELECT * FROM table_name;

2. What SQL statement is used to filter records?

SELECT * FROM table_name
WHERE condition;

The WHERE clause is used to filter records based on a specified condition.

3. How can you join multiple tables? Describe different types of JOINs.

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;

Types of JOINs:

1. INNER JOIN: Returns records with matching values in both tables

SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;

2. LEFT JOIN: Returns all records from the left table & matched records from the right table. Unmatched records will have NULL values.

SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

3. RIGHT JOIN: Returns all records from the right table & matched records from the left table. Unmatched records will have NULL values.

SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

4. FULL JOIN: Returns records when there is a match in either left or right table. Unmatched records will have NULL values.

SELECT * FROM table1
FULL JOIN table2 ON table1.column = table2.column;

4. What is the difference between WHERE & HAVING clauses?

WHERE: Filters records before any groupings are made.

SELECT * FROM table_name
WHERE condition;

HAVING: Filters records after groupings are made.

SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > value;

5. How do you calculate average, sum, minimum & maximum values in a column?

Average: SELECT AVG(column_name) FROM table_name;

Sum: SELECT SUM(column_name) FROM table_name;

Minimum: SELECT MIN(column_name) FROM table_name;

Maximum: SELECT MAX(column_name) FROM table_name;

Here you can find essential SQL Interview Resources👇
https://t.iss.one/mysqldata

Like this post if you need more 👍❤️

Hope it helps :)

We have the Key to unlock AI-Powered Data Skills!

We have got some news for College grads & pros:

Level up with PW Skills' Data Analytics & Data Science with Gen AI course!

✅ Real-world projects
✅ Professional instructors
✅ Flexible learning
✅ Job Assistance

Ready for a data career boost? ➡️
Click Here for Data Science with Generative AI Course:

https://shorturl.at/j4lTD

Click Here for Data Analytics Course:
https://shorturl.at/7nrE5

Quick Recap of SQL Concepts

1️⃣ FROM clause: Specifies the tables from which data will be retrieved.
2️⃣ WHERE clause: Filters rows based on specified conditions.
3️⃣ GROUP BY clause: Groups rows that have the same values into summary rows.
4️⃣ HAVING clause: Filters groups based on specified conditions.
5️⃣ SELECT clause: Specifies the columns to be retrieved.
6️⃣ WINDOW functions: Functions that perform calculations across a set of table rows.
7️⃣ AGGREGATE functions: Functions like COUNT, SUM, AVG that perform calculations on a set of values.
8️⃣ UNION / UNION ALL: Combines the result sets of multiple SELECT statements.
9️⃣ ORDER BY clause: Sorts the result set based on specified columns.
🔟 LIMIT / OFFSET (or FETCH / OFFSET in some databases): Controls the number of rows returned and starting point for retrieval.

SQL Essentials for Data Analysts

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.

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

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

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

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 :)

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 ❤️

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

Answers posted on our WhatsApp channel: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/966

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 😊

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

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.

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 :)

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