Querying and SQL Functions Chapter Notes | Informatics Practices for Class 12 - Humanities/Arts PDF Download

Introduction

• Building on Class XI knowledge, this chapter explores advanced SQL commands for querying databases.
• Focuses on performing various queries using single row functions, multiple row functions, sorting records in ascending or descending order, grouping records based on criteria, and working with multiple tables.
• Uses a database called CARSHOWROOM to illustrate concepts, which includes four relations:
  • INVENTORY: Stores details of cars in the showroom, including CarId, Car Name, Price, Model, Year of Manufacture, and Fuel Type.
  • CUSTOMER: Contains customer information such as Cust Id, Cust Name, Cust Add, Phone, and Email.
  • SALE: Records sales details, including InvoiceNo, CarId, CustId, SaleDate, Payment Mode, EmpId, and SalePrice.
  • EMPLOYEE: Holds employee data, including EmpId, Name, Date of Birth, Date of Joining, Designation, and Salary.
• The schema diagram of the CARSHOWROOM database outlines the relationships between these relations.

Functions in SQL

• SQL functions are used to perform specific tasks and return zero or more values.
• Functions are categorized based on their application:
  • Single Row Functions: Operate on a single value and return a single value (also called Scalar functions).
  • Aggregate Functions: Work on multiple rows and return a single value for a set of records.
• Single row functions are divided into three categories:
  • Numeric (Math) Functions: Accept numeric input and return numeric output.
  • String Functions: Accept character input and return character or numeric output.
  • Date and Time Functions: Accept date/time input and return numeric, string, or date/time output.

Numeric Functions

• POWER(X, Y) or POW(X, Y): Calculates X raised to the power Y. For example, POWER(2, 3) returns 8.
• ROUND(N, D): Rounds the number N to D decimal places. If D=0, it rounds to the nearest integer. For example, ROUND(2912.564, 1) returns 2912.6, and ROUND(283.2) returns 283.
• MOD(A, B): Returns the remainder after dividing A by B. For example, MOD(21, 2) returns 1.

String Functions

• UCASE(string) or UPPER(string): Converts a string to uppercase.
• LOWER(string) or LCASE(string): Converts a string to lowercase.
• MID(string, pos, n) or SUBSTRING(string, pos, n): Extracts a substring of length n starting from position pos. If n is not specified, it returns the substring from pos to the end.
• LENGTH(string): Returns the number of characters in a string.
• LEFT(string, N): Returns N characters from the left side of the string.
• RIGHT(string, N): Returns N characters from the right side of the string.
• INSTR(string, substring): Returns the position of the first occurrence of the substring in the string; returns 0 if the substring is not found.
• LTRIM(string): Removes leading whitespace characters from the string.
• RTRIM(string): Removes trailing whitespace characters from the string.
• TRIM(string): Removes both leading and trailing whitespace characters from the string.

Date and Time Functions

• NOW(): Returns the current system date and time. For example, it might return '2019-07-11 19:41:17'.
• DATE(): Extracts the date part from a date/time expression. For example, DATE(NOW()) returns '2019-07-11'.
• MONTH(date): Returns the month number (1-12) from a date. For example, MONTH(NOW()) might return 7.
• MONTHNAME(date): Returns the name of the month from a date. For example, MONTHNAME('2003-11-28') returns 'November'.
• YEAR(date): Returns the year from a date. For example, YEAR('2003-10-03') returns 2003.
• DAY(date): Returns the day of the month from a date. For example, DAY('2003-03-24') returns 24.
• DAYNAME(date): Returns the name of the day from a date. For example, DAYNAME('2019-07-11') returns 'Thursday'.

Aggregate Functions

Aggregate functions (multiple row functions) operate on a set of records and return a single value.

Key differences between single row and aggregate functions:

• Single row functions operate on one row at a time, while aggregate functions operate on multiple rows.
• Single row functions return one result per row, while aggregate functions return one result for a group of rows.
• Single row functions can be used in SELECT, WHERE, and ORDER BY clauses, while aggregate functions are typically used in SELECT and HAVING clauses.
• Examples of single row functions include Math, String, and Date functions, while aggregate functions include MAX, MIN, AVG, SUM, and COUNT.

Common aggregate functions:

• MAX(column): Returns the largest value in the specified column. For example, MAX(Price) from INVENTORY returns 673112.00.
• MIN(column): Returns the smallest value in the specified column. For example, MIN(Price) from INVENTORY returns 355205.00.
• AVG(column): Returns the average of the values in the specified column. For example, AVG(Price) from INVENTORY returns 576091.625000.
• SUM(column): Returns the sum of the values in the specified column. For example, SUM(Price) from INVENTORY returns 4608733.00.

Group By in SQL

• The GROUP BY clause groups rows with similar values in a specified column into summary rows.
• It is used with aggregate functions (e.g., COUNT, MAX, MIN, AVG, SUM) to produce a single value for each group.
• Commonly used to analyze data by categories, such as counting occurrences or calculating totals for each group.

Operations on Relations

• SQL supports operations like Union, Intersection, and Set Difference to combine or compare tuples from two tables.
• These are binary operations, requiring two tables with the same number of attributes and corresponding attributes having the same domain.

UNION (U):

• Combines selected rows from two tables, showing duplicate rows only once.
• For example, combining students from DANCE and MUSIC tables lists all students participating in either event.

INTERSECT (∩):

• Returns common tuples from two tables.
• For example, intersecting DANCE and MUSIC tables lists students participating in both events.

MINUS (-):

• Returns tuples present in the first table but not in the second.
• For example, DANCE - MUSIC lists students participating only in DANCE and not in MUSIC.

Cartesian Product (X):

• Combines all tuples from two tables, creating all possible pairs of rows.
• The resulting relation’s degree is the sum of the degrees of the input relations, and its cardinality is the product of their cardinalities.
• For example, applying Cartesian Product on DANCE (degree 3, cardinality 4) and MUSIC (degree 3, cardinality 5) results in a relation with degree 6 and cardinality 20.

Using Two Relations in a Query

• SQL allows querying multiple tables using operations like JOIN or Cartesian Product with conditions.

Table Aliases:

• Shortened names (e.g., D for DANCE, M for MUSIC) used to refer to tables in a query.
• Aliases are valid only for the current query, and the original table name cannot be used if an alias is defined in the FROM clause.

JOIN Operation:

• Combines tuples from two tables based on specified conditions, typically involving a primary key in one table and a foreign key in another.
• Unlike Cartesian Product, JOIN does not create all possible combinations but only those meeting the condition.
• For example, joining UNIFORM (UCode, UName, UColor) and COST (UCode, Size, Price) tables on UCode retrieves related tuples.

Types of JOIN Queries:

• Using WHERE Clause: Specifies the join condition in the WHERE clause (e.g., SELECT * FROM UNIFORM U, COST C WHERE U.UCode = C.UCode).
• Explicit JOIN Clause: Uses JOIN and ON clauses in the FROM clause (e.g., SELECT * FROM UNIFORM U JOIN COST C ON U.UCode = C.UCode).
• NATURAL JOIN: Automatically joins tables on common attributes, removing redundant columns (e.g., SELECT * FROM UNIFORM NATURAL JOIN COST).