SQL Programming Resources

❤7👏2

3.01K views20:35

✅ SQL JOINS — Scenario-Based Interview Questions with Answers

Scenario 1: Find customers who have never placed an order

Tables: customers(customer_id, name) orders(order_id, customer_id);

Question: Business wants a list of customers with zero orders.

Answer:

SELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL;

Why this works
• LEFT JOIN keeps all customers
• Orders missing → NULL
• WHERE filters only non-ordering customers

Scenario 2: Get total revenue per customer, including customers with no orders

Question: Show every customer and their total spend. If no orders, show 0.

Answer:

SELECT c.customer_id, c.name, COALESCE(SUM(o.amount), 0) AS total_spend FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name;

Explanation
• LEFT JOIN keeps all customers
• SUM aggregates orders
• COALESCE converts NULL to 0

Scenario 3: Find orders that don’t have a matching customer

Question: Audit data to find orphan orders.

Answer:

SELECT o.order_id, o.customer_id FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL;

Explanation
• LEFT JOIN from orders
• Missing customers become NULL
• Filters invalid data

Scenario 4: Get only customers who have placed at least one order

Question: Marketing wants only active customers.

Answer:

SELECT DISTINCT c.customer_id, c.name FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id;

Explanation
• INNER JOIN keeps only matching rows
• Customers without orders are excluded

Scenario 5: Find customers with more than 3 orders

Answer:

SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name HAVING COUNT(o.order_id) > 3;

Explanation
• JOIN combines data
• GROUP BY customer
• HAVING filters aggregated count

Scenario 6: Show latest order for each customer

Answer:

SELECT c.customer_id, c.name, MAX(o.order_date) AS last_order_date FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name;

Explanation
• JOIN connects customers and orders
• MAX finds latest order per customer

Scenario 7: Find customers who ordered in 2024 but not in 2025

Answer:

SELECT DISTINCT c.customer_id, c.name FROM customers c INNER JOIN orders o2024 ON c.customer_id = o2024.customer_id LEFT JOIN orders o2025 ON c.customer_id = o2025.customer_id AND o2025.order_date >= '2025-01-01' WHERE o2024.order_date BETWEEN '2024-01-01' AND '2024-12-31' AND o2025.customer_id IS NULL;

Explanation
• INNER JOIN ensures 2024 orders
• LEFT JOIN checks absence in 2025
• NULL filter removes 2025 buyers

Scenario 8: Employee-Manager hierarchy (SELF JOIN)

Table: employees(employee_id, name, manager_id);

Answer:

SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id;

Explanation
• Same table joined twice
• Shows reporting structure

Scenario 9: Revenue by city

Answer:

SELECT c.city, SUM(o.amount) AS revenue FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.city;

Explanation
• JOIN links customers to orders
• GROUP BY city
• SUM calculates revenue

Scenario 10: Duplicate explosion after JOIN (classic trap)

Question: Why does this query show inflated revenue?

SELECT SUM(o.amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

Answer:
• Customer table may have duplicates
• JOIN multiplies rows
• Revenue gets inflated

Fix:

SELECT SUM(amount) FROM orders; or deduplicate customers before joining.

Interview golden rule for JOINS
Always explain:
1️⃣ Which table is LEFT
2️⃣ Which table is RIGHT
3️⃣ What rows are kept
4️⃣ Where NULLs appear

Double Tap ♥️ For More

❤7👍1👏1

3.23K views06:38

SQL Programming Resources

❤8

3.04K views12:57

SQL Programming Resources

✅ Handling NULL Values in SQL

What is NULL in SQL?
NULL means missing or unknown data.

It does NOT mean:
- 0
- Empty string ''
- False

👉 NULL = no value at all

Why NULLs exist in real data

Real business data is messy:
- Customer didn’t provide city
- Order amount not updated yet
- Employee not assigned a manager

So databases allow NULLs.

Example Table

Data: customers
customer_id: 1, name: Rahul, city: Pune, email: [email protected]
customer_id: 2, name: Neha, city: NULL, email: [email protected]
customer_id: 3, name: Aman, city: Delhi, email: NULL

🚫 Biggest Beginner Mistake
❌ This is WRONG

SELECT * FROM customers WHERE city = NULL;

👉 This will return no rows

Why? Because NULL cannot be compared using = or !=

✅ Correct Way to Handle NULL
1️⃣ IS NULL: Used to find missing values

SELECT * FROM customers WHERE city IS NULL;

What this query does:
- Scans all rows
- Returns customers where city is missing

2️⃣ IS NOT NULL: Used to exclude missing values

SELECT * FROM customers WHERE email IS NOT NULL;

What this query does:
- Returns customers who have email
- Removes rows with NULL email

NULLs in JOINs (Very Important)
Customers data:
customer_id: 1, name: Rahul
customer_id: 2, name: Neha
customer_id: 3, name: Aman

Orders data:
order_id: 101, customer_id: 1, amount: 5000
order_id: 102, customer_id: 1, amount: 3000

LEFT JOIN with NULL check
👉 Find customers with NO orders

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

What this query does:
- Keeps all customers
- Matches orders where possible
- Filters customers without orders

NULLs in Aggregations
COUNT behavior (INTERVIEW FAVORITE)

SELECT COUNT(*) FROM customers;

👉 Counts all rows

SELECT COUNT(city) FROM customers;

👉 Counts only non-NULL cities

Example

SELECT COUNT(email) FROM customers;

✔ Counts customers who have email
❌ Ignores NULL emails

Handling NULL using COALESCE

SELECT name, COALESCE(city, 'Unknown') AS city
FROM customers;

What this query does:
- If city exists → show city
- If city is NULL → show “Unknown”

NULLs in SUM / AVG

SELECT SUM(amount) FROM orders;

👉 NULL values are ignored, not treated as 0
But if all rows are NULL, result is NULL.
Safe approach:

SELECT COALESCE(SUM(amount), 0) FROM orders;

NULL vs Empty String
- NULL: No value
- '': Empty value

WHERE email IS NULL -- missing
WHERE email = '' -- empty but exists

Common NULL Mistakes (Must Avoid)
❌ Using = NULL
❌ Forgetting NULLs in LEFT JOIN
❌ Assuming COUNT(column) counts NULL
❌ Ignoring NULL replacement in reports

Interview One-Liner 💡
> NULL represents missing data and must be handled using IS NULL, IS NOT NULL, or COALESCE, not with =.

Double Tap ♥️ For More

❤10

3.17K views14:10

SQL Programming Resources

What does NULL represent in SQL?

Anonymous Quiz

D. Missing or unknown value

👍5❤2

334 voters2.47K views05:58

SQL Programming Resources

Here are some commonly asked SQL interview questions along with brief answers:

1. What is SQL?
- SQL stands for Structured Query Language, used for managing and manipulating relational databases.

2. What are the types of SQL commands?
- SQL commands can be broadly categorized into four types: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).

3. What is the difference between CHAR and VARCHAR data types?
- CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type. CHAR will always occupy the same amount of storage space, while VARCHAR will only use the necessary space to store the actual data.

4. What is a primary key?
- A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures data integrity by enforcing uniqueness and can be used to establish relationships between tables.

5. What is a foreign key?
- A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a relationship between two tables and ensures referential integrity.

6. What is a JOIN in SQL?
- JOIN is used to combine rows from two or more tables based on a related column between them. There are different types of JOINs, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

7. What is the difference between INNER JOIN and OUTER JOIN?
- INNER JOIN returns only the rows that have matching values in both tables, while OUTER JOIN (LEFT, RIGHT, FULL) returns all rows from one or both tables, with NULL values in columns where there is no match.

8. What is the difference between GROUP BY and ORDER BY?
- GROUP BY is used to group rows that have the same values into summary rows, typically used with aggregate functions like SUM, COUNT, AVG, etc., while ORDER BY is used to sort the result set based on one or more columns.

9. What is a subquery?
- A subquery is a query nested within another query, used to return data that will be used in the main query. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements.

10. What is normalization in SQL?
- Normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves dividing large tables into smaller tables and defining relationships between them to improve data integrity and efficiency.

Around 90% questions will be asked from sql in data analytics interview, so please make sure to practice SQL skills using websites like stratascratch. ☺️💪

❤4

2.94K views21:04

SQL Programming Resources

Which condition correctly finds rows where city is missing?

Anonymous Quiz

❤3

270 voters2.33K views07:17

SQL Programming Resources

What is the result of this query?

SELECT COUNT(city) FROM customers;

Anonymous Quiz

28%

A. Counts all rows

12%

B. Counts only rows where city is NULL

55%

C. Counts only rows where city is NOT NULL

D. Returns error

❤2

258 voters2.47K views07:23

SQL Programming Resources

You want to show 0 instead of NULL for total sales. Which function should you use?

Anonymous Quiz

❤3

248 voters2.6K views07:24

SQL Programming Resources

In a LEFT JOIN, why do unmatched rows show NULL values?

Anonymous Quiz

A. Because data is deleted

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

242 voters2.56K views07:24

SQL Programming Resources

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.

❤4

3.03K views13:40

SQL Programming Resources

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

1❤6

3.03K views08:17

SQL Programming Resources

✅ 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!

❤4

2.74K views12:15

SQL Programming Resources

📌 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

❤4

2.5K views07:22

SQL Programming Resources

💡 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

Kaggle

Brazilian E-Commerce Public Dataset by Olist

100,000 Orders with product, customer and reviews info

❤5

2.73K views20:29

SQL Programming Resources

✅ 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!

❤2

2.76K views12:48

SQL Programming Resources

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!

❤25👏1

3.19K views17:25

SQL Programming Resources

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: 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

❤10

2.95K views12:14

SQL Programming Resources

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 😊

❤9

2.59K views13:43

SQL Programming Resources

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

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

2.57K viewsedited 18:49

SQL Programming Resources

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 👍👍

❤8🤔1

3.17K viewsedited 18:42

About

Blog

Apps

Platform