Querying and SQL Functions

Written and reviewed by Rohit Singh, Computer Science & Commerce faculty at UniDrill (MCA, Osmania University · 6 years teaching). Based on NCERT Informatics Practices Class XII, Chapter 1.

How does a showroom owner get totals without counting by hand?

A car showroom has hundreds of sales. The owner wants the number of cars each customer bought, the average price of a model, the day of the week an employee joined, or the customer name next to each sale. Plain SELECT and WHERE cannot do all that. You need functions, which calculate on values, grouping, which summarises rows, and joins, which combine tables. This chapter adds all three to the SQL you learned in Class XI.

All the examples use a CARSHOWROOM database with four tables.

INVENTORY table.

SALE table.

The CUSTOMER table holds CustId, CustName, CustAdd, Phone and Email (four customers, C0001 to C0004), and EMPLOYEE holds EmpID, EmpName, DOB, DOJ, Designation and Salary (seven employees). Both appear in the examples below when needed.

Two kinds of functions

A quick test. Ask "if my table had 100 rows, how many answers do I get?" A single row function gives 100; an aggregate with no GROUP BY gives 1.

Numeric functions

Worked example: the EMI plan. The dealer adds 12 per cent tax to the price of car D001 (price 582613.00), then divides the amount into 10 EMIs that are multiples of 10000, with the leftover paid at once. Find the final price, the EMI and the leftover.

The full query is SELECT CarId, FinalPrice, ROUND((FinalPrice - MOD(FinalPrice, 10000))/10, 0) AS EMI, MOD(FinalPrice, 10000) AS Remaining FROM INVENTORY;. A common wrong step is dividing the whole final price by 10, which gives 65252.66, not a multiple of 10000. MOD is what strips off the leftover first.

String functions

String functions take text and return text or a number. Positions start at 1 in SQL, not 0 as in Python.

Worked example: user name from an email. Display the part of every customer's email before the @ sign.

The result for the four customers is amitsaha2, rehnuma, charvi123 and gur_singh. The wrong step is LEFT(Email, INSTR(Email, '@')), which keeps the @ sign itself.

Another worked example: area code. If a four digit area code starts at position 3 of the phone number, SELECT MID(Phone, 3, 4) FROM CUSTOMER WHERE CustAdd LIKE '%Rohini%'; gives 1163 for the phone 6811635425: skip two digits, then take four. TRIM can also remove a named string from the ends, so TRIM('.com' FROM Email) turns [email protected] into amitsaha2@gmail.

Beyond the textbook. For the exam: LENGTH returns the number of characters. In reality: in MySQL, LENGTH() counts bytes, and CHAR_LENGTH() counts characters. For plain English letters the two agree, but a name written in Devanagari, for example, takes several bytes per character. Why it matters: when a length looks too big on non English text, use CHAR_LENGTH.

Date and time functions

Worked example: employees who did not join on a Sunday.

The EMPLOYEE table has seven rows; three joining dates (2016-06-05, 2012-07-01 and 2017-01-01) fall on a Sunday, so four rows survive. Here a single row function is used inside WHERE, something an aggregate function cannot do.

Aggregate functions

Aggregate functions summarise many rows into one value. Apart from COUNT(*), they skip NULL values.

For AVG, the filter WHERE Model = 'LXI' leaves two cars (582613 and 514000), so the average is $(582613 + 514000) / 2 = 548306.5$. COUNT(DISTINCT Model) is 6 because LXI and VXI each appear twice among 8 cars.

Pause and predict: a table MANAGER has four rows, and the MEMNAME column is AMIT, KAVREET, KAVITA and NULL. What do COUNT(MEMNAME) and COUNT(*) return?

Check your answer: COUNT(MEMNAME) is 3, because it skips the NULL. COUNT(*) is 4, because it counts rows, not values.

GROUP BY and HAVING

GROUP BY collects the rows that have the same value in a column into groups and applies an aggregate function to each group. HAVING then keeps only the groups that meet a condition.

Worked example: customers who bought more than one car.

Other useful forms from the same SALE table:

Where versus having. WHERE filters rows before grouping, and HAVING filters groups after grouping.

The writing order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. A rule that helps with SELECT: when you use GROUP BY, the plain columns in SELECT should be the grouping columns, and everything else should be an aggregate.

Pause and predict: why does SELECT CustId, COUNT(*) FROM SALE WHERE COUNT(*) > 1 GROUP BY CustId; fail?

Check your answer: WHERE is applied to single rows before any groups exist, so it cannot use an aggregate. The condition on the count belongs in HAVING.

Operations on relations

Three operations combine the rows of two tables: UNION, INTERSECT and MINUS (set difference). They are allowed only if both relations have the same number of attributes and corresponding attributes have the same domain. We use two class activity lists.

DANCE table.

MUSIC table.

The order matters for MINUS. A minus B is not the same as B minus A. The union count follows the formula $|A \cup B| = |A| + |B| - |A \cap B|$, which gives $4 + 5 - 2 = 7$.

Beyond the textbook. For the exam: the three operations are UNION, INTERSECT and MINUS. In reality: MySQL has long supported UNION, but INTERSECT and EXCEPT (the standard SQL name for MINUS, which MySQL does not use) only arrived in recent versions from 8.0.31. Other systems such as Oracle write MINUS directly. Why it matters: the idea is the same everywhere, only the keyword changes.

Cartesian product pairs every row of the first table with every row of the second. The result has degree = sum of the degrees and cardinality = product of the cardinalities. For DANCE (degree 3, cardinality 4) and MUSIC (degree 3, cardinality 5), the product has degree $3 + 3 = 6$ and cardinality $4 \times 5 = 20$.

Using two tables in one query

Listing two tables in FROM separated by a comma applies a Cartesian product first. SELECT * FROM DANCE, MUSIC; therefore returns all 20 pairs. A condition in WHERE then keeps only the meaningful pairs.

The result keeps the 2 pairs where the names agree (Mahira and Sanjay). Here D and M are table aliases; once you alias a table, you must use the alias, not the original name, inside that query. Any column found in both tables, such as Name, needs the alias prefix to remove ambiguity.

JOIN pairs only the rows whose related columns match, which is what you almost always want. Usually one column is a primary key in one table and a foreign key in the other. Take UNIFORM(UCode, UName, UColor) with three rows (1 Shirt White, 2 Pant Grey, 3 Tie Blue) and COST(UCode, Size, Price) with four rows (1 L 580, 1 M 500, 2 L 890, 2 M 810).

The first two give the same table with UCode shown twice. NATURAL JOIN shows it once.

Worked example: size the results. UNIFORM has 3 rows and COST has 4 rows.

The Tie, with UCode 3, has no cost row, so it disappears from the join. The wrong answer is 12 for a join; that is the Cartesian product, with no matching condition applied.

A bigger join from CARSHOWROOM. To show each customer's name with the price they paid:

This returns 6 rows, one per sale. Add GROUP BY CustName with SUM(SalePrice) and you get totals per customer: AmitSaha 1273229, Rehnuma 1210535, CharviNayyar 369310 and Gurpreet 604000.

To join N tables on equality you need N minus 1 joins. Joining three tables needs two. With JOIN and ON you may also use operators other than equals.

Beyond the textbook. For the exam: NATURAL JOIN removes the repeated column. In reality: it matches on every column that has the same name in both tables, and if two tables later gain another column with the same name, the join silently changes. Explicit JOIN ... ON states exactly which columns you mean. Why it matters: in real projects, an explicit join condition is the safer habit.

Function sheet

Exam traps

60-second revision

Where this chapter connects

This chapter builds on the SELECT, WHERE and ORDER BY of Introduction to Structured Query Language (SQL) and the keys of Database Concepts; the foreign keys from that chapter are exactly the columns you join on. The same grouping and aggregation ideas return in Data Handling Using Pandas - I, where a DataFrame replaces the table. The other Class XII SQL chapter is Structured Query Language (SQL).