Chapter 1: Querying and SQL Functions || Informatics Practices (IP) || Class 12th || NCERT CBSE || NOTES IN ENGLISH || 2024-25

Chapter 1: Querying and SQL Functions

Introduction to SQL Queries and Functions

SQL (Structured Query Language) is a powerful tool used to manage and query data stored in relational databases.
This chapter explores complex querying capabilities in SQL, including:
- Using functions to manipulate data in different ways.
- Grouping and aggregating data.
- Combining data from multiple tables.
The example CARSHOWROOM database contains tables like INVENTORY (car details), CUSTOMER (customer info), SALE (sales records), and EMPLOYEE (employee data), providing a practical context for applying SQL commands.

Functions in SQL

Functions in SQL perform specific operations and return results based on the input. SQL functions are essential in data analysis, helping transform, aggregate, and compute values across rows and tables. Functions in SQL are divided into:

1.2.1 Single Row Functions

Single row functions operate on each row independently, returning one result per row. These functions are also called scalar functions and can handle numeric, string, and date data types.

Numeric Functions

Numeric functions handle numeric data for operations like rounding, modular division, and exponentiation.
- ROUND(column, decimal_places): Rounds the value to a specified number of decimal places.
- POWER(base, exponent): Raises the base to the power of exponent, useful in calculations.
- MOD(number, divisor): Returns the remainder when a number is divided by a divisor, often used in cyclic calculations.
Example:
```
sql
SELECT ROUND(AVG(price), 2) FROM INVENTORY;
```

String Functions

String functions manipulate text data, allowing changes in case, extraction of substrings, and calculating string length.
- UCASE(column): Converts text to uppercase.
- LCASE(column): Converts text to lowercase.
- LENGTH(column): Returns the number of characters in a string.
- MID(column, start, length): Extracts a substring from the specified start position for a given length.
Example:
```
sql
SELECT UCASE(customer_name) FROM CUSTOMER;
```

Date Functions

Date functions allow retrieval and manipulation of date and time values.
- NOW(): Returns the current date and time.
- DATE(column): Extracts the date portion of a timestamp.
- MONTH(column), YEAR(column): Extract specific date parts for analysis.
Example:
```
sql
SELECT YEAR(sale_date) FROM SALE;
```

1.2.2 Aggregate Functions

Aggregate functions work on a set of rows and return a single summary result. These are essential for deriving insights from grouped data.

Key Aggregate Functions:

MAX(column): Retrieves the highest value in a column, useful for finding maximum prices, salaries, etc.
MIN(column): Retrieves the lowest value, helping locate minimum costs or quantities.
AVG(column): Calculates the average of numeric data, often used for average sales, prices, etc.
SUM(column): Adds up all values in a column, beneficial in calculating total sales or expenses.
COUNT(column): Counts non-null values, useful for tallying rows that meet specific criteria.

Example:

sql
SELECT COUNT(sale_id), AVG(sale_amount) FROM SALE;

GROUP BY in SQL

The GROUP BY clause groups rows sharing similar values into summary rows, allowing aggregate functions to act on each group.
Use Cases:
- Counting sales per customer.
- Summing revenue for each car model or payment method.
Example:
```
sql
SELECT customer_id, COUNT(*) FROM SALE GROUP BY customer_id;
```

HAVING Clause

Unlike WHERE, which filters rows before grouping, HAVING filters groups after aggregation.

Use Case: Find customers who made more than a certain number of purchases.

Example:

sql
SELECT customer_id, COUNT(*) FROM SALE GROUP BY customer_id HAVING COUNT(*) > 2;

Set Operations in SQL

Set operations allow for combining results from multiple tables. Common set operations include:

UNION: Combines unique rows from both tables into a single result, useful for combining lists from similar tables.
INTERSECT: Retrieves only rows that exist in both tables, highlighting common records.
MINUS (or EXCEPT in some SQL dialects): Finds rows in one table that are absent in another, aiding in comparison tasks.
Cartesian Product: Combines each row from one table with every row from another, resulting in all possible combinations.

Example (UNION):

sql
SELECT car_model FROM INVENTORY
UNION
SELECT car_model FROM SALE;

Using Two Relations in a Query

SQL allows combining data from two tables using Cartesian Product or JOIN operations.

Cartesian Product

Combines every row of one table with every row of another, resulting in all possible pairs.
Generally large, so it’s more efficient when followed by filters to extract meaningful results.
Example:
```
sql
SELECT * FROM CUSTOMER, SALE;
```

JOIN Operation

JOINs retrieve related data from two or more tables by linking rows with matching values in specified columns.
INNER JOIN: Retrieves rows that have matching values in both tables, useful in cases where only related data is needed.
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table.
RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.

NATURAL JOIN: Automatically joins tables on columns with the same name, removing duplicate columns.

Example (INNER JOIN):

sql
SELECT CUSTOMER.customer_id, SALE.sale_amount
FROM CUSTOMER
INNER JOIN SALE ON CUSTOMER.customer_id = SALE.customer_id;

Summary

Single Row Functions: Useful for individual row transformations and include numeric, string, and date functions.
Aggregate Functions: Summarize data across rows using functions like SUM, COUNT, and AVG.
GROUP BY and HAVING: Group data to apply aggregate functions and filter groups based on aggregate results.
Set Operations: Combine results from multiple tables using operations like UNION, INTERSECT, and MINUS.
JOINs: Allow for combining data from multiple tables based on matching values to efficiently retrieve related information.