Single Row Functions

Introduction

This chapter moves beyond SELECT's basics into SQL's built-in functions — starting with the ones that do arithmetic on a number.

Concept

• POWER(x, y) — x raised to the power y.
• ROUND(x, d) — rounds x to d decimal places.
• MOD(x, y) — the remainder when x is divided by y, the SQL equivalent of Python's %.

Worked Example

SELECT POWER(2, 3);
SELECT ROUND(85.6789, 2);
SELECT MOD(17, 5);

POWER(2, 3) returns 8 (2 × 2 × 2). ROUND(85.6789, 2) returns 85.68 — rounded to two decimal places. MOD(17, 5) returns 2 — the remainder after 17 divides by 5 three whole times (15), leaving 2.

Note: ROUND(x, 0) rounds to a whole number, still displayed with SQL's own formatting — it doesn't change x's underlying data type the way a cast would.

Explanation

These three functions do genuinely different jobs: POWER computes a new value from an exponent, ROUND reduces a value's precision for display, and MOD extracts specifically the remainder — the same remainder-finding idea Python's % already taught you, just written as a function call in SQL instead of an operator.

Summary

POWER(x, y) computes x to the power y; ROUND(x, d) rounds to d decimal places; MOD(x, y) returns the remainder of x divided by y — SQL's built-in math functions, called directly inside a query.

/* ==================================================
   SYSTEM SETUP (Creating Dummy Data)
   ================================================== */
DROP TABLE IF EXISTS NUMBERS;
CREATE TABLE NUMBERS (A INT, B INT, Price DECIMAL(10,3));
INSERT INTO NUMBERS VALUES (2, 3, 50.456);
INSERT INTO NUMBERS VALUES (10, 2, 99.123);
INSERT INTO NUMBERS VALUES (5, 3, 12.999);

/* ==================================================
   TASK: Apply Math Functions
   ================================================== */
SELECT 
    A, 
    B, 
    POWER(A, B) AS Power_Result,   -- 2^3 = 8
    Price,
    ROUND(Price, 1) AS Rounded_Price -- 50.456 -> 50.5
FROM NUMBERS;

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS CALC;
CREATE TABLE CALC (Num1 INT, Num2 INT);
INSERT INTO CALC VALUES (10, 3);
INSERT INTO CALC VALUES (20, 4);
INSERT INTO CALC VALUES (25, 7);

/* ==================================================
   TASK: Calculate Remainder
   Note: In some SQL engines (like MySQL), we use MOD(a,b). 
   In SQLite/Postgres, we often use the % symbol (Num1 % Num2).
   Below is the standard syntax.
   ================================================== */
SELECT 
    Num1, 
    Num2, 
    Num1 % Num2 AS Remainder -- Using % symbol for broad compatibility
FROM CALC;

Introduction

This topic covers SQL's built-in functions for text — changing case, measuring length, and removing unwanted surrounding spaces.

Concept

• UPPER(text) / LOWER(text) — converts to uppercase / lowercase.
• LENGTH(text) — the number of characters in text.
• TRIM(text) — removes leading and trailing spaces.

Worked Example

SELECT UPPER(Name) FROM STUDENT WHERE RollNo = 1;
SELECT LENGTH(Name) FROM STUDENT WHERE RollNo = 1;
SELECT TRIM('  Aisha  ');

UPPER(Name) for Aisha's row returns 'AISHA'. LENGTH(Name) for the same row returns 5 — the number of characters in "Aisha". TRIM(' Aisha ') returns 'Aisha', with the surrounding spaces removed.

Note: These functions can be used directly on a literal value (like TRIM(' Aisha ') above) or on a column from a table — both are valid, exactly like Python's str methods work the same way on a literal string or a variable holding one.

Explanation

TRIM() matters more than it looks: data typed by hand into a form often carries accidental extra spaces, which would make WHERE Name = 'Aisha' fail to match a stored 'Aisha ' with a trailing space — TRIM() is exactly the tool for cleaning that up before comparing or displaying text.

Summary

UPPER()/LOWER() convert case; LENGTH() counts characters; TRIM() removes leading and trailing spaces — SQL's basic text-cleaning functions, usable on a column or a literal string alike.

/* ==================================================
   SYSTEM SETUP (Messy Data)
   ================================================== */
DROP TABLE IF EXISTS USERS;
CREATE TABLE USERS (Name VARCHAR(20), Email VARCHAR(30));
INSERT INTO USERS VALUES ('Amit Kumar', 'AMIT@GMAIL.COM');
INSERT INTO USERS VALUES ('ravi sharma', 'Ravi.S@Yahoo.com');

/* ==================================================
   TASK: Standardize Display
   ================================================== */
SELECT 
    UPPER(Name) AS Clean_Name,    -- 'AMIT KUMAR'
    LOWER(Email) AS Clean_Email,  -- 'amit@gmail.com'
    LENGTH(Name) AS Name_Length   -- 10
FROM USERS;

/* ==================================================
   SYSTEM SETUP (Dirty Data with Spaces)
   Notice the spaces inside the quotes
   ================================================== */
DROP TABLE IF EXISTS DATA;
CREATE TABLE DATA (TextVal VARCHAR(20));
INSERT INTO DATA VALUES ('   Ravi   '); -- 3 spaces left, 3 spaces right

/* ==================================================
   TASK: Compare Lengths
   ================================================== */
SELECT 
    TextVal, 
    LENGTH(TextVal) AS Original_Len,  -- Should be 3+4+3 = 10
    TRIM(TextVal) AS Cleaned_Text,    -- 'Ravi'
    LENGTH(TRIM(TextVal)) AS Clean_Len -- Should be 4
FROM DATA;

Introduction

This topic continues SQL's text functions — pulling out part of a string, and finding where one string sits inside another.

Concept

• SUBSTRING(text, start, length) — extracts length characters starting at position start (SQL text positions start at 1, not 0).
• INSTR(text, substring) — returns the position where substring first occurs inside text, or 0 if it isn't found.
• CONCAT(text1, text2, ...) — joins strings together.

Worked Example

SELECT SUBSTRING('Chemistry', 1, 4);
SELECT INSTR('Chemistry', 'ist');
SELECT CONCAT('Aisha', ' - ', 'Science');

SUBSTRING('Chemistry', 1, 4) returns 'Chem' — four characters, starting from position 1. INSTR('Chemistry', 'ist') returns 5 — 'ist' begins at the fifth character of 'Chemistry'. CONCAT('Aisha', ' - ', 'Science') returns 'Aisha - Science'.

Warning: SQL's SUBSTRING counts positions starting at 1, not 0 — unlike Python's zero-based indexing you already know. SUBSTRING('Chemistry', 1, 4) starts at the very first character, not the second.

Explanation

This off-by-one difference from Python is worth deliberately noticing, precisely because it's the kind of detail that's easy to get wrong on an exam by instinctively applying Python's zero-based habit to SQL, where positions genuinely start counting from 1.

Summary

SUBSTRING(text, start, length) extracts part of a string, counting positions from 1; INSTR(text, substring) finds where a substring first occurs (0 if absent); CONCAT(...) joins strings together.

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS ITEMS;
CREATE TABLE ITEMS (Code VARCHAR(20));
INSERT INTO ITEMS VALUES ('NB-2024-X');

/* ==================================================
   TASK: Slice the Code
   Note: SQL Indexing starts at 1, not 0.
   SUBSTR(String, Start_Pos, Length)
   ================================================== */
SELECT 
    Code,
    LEFT(Code, 2) AS Category,      -- 'NB'
    RIGHT(Code, 1) AS Version,      -- 'X'
    SUBSTR(Code, 4, 4) AS Year      -- Starts at 4th char, takes 4 chars -> '2024'
FROM ITEMS;

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS USERS;
CREATE TABLE USERS (Email VARCHAR(30), Log VARCHAR(50));
INSERT INTO USERS VALUES ('student@school.com', 'Login Successful');
INSERT INTO USERS VALUES ('admin@gov.in', 'System Error: Disk Full');

/* ==================================================
   TASK: Find Positions
   ================================================== */
SELECT 
    Email,
    INSTR(Email, '@') AS At_Symbol_Location, -- Returns 8 for student@...
    Log,
    INSTR(Log, 'Error') AS Error_Location    -- Returns 8 for 2nd row, 0 for 1st
FROM USERS;

Introduction

This topic covers SQL's built-in functions for working with dates — getting the current date, and pulling out a specific part of one.

Concept

• NOW() — returns the current date and time.
• YEAR(date) — extracts just the year from a date value.
• MONTH(date) — extracts just the month.
• DAYNAME(date) — returns the day of the week as a name ('Monday', 'Tuesday', ...).

Worked Example

SELECT YEAR('2026-08-07');
SELECT DAYNAME('2026-08-07');

YEAR('2026-08-07') returns 2026, extracting only the year portion of that date. DAYNAME('2026-08-07') returns the name of whichever weekday that calendar date actually falls on.

Note: NOW() always returns the database server's current date and time at the moment the query runs — its result is never fixed, exactly like Python's random module from Class 11, where the same call gives a different result depending on when (or, there, how) it's actually run.

Explanation

Extracting just one part of a date — a year, a month, a weekday name — is exactly what makes it possible to filter or group records by a specific time period (every record from 2026, every Monday) without needing to compare full, exact date-and-time values.

Summary

NOW() returns the current date and time; YEAR(), MONTH(), and DAYNAME() extract a specific part from a given date value — useful for filtering or grouping data by a period rather than an exact date.

/* ==================================================
   TASK: Check System Time
   Note: We use standard syntax here.
   ================================================== */
SELECT 
    DATETIME('now') AS Current_Time_NOW,  -- Exam uses NOW()
    DATE('now') AS Current_Date_Only;     -- Exam uses CURDATE()

/* ==================================================
   SYSTEM SETUP
   ================================================== */
DROP TABLE IF EXISTS PROJECTS;
CREATE TABLE PROJECTS (ProjectName VARCHAR(20), DueDate DATE);
INSERT INTO PROJECTS VALUES ('Science Fair', '2024-08-15');

/* ==================================================
   TASK: Extract Details
   In Board Exams (MySQL), you would write:
   SELECT MONTHNAME(DueDate), DAYNAME(DueDate) FROM PROJECTS;
   
   (The code below simulates this logic for our viewer)
   ================================================== */
SELECT 
    ProjectName,
    DueDate,
    STRFTIME('%Y', DueDate) AS Year_Result,    -- Returns 2024
    STRFTIME('%m', DueDate) AS Month_Result,   -- Returns 08
    -- Simulating MONTHNAME for the viewer
    CASE STRFTIME('%m', DueDate) 
        WHEN '08' THEN 'August' 
    END AS MonthName_Result
FROM PROJECTS;

Introduction

Every function so far in this chapter computed a result from one row at a time. This closing topic covers functions that summarise an entire column across many rows into one single result.

Concept

• SUM(column) — total of all values in the column.
• AVG(column) — average.
• COUNT(column) — how many non-NULL values; COUNT(\*) counts every row, NULL or not.
• MAX(column) / MIN(column) — largest / smallest value.

Worked Example

SELECT SUM(Marks) FROM STUDENT;
SELECT AVG(Marks) FROM STUDENT;
SELECT COUNT(*) FROM STUDENT;

SUM(Marks) adds every student's Marks value into one total. AVG(Marks) computes the class average in one call. COUNT(*) returns how many rows the STUDENT table has in total, regardless of whether any column happens to be empty.

Note: COUNT(column) and COUNT() can genuinely differ — COUNT(column) skips any row where that specific column is NULL, while COUNT() always counts every row.

Explanation

These are called aggregate functions specifically because they collapse many rows down into one single summary value — the same conceptual job as Python's sum()/len()/max() on a list, or Pandas' identically-named methods from earlier in this course, just performed by the database itself before any result even reaches your program.

Summary

SUM(), AVG(), COUNT(), MAX(), and MIN() summarise an entire column across many rows into one value — COUNT(column) skips NULLs, while COUNT(*) always counts every row — the same aggregation idea already met in Python and Pandas, now performed directly by SQL.

/* ==================================================
   SYSTEM SETUP (Sales Data)
   ================================================== */
DROP TABLE IF EXISTS SALES;
CREATE TABLE SALES (Item VARCHAR(20), Amount INT);
INSERT INTO SALES VALUES ('Laptop', 50000);
INSERT INTO SALES VALUES ('Mouse', 500);
INSERT INTO SALES VALUES ('Keyboard', 1500);
INSERT INTO SALES VALUES ('Monitor', 10000);

/* ==================================================
   TASK: Calculate Aggregates
   ================================================== */
SELECT 
    SUM(Amount) AS Total_Revenue,    -- 50000 + 500 + 1500 + 10000
    AVG(Amount) AS Average_Price,    -- Total / 4
    MAX(Amount) AS Most_Expensive,   -- 50000
    MIN(Amount) AS Cheapest          -- 500
FROM SALES;

/* ==================================================
   SYSTEM SETUP (Data with NULL)
   Ravi is absent (Marks is NULL)
   ================================================== */
DROP TABLE IF EXISTS EXAM;
CREATE TABLE EXAM (Name VARCHAR(20), Marks INT);
INSERT INTO EXAM VALUES ('Amit', 90);
INSERT INTO EXAM VALUES ('Sumit', 80);
INSERT INTO EXAM VALUES ('Ravi', NULL); -- Absent
INSERT INTO EXAM VALUES ('Pooja', 95);

/* ==================================================
   TASK: Compare Counts
   ================================================== */
SELECT 
    COUNT(*) AS Total_Students,       -- Returns 4 (Everyone)
    COUNT(Marks) AS Students_Present  -- Returns 3 (Ignores Ravi)
FROM EXAM;