Write the output produced by the following SQL statements: a) SELECT POW(2,3); b) SELECT ROUND(342.9234,-1); c

Exercises · Q2

Q. Write the output produced by the following SQL statements:

a) SELECT POW(2,3);

b) SELECT ROUND(342.9234,-1);

c) SELECT LENGTH("Informatics Practices");

d) SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), DAY("1979/11/26"), MONTHNAME("1979/11/26");

e) SELECT LEFT("INDIA",3), RIGHT("Computer Science",4), MID("Informatics",3,4), SUBSTR("Practices",3);

Tripura TbseTextbookSubjective· 3mImportance★★★★★

44% · 42/95 Questions

✓ Free question

Concept understanding — String Functions

String Functions: A First Look

Think of a string as a piece of text — a word, a sentence, a name, or even a single character. In computing, strings are everywhere: your name in a form, a product code in a database, a tweet, a paragraph in a document. String functions are the tools that let you work with that text: cut it, join it, clean it, search inside it, or change its appearance.

You already use these ideas in everyday life. When you take a long sentence and pick out just the first word, you are performing a "substring" operation. When you combine your first name and last name into a full name, you are "concatenating" two strings. When you check whether an email address contains an "@" symbol, you are "searching" within a string. String functions are just these familiar actions, given precise names and made available in software.

The Core Idea

A string function takes one or more strings as input and returns a new string or a piece of information about the string. The function does not change the original string — it produces a result based on it. This is important: the original data stays untouched unless you explicitly replace it.

Note

Strings are treated as sequences of characters. Each character — letter, digit, space, punctuation — occupies a position. In most systems, counting starts at 1 (the first character is position 1), though some languages start at 0. Always check which convention your tool uses.

Common Types of String Functions

1. Changing Case

These functions convert text to uppercase or lowercase. They are useful when you want to compare names or addresses without worrying about whether someone typed "Delhi" or "delhi".

UPPER or UCASE: converts all letters to capitals
LOWER or LCASE: converts all letters to small letters
PROPER or TITLE: capitalises the first letter of each word

2. Extracting Parts of a String

You often need to pull out a specific portion of text — the first name from a full name, the area code from a phone number, the year from a date.

LEFT: returns a given number of characters from the start
RIGHT: returns a given number of characters from the end
MID or SUBSTRING: returns characters starting from a specified position for a specified length

3. Finding and Replacing

These functions let you search inside text and optionally replace what you find.

FIND or INSTR: locates the position of one string inside another
REPLACE: substitutes all occurrences of a substring with another string
TRIM: removes extra spaces from the beginning and end of a string

4. Joining and Splitting

CONCAT or the ampersand (&): joins two or more strings end to end
SPLIT (in some systems): breaks a string into parts based on a delimiter (like a comma or space)

5. Measuring Length

LEN or LENGTH: returns the number of characters in a string

Why They Matter

In any real-world data task — whether you are cleaning a customer list, generating reports, validating user input, or preparing data for analysis — you will work with text. String functions give you the power to:

Standardise messy data (e.g., "john", "John", "JOHN" all become "John")
Extract meaningful pieces from longer text (e.g., get the domain from an email)
Combine information from different columns (e.g., first name + last name)
Check for errors or patterns (e.g., does a product code have exactly 10 characters?)
Remove unwanted characters or spaces

Important

String functions are case-sensitive by default in many systems. "Apple" and "apple" are different strings unless you explicitly convert them to the same case before comparing. This is a common source of bugs — always decide whether case matters for your task.

A Simple Example in Plain Language

Suppose you have a list of full names like "Ms. Priya Sharma". You want to extract just the last name. You would:

Find the position of the space after "Ms." — that is position 4
Find the position of the space after "Priya" — that is position 11
Use a substring function to extract characters from position 5 to position 10 (the first name)
Use a substring function to extract characters from position 12 onward (the last name)

Each of these steps uses a string function. The computer does not "understand" names — it just follows the rules you give it using these tools.

The Big Picture

String functions are not about memorising a list of names. They are about recognising that text is data, and data needs manipulation. Every time you need to clean, combine, search, or reshape text, you are reaching for a string function. Learn the logic — what each type does — and the specific names will follow naturally when you encounter them in a particular software or programming language.

Shape

This is a code task requiring the output of five SQL statements that use built-in functions (numeric, string, and date).

(a) `SELECT POW(2,3);`

POW(base, exponent) returns $base^{exponent}$ . Here $2^3 = 8$ .

SELECT POW(2,3);

Output:

POW(2,3)
8

(b) `SELECT ROUND(342.9234,-1);`

ROUND(number, decimals) rounds to the specified number of decimal places. A negative decimals value rounds to the left of the decimal point. -1 rounds to the nearest ten: $342.9234 \to 340$ .

SELECT ROUND(342.9234,-1);

Output:

ROUND(342.9234,-1)
340

(c) `SELECT LENGTH("Informatics Practices");`

LENGTH(string) returns the number of characters, including spaces. "Informatics Practices" has 11 letters in "Informatics", 1 space, and 9 letters in "Practices" = 21 characters.

SELECT LENGTH("Informatics Practices");

Output:

LENGTH("Informatics Practices")
21

(d) `SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), DAY("1979/11/26"), MONTHNAME("1979/11/26");`

These date functions extract components from the date string "1979/11/26" (interpreted as 26 November 1979):

YEAR() → 1979
MONTH() → 11
DAY() → 26
MONTHNAME() → "November"

SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), DAY("1979/11/26"), MONTHNAME("1979/11/26");

Output:

YEAR("1979/11/26")	MONTH("1979/11/26")	DAY("1979/11/26")	MONTHNAME("1979/11/26")
1979	11	26	November

(e) `SELECT LEFT("INDIA",3), RIGHT("Computer Science",4), MID("Informatics",3,4), SUBSTR("Practices",3);`

Four substring functions:

LEFT(string, n) returns the leftmost n characters: "INDIA" → "IND".
RIGHT(string, n) returns the rightmost n characters: "Computer Science" → "ence".
MID(string, start, length) extracts length characters starting at position start (1-indexed): "Informatics" from position 3 for 4 characters → "form".
SUBSTR(string, start) extracts from position start to the end (1-indexed): "Practices" from position 3 → "actices".

SELECT LEFT("INDIA",3), RIGHT("Computer Science",4), MID("Informatics",3,4), SUBSTR("Practices",3);

Output:

LEFT("INDIA",3)	RIGHT("Computer Science",4)	MID("Informatics",3,4)	SUBSTR("Practices",3)
IND	ence	form	actices

✓Final answer

The outputs are: (a) 8; (b) 340; (c) 21; (d) 1979, 11, 26, November; (e) IND, ence, form, actices.

These SQL statements demonstrate built-in string, numeric, and date functions that manipulate or extract information from values without querying any table.

Why These Functions Matter

SQL provides a rich library of built-in functions that operate on literals (constant values) or column data. When you SELECT a function with a literal argument and no FROM clause, SQL evaluates the function immediately and returns the result as a single-row output. These functions are essential for data transformation, formatting reports, and extracting meaningful information from stored values.

Let's trace each statement and understand what every function does.

(a) `SELECT POW(2,3);`

Function: POW(base, exponent) raises the base to the power of the exponent.

Here, $2^3 = 2 \times 2 \times 2 = 8$ .

Output:

+----------+
| POW(2,3) |
+----------+
|        8 |
+----------+

(b) `SELECT ROUND(342.9234,-1);`

Function: ROUND(number, decimals) rounds a number to a specified number of decimal places. When decimals is negative, rounding happens to the left of the decimal point.

ROUND(342.9234, -1) rounds to the nearest ten.
The digit in the tens place is 4. The digit to its right (units place) is 2, which is less than 5, so we round down.
Result: $340.0$ (displayed as 340 in most SQL engines).

Output:

+------------------------+
| ROUND(342.9234,-1)     |
+------------------------+
|                    340 |
+------------------------+

Tip

Negative precision in ROUND is powerful for aggregating data to the nearest hundred, thousand, etc. — useful in financial or statistical summaries.

(c) `SELECT LENGTH("Informatics Practices");`

Function: LENGTH(string) returns the number of characters in the string, including spaces.

Count the characters in "Informatics Practices": "Informatics" has 11 letters, then 1 space, then "Practices" has 9 letters.

Total: 21 characters (11 in "Informatics", 1 space, 9 in "Practices").

Output:

+----------------------------------+
| LENGTH("Informatics Practices")  |
+----------------------------------+
|                               21 |
+----------------------------------+

(d) `SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), DAY("1979/11/26"), MONTHNAME("1979/11/26");`

Functions: These extract components from a date string in YYYY/MM/DD format.

YEAR("1979/11/26") → 1979
MONTH("1979/11/26") → 11 (November is the 11th month)
DAY("1979/11/26") → 26
MONTHNAME("1979/11/26") → "November" (the full name of the month)

Output:

+---------------------+----------------------+--------------------+-----------------------------+
| YEAR("1979/11/26")  | MONTH("1979/11/26")  | DAY("1979/11/26")  | MONTHNAME("1979/11/26")     |
+---------------------+----------------------+--------------------+-----------------------------+
|                1979 |                   11 |                 26 | November                    |
+---------------------+----------------------+--------------------+-----------------------------+

Note

MONTHNAME returns the locale-dependent full month name. In English environments, November; in other locales, the translated equivalent.

(e) `SELECT LEFT("INDIA",3), RIGHT("Computer Science",4), MID("Informatics",3,4), SUBSTR("Practices",3);`

These are substring extraction functions.

`LEFT("INDIA", 3)`

Extracts the leftmost 3 characters from "INDIA".

Result: "IND"

`RIGHT("Computer Science", 4)`

Extracts the rightmost 4 characters from "Computer Science".

The string ends with "...ence", so the last 4 are: "ence"

`MID("Informatics", 3, 4)`

MID(string, start, length) extracts a substring starting at position start (1-indexed) for length characters.

Start at position 3 of "Informatics": the 3rd character is 'f'.
Extract 4 characters: 'f', 'o', 'r', 'm'.
Result: "form"

`SUBSTR("Practices", 3)`

SUBSTR(string, start) extracts from position start to the end of the string.

Start at position 3 of "Practices": the 3rd character is 'a'.
Extract from 'a' to the end: "actices".
Result: "actices"

Output:

+-------------------+-------------------------------+---------------------------+------------------------+
| LEFT("INDIA",3)   | RIGHT("Computer Science",4)   | MID("Informatics",3,4)    | SUBSTR("Practices",3)  |
+-------------------+-------------------------------+---------------------------+------------------------+
| IND               | ence                          | form                      | actices                |
+-------------------+-------------------------------+---------------------------+------------------------+

Watch out

SQL string positions are 1-indexed, not 0-indexed like Python or C. MID("ABC", 1, 1) returns "A", not "B".

✓Final answer

(a) 8 (b) 340 (c) 21 (d) 1979 | 11 | 26 | November (e) IND | ence | form | actices

←Previous questionQ1 — Answer the following questions: a) Define RDBMS. Name any two RDBMS so…→Next questionQ3 — Consider the following MOVIE table and write the SQL queries based on …

Q.Write the output produced by the following SQL statements:

Why These Functions Matter

(a) SELECT POW(2,3);

(b) SELECT ROUND(342.9234,-1);

(c) SELECT LENGTH("Informatics Practices");

(d) SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), DAY("1979/11/26"), MONTHNAME("1979/11/26");

(e) SELECT LEFT("INDIA",3), RIGHT("Computer Science",4), MID("Informatics",3,4), SUBSTR("Practices",3);

LEFT("INDIA", 3)

RIGHT("Computer Science", 4)

MID("Informatics", 3, 4)

SUBSTR("Practices", 3)

🎓Unlock everything free for 14 days

(a) `SELECT POW(2,3);`

(b) `SELECT ROUND(342.9234,-1);`

(c) `SELECT LENGTH("Informatics Practices");`

(d) `SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), DAY("1979/11/26"), MONTHNAME("1979/11/26");`

(e) `SELECT LEFT("INDIA",3), RIGHT("Computer Science",4), MID("Informatics",3,4), SUBSTR("Practices",3);`

`LEFT("INDIA", 3)`

`RIGHT("Computer Science", 4)`

`MID("Informatics", 3, 4)`

`SUBSTR("Practices", 3)`

Unlock everything free for 14 days