In a LEFT JOIN, why do unmatched rows show NULL values?
Anonymous Quiz
4%
A. Because data is deleted
5%
B. Because INNER JOIN is used
78%
C. Because matching rows donβt exist in the right table
14%
D. Because NULL is a default value
β€3
SQL Interview Questions for 0-1 year of Experience (Asked in Top Product-Based Companies).
Sharpen your SQL skills with these real interview questions!
Q1. Customer Purchase Patterns -
You have two tables, Customers and Purchases: CREATE TABLE Customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(255) ); CREATE TABLE Purchases ( purchase_id INT PRIMARY KEY, customer_id INT, product_id INT, purchase_date DATE );
Assume necessary INSERT statements are already executed.
Write an SQL query to find the names of customers who have purchased more than 5 different products within the last month. Order the result by customer_name.
Q2. Call Log Analysis -
Suppose you have a CallLogs table: CREATE TABLE CallLogs ( log_id INT PRIMARY KEY, caller_id INT, receiver_id INT, call_start_time TIMESTAMP, call_end_time TIMESTAMP );
Assume necessary INSERT statements are already executed.
Write a query to find the average call duration per user. Include only users who have made more than 10 calls in total. Order the result by average duration descending.
Q3. Employee Project Allocation - Consider two tables, Employees and Projects:
CREATE TABLE Employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(255), department VARCHAR(255) ); CREATE TABLE Projects ( project_id INT PRIMARY KEY, lead_employee_id INT, project_name VARCHAR(255), start_date DATE, end_date DATE );
Assume necessary INSERT statements are already executed.
The goal is to write an SQL query to find the names of employees who have led more than 3 projects in the last year. The result should be ordered by the number of projects led.
Sharpen your SQL skills with these real interview questions!
Q1. Customer Purchase Patterns -
You have two tables, Customers and Purchases: CREATE TABLE Customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(255) ); CREATE TABLE Purchases ( purchase_id INT PRIMARY KEY, customer_id INT, product_id INT, purchase_date DATE );
Assume necessary INSERT statements are already executed.
Write an SQL query to find the names of customers who have purchased more than 5 different products within the last month. Order the result by customer_name.
Q2. Call Log Analysis -
Suppose you have a CallLogs table: CREATE TABLE CallLogs ( log_id INT PRIMARY KEY, caller_id INT, receiver_id INT, call_start_time TIMESTAMP, call_end_time TIMESTAMP );
Assume necessary INSERT statements are already executed.
Write a query to find the average call duration per user. Include only users who have made more than 10 calls in total. Order the result by average duration descending.
Q3. Employee Project Allocation - Consider two tables, Employees and Projects:
CREATE TABLE Employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(255), department VARCHAR(255) ); CREATE TABLE Projects ( project_id INT PRIMARY KEY, lead_employee_id INT, project_name VARCHAR(255), start_date DATE, end_date DATE );
Assume necessary INSERT statements are already executed.
The goal is to write an SQL query to find the names of employees who have led more than 3 projects in the last year. The result should be ordered by the number of projects led.
β€4
SQL Interview Trap π¨ Consecutive Orders Logic
You have a table:
orders
order_id | customer_id | order_date | amount
π Question:
Find customers who placed orders on 3 or more consecutive days,
but return only the first date of each such streak per customer.
β οΈ No temp tables.
β οΈ Assume multiple orders per day are possible.
π§ Most candidates fail because they:
- Forget to handle multiple orders on the same day
- Misuse ROW_NUMBER()
- Miss the date gap logic
β Correct SQL Solution:
WITH distinct_orders AS (
SELECT DISTINCT customer_id, order_date
FROM orders
),
grp AS (
SELECT
customer_id,
order_date,
order_date - INTERVAL '1 day' *
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS grp_id
FROM distinct_orders
)
SELECT
customer_id,
MIN(order_date) AS streak_start_date
FROM grp
GROUP BY customer_id, grp_id
HAVING COUNT(*) >= 3;
π‘ Why this works (Interview Gold):
- DISTINCT removes same-day duplicates
- ROW_NUMBER() creates a sequence
- Date minus row number groups consecutive dates
- HAVING COUNT(*) >= 3 filters valid streaks
π₯ React with π₯ if this bent your brain
π Follow the channel for REAL interview-level SQL Content
You have a table:
orders
order_id | customer_id | order_date | amount
π Question:
Find customers who placed orders on 3 or more consecutive days,
but return only the first date of each such streak per customer.
β οΈ No temp tables.
β οΈ Assume multiple orders per day are possible.
π§ Most candidates fail because they:
- Forget to handle multiple orders on the same day
- Misuse ROW_NUMBER()
- Miss the date gap logic
β Correct SQL Solution:
WITH distinct_orders AS (
SELECT DISTINCT customer_id, order_date
FROM orders
),
grp AS (
SELECT
customer_id,
order_date,
order_date - INTERVAL '1 day' *
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS grp_id
FROM distinct_orders
)
SELECT
customer_id,
MIN(order_date) AS streak_start_date
FROM grp
GROUP BY customer_id, grp_id
HAVING COUNT(*) >= 3;
π‘ Why this works (Interview Gold):
- DISTINCT removes same-day duplicates
- ROW_NUMBER() creates a sequence
- Date minus row number groups consecutive dates
- HAVING COUNT(*) >= 3 filters valid streaks
π₯ React with π₯ if this bent your brain
π Follow the channel for REAL interview-level SQL Content
1β€6
β
Essential Tools for Data Analytics ππ οΈ
π£ 1οΈβ£ Excel / Google Sheets
β’ Quick data entry & analysis
β’ Pivot tables, charts, functions
β’ Good for early-stage exploration
π» 2οΈβ£ SQL (Structured Query Language)
β’ Work with databases (MySQL, PostgreSQL, etc.)
β’ Query, filter, join, and aggregate data
β’ Must-know for data from large systems
π 3οΈβ£ Python (with Libraries)
β’ Pandas β Data manipulation
β’ NumPy β Numerical analysis
β’ Matplotlib / Seaborn β Data visualization
β’ OpenPyXL / xlrd β Work with Excel files
π 4οΈβ£ Power BI / Tableau
β’ Create dashboards and visual reports
β’ Drag-and-drop interface for non-coders
β’ Ideal for business insights & presentations
π 5οΈβ£ Google Data Studio
β’ Free dashboard tool
β’ Connects easily to Google Sheets, BigQuery
β’ Great for real-time reporting
π§ͺ 6οΈβ£ Jupyter Notebook
β’ Interactive Python coding
β’ Combine code, text, and visuals in one place
β’ Perfect for storytelling with data
π οΈ 7οΈβ£ R Programming (Optional)
β’ Popular in statistical analysis
β’ Strong in academic and research settings
βοΈ 8οΈβ£ Cloud & Big Data Tools
β’ Google BigQuery, Snowflake β Large-scale analysis
β’ Excel + SQL + Python still work as a base
π‘ Tip:
Start with Excel + SQL + Python (Pandas) β Add BI tools for reporting.
π¬ Tap β€οΈ for more!
π£ 1οΈβ£ Excel / Google Sheets
β’ Quick data entry & analysis
β’ Pivot tables, charts, functions
β’ Good for early-stage exploration
π» 2οΈβ£ SQL (Structured Query Language)
β’ Work with databases (MySQL, PostgreSQL, etc.)
β’ Query, filter, join, and aggregate data
β’ Must-know for data from large systems
π 3οΈβ£ Python (with Libraries)
β’ Pandas β Data manipulation
β’ NumPy β Numerical analysis
β’ Matplotlib / Seaborn β Data visualization
β’ OpenPyXL / xlrd β Work with Excel files
π 4οΈβ£ Power BI / Tableau
β’ Create dashboards and visual reports
β’ Drag-and-drop interface for non-coders
β’ Ideal for business insights & presentations
π 5οΈβ£ Google Data Studio
β’ Free dashboard tool
β’ Connects easily to Google Sheets, BigQuery
β’ Great for real-time reporting
π§ͺ 6οΈβ£ Jupyter Notebook
β’ Interactive Python coding
β’ Combine code, text, and visuals in one place
β’ Perfect for storytelling with data
π οΈ 7οΈβ£ R Programming (Optional)
β’ Popular in statistical analysis
β’ Strong in academic and research settings
βοΈ 8οΈβ£ Cloud & Big Data Tools
β’ Google BigQuery, Snowflake β Large-scale analysis
β’ Excel + SQL + Python still work as a base
π‘ Tip:
Start with Excel + SQL + Python (Pandas) β Add BI tools for reporting.
π¬ Tap β€οΈ for more!
β€4
π SQL Subqueries CTEs
1οΈβ£ What is a Subquery?
A subquery is a query inside another query. It runs first and passes its result to the outer query.
Think like this π
> βFirst find something β then use it to filter or calculate something elseβ
Why Subqueries exist (business thinking)
Real questions like:
β’ Find customers who spent more than average
β’ Find products with highest sales
β’ Find employees earning more than their manager
These need one queryβs result inside another query.
β Basic Subquery Structure
SELECT column
FROM text
WHERE column OPERATOR (
SELECT column
FROM text
);
Example Tables: orders
order_id | customer_id | amount
1 | 101 | 5000
2 | 102 | 8000
3 | 103 | 3000
2οΈβ£ Subquery in WHERE clause (Most Common)
πΉ Scenario: Find orders with amount greater than average order value
SELECT *
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
);
What this query does
1. Inner query calculates average order amount
2. Outer query keeps only orders above that average
β Very common interview question
3οΈβ£ Subquery with IN
πΉ Scenario: Find customers who have placed at least one order
Tables: customers(customer_id, name) orders(customer_id)
SELECT name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);
What this query does
β’ Inner query gets customers who ordered
β’ Outer query fetches their names
4οΈβ£ Subquery in SELECT clause
πΉ Scenario: Show each order with total number of orders
SELECT order_id, amount, (
SELECT COUNT(*)
FROM orders
) AS total_orders
FROM orders;
What this query does
β’ Inner query runs once
β’ Adds total order count to every row
β οΈ Use carefully β can be inefficient
5οΈβ£ Correlated Subquery (Important)
A correlated subquery depends on the outer query. It runs once per row.
πΉ Scenario: Find customers who spent more than their cityβs average
Tables: customers(customer_id, city) orders(customer_id, amount)
SELECT c.customer_id
FROM customers c
WHERE (
SELECT AVG(o.amount)
FROM orders o
WHERE o.customer_id = c.customer_id
) > 5000;
What this query does
β’ For each customer
β’ Calculates their average spend
β’ Filters based on condition
β οΈ Powerful but slower on large data
6οΈβ£ Problems with Subqueries
β Hard to read
β Hard to debug
β Performance issues
β Nested logic becomes messy
π Thatβs why CTEs exist
7οΈβ£ What is a CTE (Common Table Expression)?
A CTE is a named temporary result.
It makes complex queries readable and reusable.
CTE Syntax
WITH cte_name AS (
SELECT ...
)
SELECT *
FROM cte_name;
8οΈβ£ Same Problem Solved Using CTE (Cleaner)
πΉ Find customers with total spend > 10,000
WITH customer_spend AS (
SELECT customer_id, SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
)
SELECT *
FROM customer_spend
WHERE total_spend > 10000;
What this does
β’ First block calculates spend
β’ Second block filters results
β’ Very readable
9οΈβ£ CTE vs Subquery
β’ Readability: CTE is excellent, Subquery is poor
β’ Reusability: CTE is yes, Subquery is no
β’ Debugging: CTE is easy, Subquery is hard
β’ Performance: Both depend on usage
π When to Use What?
Use Subquery when:
β Logic is small
β Used only once
Use CTE when:
β Logic is complex
β Multiple steps
β Interview or production query
Common Beginner Mistakes
β Writing very deep nested subqueries
β Using correlated subqueries unnecessarily
β Forgetting CTE scope (only valid for one query)
Interview Tip π‘
> Subqueries solve problems inside queries, while CTEs solve readability and maintainability.
Double Tap β₯οΈ For More
1οΈβ£ What is a Subquery?
A subquery is a query inside another query. It runs first and passes its result to the outer query.
Think like this π
> βFirst find something β then use it to filter or calculate something elseβ
Why Subqueries exist (business thinking)
Real questions like:
β’ Find customers who spent more than average
β’ Find products with highest sales
β’ Find employees earning more than their manager
These need one queryβs result inside another query.
β Basic Subquery Structure
SELECT column
FROM text
WHERE column OPERATOR (
SELECT column
FROM text
);
Example Tables: orders
order_id | customer_id | amount
1 | 101 | 5000
2 | 102 | 8000
3 | 103 | 3000
2οΈβ£ Subquery in WHERE clause (Most Common)
πΉ Scenario: Find orders with amount greater than average order value
SELECT *
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
);
What this query does
1. Inner query calculates average order amount
2. Outer query keeps only orders above that average
β Very common interview question
3οΈβ£ Subquery with IN
πΉ Scenario: Find customers who have placed at least one order
Tables: customers(customer_id, name) orders(customer_id)
SELECT name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);
What this query does
β’ Inner query gets customers who ordered
β’ Outer query fetches their names
4οΈβ£ Subquery in SELECT clause
πΉ Scenario: Show each order with total number of orders
SELECT order_id, amount, (
SELECT COUNT(*)
FROM orders
) AS total_orders
FROM orders;
What this query does
β’ Inner query runs once
β’ Adds total order count to every row
β οΈ Use carefully β can be inefficient
5οΈβ£ Correlated Subquery (Important)
A correlated subquery depends on the outer query. It runs once per row.
πΉ Scenario: Find customers who spent more than their cityβs average
Tables: customers(customer_id, city) orders(customer_id, amount)
SELECT c.customer_id
FROM customers c
WHERE (
SELECT AVG(o.amount)
FROM orders o
WHERE o.customer_id = c.customer_id
) > 5000;
What this query does
β’ For each customer
β’ Calculates their average spend
β’ Filters based on condition
β οΈ Powerful but slower on large data
6οΈβ£ Problems with Subqueries
β Hard to read
β Hard to debug
β Performance issues
β Nested logic becomes messy
π Thatβs why CTEs exist
7οΈβ£ What is a CTE (Common Table Expression)?
A CTE is a named temporary result.
It makes complex queries readable and reusable.
CTE Syntax
WITH cte_name AS (
SELECT ...
)
SELECT *
FROM cte_name;
8οΈβ£ Same Problem Solved Using CTE (Cleaner)
πΉ Find customers with total spend > 10,000
WITH customer_spend AS (
SELECT customer_id, SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
)
SELECT *
FROM customer_spend
WHERE total_spend > 10000;
What this does
β’ First block calculates spend
β’ Second block filters results
β’ Very readable
9οΈβ£ CTE vs Subquery
β’ Readability: CTE is excellent, Subquery is poor
β’ Reusability: CTE is yes, Subquery is no
β’ Debugging: CTE is easy, Subquery is hard
β’ Performance: Both depend on usage
π When to Use What?
Use Subquery when:
β Logic is small
β Used only once
Use CTE when:
β Logic is complex
β Multiple steps
β Interview or production query
Common Beginner Mistakes
β Writing very deep nested subqueries
β Using correlated subqueries unnecessarily
β Forgetting CTE scope (only valid for one query)
Interview Tip π‘
> Subqueries solve problems inside queries, while CTEs solve readability and maintainability.
Double Tap β₯οΈ For More
β€4
π‘ 10 SQL Projects You Can Start Today (With Datasets)
1) E-commerce Deep Dive π
Brazilian orders, payments, reviews, deliveries β the full package.
https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce
2) Sales Performance Tracker π
Perfect for learning KPIs, revenue trends, and top products.
https://www.kaggle.com/datasets/kyanyoga/sample-sales-data
3) HR Analytics (Attrition + Employee Insights) π₯
Analyze why employees leave + build dashboards with SQL.
https://www.kaggle.com/datasets/pavansubhasht/ibm-hr-analytics-attrition-dataset
4) Banking + Financial Data π³
Great for segmentation, customer behavior, and risk analysis.
https://www.kaggle.com/datasets?tags=11129-Banking
5) Healthcare & Mortality Analysis π₯
Serious dataset for serious SQL practice (filters, joins, grouping).
https://www.kaggle.com/datasets/cdc/mortality
6) Marketing + Customer Value (CRM) π―
Customer lifetime value, retention, and segmentation projects.
https://www.kaggle.com/datasets/pankajjsh06/ibm-watson-marketing-customer-value-data
7) Supply Chain & Procurement Analytics π
Great for vendor performance + procurement cost tracking.
https://www.kaggle.com/datasets/shashwatwork/dataco-smart-supply-chain-for-big-data-analysis
8) Inventory Management π¦
Search and pick a dataset β tons of options here.
https://www.kaggle.com/datasets/fayez1/inventory-management
9) Web/Product Review Analytics βοΈ
Use SQL to analyze ratings, trends, and categories.
https://www.kaggle.com/datasets/zynicide/wine-reviews
10) Social Mediaβ Style Analytics (User Behavior / Health Trends) π
This one is more behavioral analytics than social media, but still great for SQL practice.
https://www.kaggle.com/datasets/aasheesh200/framingham-heart-study-dataset
1) E-commerce Deep Dive π
Brazilian orders, payments, reviews, deliveries β the full package.
https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce
2) Sales Performance Tracker π
Perfect for learning KPIs, revenue trends, and top products.
https://www.kaggle.com/datasets/kyanyoga/sample-sales-data
3) HR Analytics (Attrition + Employee Insights) π₯
Analyze why employees leave + build dashboards with SQL.
https://www.kaggle.com/datasets/pavansubhasht/ibm-hr-analytics-attrition-dataset
4) Banking + Financial Data π³
Great for segmentation, customer behavior, and risk analysis.
https://www.kaggle.com/datasets?tags=11129-Banking
5) Healthcare & Mortality Analysis π₯
Serious dataset for serious SQL practice (filters, joins, grouping).
https://www.kaggle.com/datasets/cdc/mortality
6) Marketing + Customer Value (CRM) π―
Customer lifetime value, retention, and segmentation projects.
https://www.kaggle.com/datasets/pankajjsh06/ibm-watson-marketing-customer-value-data
7) Supply Chain & Procurement Analytics π
Great for vendor performance + procurement cost tracking.
https://www.kaggle.com/datasets/shashwatwork/dataco-smart-supply-chain-for-big-data-analysis
8) Inventory Management π¦
Search and pick a dataset β tons of options here.
https://www.kaggle.com/datasets/fayez1/inventory-management
9) Web/Product Review Analytics βοΈ
Use SQL to analyze ratings, trends, and categories.
https://www.kaggle.com/datasets/zynicide/wine-reviews
10) Social Mediaβ Style Analytics (User Behavior / Health Trends) π
This one is more behavioral analytics than social media, but still great for SQL practice.
https://www.kaggle.com/datasets/aasheesh200/framingham-heart-study-dataset
Kaggle
Brazilian E-Commerce Public Dataset by Olist
100,000 Orders with product, customer and reviews info
β€5
β
15 Power BI Interview Questions for Freshers ππ»
1οΈβ£ What is Power BI and what is it used for?
Answer: Power BI is a business analytics tool by Microsoft to visualize data, create reports, and share insights across organizations.
2οΈβ£ What are the main components of Power BI?
Answer: Power BI Desktop, Power BI Service (Cloud), Power BI Mobile, Power BI Gateway, and Power BI Report Server.
3οΈβ£ What is a DAX in Power BI?
Answer: Data Analysis Expressions (DAX) is a formula language used to create custom calculations in Power BI.
4οΈβ£ What is the difference between a calculated column and a measure?
Answer: Calculated columns are row-level computations stored in the table. Measures are aggregations computed at query time.
5οΈβ£ What is the difference between Power BI Desktop and Power BI Service?
Answer: Desktop is for building reports and data modeling. Service is for publishing, sharing, and collaboration online.
6οΈβ£ What is a data model in Power BI?
Answer: A data model organizes tables, relationships, and calculations to efficiently analyze and visualize data.
7οΈβ£ What is the difference between DirectQuery and Import mode?
Answer: Import loads data into Power BI, faster for analysis. DirectQuery queries the source directly, no data is imported.
8οΈβ£ What are slicers in Power BI?
Answer: Visual filters that allow users to dynamically filter report data.
9οΈβ£ What is Power Query?
Answer: A data connection and transformation tool in Power BI used for cleaning and shaping data before loading.
1οΈβ£0οΈβ£ What is the difference between a table visual and a matrix visual?
Answer: Table displays data in simple rows and columns. Matrix allows grouping, row/column hierarchies, and aggregations.
1οΈβ£1οΈβ£ What is a Power BI dashboard?
Answer: A single-page collection of visualizations from multiple reports for quick insights.
1οΈβ£2οΈβ£ What is a relationship in Power BI?
Answer: Links between tables that define how data is connected for accurate aggregations and filtering.
1οΈβ£3οΈβ£ What are filters in Power BI?
Answer: Visual-level, page-level, or report-level filters to restrict data shown in reports.
1οΈβ£4οΈβ£ What is Power BI Gateway?
Answer: A bridge between on-premise data sources and Power BI Service for scheduled refreshes.
1οΈβ£5οΈβ£ What is the difference between a report and a dashboard?
Answer: Reports can have multiple pages and visuals; dashboards are single-page, with pinned visuals from reports.
Power BI Resources: https://whatsapp.com/channel/0029Vai1xKf1dAvuk6s1v22c
π¬ React with β€οΈ for more!
1οΈβ£ What is Power BI and what is it used for?
Answer: Power BI is a business analytics tool by Microsoft to visualize data, create reports, and share insights across organizations.
2οΈβ£ What are the main components of Power BI?
Answer: Power BI Desktop, Power BI Service (Cloud), Power BI Mobile, Power BI Gateway, and Power BI Report Server.
3οΈβ£ What is a DAX in Power BI?
Answer: Data Analysis Expressions (DAX) is a formula language used to create custom calculations in Power BI.
4οΈβ£ What is the difference between a calculated column and a measure?
Answer: Calculated columns are row-level computations stored in the table. Measures are aggregations computed at query time.
5οΈβ£ What is the difference between Power BI Desktop and Power BI Service?
Answer: Desktop is for building reports and data modeling. Service is for publishing, sharing, and collaboration online.
6οΈβ£ What is a data model in Power BI?
Answer: A data model organizes tables, relationships, and calculations to efficiently analyze and visualize data.
7οΈβ£ What is the difference between DirectQuery and Import mode?
Answer: Import loads data into Power BI, faster for analysis. DirectQuery queries the source directly, no data is imported.
8οΈβ£ What are slicers in Power BI?
Answer: Visual filters that allow users to dynamically filter report data.
9οΈβ£ What is Power Query?
Answer: A data connection and transformation tool in Power BI used for cleaning and shaping data before loading.
1οΈβ£0οΈβ£ What is the difference between a table visual and a matrix visual?
Answer: Table displays data in simple rows and columns. Matrix allows grouping, row/column hierarchies, and aggregations.
1οΈβ£1οΈβ£ What is a Power BI dashboard?
Answer: A single-page collection of visualizations from multiple reports for quick insights.
1οΈβ£2οΈβ£ What is a relationship in Power BI?
Answer: Links between tables that define how data is connected for accurate aggregations and filtering.
1οΈβ£3οΈβ£ What are filters in Power BI?
Answer: Visual-level, page-level, or report-level filters to restrict data shown in reports.
1οΈβ£4οΈβ£ What is Power BI Gateway?
Answer: A bridge between on-premise data sources and Power BI Service for scheduled refreshes.
1οΈβ£5οΈβ£ What is the difference between a report and a dashboard?
Answer: Reports can have multiple pages and visuals; dashboards are single-page, with pinned visuals from reports.
Power BI Resources: https://whatsapp.com/channel/0029Vai1xKf1dAvuk6s1v22c
π¬ React with β€οΈ for more!
β€2
Top 50 SQL Interview Questions
1. What is SQL?
2. Differentiate between SQL and NoSQL databases.
3. What are the different types of SQL commands?
4. Explain the difference between WHERE and HAVING clauses.
5. Write a SQL query to find the second highest salary in a table.
6. What is a JOIN? Explain different types of JOINs.
7. How do you optimize slow-performing SQL queries?
8. What is a primary key? What is a foreign key?
9. What are indexes? Explain clustered and non-clustered indexes.
10. Write a SQL query to fetch the top 5 records from a table.
11. What is a subquery? Give an example.
12. Explain the concept of normalization.
13. What is denormalization? When is it used?
14. Describe transactions and their properties (ACID).
15. What is a stored procedure?
16. How do you handle NULL values in SQL?
17. Explain the difference between UNION and UNION ALL.
18. What are views? How are they useful?
19. What is a trigger? Give use cases.
20. How do you perform aggregate functions in SQL?
21. What is data partitioning?
22. How do you find duplicates in a table?
23. What is the difference between DELETE and TRUNCATE?
24. Explain window functions with examples.
25. What is the difference between correlated and non-correlated subqueries?
26. How do you enforce data integrity?
27. What are CTEs (Common Table Expressions)?
28. Explain EXISTS and NOT EXISTS operators.
29. How do SQL constraints work?
30. What is an execution plan? How do you use it?
31. Describe how to handle errors in SQL.
32. What are temporary tables?
33. Explain the difference between CHAR and VARCHAR.
34. How do you perform pagination in SQL?
35. What is a composite key?
36. How do you convert data types in SQL?
37. Explain locking and isolation levels in SQL.
38. How do you write recursive queries?
39. What are the advantages of using prepared statements?
40. How to debug SQL queries?
41. Differentiate between OLTP and OLAP databases.
42. What is schema in SQL?
43. How do you implement many-to-many relationships in SQL?
44. What is query optimization?
45. How do you handle large datasets in SQL?
46. Explain the difference between CROSS JOIN and INNER JOIN.
47. What is a materialized view?
48. How do you backup and restore a database?
49. Explain how indexing can degrade performance.
50. Can you write a query to find employees with no managers?
Double tap β€οΈ for detailed answers!
1. What is SQL?
2. Differentiate between SQL and NoSQL databases.
3. What are the different types of SQL commands?
4. Explain the difference between WHERE and HAVING clauses.
5. Write a SQL query to find the second highest salary in a table.
6. What is a JOIN? Explain different types of JOINs.
7. How do you optimize slow-performing SQL queries?
8. What is a primary key? What is a foreign key?
9. What are indexes? Explain clustered and non-clustered indexes.
10. Write a SQL query to fetch the top 5 records from a table.
11. What is a subquery? Give an example.
12. Explain the concept of normalization.
13. What is denormalization? When is it used?
14. Describe transactions and their properties (ACID).
15. What is a stored procedure?
16. How do you handle NULL values in SQL?
17. Explain the difference between UNION and UNION ALL.
18. What are views? How are they useful?
19. What is a trigger? Give use cases.
20. How do you perform aggregate functions in SQL?
21. What is data partitioning?
22. How do you find duplicates in a table?
23. What is the difference between DELETE and TRUNCATE?
24. Explain window functions with examples.
25. What is the difference between correlated and non-correlated subqueries?
26. How do you enforce data integrity?
27. What are CTEs (Common Table Expressions)?
28. Explain EXISTS and NOT EXISTS operators.
29. How do SQL constraints work?
30. What is an execution plan? How do you use it?
31. Describe how to handle errors in SQL.
32. What are temporary tables?
33. Explain the difference between CHAR and VARCHAR.
34. How do you perform pagination in SQL?
35. What is a composite key?
36. How do you convert data types in SQL?
37. Explain locking and isolation levels in SQL.
38. How do you write recursive queries?
39. What are the advantages of using prepared statements?
40. How to debug SQL queries?
41. Differentiate between OLTP and OLAP databases.
42. What is schema in SQL?
43. How do you implement many-to-many relationships in SQL?
44. What is query optimization?
45. How do you handle large datasets in SQL?
46. Explain the difference between CROSS JOIN and INNER JOIN.
47. What is a materialized view?
48. How do you backup and restore a database?
49. Explain how indexing can degrade performance.
50. Can you write a query to find employees with no managers?
Double tap β€οΈ for detailed answers!
β€25π1
ππ»ππ²πΏππΆπ²ππ²πΏ: You have 2 minutes to solve this SQL query.
Retrieve the department name and the highest salary in each department from the employees table, but only for departments where the highest salary is greater than $70,000.
π π²: Challenge accepted!
SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 70000;
I used GROUP BY to group employees by department, MAX() to get the highest salary, and HAVING to filter the result based on the condition that the highest salary exceeds $70,000. This solution effectively shows my understanding of aggregation functions and how to apply conditions on the result of those aggregations.
π§πΆπ½ π³πΌπΏ π¦π€π ππΌπ― π¦π²π²πΈπ²πΏπ:
It's not about writing complex queries; it's about writing clean, efficient, and scalable code. Focus on mastering subqueries, joins, and aggregation functions to stand out!
React with β€οΈ for more
Retrieve the department name and the highest salary in each department from the employees table, but only for departments where the highest salary is greater than $70,000.
π π²: Challenge accepted!
SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 70000;
I used GROUP BY to group employees by department, MAX() to get the highest salary, and HAVING to filter the result based on the condition that the highest salary exceeds $70,000. This solution effectively shows my understanding of aggregation functions and how to apply conditions on the result of those aggregations.
π§πΆπ½ π³πΌπΏ π¦π€π ππΌπ― π¦π²π²πΈπ²πΏπ:
It's not about writing complex queries; it's about writing clean, efficient, and scalable code. Focus on mastering subqueries, joins, and aggregation functions to stand out!
React with β€οΈ for more
β€10
If I need to teach someone data analytics from the basics, here is my strategy:
1. I will first remove the fear of tools from that person
2. i will start with the excel because it looks familiar and easy to use
3. I put more emphasis on projects like at least 5 to 6 with the excel. because in industry you learn by doing things
4. I will release the person from the tutorial hell and move into a more action oriented person
5. Then I move to the sql because every job wants it , even with the ai tools you need strong understanding for it if you are going to use it daily
6. After strong understanding, I will push the person to solve 100 to 150 Sql problems from basic to advance
7. It helps the person to develop the analytical thinking
8. Then I push the person to solve 3 case studies as it helps how we pull the data in the real life
9. Then I move the person to power bi to do again 5 projects by using either sql or excel files
10. Now the fear is removed.
11. Now I push the person to solve unguided challenges and present them by video recording as it increases the problem solving, communication and data story telling skills
12. Further it helps you to clear case study round given by most of the companies
13. Now i help the person how to present them in resume and also how these tools are used in real world.
14. You know the interesting fact, all of above is present free in youtube and I also mentor the people through existing youtube videos.
15. But people stuck in the tutorial hell, loose motivation , stay confused that they are either in the right direction or not.
16. As a personal mentor , I help them to get of the tutorial hell, set them in the right direction and they stay motivated when they start to see the difference before amd after mentorship
I have curated best 80+ top-notch Data Analytics Resources ππ
https://topmate.io/analyst/861634
Hope this helps you π
1. I will first remove the fear of tools from that person
2. i will start with the excel because it looks familiar and easy to use
3. I put more emphasis on projects like at least 5 to 6 with the excel. because in industry you learn by doing things
4. I will release the person from the tutorial hell and move into a more action oriented person
5. Then I move to the sql because every job wants it , even with the ai tools you need strong understanding for it if you are going to use it daily
6. After strong understanding, I will push the person to solve 100 to 150 Sql problems from basic to advance
7. It helps the person to develop the analytical thinking
8. Then I push the person to solve 3 case studies as it helps how we pull the data in the real life
9. Then I move the person to power bi to do again 5 projects by using either sql or excel files
10. Now the fear is removed.
11. Now I push the person to solve unguided challenges and present them by video recording as it increases the problem solving, communication and data story telling skills
12. Further it helps you to clear case study round given by most of the companies
13. Now i help the person how to present them in resume and also how these tools are used in real world.
14. You know the interesting fact, all of above is present free in youtube and I also mentor the people through existing youtube videos.
15. But people stuck in the tutorial hell, loose motivation , stay confused that they are either in the right direction or not.
16. As a personal mentor , I help them to get of the tutorial hell, set them in the right direction and they stay motivated when they start to see the difference before amd after mentorship
I have curated best 80+ top-notch Data Analytics Resources ππ
https://topmate.io/analyst/861634
Hope this helps you π
β€9
β
SQL Subquery Practice Questions with Answers
π Q1. Retrieve employees whose salary is greater than the companyβs average salary.
ποΈ Table: employees(emp_id, name, salary)
β Answer:
---
π Q2. Identify customers who have placed more than three orders.
ποΈ Table: orders(order_id, customer_id, order_date)
β Answer:
---
π Q3. Display employees working in departments where the average salary exceeds 60,000.
ποΈ Table: employees(emp_id, name, department_id, salary)
β Answer:
---
π Q4. Show products that have never been ordered.
ποΈ Tables: products(product_id, product_name), orders(order_id, product_id)
β Answer:
(Alternative safe approach to handle NULLs in orders)
---
π Q5. Fetch employee(s) receiving the maximum salary in the organization.
ποΈ Table: employees(emp_id, name, salary)
β Answer:
Double Tap β₯οΈ For More
π Q1. Retrieve employees whose salary is greater than the companyβs average salary.
ποΈ Table: employees(emp_id, name, salary)
β Answer:
SELECT emp_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
---
π Q2. Identify customers who have placed more than three orders.
ποΈ Table: orders(order_id, customer_id, order_date)
β Answer:
SELECT customer_id
FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 3
);
---
π Q3. Display employees working in departments where the average salary exceeds 60,000.
ποΈ Table: employees(emp_id, name, department_id, salary)
β Answer:
SELECT emp_id, name, department_id
FROM employees e
WHERE department_id IN (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000
);
---
π Q4. Show products that have never been ordered.
ποΈ Tables: products(product_id, product_name), orders(order_id, product_id)
β Answer:
SELECT product_id, product_name
FROM products
WHERE product_id NOT IN (SELECT product_id FROM orders);
(Alternative safe approach to handle NULLs in orders)
SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
WHERE o.product_id IS NULL;
---
π Q5. Fetch employee(s) receiving the maximum salary in the organization.
ποΈ Table: employees(emp_id, name, salary)
β Answer:
SELECT emp_id, name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
Double Tap β₯οΈ For More
β€12
Master SQL step-by-step! From basics to advanced, here are the key topics you need for a solid SQL foundation. π
1. Foundations:
- Learn basic SQL syntax, including SELECT, FROM, WHERE clauses.
- Understand data types, constraints, and the basic structure of a database.
2. Database Design:
- Study database normalization to ensure efficient data organization.
- Learn about primary keys, foreign keys, and relationships between tables.
3. Queries and Joins:
- Practice writing simple to complex SELECT queries.
- Master different types of joins (INNER, LEFT, RIGHT, FULL) to combine data from multiple tables.
4. Aggregation and Grouping:
- Explore aggregate functions like COUNT, SUM, AVG, MAX, and MIN.
- Understand GROUP BY clause for summarizing data based on specific criteria.
5. Subqueries and Nested Queries:
- Learn how to use subqueries to perform operations within another query.
- Understand the concept of nested queries and their practical applications.
6. Indexing and Optimization:
- Study indexing for enhancing query performance.
- Learn optimization techniques, such as avoiding SELECT * and using appropriate indexes.
7. Transactions and ACID Properties:
- Understand the basics of transactions and their role in maintaining data integrity.
- Explore ACID properties (Atomicity, Consistency, Isolation, Durability) in database management.
8. Views and Stored Procedures:
- Create and use views to simplify complex queries.
- Learn about stored procedures for reusable and efficient query execution.
9. Security and Permissions:
- Understand SQL injection risks and how to prevent them.
- Learn how to manage user permissions and access control.
10. Advanced Topics:
- Explore advanced SQL concepts like window functions, CTEs (Common Table Expressions), and recursive queries.
- Familiarize yourself with database-specific features (e.g., PostgreSQL's JSON functions, MySQL's spatial data types).
11. Real-world Projects:
- Apply your knowledge to real-world scenarios by working on projects.
- Practice with sample databases or create your own to reinforce your skills.
12. Continuous Learning:
- Stay updated on SQL advancements and industry best practices.
- Engage with online communities, forums, and resources for ongoing learning and problem-solving.
Here are some free resources to learn & practice SQL ππ
SQL For Data Analysis: https://t.iss.one/sqlanalyst
For Practice- https://stratascratch.com/?via=free
SQL Learning Series: https://t.iss.one/sqlspecialist/567
Top 10 SQL Projects with Datasets: https://t.iss.one/DataPortfolio/16
Join for more free resources: https://t.iss.one/free4unow_backup
ENJOY LEARNING ππ
1. Foundations:
- Learn basic SQL syntax, including SELECT, FROM, WHERE clauses.
- Understand data types, constraints, and the basic structure of a database.
2. Database Design:
- Study database normalization to ensure efficient data organization.
- Learn about primary keys, foreign keys, and relationships between tables.
3. Queries and Joins:
- Practice writing simple to complex SELECT queries.
- Master different types of joins (INNER, LEFT, RIGHT, FULL) to combine data from multiple tables.
4. Aggregation and Grouping:
- Explore aggregate functions like COUNT, SUM, AVG, MAX, and MIN.
- Understand GROUP BY clause for summarizing data based on specific criteria.
5. Subqueries and Nested Queries:
- Learn how to use subqueries to perform operations within another query.
- Understand the concept of nested queries and their practical applications.
6. Indexing and Optimization:
- Study indexing for enhancing query performance.
- Learn optimization techniques, such as avoiding SELECT * and using appropriate indexes.
7. Transactions and ACID Properties:
- Understand the basics of transactions and their role in maintaining data integrity.
- Explore ACID properties (Atomicity, Consistency, Isolation, Durability) in database management.
8. Views and Stored Procedures:
- Create and use views to simplify complex queries.
- Learn about stored procedures for reusable and efficient query execution.
9. Security and Permissions:
- Understand SQL injection risks and how to prevent them.
- Learn how to manage user permissions and access control.
10. Advanced Topics:
- Explore advanced SQL concepts like window functions, CTEs (Common Table Expressions), and recursive queries.
- Familiarize yourself with database-specific features (e.g., PostgreSQL's JSON functions, MySQL's spatial data types).
11. Real-world Projects:
- Apply your knowledge to real-world scenarios by working on projects.
- Practice with sample databases or create your own to reinforce your skills.
12. Continuous Learning:
- Stay updated on SQL advancements and industry best practices.
- Engage with online communities, forums, and resources for ongoing learning and problem-solving.
Here are some free resources to learn & practice SQL ππ
SQL For Data Analysis: https://t.iss.one/sqlanalyst
For Practice- https://stratascratch.com/?via=free
SQL Learning Series: https://t.iss.one/sqlspecialist/567
Top 10 SQL Projects with Datasets: https://t.iss.one/DataPortfolio/16
Join for more free resources: https://t.iss.one/free4unow_backup
ENJOY LEARNING ππ
β€8π€1
β
SQL Subquery Practice Questions with Answers β Part 2 π§ ποΈ
π Q1. Find employees earning more than the average salary of their department.
ποΈ Table: "employees(emp_id, name, department_id, salary)"
β Answer:
π Q2. Get customers who never placed any order.
ποΈ Tables: "customers(customer_id, name)", "orders(order_id, customer_id)"
β Answer:
π Q3. Find the second highest salary from employees.
ποΈ Table: "employees(emp_id, name, salary)"
β Answer:
π Q4. List products priced higher than the average product price.
ποΈ Table: "products(product_id, product_name, price)"
β Answer:
π Q5. Find employees who work in the same department as 'John'.
ποΈ Table: "employees(emp_id, name, department_id)"
β Answer:
Double Tap β₯οΈ For More
π Q1. Find employees earning more than the average salary of their department.
ποΈ Table: "employees(emp_id, name, department_id, salary)"
β Answer:
SELECT name, department_id, salary
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id
);
π Q2. Get customers who never placed any order.
ποΈ Tables: "customers(customer_id, name)", "orders(order_id, customer_id)"
β Answer:
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
);
π Q3. Find the second highest salary from employees.
ποΈ Table: "employees(emp_id, name, salary)"
β Answer:
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
π Q4. List products priced higher than the average product price.
ποΈ Table: "products(product_id, product_name, price)"
β Answer:
SELECT product_name, price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);
π Q5. Find employees who work in the same department as 'John'.
ποΈ Table: "employees(emp_id, name, department_id)"
β Answer:
SELECT name, department_id
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE name = 'John'
);
Double Tap β₯οΈ For More
β€11π2
β
SQL Interview Roadmap β Step-by-Step Guide to Crack Any SQL Round πΌπ
Whether you're applying for Data Analyst, BI, or Data Engineer roles β SQL rounds are must-clear. Here's your focused roadmap:
1οΈβ£ Core SQL Concepts
πΉ Understand RDBMS, tables, keys, schemas
πΉ Data types,
π§ Interview Tip: Be able to explain
2οΈβ£ Basic Queries
πΉ
π§ Practice: Filter and sort data by multiple columns.
3οΈβ£ Joins β Very Frequently Asked!
πΉ
π§ Interview Tip: Explain the difference with examples.
π§ͺ Practice: Write queries using joins across 2β3 tables.
4οΈβ£ Aggregations & GROUP BY
πΉ
π§ Common Question: Total sales per category where total > X.
5οΈβ£ Window Functions
πΉ
π§ Interview Favorite: Top N per group, previous row comparison.
6οΈβ£ Subqueries & CTEs
πΉ Write queries inside
π§ Use Case: Filtering on aggregated data, simplifying logic.
7οΈβ£ CASE Statements
πΉ Add logic directly in
π§ Example: Categorize users based on spend or activity.
8οΈβ£ Data Cleaning & Transformation
πΉ Handle
π§ Real-world Task: Clean user input data.
9οΈβ£ Query Optimization Basics
πΉ Understand indexing, query plan, performance tips
π§ Interview Tip: Difference between
π Real-World Scenarios
π§ Must Practice:
β’ Sales funnel
β’ Retention cohort
β’ Churn rate
β’ Revenue by channel
β’ Daily active users
π§ͺ Practice Platforms
β’ LeetCode (EasyβHard SQL)
β’ StrataScratch (Real business cases)
β’ Mode Analytics (SQL + Visualization)
β’ HackerRank SQL (MCQs + Coding)
πΌ Final Tip:
Explain why your query works, not just what it does. Speak your logic clearly.
π¬ Tap β€οΈ for more!
Whether you're applying for Data Analyst, BI, or Data Engineer roles β SQL rounds are must-clear. Here's your focused roadmap:
1οΈβ£ Core SQL Concepts
πΉ Understand RDBMS, tables, keys, schemas
πΉ Data types,
NULLs, constraints π§ Interview Tip: Be able to explain
Primary vs Foreign Key.2οΈβ£ Basic Queries
πΉ
SELECT, FROM, WHERE, ORDER BY, LIMIT π§ Practice: Filter and sort data by multiple columns.
3οΈβ£ Joins β Very Frequently Asked!
πΉ
INNER, LEFT, RIGHT, FULL OUTER JOIN π§ Interview Tip: Explain the difference with examples.
π§ͺ Practice: Write queries using joins across 2β3 tables.
4οΈβ£ Aggregations & GROUP BY
πΉ
COUNT, SUM, AVG, MIN, MAX, HAVING π§ Common Question: Total sales per category where total > X.
5οΈβ£ Window Functions
πΉ
ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD() π§ Interview Favorite: Top N per group, previous row comparison.
6οΈβ£ Subqueries & CTEs
πΉ Write queries inside
WHERE, FROM, and using WITH π§ Use Case: Filtering on aggregated data, simplifying logic.
7οΈβ£ CASE Statements
πΉ Add logic directly in
SELECT π§ Example: Categorize users based on spend or activity.
8οΈβ£ Data Cleaning & Transformation
πΉ Handle
NULLs, format dates, string manipulation (TRIM, SUBSTRING) π§ Real-world Task: Clean user input data.
9οΈβ£ Query Optimization Basics
πΉ Understand indexing, query plan, performance tips
π§ Interview Tip: Difference between
WHERE and HAVING.π Real-World Scenarios
π§ Must Practice:
β’ Sales funnel
β’ Retention cohort
β’ Churn rate
β’ Revenue by channel
β’ Daily active users
π§ͺ Practice Platforms
β’ LeetCode (EasyβHard SQL)
β’ StrataScratch (Real business cases)
β’ Mode Analytics (SQL + Visualization)
β’ HackerRank SQL (MCQs + Coding)
πΌ Final Tip:
Explain why your query works, not just what it does. Speak your logic clearly.
π¬ Tap β€οΈ for more!
β€14π1
β
SQL Mistakes Beginners Should Avoid π§ π»
1οΈβ£ Using SELECT *
β’ Pulls unused columns
β’ Slows queries
β’ Breaks when schema changes
β’ Use only required columns
2οΈβ£ Ignoring NULL Values
β’ NULL breaks calculations
β’ COUNT(column) skips NULL
β’ Use
3οΈβ£ Wrong JOIN Type
β’ INNER instead of LEFT
β’ Data silently disappears
β’ Always ask: Do you need unmatched rows?
4οΈβ£ Missing JOIN Conditions
β’ Creates cartesian product
β’ Rows explode
β’ Always join on keys
5οΈβ£ Filtering After JOIN Instead of Before
β’ Processes more rows than needed
β’ Slower performance
β’ Filter early using
6οΈβ£ Using WHERE Instead of HAVING
β’
β’
β’ Aggregates fail without
7οΈβ£ Not Using Indexes
β’ Full table scans
β’ Slow dashboards
β’ Index columns used in
8οΈβ£ Relying on ORDER BY in Subqueries
β’ Order not guaranteed
β’ Results change
β’ Use
9οΈβ£ Mixing Data Types
β’ Implicit conversions
β’ Index not used
β’ Match column data types
π No Query Validation
β’ Results look right but are wrong
β’ Always cross-check counts and totals
π§ Practice Task
β’ Rewrite one query
β’ Remove
β’ Add proper
β’ Handle
β’ Compare result count
SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
β€οΈ Double Tap For More
1οΈβ£ Using SELECT *
β’ Pulls unused columns
β’ Slows queries
β’ Breaks when schema changes
β’ Use only required columns
2οΈβ£ Ignoring NULL Values
β’ NULL breaks calculations
β’ COUNT(column) skips NULL
β’ Use
COALESCE or IS NULL checks3οΈβ£ Wrong JOIN Type
β’ INNER instead of LEFT
β’ Data silently disappears
β’ Always ask: Do you need unmatched rows?
4οΈβ£ Missing JOIN Conditions
β’ Creates cartesian product
β’ Rows explode
β’ Always join on keys
5οΈβ£ Filtering After JOIN Instead of Before
β’ Processes more rows than needed
β’ Slower performance
β’ Filter early using
WHERE or subqueries6οΈβ£ Using WHERE Instead of HAVING
β’
WHERE filters rowsβ’
HAVING filters groupsβ’ Aggregates fail without
HAVING7οΈβ£ Not Using Indexes
β’ Full table scans
β’ Slow dashboards
β’ Index columns used in
JOIN, WHERE, ORDER BY8οΈβ£ Relying on ORDER BY in Subqueries
β’ Order not guaranteed
β’ Results change
β’ Use
ORDER BY only in final query9οΈβ£ Mixing Data Types
β’ Implicit conversions
β’ Index not used
β’ Match column data types
π No Query Validation
β’ Results look right but are wrong
β’ Always cross-check counts and totals
π§ Practice Task
β’ Rewrite one query
β’ Remove
SELECT *β’ Add proper
JOINβ’ Handle
NULLsβ’ Compare result count
SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
β€οΈ Double Tap For More
β€7
Best practices for writing SQL queries:
Join for more: https://t.iss.one/learndataanalysis
1- Write SQL keywords in capital letters.
2- Use table aliases with columns when you are joining multiple tables.
3- Never use select *, always mention list of columns in select clause.
4- Add useful comments wherever you write complex logic. Avoid too many comments.
5- Use joins instead of subqueries when possible for better performance.
6- Create CTEs instead of multiple sub queries , it will make your query easy to read.
7- Join tables using JOIN keywords instead of writing join condition in where clause for better readability.
8- Never use order by in sub queries , It will unnecessary increase runtime.
9- If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance.
Join for more: https://t.iss.one/learndataanalysis
1- Write SQL keywords in capital letters.
2- Use table aliases with columns when you are joining multiple tables.
3- Never use select *, always mention list of columns in select clause.
4- Add useful comments wherever you write complex logic. Avoid too many comments.
5- Use joins instead of subqueries when possible for better performance.
6- Create CTEs instead of multiple sub queries , it will make your query easy to read.
7- Join tables using JOIN keywords instead of writing join condition in where clause for better readability.
8- Never use order by in sub queries , It will unnecessary increase runtime.
9- If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance.
β€7
SQL Interview Questions with Answers Part-1: βοΈ
1. What is SQL?
SQL (Structured Query Language) is a standardized programming language designed to manage and manipulate relational databases. It allows you to query, insert, update, and delete data, as well as create and modify schema objects like tables and views.
2. Differentiate between SQL and NoSQL databases.
SQL databases are relational, table-based, and use structured query language with fixed schemas, ideal for complex queries and transactions. NoSQL databases are non-relational, can be document, key-value, graph, or column-oriented, and are schema-flexible, designed for scalability and handling unstructured data.
3. What are the different types of SQL commands?
β¦ DDL (Data Definition Language): CREATE, ALTER, DROP (define and modify structure)
β¦ DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE (data operations)
β¦ DCL (Data Control Language): GRANT, REVOKE (permission control)
β¦ TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT (transaction management)
4. Explain the difference between WHERE and HAVING clauses.
β¦
β¦
5. Write a SQL query to find the second highest salary in a table.
Using a subquery:
Or using DENSE_RANK():
6. What is a JOIN? Explain different types of JOINs.
A JOIN combines rows from two or more tables based on a related column:
β¦ INNER JOIN: returns matching rows from both tables.
β¦ LEFT JOIN (LEFT OUTER JOIN): all rows from the left table, matched rows from right.
β¦ RIGHT JOIN (RIGHT OUTER JOIN): all rows from right table, matched rows from left.
β¦ FULL JOIN (FULL OUTER JOIN): all rows when thereβs a match in either table.
β¦ CROSS JOIN: Cartesian product of both tables.
7. How do you optimize slow-performing SQL queries?
β¦ Use indexes appropriately to speed up lookups.
β¦ Avoid SELECT *; only select necessary columns.
β¦ Use joins carefully; filter early with WHERE clauses.
β¦ Analyze execution plans to identify bottlenecks.
β¦ Avoid unnecessary subqueries; use EXISTS or JOINs.
β¦ Limit result sets with pagination if dealing with large datasets.
8. What is a primary key? What is a foreign key?
β¦ Primary Key: A unique identifier for records in a table; it cannot be NULL.
β¦ Foreign Key: A field that creates a link between two tables by referring to the primary key in another table, enforcing referential integrity.
9. What are indexes? Explain clustered and non-clustered indexes.
β¦ Indexes speed up data retrieval by providing quick lookups.
β¦ Clustered Index: Sorts and stores the actual data rows in the table based on the key; a table can have only one clustered index.
β¦ Non-Clustered Index: Creates a separate structure that points to the data rows; tables can have multiple non-clustered indexes.
10. Write a SQL query to fetch the top 5 records from a table.
In SQL Server and PostgreSQL:
In SQL Server (older syntax):
React β₯οΈ for Part 2
1. What is SQL?
SQL (Structured Query Language) is a standardized programming language designed to manage and manipulate relational databases. It allows you to query, insert, update, and delete data, as well as create and modify schema objects like tables and views.
2. Differentiate between SQL and NoSQL databases.
SQL databases are relational, table-based, and use structured query language with fixed schemas, ideal for complex queries and transactions. NoSQL databases are non-relational, can be document, key-value, graph, or column-oriented, and are schema-flexible, designed for scalability and handling unstructured data.
3. What are the different types of SQL commands?
β¦ DDL (Data Definition Language): CREATE, ALTER, DROP (define and modify structure)
β¦ DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE (data operations)
β¦ DCL (Data Control Language): GRANT, REVOKE (permission control)
β¦ TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT (transaction management)
4. Explain the difference between WHERE and HAVING clauses.
β¦
WHERE filters rows before grouping (used with SELECT, UPDATE).β¦
HAVING filters groups after aggregation (used with GROUP BY), e.g., filtering aggregated results like sums or counts.5. Write a SQL query to find the second highest salary in a table.
Using a subquery:
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Or using DENSE_RANK():
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees) t
WHERE rnk = 2;
6. What is a JOIN? Explain different types of JOINs.
A JOIN combines rows from two or more tables based on a related column:
β¦ INNER JOIN: returns matching rows from both tables.
β¦ LEFT JOIN (LEFT OUTER JOIN): all rows from the left table, matched rows from right.
β¦ RIGHT JOIN (RIGHT OUTER JOIN): all rows from right table, matched rows from left.
β¦ FULL JOIN (FULL OUTER JOIN): all rows when thereβs a match in either table.
β¦ CROSS JOIN: Cartesian product of both tables.
7. How do you optimize slow-performing SQL queries?
β¦ Use indexes appropriately to speed up lookups.
β¦ Avoid SELECT *; only select necessary columns.
β¦ Use joins carefully; filter early with WHERE clauses.
β¦ Analyze execution plans to identify bottlenecks.
β¦ Avoid unnecessary subqueries; use EXISTS or JOINs.
β¦ Limit result sets with pagination if dealing with large datasets.
8. What is a primary key? What is a foreign key?
β¦ Primary Key: A unique identifier for records in a table; it cannot be NULL.
β¦ Foreign Key: A field that creates a link between two tables by referring to the primary key in another table, enforcing referential integrity.
9. What are indexes? Explain clustered and non-clustered indexes.
β¦ Indexes speed up data retrieval by providing quick lookups.
β¦ Clustered Index: Sorts and stores the actual data rows in the table based on the key; a table can have only one clustered index.
β¦ Non-Clustered Index: Creates a separate structure that points to the data rows; tables can have multiple non-clustered indexes.
10. Write a SQL query to fetch the top 5 records from a table.
In SQL Server and PostgreSQL:
SELECT * FROM table_name
ORDER BY some_column DESC
LIMIT 5;
In SQL Server (older syntax):
SELECT TOP 5 * FROM table_name
ORDER BY some_column DESC;
React β₯οΈ for Part 2
β€16