Checkpoint 6.

Question 1
Which function can be used to concatenate two strings ?
Answer
The CONCAT() function is used to concatenate two strings in a query result.
Question 2
Which function(s) can be used for extracting a substrings ?
Answer
The SUBSTRING()/SUBSTR() and MID() functions can be used for extracting a substrings.
Question 3
What is the difference between Trim() and Rtrim() ?
Answer
The difference between the TRIM() and RTRIM() functions is that the TRIM() function removes both leading
and trailing spaces from a given string, performing the combined functions of LTRIM() and RTRIM(). On the
other hand, the RTRIM() function only removes trailing spaces, i.e., spaces from the right side of the given string.
Question 4
What is the difference between round() and truncate() ?
Answer
The difference between the ROUND() and TRUNCATE() functions is that the ROUND() function returns a number
rounded off according to the given specifications. On the other hand, the TRUNCATE() function returns a number
with some digits truncated. The TRUNCATE() function simply removes the specified digits without rounding them
off.
Question 5
Which function returns the current date and current time ?
Answer
The NOW() function returns the current date and current time.
Question 6
What is the difference between sysdate() and now() functions ?
Answer
The difference between the SYSDATE() and NOW() functions is that the SYSDATE() function returns the exact
time at which the function executes. On the other hand, the NOW() function returns the current date and time at
the beginning of the statement execution.
Multiple Choice Questions

Question 1
A function working with every row of a table, is a ............... function.
1.​ Aggregate
2.​ Single value
3.​ Single row
4.​ Summary
Answer
Single row
Reason — Single-row functions in SQL work with one row at a time and return a result for every row of a queried
table.
Question 2
Which of the following is not a text function ?
1.​ TRIM()
1 | 12_IP_Ch_6_MYSQL Functions HEMA E J
 2.​ TRUNCATE()
3.​ LEFT()
4.​ MID()
Answer
TRUNCATE()
Reason — TRUNCATE() is a numeric function that returns a number with some digits truncated, whereas
TRIM(), LEFT(), and MID() are text functions.
Question 3
Which of the following is not a numeric function ?
1.​ MOD
2.​ SIGN
3.​ MID
4.​ POW
Answer
MID
Reason — MID() is a text or string function that returns a substring starting from the specified position, whereas
MOD(), SIGN(), and POW() are numeric functions.
Question 4
Which of the following is not a date function ?
1.​ Month
2.​ Year
3.​ NOW
4.​ POW
Answer
POW
Reason — POW() is a numeric function that returns mn i.e., a number m raised to the nth power, whereas
MONTH(), YEAR(), and NOW() are date functions.
Question 5
Which of the following functions returns the substring from a given string ?
1.​ MID
2.​ INSTR
3.​ SUBSTR
4.​ CHAR
Answer
MID, SUBSTR
Reason — The SUBSTRING()/SUBSTR() and MID() functions can be used to extract substrings from a given
string.
Question 6
Which of the following functions returns the position of a substring in a given string ?
1.​ MID
2.​ INSTR
3.​ SUBSTR
4.​ CHAR
Answer
INSTR
Reason — The INSTR() function searches for given second string into the given first string and returns the
position.
Question 7

2 | 12_IP_Ch_6_MYSQL Functions HEMA E J

Which one of the following is not an aggregate function ?
1.​ ROUND()
2.​ SUM()
3.​ COUNT()
4.​ AVG()
Answer
ROUND()
Reason — Aggregate functions in SQL include AVG(), COUNT(), MAX(), MIN(), and SUM().
Question 8
Which of the following SQL functions does not belong to the Math functions category ?
1.​ POWER()
2.​ ROUND()
3.​ LENGTH()
4.​ MOD()
Answer
LENGTH()
Reason — LENGTH() is a text/string function that returns the length of a given string in bytes.
Question 9
Which of the following is not a valid aggregate function in MYSQL ?
1.​ COUNT()
2.​ SUM()
3.​ MAX()
4.​ LEN()
Answer
LEN()
Reason — Aggregate functions in SQL include AVG(), COUNT(), MAX(), MIN(), and SUM().
Question 10
What will be printed by the given query ?
SELECT LENGTH("WINNER");

1.​ 7
2.​ 6
3.​ 8
4.​ 9
Answer
6
Reason — The LENGTH() function in MySQL returns the length of the string specified as its argument in
characters. In the given query, the string "WINNER" has a length of 6 characters, so the LENGTH() function will
return 6.
Question 11
What will be returned by the given query ?
SELECT INSTR("INDIA", "DI");

1.​ 2
2.​ 3
3.​ -2
4.​ -3
Answer

3 | 12_IP_Ch_6_MYSQL Functions HEMA E J

3
Reason — The INSTR() function in MySQL returns the position of the first occurrence of a substring within a
string. The position of "DI" within "INDIA" is at the third position in the string "INDIA". Therefore, the INSTR()
function will return 3.
Question 12
If the substring is not present in a string, the INSTR() returns:
1.​ -1
2.​ 1
3.​ NULL
4.​ 0
Answer
0
Reason — When the substring is not found within the string, the INSTR() function in MySQL returns 0.
Question 13
What will be returned by the given query ?
SELECT concat("It", "was", "ok");
1.​ "It was ok"
2.​ "It wasok"
3.​ "Itwasok"
4.​ "Itwas ok"
Answer
"Itwasok"
Reason — The CONCAT() function in MySQL is used to concatenate multiple strings into a single string. When we
use SELECT CONCAT("It", "was", "ok");, it combines the strings "It", "was", and "ok" without any spaces
or additional characters between them.
Question 14
Predict the output of the following query :
SELECT LCASE(MONTHNAME('2023-03-05'));
1.​ May
2.​ March
3.​ may
4.​ march
Answer
march
Reason — The MONTHNAME() function in MySQL returns the name of the month for a given date. In this case, the
date '2023-03-05' corresponds to March, so MONTHNAME('2023-03-05') will return "March". Then LCASE()
function is used to convert the result to lowercase, resulting in "march" as the output.
Question 15
To remove the leading and trailing space from data values in a column of MySQL Table, we use
1.​ Left()
2.​ Right()
3.​ Trim()
4.​ Ltrim()
Answer
Trim()
Reason — The TRIM() function removes leading and trailing spaces from a given string. It performs combined
functions of LTRIM() and RTRIM().
Question 16
4 | 12_IP_Ch_6_MYSQL Functions HEMA E J
What will be returned by the given query ?
SELECT Round(153.669, 2);
1.​ 153.6
2.​ 153.66
3.​ 153.67
4.​ 153.7
Answer
153.67
Reason — The ROUND() function in MySQL is used to round a number to a specified number of decimal places.
In this query, ROUND(153.669, 2) rounds the number 153.669 to 2 decimal places, resulting in the output
153.67.
Question 17
What will be returned by the given query ?
SELECT Sign(26);
1.​ 1
2.​ -1
3.​ 0
4.​ none of these
Answer
1
Reason — The SIGN() function is used to determine the sign of a given number. It returns 1 if the number is
positive, -1 if the number is negative, and 0 if the number is zero. In this query, SIGN(26) represents a positive
number. Therefore, it will return 1 as the output.
Question 18
What will returned by the given query ?
SELECT Truncate(15.79, -1), Truncate(15.79, 0), Truncate(15.79, 1);
1.​ 15 15 15.7
2.​ 10 15.7 15.9
3.​ 10 15 15.7
4.​ 10 10 15.9
Answer
10 15 15.7
Reason — The TRUNCATE function in SQL truncates a number to a specified position. In the given query,
Truncate(15.79, -1) truncates 15.79 to the nearest multiple of 10, yielding 10. Truncate(15.79, 0)
truncates 15.79 to a whole number, giving 15. Finally, Truncate(15.79, 1) truncates 15.79 to one decimal
place, resulting in 15.7. Thus, the output of the query is 10, 15, and 15.7, making option 3 as the correct answer.
Question 19
What will be returned by the given query ?
SELECT month('2020-05-11');
1.​ 5
2.​ 11
3.​ May
4.​ November
Answer
5
Reason — The MONTH() function extracts the month component from a date passed. For the date '2020-05-11',
since the month is 05, it returns 5.
Question 20
5 | 12_IP_Ch_6_MYSQL Functions HEMA E J
Which of the following are correct aggregate functions in SQL ?
1.​ AVERAGE()
2.​ MAX()
3.​ COUNT()
4.​ TOTAL()
Answer
MAX(), COUNT()
Reason — Aggregate functions in SQL include AVG(), COUNT(), MAX(), MIN(), and SUM().
Question 21
Write the output of the following SQL command :
SELECT Round(49.88);
1.​ 49.88
2.​ 49.8
3.​ 49.0
4.​ 50
Answer
50
Reason — The ROUND() function rounds the numeric value "49.88" to the nearest whole number, which is 50.
Question 22
Predict the output of the following query :
SELECT MOD(9, 0);
1.​ 0
2.​ NULL
3.​ NaN
4.​ 9
Answer
NULL
Reason — The MOD() function returns NULL when the divisor is zero because division by zero is undefined in
SQL.
Question 23
The avg() function in MySQL is an example of ............... .
1.​ Math function
2.​ Text function
3.​ Date Function
4.​ Aggregate Function
Answer
Aggregate Function
Reason — The AVG() function is classified as an aggregate function because it performs a calculation on a set of
values to return a single value.
Question 24
An aggregate function performs a calculation on ............... and returns a single value.
1.​ single value
2.​ multiple values
3.​ no value
4.​ none of these
Answer
multiple values

6 | 12_IP_Ch_6_MYSQL Functions HEMA E J

Reason — An aggregate function performs a calculation on multiple rows and returns a summary result for a group
of rows.
Fill in the Blanks

Question 1
The functions that work with one row at a time are called single row functions.
Question 2
The functions that work with multiple rows and return aggregated result, are called multiple rows functions.
Question 3
The other name of multiple rows functions is aggregate functions.
Question 4
To get a substring of a string, other than Substr(), function mid() is also used.
Question 5
To get the day part of a date, Day() function is used.
Question 6
To get the day name from a date, Dayname() function is used.
Question 7
To remove a character from the right side of a string, Rtrim() function is used.
Question 8
To get the current date, Curdate() Sysdate function is used.
True/False Questions

Question 1
SQRT() is an aggregate function.
Answer
False
Reason — The SQRT() is a numeric function that calculates the square root of a given number.
Question 2
SUM() is an aggregate function.
Answer
True
Reason — The SUM() function is an aggregate function because it performs a calculation on a set of values and
returns the sum of values in a given column or expression.
Question 3
Truncate() is a text function.
Answer
False
Reason — The TRUNCATE() is a numeric function. It is used to truncate a number to a specified number of
decimal places.
Question 4
Length() is a numeric function.
Answer
False
Reason — The length() is a text function. It returns the length of a given string in bytes.
Question 5
Functions MID() and SUBSTR() do the same thing.
Answer
True
Reason — Both the functions MID() and SUBSTR() extract a substring from a given string.
7 | 12_IP_Ch_6_MYSQL Functions HEMA E J
Question 6
Date() and Day() return the same thing.
Answer
False
Reason — The DATE() function extracts the date part of the date or datetime expression, whereas the DAY()
function returns the day part of a date.
Question 7
INSTR() and SUBSTR() work identically.
Answer
False
Reason — The INSTR() function searches for the given second string into the given first string, while the
SUBSTR() function extracts a substring from a given string.
Assertions and Reasons

Question 1
Assertion. Single row functions when applied on a column in a table, yield multiple values equal to number of rows
in the table.
Reason. Single row functions work with individual rows and yield values accordingly.
1.​ Both A and R are true and R is the correct explanation of A.
2.​ Both A and R are true but R is not the correct explanation of A.
3.​ A is true but R is false.
4.​ A is false but R is true.
Answer
Both A and R are true and R is the correct explanation of A.
Explanation​
When a single row function is applied to a column in a table, it produces output for each row. Therefore, if there are
n rows in the table, the single row function will yield n values, one for each row, because single row functions work
with individual rows of a table.
Question 2
Assertion. Multi-row functions when applied on a column in a table, yield values which are not equal to number of
rows in the table.
Reason. Multiple-rows functions do not work with all the rows in the table.
1.​ Both A and R are true and R is the correct explanation of A.
2.​ Both A and R are true but R is not the correct explanation of A.
3.​ A is true but R is false.
4.​ A is false but R is true.
Answer
A is true but R is false.
Explanation​
Multiple row functions when applied on a column in a table, work on multiple rows together and return a summary
result for a group of rows. Hence, the result values are not equal to number of rows in the table.
Question 3
Assertion. Multiple rows functions when applied on a column in a table, yield values which are not equal to number
of rows in the table.
Reason. The multi-row functions work with data of multiple rows at a time and return aggregated value.
1.​ Both A and R are true and R is the correct explanation of A.
2.​ Both A and R are true but R is not the correct explanation of A.
3.​ A is true but R is false.
4.​ A is false but R is true.

8 | 12_IP_Ch_6_MYSQL Functions HEMA E J

Answer
Both A and R are true and R is the correct explanation of A.
Explanation​
Multiple row functions, when applied to a column in a table, yield values that are not equal to the number of rows in
the table. This is because these functions work on multiple rows together and return a summary result or
aggregated value for a group of rows.
Question 4
Assertion. The count(*) will yield a single value while round() will yield number of values equal to the cardinality of
the table.
Reason. The count (*) is a multiple-rows function and round() is a single-row function.
1.​ Both A and R are true and R is the correct explanation of A.
2.​ Both A and R are true but R is not the correct explanation of A.
3.​ A is true but R is false.
4.​ A is false but R is true.
Answer
Both A and R are true and R is the correct explanation of A.
Explanation​
The count(*) function returns a single value, while round() will yield number of values equal to the cardinality
(rows) of the table. This is because count() operates on all rows in a table (or a group of rows), producing a
single result. In contrast, round() operates on each row individually, producing multiple results depending on the
cardinality of the table.
Type A: Short Answer Questions/Conceptual Questions

Question 1
Define a function.
Answer
A function is a special type of predefined command set that performs some operation and returns a single value.
Question 2(i)
Explain the UCASE() SQL function using suitable examples.
Answer
The UCASE()/UPPER() function converts the given string into uppercase. The syntax is UPPER(str) or
UCASE(str). It returns the argument str with all letters capitalized, and the return value has the same data type
as the argument str. For example,
SELECT UPPER('Large') "Uppercase";
Output
+-----------+
| Uppercase |
+-----------+
| LARGE |
+-----------+

Question 2(ii)
Explain the TRIM() SQL function using suitable examples.
Answer
The TRIM() function removes leading and trailing spaces from a given string. It performs the combined functions
of LTRIM() and RTRIM(). The syntax is:
TRIM([{BOTH|LEADING|TRAILING} [remstr] FROM] str) or TRM([remstr FROM] str).

9 | 12_IP_Ch_6_MYSQL Functions HEMA E J

It returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers (BOTH, LEADING,
or TRAILING) are given, BOTH is assumed. remstr is optional, and if not specified, spaces are removed. For
example,
SELECT TRIM(' Bar One ');
Output
+----------------------+
| TRIM(' Bar One ') |
+----------------------+
| Bar One |
+----------------------+

Question 2(iii)
Explain the MID() SQL function using suitable examples.
Answer
The MID() function returns a substring starting from the specified position. The syntax is:
MID(str, pos, len).
It returns a substring from str starting at position pos and containing len characters. For example,
SELECT MID('Quadratically', 5, 6);
Output
+---------------------------+
| MID('Quadratically', 5,6) |
+---------------------------+
| ratica |
+---------------------------+

Question 2(iv)
Explain the DAYNAME() SQL function using suitable examples.
Answer
The DAYNAME() function returns the name of weekday for date. The syntax is DAYNAME(date). For example,
SELECT DAYNAME('2024-05-20');
Output
+-----------------------+
| DAYNAME('2024-05-20') |
+-----------------------+
| Monday |
+-----------------------+

Question 2(v)
Explain the POWER() SQL function using suitable examples.
Answer
The POWER()/POW() function returns the value of m raised to the nth power, denoted as mn. The syntax is
POWER(m, n) or POW(m, n). Both m and n can be any numbers, but if m is negative, n must be an integer. For
example,
SELECT POWER(4, 2) "Raised";
Output
+--------+
| Raised |
10 | 12_IP_Ch_6_MYSQL Functions HEMA E J
+--------+
| 16 |
+--------+

Question 3(i)
What will be the output of following code ?
mysql> SELECT CONCAT(CONCAT('Inform', 'atics'), 'Practices');
Answer
Output
+------------------------------------------------+
| CONCAT(CONCAT('Inform', 'atics'), 'Practices') |
+------------------------------------------------+
| InformaticsPractices |
+------------------------------------------------+

Explanation
The CONCAT() function in SQL is used to concatenate two or more strings into a single string. In this query, the
inner CONCAT('Inform', 'atics') concatenates 'Inform' and 'atics' to produce 'Informatics'. The outer
CONCAT() then concatenates 'Informatics' with 'Practices' to produce 'InformaticsPractices'. Therefore, the final
output is 'InformaticsPractices'.
Question 3(ii)
What will be the output of following code ?
mysql> SELECT LCASE('INFORMATICS PRACTICES CLASS 11TH');
Answer
Output
+-------------------------------------------+
| LCASE('INFORMATICS PRACTICES CLASS 11TH') |
+-------------------------------------------+
| informatics practices class 11th |
+-------------------------------------------+

Explanation
The LCASE() function in SQL is used to convert all characters of a given string to lowercase. In the query,
LCASE('INFORMATICS PRACTICES CLASS 11TH') converts the entire string to 'informatics practices class
11th'. Therefore, the output is 'informatics practices class 11th'.
Question 3(iii)
What will be the output of following code ?
mysql> SELECT UCASE('Computer studies');
Answer
Output
+---------------------------+
| UCASE('Computer studies') |
+---------------------------+
| COMPUTER STUDIES |
+---------------------------+

Explanation
11 | 12_IP_Ch_6_MYSQL Functions HEMA E J
The UCASE() function in SQL is used to convert all characters of a given string to uppercase. In this query,
UCASE('Computer studies') converts the entire string to 'COMPUTER STUDIES'. Therefore, the output is
'COMPUTER STUDIES'.
Question 3(iv)
What will be the output of following code ?
mysql> SELECT CONCAT(LOWER('Class'), UPPER('xii'));
Answer
Output
+--------------------------------------+
| CONCAT(LOWER('Class'), UPPER('xii')) |
+--------------------------------------+
| classXII |
+--------------------------------------+

Explanation
In the query, LOWER('Class') converts 'Class' to lowercase, resulting in 'class', and UPPER('xii') converts
'xii' to uppercase, resulting in 'XII'. The CONCAT() function then combines these two results, producing the final
output 'classXII'.
Question 4
If Str = "INFORMATICS PRACTICES ...." and Str1 = "... FOR CLASS XI"
Write commands to print the output as 'informatics practices for class xi'
Answer
SELECT CONCAT(LCASE('INFORMATICS PRACTICES'), ' ', LCASE('FOR CLASS XI'));
Output
+--------------------------------------------------------------------+
| CONCAT(LCASE('INFORMATICS PRACTICES'), ' ', LCASE('FOR CLASS XI')) |
+--------------------------------------------------------------------+
| informatics practices for class xi |
+--------------------------------------------------------------------+

Question 5
Write commands to display the system date.
Answer
SELECT SYSDATE();
Output
+---------------------+
| SYSDATE() |
+---------------------+
| 2024-05-20 15:31:25 |
+---------------------+

Question 6
Write a command to display the name of current month.
Answer
SELECT MONTHNAME(CURDATE());
Output
+----------------------+
12 | 12_IP_Ch_6_MYSQL Functions HEMA E J
| MONTHNAME(CURDATE()) |
+----------------------+
| May |
+----------------------+

Question 7
Write SQL statement to display
Today, the date is <current date>

Answer
SELECT CONCAT('Today, the date is ', CURDATE()) AS CURDATE;
Output
+-------------------------------+
| CURDATE |
+-------------------------------+
| Today, the date is 2024-05-20 |
+-------------------------------+

Question 8
Write command to print the day of the week of your Birthday in the year 1999.
Answer
SELECT DAYOFWEEK('1999-07-21');
Output
+-------------------------+
| DAYOFWEEK('1999-07-21') |
+-------------------------+
| 4 |
+-------------------------+

Question 9
Write a command to display the current time.
Answer
SELECT TIME(NOW());
Output
+-------------+
| TIME(NOW()) |
+-------------+
| 15:40:57 |
+-------------+

Question 10
Consider two fields B_date, which stores the birth date and J_date, which stores the joining date of an employee.
Write commands to find out and display the approximate age of an employee as on today.
Answer
SELECT (YEAR(CURDATE()) - YEAR(B_DATE)) AS AGE FROM EMPLOYEE;
Question 11(i)
Find the output of the following SQL Query :
13 | 12_IP_Ch_6_MYSQL Functions HEMA E J
SELECT ROUND(7658.345, 2);

Answer
Output
+--------------------+
| ROUND(7658.345, 2) |
+--------------------+
| 7658.35 |
+--------------------+

Explanation
In the query, the ROUND function is used to round a number to 2 decimal places, resulting in 7658.35.
Question 11(ii)
Find the output of the following SQL Query :
SELECT MOD(ROUND (13.9, 0), 3);

Answer
Output
+------------------------+
| MOD(ROUND(13.9, 0), 3) |
+------------------------+
| 2 |
+------------------------+

Explanation
In the above query, 13.9 is first rounded to the nearest whole number (0 decimal places), resulting in 14 using the
ROUND function. Then, the MOD function computes the remainder of 14 divided by 3, resulting in 2. Therefore, the
output of the query is 2.
Question 12
Write the SQL functions which will perform the following operations :
(i) To display the name of the month of the current date.
(ii) To remove spaces from the beginning and end of a string, "Panorama".
(iii) To display the name of the day e.g., Friday or Sunday from your date of birth, dob.
(iv) To display the starting position of your first name(fname) from your whole name (name).
(v) To compute the remainder of division between two numbers, n1 and n2.
Answer
(i)
SELECT MONTHNAME(CURDATE());
Output
+----------------------+
| MONTHNAME(CURDATE()) |
+----------------------+
| May |
+----------------------+

(ii)
SELECT TRIM(" Panorama ");

14 | 12_IP_Ch_6_MYSQL Functions HEMA E J

Output
+----------------------+
| TRIM(" Panorama ") |
+----------------------+
| Panorama |
+----------------------+

(iii)
SELECT DAYNAME('2000-07-22');
Output
+-----------------------+
| DAYNAME('2000-07-22') |
+-----------------------+
| Saturday |
+-----------------------+

(iv)
SELECT INSTR('Gupta Ashwini', 'Ashwini') AS StartingPosition;
Output
+------------------+
| StartingPosition |
+------------------+
| 7 |
+------------------+

(v)
SELECT MOD(n1, n2);
Question 13
Write suitable SQL query for the following :
(i) Display 7 characters extracted from 7th left character onwards from the string 'INDIA SHINING'.
(ii) Display the position of occurrence of string 'COME' in the string 'WELCOME WORLD'.
(iii) Round off the value 23.78 to one decimal place.
(iv) Display the remainder of 100 divided by 9.
(v) Remove all the expected leading and trailing spaces from a column userid of the table 'USERS'.
Answer
(i)
SELECT SUBSTR('INDIA SHINING', 7, 7);
Output
+-------------------------------+
| SUBSTR('INDIA SHINING', 7, 7) |
+-------------------------------+
| SHINING |
+-------------------------------+

(ii)
SELECT INSTR('WELCOME WORLD', 'COME');
Output
15 | 12_IP_Ch_6_MYSQL Functions HEMA E J
+--------------------------------+
| INSTR('WELCOME WORLD', 'COME') |
+--------------------------------+
| 4 |
+--------------------------------+

(iii)
SELECT ROUND(23.78, 1);
Output
+-----------------+
| ROUND(23.78, 1) |
+-----------------+
| 23.8 |
+-----------------+

(iv)
SELECT MOD(100, 9);
Output
+-------------+
| MOD(100, 9) |
+-------------+
| 1 |
+-------------+

(v)
SELECT TRIM(userid) FROM USERS;
Type B: Short Answer Questions

Question 1
Based on the SQL table CAR_SALES, write suitable queries for the following :
NUMBER SEGMENT FUEL QT1 QT2

1 Compact HatchBack Petrol 56000 70000

2 Compact HatchBack Diesel 34000 40000

3 MUV Petrol 33000 35000

4 MUV Diesel 14000 15000

5 SUV Petrol 27000 54000

6 SUV Diesel 18000 30000

7 Sedan Petrol 8000 10000

8 Sedan Diesel 1000 5000

(i) Display fuel wise average sales in the first quarter.
(ii) Display segment wise highest sales in the second quarter.

16 | 12_IP_Ch_6_MYSQL Functions HEMA E J

(iii) Display the records in the descending order of sales in the second quarter.
Answer
(i)
SELECT FUEL, AVG(QT1) AS Avg_Sales_QT1
FROM CAR_SALES
GROUP BY FUEL;
Output
+--------+---------------+
| FUEL | Avg_Sales_QT1 |
+--------+---------------+
| Petrol | 31000.0000 |
| Diesel | 16750.0000 |
+--------+---------------+

(ii)
SELECT SEGMENT, MAX(QT2) AS HIGHEST_SALES
FROM CAR_SALES
GROUP BY SEGMENT;
Output
+-------------------+---------------+
| SEGMENT | HIGHEST_SALES |
+-------------------+---------------+
| Compact HatchBack | 70000 |
| MUV | 35000 |
| SUV | 54000 |
| Sedan | 10000 |
+-------------------+---------------+

(iii)
SELECT * FROM CAR_SALES
ORDER BY QT2 DESC;
Output
+--------+-------------------+--------+-------+-------+
| NUMBER | SEGMENT | FUEL | QT1 | QT2 |
+--------+-------------------+--------+-------+-------+
| 1 | Compact HatchBack | Petrol | 56000 | 70000 |
| 5 | SUV | Petrol | 27000 | 54000 |
| 2 | Compact HatchBack | Diesel | 34000 | 40000 |
| 3 | MUV | Petrol | 33000 | 35000 |
| 6 | SUV | Diesel | 18000 | 30000 |
| 4 | MUV | Diesel | 14000 | 15000 |
| 7 | Sedan | Petrol | 8000 | 10000 |
| 8 | Sedan | Diesel | 1000 | 5000 |
+--------+-------------------+--------+-------+-------+

Question 2
Predict the output of the following queries based on the table CAR_SALES given below :

17 | 12_IP_Ch_6_MYSQL Functions HEMA E J

 NUMBER SEGMENT FUEL QT1 QT2

1 Compact HatchBack Petrol 56000 70000

2 Compact HatchBack Diesel 34000 40000

3 MUV Petrol 33000 35000

4 MUV Diesel 14000 15000

5 SUV Petrol 27000 54000

6 SUV Diesel 18000 30000

7 Sedan Petrol 8000 10000

8 Sedan Diesel 1000 5000

(i) SELECT LEFT(SEGMENT, 2) FROM CAR_SALES WHERE FUEL= "PETROL";
(ii) SELECT (QT2-QT1)/2 "AVG SALE" FROM CAR_SALES WHERE SEGMENT= "SUV";
(iii) SELECT SUM(QT1) "TOT SALE" FROM CAR_SALES WHERE FUEL= "DIESEL";
Answer
(i) SELECT LEFT(SEGMENT, 2) FROM CAR_SALES WHERE FUEL= "PETROL";
Output
+------------------+
| LEFT(SEGMENT, 2) |
+------------------+
| Co |
| MU |
| SU |
| Se |
+------------------+

Explanation
In the query SELECT LEFT(SEGMENT, 2) FROM CAR_SALES WHERE FUEL= "PETROL";, the function
LEFT(SEGMENT, 2) takes the leftmost characters of each SEGMENT value, starting from the first character, and
returns two characters. The WHERE FUEL = 'PETROL' clause filters the rows to include only those with
'PETROL' as the fuel type.
(ii) SELECT (QT2-QT1)/2 "AVG SALE" FROM CAR_SALES WHERE SEGMENT= "SUV";
Output
+------------+
| AVG SALE |
+------------+
| 13500.0000 |
| 6000.0000 |
+------------+

Explanation
The SQL query SELECT (QT2-QT1)/2 "AVG SALE" FROM CAR_SALES WHERE SEGMENT= "SUV";
calculates the average sale for the "SUV" segment in the CAR_SALES table. It does this by subtracting the first

18 | 12_IP_Ch_6_MYSQL Functions HEMA E J

quarter sales (QT1) from the second quarter sales (QT2) for each record in the "SUV" segment and then dividing
the result by 2. The alias "AVG SALE" is assigned to the computed value.
(iii) SELECT SUM(QT1) "TOT SALE" FROM CAR_SALES WHERE FUEL= "DIESEL";
Output
+----------+
| TOT SALE |
+----------+
| 67000 |
+----------+

Explanation
The query SELECT SUM(QT1) "TOT SALE" FROM CAR_SALES WHERE FUEL= "DIESEL"; calculates the
total sales for the "DIESEL" fuel type in the CAR_SALES table. It does this by summing up the values in the QT1
column for rows where the FUEL column is equal to "DIESEL". The alias "TOT SALE" is assigned to the computed
sum.
Question 3
Given the following table :
Table : STUDENT1
No. Name Stipend Stream AvgMark Grade Class

1 Karan 400.00 Medical 78.5 B 12B

2 Divakar 450.00 Commerce 89.2 A 11C

3 Divya 300.00 Commerce 68.6 C 12C

4 Arun 350.00 Humanities 73.1 B 12C

5 Sabina 500.00 Nonmedical 90.6 A 11A

6 John 400.00 Medical 75.4 B 12B

7 Robert 250.00 Humanities 64.4 C 11A

8 Rubina 450.00 Nonmedical 88.5 A 12A

9 Vikas 500.00 Nonmedical 92.0 A 12A

10 Mohan 300.00 Commerce 67.5 C 12C

Give the output of following SQL statement :
(i) SELECT TRUNCATE(AvgMark) FROM Student1 WHERE AvgMark < 75 ;
(ii) SELECT ROUND(AvgMark) FROM Student1 WHERE Grade = 'B' ;
(iii) SELECT CONCAT(Name, Stream) FROM Student1 WHERE Class = '12A' ;
(iv) SELECT RIGHT(Stream, 2) FROM Student1 ;
Answer
(i) It will return error because no argument is passed as decimal places to truncate. Syntax of truncate function is
TRUNCATE(number, decimals).
(ii)
Output
+----------------+

19 | 12_IP_Ch_6_MYSQL Functions HEMA E J

| ROUND(AvgMark) |
+----------------+
| 78 |
| 73 |
| 75 |
+----------------+

(iii)
Output
+----------------------+
| CONCAT(Name, Stream) |
+----------------------+
| RubinaNonmedical |
| VikasNonmedical |
+----------------------+

(iv)
Output
+------------------+
| RIGHT(Stream, 2) |
+------------------+
| al |
| ce |
| ce |
| es |
| al |
| al |
| es |
| al |
| al |
| ce |
+------------------+

Question 4
Given the table LIBRARY :
No Title Author Type Pub Qty Price

1 Data Structure Lipschutz DS McGraw 4 217

2 Computer Studies French FND Galgotia 2 75

3 Advanced Pascal Schildt PROG McGraw 4 350

4 Dbase dummies Palmer DBMS PustakM 5 130

5 Mastering C + + Gurewich PROG BPB 3 295

6 Guide Network Freed NET ZPress 3 200

20 | 12_IP_Ch_6_MYSQL Functions HEMA E J

 7 Mastering Foxpro Seigal DBMS BPB 2 135

8 DOS guide Norton OS PHI 3 175

9 Basic for Beginners Morton PROG BPB 3 40

10 Mastering Window Cowart OS BPB 1 225

Give the output of following SQL commands on the basis of table Library.
(i) SELECT UPPER(Title) FROM Library WHERE Price < 150 ;
(ii) SELECT CONCAT(Author, Type) FROM Library WHERE Qty < 3 ;
(iii) SELECT MOD(Qty, 4) FROM Library ;
Answer
(i) SELECT UPPER(Title) FROM Library WHERE Price < 150 ;
Output
+---------------------+
| UPPER(Title) |
+---------------------+
| COMPUTER STUDIES |
| DBASE DUMMIES |
| MASTERING FOXPRO |
| BASIC FOR BEGINNERS |
+---------------------+

Explanation
The SQL query SELECT UPPER(Title) FROM Library WHERE Price < 150; returns the uppercase
version of the Title column for all rows in the LIBRARY table where the Price column is less than 150.
(ii) SELECT CONCAT(Author, Type) FROM Library WHERE Qty < 3 ;
Output
+----------------------+
| CONCAT(Author, Type) |
+----------------------+
| FrenchFND |
| SeigalDBMS |
| CowartOS |
+----------------------+

Explanation
The query SELECT CONCAT(Author, Type) FROM Library WHERE Qty < 3; concatenates the Author
and Type columns using the CONCAT() function from the LIBRARY table for books that have a quantity less than
3.
(iii) SELECT MOD(Qty, 4) FROM Library ;
Output
+-------------+
| MOD(Qty, 4) |
+-------------+
| 0 |
| 2 |
| 0 |

21 | 12_IP_Ch_6_MYSQL Functions HEMA E J

| 1 |
| 3 |
| 3 |
| 2 |
| 3 |
| 3 |
| 1 |
+-------------+

Explanation
The SQL query SELECT MOD(Qty, 4) FROM Library; calculates the remainder when each book's quantity
(Qty) in the LIBRARY table is divided by 4 using the MOD() function.
Question 5
Write a query to show the current date and time.
Answer
SELECT NOW();
Output
+---------------------+
| NOW() |
+---------------------+
| 2024-05-21 12:20:03 |
+---------------------+

Question 6
Perform the following question based on these tables :
table PAYDAY (contains one column only)

CycleDate DATE

table ADDRESS ( contains following eight columns)

LastName​ VARCHAR(25),
FirstName​ VARCHAR(25),
Street​ VARCHAR(50),
City​ VARCHAR(25)
State​ CHAR(2),
Zip​ NUMBER,
Phone​ VARCHAR(12),
Ext​ VARCHAR(5)

Write a query to show the city of user with first name as 'MARK'.
Answer
SELECT City
FROM Users
WHERE FirstName = 'MARK';
Question 7
Show via query how many days remain until Christmas. Round fractional days up using the numeric function
ROUND.
22 | 12_IP_Ch_6_MYSQL Functions HEMA E J
Answer
SELECT ROUND(DAYOFYEAR('2024-12-25') - DAYOFYEAR(CURDATE())) AS
DaysUntilChristmas;
Output
+--------------------+
| DaysUntilChristmas |
+--------------------+
| 218 |
+--------------------+

Question 8
Write the SQL queries which will perform the following operations :
(i) To display the year from your Date of Admission which is '2023-05-15'.
(ii) To convert your email id [email protected] to lowercase.
(iii) To remove leading spaces from a string 'my country'.
(iv) To display current date.
(v) To display the value of 106.
Answer
(i)
SELECT YEAR('2023-05-15');
Output
+--------------------+
| YEAR('2023-05-15') |
+--------------------+
| 2023 |
+--------------------+

(ii)
SELECT LCASE('[email protected]');
Output
+----------------------+
| LCASE('[email protected]') |
+----------------------+
| [email protected] |
+----------------------+

(iii)
SELECT LTRIM(' my country');
Output
+---------------------+
| LTRIM('my country') |
+---------------------+
| my country |
+---------------------+

(iv)
SELECT CURDATE();
23 | 12_IP_Ch_6_MYSQL Functions HEMA E J
Output
+------------+
| CURDATE() |
+------------+
| 2024-05-21 |
+------------+

(v)
SELECT POWER(10, 6);
Output
+--------------+
| POWER(10, 6) |
+--------------+
| 1000000 |
+--------------+

Question 9
Write a query against the EMPL table to show the names of all employees concatenated with their jobtypes.
Answer
SELECT CONCAT(NAME, JOBTYPE)
FROM EMPL;
Question 10
Write a query against the ADDRESS table to show the names (first name, last name) and phones of all persons
concatenated in following form :
TinaSeth23456789
MoradK.22211890

Table ADDRESS ( contains following eight columns)

LastName​ VARCHAR(25),
FirstName​ VARCHAR(25),
Street​ VARCHAR(50),
City​ VARCHAR(25)
State​ CHAR(2),
Zip​ NUMBER,
Phone​ VARCHAR(12),
Ext​ VARCHAR(5)

Answer
SELECT CONCAT(FIRSTNAME, LASTNAME, PHONE)
FROM ADDRESS;
Question 11
Write a query against the ADDRESS table to select a list of names and phone numbers. The output should match
these requirements :
●​ The name column should contain both the first and last names with a blank space between them. Use the
string concatenation.
●​ The second column will contain the phone number.
●​ Phone number should be in the format (999) 999-9999. Use the SUBSTR function and CONCAT.
●​ Order the query by last name then first name.
24 | 12_IP_Ch_6_MYSQL Functions HEMA E J
Table ADDRESS ( contains following eight columns)
LastName​ VARCHAR(25),
FirstName​ VARCHAR(25),
Street​ VARCHAR(50),
City​ VARCHAR(25)
State​ CHAR(2),
Zip​ NUMBER,
Phone​ VARCHAR(12),
Ext​ VARCHAR(5)

Answer
SELECT CONCAT(FirstName, ' ', LastName) AS Name,
CONCAT('(', SUBSTR(Phone, 1, 3), ') ',
SUBSTR(Phone, 4, 3), '-',
SUBSTR(Phone, 7, 4)) AS Phone_Number
FROM ADDRESS
ORDER BY LastName, FirstName;

25 | 12_IP_Ch_6_MYSQL Functions HEMA E J