SQL SERVER DATABASE

on

📊 SQL Basics – Columns, GROUP BY, HAVING & ORDER BY

A quick and structured guide to understand some fundamental SQL concepts with examples.

🧾 Query to Get All Columns of a Table

📌 Problem

Retrieve all column names from the customers table.

✅ Solution

select COLUMN_NAME AS "ALL COLUMNS" 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = 'customers';

📊 GROUP BY Clause

📌 Concept

  • Used to group data based on a column.

  • Helps in aggregation (COUNT, SUM, AVG, etc.).

  • Reduces redundancy by grouping similar values.

✅ Example

SELECT
    city,
    COUNT (*)
FROM
    customers
WHERE
    state = 'MP'
GROUP BY
    city
ORDER BY
    city;

📌 Explanation

  • Filters customers from state = 'MP'

  • Groups data by city

  • Counts number of records per city

  • Orders result in ascending order of city

⚙️ Execution Order in SQL

SQL Server processes the query in this order:

FROM → WHERE → GROUP BY → SELECT → ORDER BY

🎯 HAVING Clause

📌 Concept

  • Used to filter grouped data (after GROUP BY).

  • Works like WHERE, but on aggregated results.

✅ Example

SELECT
    city,
    COUNT (*)
FROM
    customers
WHERE
    state = 'MP'
GROUP BY
    city
HAVING 
    COUNT(*) > 10
ORDER BY
    city;

📌 Explanation

  • Only shows cities where count > 10

  • Applied after grouping

🔽 ORDER BY Clause

📌 Concept

  • Used to sort the result set.

  • Default → Ascending (ASC)

  • Can also sort in Descending (DESC)

✅ Example

SELECT
   *
FROM
    customers
ORDER BY 
    city DESC,
    first_name ASC;

📌 Explanation

  • Sort by city in descending order

  • Then sort by first_name in ascending order

🧠 Key Takeaways

  • INFORMATION_SCHEMA.COLUMNS helps fetch metadata like column names.

  • GROUP BY is essential for aggregation.

  • HAVING filters grouped data, unlike WHERE.

  • ORDER BY controls sorting of results.

  • Understanding execution order is crucial for writing optimized queries.

These concepts form the foundation of SQL querying and are frequently asked in interviews as well as used in real-world applications.

Comments

Post a Comment