Scribd
Upload
5 views3 pages

SQL Guide: GROUP BY, JOINS, and More

The document provides a guide on SQL concepts including GROUP BY, HAVING, JOINS, and advanced clauses. It explains the syntax and usage of these concepts with examples, such as using GROUP BY for aggregation and various types of JOINS for combining tables. Additionally, it covers conditional logic with CASE statements, handling null values with COALESCE and IFNULL, and using window functions for ranking and aggregating data.

Uploaded by

hemavinila24
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views3 pages

SQL Guide: GROUP BY, JOINS, and More

The document provides a guide on SQL concepts including GROUP BY, HAVING, JOINS, and advanced clauses. It explains the syntax and usage of these concepts with examples, such as using GROUP BY for aggregation and various types of JOINS for combining tables. Additionally, it covers conditional logic with CASE statements, handling null values with COALESCE and IFNULL, and using window functions for ranking and aggregating data.

Uploaded by

hemavinila24
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Guide: GROUP BY, HAVING, JOINS, and Advanced Clauses

1. GROUP BY and HAVING

GROUP BY groups rows with the same values. HAVING filters groups after aggregation.

Syntax:

SELECT column, AGG_FUNCTION(column)

FROM table

GROUP BY column

HAVING condition;

Example:

SELECT department, COUNT(*) AS emp_count

FROM Employees

GROUP BY department

HAVING COUNT(*) > 5;

2. JOINS

Joins combine rows from two or more tables.

INNER JOIN: Only matching rows.

LEFT JOIN: All rows from left + matched right rows.

RIGHT JOIN: All rows from right + matched left rows.

FULL JOIN: All rows from both sides.

Example:

SELECT [Link], [Link] AS department

FROM Employees e

INNER JOIN Departments d ON e.dept_id = [Link];

3. Self JOIN
SQL Guide: GROUP BY, HAVING, JOINS, and Advanced Clauses

Used to relate data within the same table.

Example:

SELECT [Link] AS Employee, [Link] AS Manager

FROM Employees e

LEFT JOIN Employees m ON e.manager_id = [Link];

4. CASE Statement

Used for conditional logic.

Example:

SELECT name,

CASE

WHEN salary > 5000 THEN 'High'

WHEN salary > 3000 THEN 'Medium'

ELSE 'Low'

END AS salary_band

FROM Employees;

5. COALESCE & IFNULL

COALESCE returns the first non-null value.

Example:

SELECT name, COALESCE(email, phone, 'No Contact') AS contact FROM Users;

IFNULL (MySQL-specific):

SELECT name, IFNULL(email, 'No Email') FROM Users;


SQL Guide: GROUP BY, HAVING, JOINS, and Advanced Clauses

6. Window Functions

Used for ranking and aggregating over partitions without grouping.

ROW_NUMBER(): Unique row numbers.

RANK(): Ranks with gaps.

DENSE_RANK(): Ranks without gaps.

Example:

SELECT name, salary,

RANK() OVER (ORDER BY salary DESC) AS rnk,

DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk,

ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num

FROM Employees;

Partition Example:

SELECT department, name, salary,

RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank

FROM Employees;

You might also like