Scribd
Upload
5 views4 pages

Advanced SQL: Filtering & Aggregates

The document provides an overview of advanced data retrieval techniques in SQL, focusing on filtering, sorting, and aggregation. It includes syntax and examples for using the WHERE clause, ORDER BY, aggregate functions, GROUP BY, and HAVING. Additionally, it presents exercises to practice these concepts using a 'books' table.

Uploaded by

sachinnayaka999
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 views4 pages

Advanced SQL: Filtering & Aggregates

The document provides an overview of advanced data retrieval techniques in SQL, focusing on filtering, sorting, and aggregation. It includes syntax and examples for using the WHERE clause, ORDER BY, aggregate functions, GROUP BY, and HAVING. Additionally, it presents exercises to practice these concepts using a 'books' table.

Uploaded by

sachinnayaka999
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

Day 3: Advanced Data Retrieval - Filtering, Sorting, and Aggregates

### 1. Filtering Data Using WHERE Clause

The WHERE clause allows you to filter records based on specific conditions.

Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Example:

SELECT * FROM books WHERE price > 8;

Exercise:

1. Write a query to find all books by "George Orwell".

2. Find books priced between $7 and $10.

### 2. Sorting Data Using ORDER BY

ORDER BY sorts result sets by columns in ascending or descending order.

Syntax:

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 [ASC|DESC];

Example:
SELECT * FROM books ORDER BY price DESC;

Exercise:

1. Sort books by title alphabetically.

2. List books ordered by price ascending.

### 3. Aggregate Functions

Common aggregate functions include COUNT(), SUM(), AVG(), MIN(), MAX().

Example:

SELECT COUNT(*) AS total_books FROM books;

SELECT MAX(price) AS highest_price FROM books;

Exercise:

1. Find the sum of all book prices.

2. Calculate the average price.

### 4. Grouping Data Using GROUP BY

GROUP BY groups rows by common column values and is often used with aggregates.

Syntax:

SELECT column1, aggregate_function(column2)

FROM table_name

GROUP BY column1;

Example:

SELECT author, COUNT(*) AS books_count FROM books GROUP BY author;


Exercise:

1. Group books by price and count them.

### 5. Filtering Grouped Data Using HAVING

HAVING filters groups after they have been created.

Syntax:

SELECT column1, aggregate_function(column2)

FROM table_name

GROUP BY column1

HAVING condition;

Example:

SELECT author, COUNT(*) AS books_count FROM books GROUP BY author HAVING COUNT(*)

> 1;

Exercise:

1. Group books by author and show only those with more than 1 book.

### Complete Day 3 Exercises:

Scenario:

You are working with a 'books' table with columns: book_id, title, author, price.

1. Write a query to find books priced above $9.

2. Count the number of books each author has.


3. List books sorted by price (ascending).

4. Find the highest and lowest book prices.

5. Count total books and calculate the average price.

6. Find books whose titles start with 'T'.

7. Group books by author and show those with more than 2 books.

You might also like