Structured Query Language - II

1. Introduction to SQL Functions

A function in SQL is a special command used to perform a specific task that returns a value as a result. In SQL, functions are broadly categorised into two types based on how they process rows:

1. Single Row Functions (Scalar Functions): These operate on a single value and return a single value for every row in the result set. Examples include Math, String, and Date functions.

2. Multiple Row Functions (Aggregate Functions): These work on a set of records as a whole and return one single value for the entire group.

2. Date and Time Functions

Date functions allow you to manipulate and extract specific parts of date and time data stored in a database. In MySQL, dates are typically stored in the 'YYYY-MM-DD' format.

• NOW(): Returns the current system date and time.

◦ Example: SELECT NOW(); → Output: 2023-10-27 10:30:00

• DATE(): Extracts the date part from a given date/time expression.

◦ Example: SELECT DATE(NOW()); → Output: 2023-10-27

• MONTH(): Returns the month in numeric form (1 to 12) from a specified date.

    ◦ Example: SELECT MONTH('2023-10-27'); → Output: 10

• MONTHNAME(): Returns the full name of the month (e.g., January, October) from a date.

    ◦ Example: SELECT MONTHNAME('2023-10-27'); → Output: October

• YEAR(): Returns the four-digit year from a date.

    ◦ Example: SELECT YEAR('2023-10-27'); → Output: 2023

• DAY(): Returns the day of the month (1 to 31) from a date.

    ◦ Example: SELECT DAY('2023-10-27'); → Output: 27

• DAYNAME(): Returns the name of the day of the week (e.g., Monday, Friday).

    ◦ Example: SELECT DAYNAME('2023-10-27'); → Output: Friday

3. Aggregate Functions (Multiple Row Functions)

Aggregate functions perform a calculation on a set of values and return a single value. These functions are essential for summarizing data.

• MAX(column): Returns the largest value in the specified column.

• MIN(column): Returns the smallest value in the specified column.

• AVG(column): Calculates the average (mean) of the values in a column.

• SUM(column): Calculates the total sum of all values in a column.

• COUNT(column): Returns the number of non-NULL values in a specific column.

• COUNT(*): Returns the total number of records (rows) in a table, including those with NULL values.

Key difference: COUNT(column) ignores NULL values, whereas COUNT(*) counts every row regardless of its content.

4. Advanced Querying: GROUP BY, HAVING, and ORDER BY

To perform deeper analysis, SQL provides clauses to group and filter data based on aggregate results.

A. The GROUP BY Clause

The GROUP BY clause is used to group rows that have the same values in specified columns. It is almost always used with aggregate functions to produce a summary for each group.

• Example: To find the number of cars purchased by each customer: SELECT CustID, COUNT(*) FROM SALE GROUP BY CustID;

B. The HAVING Clause

While the WHERE clause filters individual rows before grouping, the HAVING clause is used to filter groups after they have been formed by GROUP BY. You cannot use aggregate functions inside a WHERE clause; you must use HAVING for that purpose.

• Example: To find customers who bought more than one car: SELECT CustID, COUNT(*) FROM SALE GROUP BY CustID HAVING COUNT(*) > 1;

C. The ORDER BY Clause

The ORDER BY clause is used to sort the result of a query in ascending (ASC, default) or descending (DESC) order based on one or more columns.

• Example: To list employees in descending order of salary: SELECT * FROM EMPLOYEE ORDER BY Salary DESC;

5. Operations on Relations (Set Theory)

In the relational model, tables are treated as sets. These binary operations merge results from two tables. For these to work, both tables must have the same number of attributes and matching data types (domains).

• UNION (∪): Combines the results of two queries into a single result set, removing duplicate rows.

    ◦ Example: Combining a list of DANCE participants and MUSIC participants to see everyone involved in either event.

• INTERSECT (∩): Returns only the rows that are common to both tables.

    ◦ Example: Finding students who participate in both DANCE and MUSIC.

• MINUS / SET DIFFERENCE (-): Returns rows that are present in the first table but not in the second.

    ◦ Example: Finding students who are in MUSIC but not in DANCE.

• CARTESIAN PRODUCT (X): Combines every row of the first table with every row of the second table.

    ◦ Degree: Sum of the degrees of both tables.

    ◦ Cardinality: Product of the cardinalities of both tables.

    ◦ Note: This operation is often the first step the DBMS takes when querying multiple tables before applying join conditions.

6. Joining Multiple Tables (JOIN)

A JOIN is used to combine rows from two or more tables based on a related column (usually a Primary Key in one table and a Foreign Key in another).

A. Cartesian Product vs. Join

A Cartesian Product gives all possible combinations (many of which are meaningless), whereas a JOIN uses a specific condition to filter only the related rows.

B. Writing a Join Query

Join conditions can be specified in the WHERE clause or explicitly using the JOIN keyword.

• Example using WHERE: SELECT * FROM UNIFORM U, COST C WHERE U.UCode = C.UCode;

• Example using JOIN ... ON: SELECT * FROM UNIFORM U JOIN COST C ON U.UCode = C.UCode;

C. NATURAL JOIN

A NATURAL JOIN is a type of join that automatically joins tables based on columns with the same name and data type in both tables. Unlike a standard join, it removes redundant columns from the result set (it only shows the common joining column once).

• Syntax: SELECT * FROM UNIFORM NATURAL JOIN COST;

7. Summary Table: Single Row vs. Multiple Row Functions