🧠 SQL Interview Question (Top-N per Group + Tie Handling - Tricky)
📌

scores(student_id, subject, marks)

❓ Ques :

👉 Find students who scored the highest marks in each subject.

👉 If multiple students have the same top score, include all of them (handle ties).

🧩 How Interviewers Expect You to Think

• Group data by subject 📚
• Identify highest marks within each group
• Handle ties correctly (don’t lose rows)
• Use window functions (RANK vs ROW_NUMBER)

💡 SQL Solution

SELECT
student_id,
subject,
marks
FROM (
SELECT
scores.student_id,
scores.subject,
scores.marks,
RANK() OVER (
PARTITION BY scores.subject
ORDER BY scores.marks DESC
) AS rnk
FROM scores
) ranked_scores
WHERE ranked_scores.rnk = 1;

🔥 Why This Question Is Powerful

• Tests Top-N per group (very common pattern) 🧠
• Checks understanding of RANK vs ROW_NUMBER

❤️ React if you want more real interview-level SQL questions 🚀

Learn Ai in 2026 —Absolutely FREE!🚀

💸 Cost: ~₹10,000~ ₹0 (FREE!)

What you’ll learn:
✅ 25+ Powerful AI Tools 
✅ Crack Interviews with Ai 
✅ Build Websites in seconds 
✅ Make Videos  PPT 

Enroll Now (free): https://tinyurl.com/Free-Ai-Course-a

⚠️ Register  Get Ai Certificate for resume

✅ Core SQL Interview Questions With Answers 🖥️

1 What is SQL
• SQL stands for Structured Query Language
• You use it to read and manage data in relational databases
• Used in MySQL, PostgreSQL, SQL Server, Oracle

2 What is an RDBMS
• Relational Database Management System
• Stores data in tables with rows and columns
• Uses keys to link tables
• Example. Customer table linked to Orders table using customer_id

3 What is a table
• Structured storage for data
• Rows are records
• Columns are attributes
• Example. One row equals one customer

4 What is a primary key
• Uniquely identifies each row
• Cannot be NULL
• No duplicate values
• Example. user_id in users table

5 What is a foreign key
• Links one table to another
• Refers to a primary key in another table
• Allows duplicate values
• Example. user_id in orders table

6 Difference between primary key and foreign key
• Primary key ensures uniqueness
• Foreign key ensures relationship
• One table can have one primary key
• One table can have multiple foreign keys

7 What is NULL
• Represents missing or unknown value
• Not equal to zero or empty string
• Use IS NULL or IS NOT NULL to check

8 What are constraints
• Rules applied on columns
• Maintain data quality
• Common constraints
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK

9 What are data types
• Define type of data stored
• Common types
– INT for numbers
– VARCHAR for text
– DATE for dates
– FLOAT or DECIMAL for decimals

10 Interview tip you must remember
• Always explain with a small example
• Speak logic before syntax
• Keep answers short and direct

Double Tap ❤️ For More

Hey guys,

Here is the list of best curated Telegram Channels for free education 👇👇

Free Courses with Certificate

Web Development Free Resources

Data Science & Machine Learning

Programming Free Books

Python Free Courses

Python Interview Resources

Ethical Hacking & Cyber Security

English Speaking & Communication

Stock Marketing & Investment Banking

Coding Projects

Jobs & Internship Opportunities

Learn Digital Marketing

Crack your coding Interviews

Udemy Free Courses with Certificate

Earn $10000 with ChatGPT

Google Jobs

Java Programming Free Resources

Learn Blockchain & Crypto

Data Analyst Jobs

Artificial Intelligence

Free access to all the Paid Channels
👇👇
https://t.me/addlist/4q2PYC0pH_VjZDk5

Do react with ♥️ if you need more content free resources

ENJOY LEARNING 👍👍

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

Freshers are getting paid 10 - 15 Lakhs by learning AI & ML skill

📢 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗔𝗹𝗲𝗿𝘁 – 𝗔𝗿𝘁𝗶𝗳𝗶𝗰𝗶𝗮𝗹 𝗜𝗻𝘁𝗲𝗹𝗹𝗶𝗴𝗲𝗻𝗰𝗲 𝗮𝗻𝗱 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴

Open for all. No Coding Background Required

📊 Learn AI/ML from Scratch
🤖 AI Tools & Automation
📈 Build real world Projects for job ready portfolio
🎓 Vishlesan i-Hub, IIT Patna Certification Program

🔥Deadline :- 12th April

𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄👇 :- 

https://pdlink.in/41ZttiU
.
Get Placement Assistance With 5000+ Companies from Masai School

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have 2 minutes to solve this SQL query.

List employees who earn more than their department manager's salary from the employees table (manager_id references employee id).

𝗠𝗲: Challenge accepted!

SELECT e1.name, e1.department, e1.salary
FROM employees e1
JOIN employees e2 ON e1.department = e2.department AND e1.manager_id = e2.id
WHERE e1.salary > e2.salary;

I self-joined the employees table on department and manager_id to compare each employee's salary against their manager's in the same department. The WHERE clause filters for employees earning more. This tests join mastery for hierarchical data, common in org charts.

𝗧𝗶𝗽 𝗳𝗼𝗿 𝗦𝗤𝗟 𝗝𝗼𝗯 𝗦𝗲𝗲𝗸𝗲𝗿𝘀:
Self-joins shine for intra-table relationships like managers/employees or siblings—practice with employee hierarchies to impress in data engineering interviews!

React with ❤️ for more

𝗧𝗼𝗽 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻𝘀 𝘁𝗼 𝗟𝗮𝗻𝗱 𝗮 𝗛𝗶𝗴𝗵-𝗣𝗮𝘆𝗶𝗻𝗴 𝗝𝗼𝗯 𝗶𝗻 𝟮𝟬𝟮𝟲🔥

Learn from scratch → Build real projects → Get placed

✅ 2000+ Students Already Placed
🤝 500+ Hiring Partners
💼 Avg Salary: ₹7.4 LPA
🚀 Highest Package: ₹41 LPA

Fullstack :- https://pdlink.in/4hO7rWY

Data Analytics :- https://pdlink.in/4fdWxJB

📈 Don’t just scroll… Start today & secure your 2026 job NOW

✅ Step-by-Step Guide to Create a Data Science Portfolio 🎯📊

✅ 1️⃣ Pick Your Focus Area
Decide what kind of data scientist you want to be:
• Data Analyst → Excel, SQL, Power BI/Tableau 📈
• Machine Learning → Python, Scikit-learn, TensorFlow 🧠
• Data Engineer → Python, Spark, Airflow, Cloud ⚙️
• Full-stack DS → Mix of analysis + ML + deployment 🧑‍💻

✅ 2️⃣ Plan Your Portfolio Sections
Your portfolio should include:
• Home Page – Quick intro about you 👋
• About Me – Education, tools, skills 📝
• Projects – With code, visuals & explanations 📊
• Blog (optional) – Share insights & tutorials ✍️
• Contact – Email, LinkedIn, GitHub, etc. ✉️

✅ 3️⃣ Build the Portfolio Website
Options to build:
• Use Jupyter Notebook + GitHub Pages 🌐
• Create with Streamlit or Gradio (for interactive apps) ✨
• Full site: HTML/CSS or React + deploy on Netlify/Vercel 🚀

✅ 4️⃣ Add 2–4 Quality Projects
Project ideas:
• EDA on real-world datasets 🔍
• Machine learning prediction model 🔮
• NLP app (e.g., sentiment analysis) 💬
• Dashboard in Power BI/Tableau 📈
• Time series forecasting ⏳

Each project should include:
• Problem statement ❓
• Dataset source 📁
• Visualizations 📊
• Model performance ✅
• GitHub repo + live app link (if any) 🔗
• Brief write-up or blog 📄

✅ 5️⃣ Showcase on GitHub
• Create clean repos with README files 🌟
• Add visuals, summaries, and instructions 📸
• Use Jupyter notebooks or Markdown ✏️

✅ 6️⃣ Deploy and Share
• Use Streamlit Cloud, Hugging Face, or Netlify 🚀
• Share on LinkedIn & Kaggle 🤝
• Use Medium/Hashnode for blogs 📝
• Create a resume link to your portfolio 🔗

💡 Pro Tips:
• Focus on storytelling: Why the project matters 📖
• Show your thought process, not just code 🤔
• Keep UI simple and clean ✨
• Add certifications and tools logos if needed 🏅
• Keep your portfolio updated every 2–3 months 🔄

🎯 Goal: When someone views your site, they should instantly see your skills, your projects, and your ability to solve real-world data problems.

💬 Tap ❤️ if this helped you!

𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀, 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝘄𝗶𝘁𝗵 𝗔𝗜 𝗮𝗿𝗲 𝗵𝗶𝗴𝗵𝗹𝘆 𝗱𝗲𝗺𝗮𝗻𝗱𝗶𝗻𝗴 𝗶𝗻 𝟮𝟬𝟮𝟲😍

Learn Data Science and AI Taught by Top Tech professionals

60+ Hiring Drives Every Month

𝗛𝗶𝗴𝗵𝗹𝗶𝗴𝗵𝘁𝗲𝘀:- 
- 12.65 Lakhs Highest Salary
- 500+ Partner Companies
- 100% Job Assistance
- 5.7 LPA Average Salary

𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄👇:-

 Online :- https://pdlink.in/4fdWxJB

🔹 Hyderabad :- https://pdlink.in/4kFhjn3

🔹 Pune:-  https://pdlink.in/45p4GrC

🔹 Noida :-  https://linkpd.in/DaNoida

Hurry Up 🏃‍♂️! Limited seats are available.

✅ Complete SQL Roadmap in 2 Months

Month 1: Strong SQL Foundations
Week 1: Database and query basics
- What SQL does in analytics and business
- Tables, rows, columns
- Primary key and foreign key
- SELECT, DISTINCT
- WHERE with AND, OR, IN, BETWEEN
Outcome: You understand data structure and fetch filtered data.

Week 2: Sorting and aggregation
- ORDER BY and LIMIT
- COUNT, SUM, AVG, MIN, MAX
- GROUP BY
- HAVING vs WHERE
- Use case like total sales per product
Outcome: You summarize data clearly.

Week 3: Joins fundamentals
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- Join conditions
- Handling NULL values
Outcome: You combine multiple tables correctly.

Week 4: Joins practice and cleanup
- Duplicate rows after joins
- SELF JOIN with examples
- Data cleaning using SQL
- Daily join-based questions
Outcome: You stop making join mistakes.

Month 2: Analytics-Level SQL
Week 5: Subqueries and CTEs
- Subqueries in WHERE and SELECT
- Correlated subqueries
- Common Table Expressions
- Readability and reuse
Outcome: You write structured queries.

Week 6: Window functions
- ROW_NUMBER, RANK, DENSE_RANK
- PARTITION BY and ORDER BY
- Running totals
- Top N per category problems
Outcome: You solve advanced analytics queries.

Week 7: Date and string analysis
- Date functions for daily, monthly analysis
- Year-over-year and month-over-month logic
- String functions for text cleanup
Outcome: You handle real business datasets.

Week 8: Project and interview prep
- Build a SQL project using sales or HR data
- Write KPI queries
- Explain query logic step by step
- Daily interview questions practice
Outcome: You are SQL interview ready.

Practice platforms
- LeetCode SQL
- HackerRank SQL
- Kaggle datasets

Double Tap ♥️ For Detailed Explanation of Each Topic

𝗔𝗜/𝗠𝗟 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗣𝗿𝗼𝗴𝗿𝗮𝗺 𝗕𝘆  𝗩𝗶𝘀𝗵𝗹𝗲𝘀𝗮𝗻 𝗶-𝗛𝘂𝗯, 𝗜𝗜𝗧 𝗣𝗮𝘁𝗻𝗮 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻😍

Freshers are getting paid 10 - 15 Lakhs by learning AI & ML skill

Upgrade your career with a beginner-friendly AI/ML certification.

👉Open for all. No Coding Background Required
💻 Learn AI/ML from Scratch
🎓 Build real world Projects for job ready portfolio 

🔥Deadline :- 19th April

    𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄👇 :- 

https://pdlink.in/41ZttiU
.
Get Placement Assistance With 5000+ Companies

🚀 How to Land a Data Analyst Job Without Experience?

Many people asked me this question, so I thought to answer it here to help everyone. Here is the step-by-step approach i would recommend:

✅ Step 1: Master the Essential Skills

You need to build a strong foundation in:

🔹 SQL – Learn how to extract and manipulate data
🔹 Excel – Master formulas, Pivot Tables, and dashboards
🔹 Python – Focus on Pandas, NumPy, and Matplotlib for data analysis
🔹 Power BI/Tableau – Learn to create interactive dashboards
🔹 Statistics & Business Acumen – Understand data trends and insights

Where to learn?
📌 Google Data Analytics Course
📌 SQL – Mode Analytics (Free)
📌 Python – Kaggle or DataCamp


✅ Step 2: Work on Real-World Projects

Employers care more about what you can do rather than just your degree. Build 3-4 projects to showcase your skills.

🔹 Project Ideas:

✅ Analyze sales data to find profitable products
✅ Clean messy datasets using SQL or Python
✅ Build an interactive Power BI dashboard
✅ Predict customer churn using machine learning (optional)

Use Kaggle, Data.gov, or Google Dataset Search to find free datasets!


✅ Step 3: Build an Impressive Portfolio

Once you have projects, showcase them! Create:
📌 A GitHub repository to store your SQL/Python code
📌 A Tableau or Power BI Public Profile for dashboards
📌 A Medium or LinkedIn post explaining your projects

A strong portfolio = More job opportunities! 💡


✅ Step 4: Get Hands-On Experience

If you don’t have experience, create your own!
📌 Do freelance projects on Upwork/Fiverr
📌 Join an internship or volunteer for NGOs
📌 Participate in Kaggle competitions
📌 Contribute to open-source projects

Real-world practice > Theoretical knowledge!


✅ Step 5: Optimize Your Resume & LinkedIn Profile

Your resume should highlight:
✔️ Skills (SQL, Python, Power BI, etc.)
✔️ Projects (Brief descriptions with links)
✔️ Certifications (Google Data Analytics, Coursera, etc.)

Bonus Tip:
🔹 Write "Data Analyst in Training" on LinkedIn
🔹 Start posting insights from your learning journey
🔹 Engage with recruiters & join LinkedIn groups


✅ Step 6: Start Applying for Jobs

Don’t wait for the perfect job—start applying!
📌 Apply on LinkedIn, Indeed, and company websites
📌 Network with professionals in the industry
📌 Be ready for SQL & Excel assessments

Pro Tip: Even if you don’t meet 100% of the job requirements, apply anyway! Many companies are open to hiring self-taught analysts.

You don’t need a fancy degree to become a Data Analyst. Skills + Projects + Networking = Your job offer!

🔥 Your Challenge: Start your first project today and track your progress!

Share with credits: https://t.me/sqlspecialist

Hope it helps :)

𝗙𝘂𝗹𝗹𝘀𝘁𝗮𝗰𝗸 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗺𝗲𝗻𝘁 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗪𝗶𝘁𝗵 𝗚𝗲𝗻𝗔𝗜😍

Curriculum designed and taught by alumni from IITs & leading tech companies, with practical GenAI applications.

* 2000+ Students Placed
* 41LPA Highest Salary
* 500+ Partner Companies
- 7.4 LPA Avg Salary

𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄👇:-

🔹 Online :- https://pdlink.in/4hO7rWY

🔹 Hyderabad :- https://pdlink.in/4cJUWtx

🔹 Pune :-  https://pdlink.in/3YA32zi

🔹 Noida :-  https://linkpd.in/NoidaFSD

Hurry Up 🏃‍♂️! Limited seats are available.

✅ Data Analytics Roadmap for Freshers 🚀📊

1️⃣ Understand What a Data Analyst Does
🔍 Analyze data, find insights, create dashboards, support business decisions.

2️⃣ Start with Excel
📈 Learn:
• Basic formulas
• Charts Pivot Tables
• Data cleaning

💡 Excel is still the #1 tool in many companies.

3️⃣ Learn SQL
🧩 SQL helps you pull and analyze data from databases.
Start with:
• SELECT, WHERE, JOIN, GROUP BY

🛠️ Practice on platforms like W3Schools or Mode Analytics.

4️⃣ Pick a Programming Language
🐍 Start with Python (easier) or R
• Learn pandas, matplotlib, numpy
• Do small projects (e.g. analyze sales data)

5️⃣ Data Visualization Tools
📊 Learn:
• Power BI or Tableau
• Build simple dashboards

💡 Start with free versions or YouTube tutorials.

6️⃣ Practice with Real Data
🔍 Use sites like Kaggle or Data.gov
• Clean, analyze, visualize
• Try small case studies (sales report, customer trends)

7️⃣ Create a Portfolio
💻 Share projects on:
• GitHub
• Notion or a simple website

📌 Add visuals + brief explanations of your insights.

8️⃣ Improve Soft Skills
🗣️ Focus on:
• Presenting data in simple words
• Asking good questions
• Thinking critically about patterns

9️⃣ Certifications to Stand Out
🎓 Try:
• Google Data Analytics (Coursera)
• IBM Data Analyst
• LinkedIn Learning basics

🔟 Apply for Internships Entry Jobs
🎯 Titles to look for:
• Data Analyst (Intern)
• Junior Analyst
• Business Analyst

💬 React ❤️ for more!

𝗜𝗜𝗧 & 𝗜𝗜𝗠 𝗢𝗳𝗳𝗲𝗿𝗶𝗻𝗴 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗣𝗿𝗼𝗴𝗿𝗮𝗺𝘀😍

👉Open for all. No Coding Background Required

AI/ML By IIT Patna  :- https://pdlink.in/41ZttiU

Business Analytics With AI :- https://pdlink.in/41h8gRt

Digital Marketing With AI :-https://pdlink.in/47BxVYG

AI/ML By IIT Mandi :- https://pdlink.in/4cvXBaz

🔥Get Placement Assistance With 5000+ Companies🎓

✅SQL Roadmap: Step-by-Step Guide to Master SQL 🧠💻

Whether you're aiming to be a backend dev, data analyst, or full-time SQL pro — this roadmap has got you covered 👇

📍 1. SQL Basics
⦁  SELECT, FROM, WHERE
⦁  ORDER BY, LIMIT, DISTINCT 
   Learn data retrieval & filtering.

📍 2. Joins Mastery
⦁  INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN
⦁  SELF JOIN, CROSS JOIN 
   Master table relationships.

📍 3. Aggregate Functions
⦁  COUNT(), SUM(), AVG(), MIN(), MAX() 
   Key for reporting & analytics.

📍 4. Grouping Data
⦁  GROUP BY to group
⦁  HAVING to filter groups 
   Example: Sales by region, top categories.

📍 5. Subqueries & Nested Queries
⦁  Use subqueries in WHERE, FROM, SELECT
⦁  Use EXISTS, IN, ANY, ALL 
   Build complex logic without extra joins.

📍 6. Data Modification
⦁  INSERT INTO, UPDATE, DELETE
⦁  MERGE (advanced) 
   Safely change dataset content.

📍 7. Database Design Concepts
⦁  Normalization (1NF to 3NF)
⦁  Primary, Foreign, Unique Keys 
   Design scalable, clean DBs.

📍 8. Indexing & Query Optimization
⦁  Speed queries with indexes
⦁  Use EXPLAIN, ANALYZE to tune 
   Vital for big data/enterprise work.

📍 9. Stored Procedures & Functions
⦁  Reusable logic, control flow (IF, CASE, LOOP) 
   Backend logic inside the DB.

📍 10. Transactions & Locks
⦁  ACID properties
⦁  BEGIN, COMMIT, ROLLBACK
⦁  Lock types (SHARED, EXCLUSIVE) 
   Prevent data corruption in concurrency.

📍 11. Views & Triggers
⦁  CREATE VIEW for abstraction
⦁  TRIGGERS auto-run SQL on events 
   Automate & maintain logic.

📍 12. Backup & Restore
⦁  Backup/restore with tools (mysqldump, pg_dump) 
   Keep your data safe.

📍 13. NoSQL Basics (Optional)
⦁  Learn MongoDB, Redis basics
⦁  Understand where SQL ends & NoSQL begins.

📍 14. Real Projects & Practice
⦁  Build projects: Employee DB, Sales Dashboard, Blogging System
⦁  Practice on LeetCode, StrataScratch, HackerRank

📍 15. Apply for SQL Dev Roles
⦁  Tailor resume with projects & optimization skills
⦁  Prepare for interviews with SQL challenges
⦁  Know common business use cases

💡 Pro Tip: Combine SQL with Python or Excel to boost your data career options.

💬 Double Tap ♥️ For More!

Every day you login... Work.. and logout.

Days become months.
Months become years.

But nothing changes.

Same role. Same work. Same pay.

Meanwhile, others are moving into Cloud & Data Engineering…
building real systems and earning better.

If you are looking to get into Azure Data Engineering then..

𝗝𝗼𝗶𝗻 𝘁𝗵𝗲 3 months 𝗟𝗶𝘃𝗲 𝗣𝗿𝗼𝗴𝗿𝗮𝗺

📌 Start Date: 20th April 2026

⏰ Time: 9 PM – 10 PM IST | Monday

👉 𝐌𝐞𝐬𝐬𝐚𝐠𝐞 𝐮𝐬 𝐨𝐧 𝐖𝐡𝐚𝐭𝐬𝐀𝐩𝐩:

https://wa.me/917032678595?text=Interested_to_join_Azure_Data_Engineering_live_sessions

🔹 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗵𝗲𝗿𝗲:

https://forms.gle/DRXEhvyG9ENDsNYR9

🎟️ 𝗝𝗼𝗶𝗻 𝗪𝗵𝗮𝘁𝘀𝗔𝗽𝗽 𝗚𝗿𝗼𝘂𝗽:

https://chat.whatsapp.com/GCG3Si7vhrJD1evV9NAbhL

🏀 𝗖𝗼𝘂𝗿𝘀𝗲 𝗖𝗼𝗻𝘁𝗲𝗻𝘁:

https://drive.google.com/file/d/1QKqhRMHx2SDNDTmPAf3_54fA6LljKHm6/view

✅ SQL Interview Questions with Answers

1. What is a window function?
A window function computes results over a group ("window") of rows related to the current row, without collapsing them (like GROUP BY).

Examples: ROW_NUMBER(), RANK(), SUM() OVER(...) for running totals, rankings, or moving averages.


2. What is the difference between RANK() and ROW_NUMBER()?
- ROW_NUMBER(): assigns unique sequential numbers to all rows, even if values are equal.
- RANK(): gives same rank to tied values, then skips the next rank (e.g., 1, 1, 3).


3. How do you find the second highest salary?
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
) t
WHERE rnk = 2;
This avoids ties if you want exactly the second‑highest value.


4. What is a recursive CTE?
A recursive CTE refers to itself in its WITH definition, usually in the form "anchor + UNION ALL recursive step". It is used for hierarchical data like managers‑employees, org charts, or tree structures.


5. What is the difference between correlated and non-correlated subquery?
- Non‑correlated: runs once, independent of the outer query.
- Correlated: references columns from the outer query and runs once per outer row (e.g., SELECT ... FROM t1 WHERE col > (SELECT AVG(col) FROM t2 WHERE t2.id = t1.id)).


6. How do you remove duplicates without DISTINCT?
Use window functions:
DELETE FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) as rn
FROM table
) t
WHERE rn > 1;
Or use GROUP BY and keep one row per group.


7. What is an INDEX and when do you use it?
An index speeds up data retrieval on specified columns (used in WHERE, JOIN, ORDER BY). Use it on columns that are frequently filtered or joined; avoid on very small tables or columns updated often.


8. Explain self-join with example.
A self‑join joins a table to itself using aliases. Example:
SELECT e1.name as employee, e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;
Useful for parent‑child relationships.


9. What is the difference between DELETE, DROP, and TRUNCATE?
- DELETE: removes rows (can be filtered by WHERE), can be rolled back.
- TRUNCATE: removes all rows quickly, resets storage; often not logged per row.
- DROP: removes entire table (structure + data); cannot be rolled back.

10. How do you pivot/unpivot data in SQL?
- Pivot: turns rows into columns (e.g., sales per month as columns) using PIVOT or conditional aggregation (MAX(CASE WHEN ... END)).
- Unpivot: turns columns into rows (e.g., multiple month columns → one month column) using UNPIVOT or UNION ALL/VALUES.

11. What is LAG() and LEAD()?
- LAG(col, n): value of col from n rows before current row.
- LEAD(col, n): value from n rows after. Used for time‑series analysis (MoM change, prior/next values).

12. How do you handle NULL in aggregates?
Most aggregates (SUM, AVG, MAX, MIN) ignore NULL.
- COUNT(col) ignores NULL; COUNT() counts all rows.
- Use COALESCE() or ISNULL() to replace NULL before aggregating.

13. What is the difference between VIEW and MATERIALIZED VIEW?
- VIEW: virtual table; query runs every time you select.
- MATERIALIZED VIEW: stores result physically and refreshes periodically; faster reads, slower updates.


14. Explain ACID properties.
- Atomicity: transaction is "all or nothing".
- Consistency: valid state before and after.
- Isolation: concurrent transactions don't interfere.
- Durability: committed changes survive crashes.


15. How do you optimize a slow query?
- Add proper indexes on WHERE, JOIN, ORDER BY columns.
- Remove unnecessary SELECT , DISTINCT, or functions on indexed columns.
- Check execution plan and avoid large scans; use LIMIT or partitioning if possible.

16. What is the difference between INNER JOIN and EXISTS?
- INNER JOIN: returns combined columns from both tables where keys match.
- EXISTS: checks if a subquery returns any rows; usually faster when you only care about existence (e.g., filtering with WHERE EXISTS).


17. What is a FULL OUTER JOIN?
Returns all rows from both tables. If there's no match, unmatched sides are filled with NULL. Useful to see data that exists in either table but not in both.


18. How do you find duplicates across tables?
Use INTERSECT or:
SELECT t1.
FROM table1 t1
WHERE EXISTS (
SELECT 1 FROM table2 t2
WHERE t1.key = t2.key
);
Or use UNION ALL + GROUP BY to count occurrences.


19. What are SQL constraints?
Rules that enforce data integrity:
- PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.
They keep data consistent and help the query optimizer.


20. Explain GROUPING SETS.

GROUPING SETS lets you define multiple grouping levels in one GROUP BY:

GROUP BY GROUPING SETS (
(), -- grand total
(a), -- by a
(b), -- by b
(a, b) -- by a and b
);
Useful for multi‑level summaries (like OLAP reports).

SQL Programming: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Double Tap ❤️ For More