MySQL Functions

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.

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.

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.

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.

The CONCAT() function is used to concatenate two strings in a query result.

The SUBSTRING()/SUBSTR() and MID() functions can be used for extracting a substrings.

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.

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.

The NOW() function returns the current date and current time.

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.

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.

TRUNCATE()

Reason — TRUNCATE() is a numeric function that returns a number with some digits truncated, whereas TRIM(), LEFT(), and MID() are text functions.

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.

POW

Reason — POW() is a numeric function that returns mⁿ i.e., a number m raised to the n^th power, whereas MONTH(), YEAR(), and NOW() are date functions.

MID, SUBSTR

Reason — The SUBSTRING()/SUBSTR() and MID() functions can be used to extract substrings from a given string.

INSTR

Reason — The INSTR() function searches for given second string into the given first string and returns the position.

ROUND()

Reason — Aggregate functions in SQL include AVG(), COUNT(), MAX(), MIN(), and SUM().

LENGTH()

Reason — LENGTH() is a text/string function that returns the length of a given string in bytes.

LEN()

Reason — Aggregate functions in SQL include AVG(), COUNT(), MAX(), MIN(), and SUM().

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.

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.

0

Reason — When the substring is not found within the string, the INSTR() function in MySQL returns 0.

"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.

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.

Trim()

Reason — The TRIM() function removes leading and trailing spaces from a given string. It performs combined functions of LTRIM() and RTRIM().

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.

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.

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.

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.

MAX(), COUNT()

Reason — Aggregate functions in SQL include AVG(), COUNT(), MAX(), MIN(), and SUM().

50

Reason — The ROUND() function rounds the numeric value "49.88" to the nearest whole number, which is 50.

NULL

Reason — The MOD() function returns NULL when the divisor is zero because division by zero is undefined in SQL.

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.

multiple values

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

False

Reason — The SQRT() is a numeric function that calculates the square root of a given number.

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.

False

Reason — The TRUNCATE() is a numeric function. It is used to truncate a number to a specified number of decimal places.

False

Reason — The length() is a text function. It returns the length of a given string in bytes.

True

Reason — Both the functions MID() and SUBSTR() extract a substring from a given string.

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.

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.

A function is a special type of predefined command set that performs some operation and returns a single value.

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";

+-----------+
| Uppercase |
+-----------+
| LARGE     |
+-----------+

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).

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  ');

+----------------------+
| TRIM('   Bar One  ') |
+----------------------+
| Bar One              |
+----------------------+

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);

+---------------------------+
| MID('Quadratically', 5,6) |
+---------------------------+
| ratica                    |
+---------------------------+

The DAYNAME() function returns the name of weekday for date. The syntax is DAYNAME(date). For example,

SELECT DAYNAME('2024-05-20');

+-----------------------+
| DAYNAME('2024-05-20') |
+-----------------------+
| Monday                |
+-----------------------+

The POWER()/POW() function returns the value of m raised to the n^th power, denoted as mⁿ. 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";

+--------+
| Raised |
+--------+
|     16 |
+--------+

+------------------------------------------------+
| CONCAT(CONCAT('Inform', 'atics'), 'Practices') |
+------------------------------------------------+
| InformaticsPractices                           |
+------------------------------------------------+

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'.

+-------------------------------------------+
| LCASE('INFORMATICS PRACTICES CLASS 11TH') |
+-------------------------------------------+
| informatics practices class 11th          |
+-------------------------------------------+

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'.

+---------------------------+
| UCASE('Computer studies') |
+---------------------------+
| COMPUTER STUDIES          |
+---------------------------+

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'.

+--------------------------------------+
| CONCAT(LOWER('Class'), UPPER('xii')) |
+--------------------------------------+
| classXII                             |
+--------------------------------------+

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'.

SELECT CONCAT(LCASE('INFORMATICS PRACTICES'), ' ', LCASE('FOR CLASS XI'));

+--------------------------------------------------------------------+
| CONCAT(LCASE('INFORMATICS PRACTICES'), ' ', LCASE('FOR CLASS XI')) |
+--------------------------------------------------------------------+
| informatics practices for class xi                                 |
+--------------------------------------------------------------------+

SELECT SYSDATE();

+---------------------+
| SYSDATE()           |
+---------------------+
| 2024-05-20 15:31:25 |
+---------------------+

SELECT MONTHNAME(CURDATE());

+----------------------+
| MONTHNAME(CURDATE()) |
+----------------------+
| May                  |
+----------------------+

SELECT CONCAT('Today, the date is ', CURDATE()) AS CURDATE;

+-------------------------------+
| CURDATE                       |
+-------------------------------+
| Today, the date is 2024-05-20 |
+-------------------------------+

SELECT DAYOFWEEK('1999-07-21');

+-------------------------+
| DAYOFWEEK('1999-07-21') |
+-------------------------+
|                       4 |
+-------------------------+

SELECT TIME(NOW());

+-------------+
| TIME(NOW()) |
+-------------+
| 15:40:57    |
+-------------+

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

In the query, the ROUND function is used to round a number to 2 decimal places, resulting in 7658.35.

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

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.

(i)

SELECT MONTHNAME(CURDATE());

+----------------------+
| MONTHNAME(CURDATE()) |
+----------------------+
| May                  |
+----------------------+

(ii)

SELECT TRIM("  Panorama  ");

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

(iii)

SELECT DAYNAME('2000-07-22');

+-----------------------+
| DAYNAME('2000-07-22') |
+-----------------------+
| Saturday              |
+-----------------------+

(iv)

SELECT INSTR('Gupta Ashwini', 'Ashwini') AS StartingPosition;

+------------------+
| StartingPosition |
+------------------+
|                7 |
+------------------+

(v)

SELECT MOD(n1, n2);

(i)

SELECT SUBSTR('INDIA SHINING', 7, 7);

+-------------------------------+
| SUBSTR('INDIA SHINING', 7, 7) |
+-------------------------------+
| SHINING                       |
+-------------------------------+

(ii)

SELECT INSTR('WELCOME WORLD', 'COME');

+--------------------------------+
| INSTR('WELCOME WORLD', 'COME') |
+--------------------------------+
|                              4 |
+--------------------------------+

(iii)

SELECT ROUND(23.78, 1);

+-----------------+
| ROUND(23.78, 1) |
+-----------------+
|            23.8 |
+-----------------+

(iv)

SELECT MOD(100, 9);

+-------------+
| MOD(100, 9) |
+-------------+
|           1 |
+-------------+

(v)

SELECT TRIM(userid) FROM USERS;

(i)

SELECT FUEL, AVG(QT1) AS Avg_Sales_QT1 
FROM CAR_SALES 
GROUP BY FUEL;

+--------+---------------+
| FUEL   | Avg_Sales_QT1 |
+--------+---------------+
| Petrol |    31000.0000 |
| Diesel |    16750.0000 |
+--------+---------------+

(ii)

SELECT SEGMENT, MAX(QT2) AS HIGHEST_SALES
FROM CAR_SALES
GROUP BY SEGMENT;

+-------------------+---------------+
| SEGMENT           | HIGHEST_SALES |
+-------------------+---------------+
| Compact HatchBack |         70000 |
| MUV               |         35000 |
| SUV               |         54000 |
| Sedan             |         10000 |
+-------------------+---------------+

(iii)

SELECT * FROM CAR_SALES
ORDER BY QT2 DESC;

+--------+-------------------+--------+-------+-------+
| 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 |
+--------+-------------------+--------+-------+-------+

(i) SELECT LEFT(SEGMENT, 2) FROM CAR_SALES WHERE FUEL= "PETROL";

+------------------+
| LEFT(SEGMENT, 2) |
+------------------+
| Co               |
| MU               |
| SU               |
| Se               |
+------------------+

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";

+------------+
| AVG SALE   |
+------------+
| 13500.0000 |
|  6000.0000 |
+------------+

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 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";

+----------+
| TOT SALE |
+----------+
|    67000 |
+----------+

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.

(i) It will return error because no argument is passed as decimal places to truncate. Syntax of truncate function is TRUNCATE(number, decimals).

(ii)

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

(iii)

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

(iv)

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

(i) SELECT UPPER(Title) FROM Library WHERE Price < 150 ;

+---------------------+
| UPPER(Title)        |
+---------------------+
| COMPUTER STUDIES    |
| DBASE DUMMIES       |
| MASTERING FOXPRO    |
| BASIC FOR BEGINNERS |
+---------------------+

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 ;

+----------------------+
| CONCAT(Author, Type) |
+----------------------+
| FrenchFND            |
| SeigalDBMS           |
| CowartOS             |
+----------------------+

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 ;

+-------------+
| MOD(Qty, 4) |
+-------------+
|           0 |
|           2 |
|           0 |
|           1 |
|           3 |
|           3 |
|           2 |
|           3 |
|           3 |
|           1 |
+-------------+

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.

SELECT NOW();

+---------------------+
| NOW()               |
+---------------------+
| 2024-05-21 12:20:03 |
+---------------------+

SELECT ROUND(DAYOFYEAR('2024-12-25') - DAYOFYEAR(CURDATE())) AS DaysUntilChristmas;

+--------------------+
| DaysUntilChristmas |
+--------------------+
|                218 |
+--------------------+

(i)

SELECT YEAR('2023-05-15');

+--------------------+
| YEAR('2023-05-15') |
+--------------------+
|               2023 |
+--------------------+

(ii)

SELECT LCASE('ABC@XYZ.com');

+----------------------+
| LCASE('ABC@XYZ.com') |
+----------------------+
| abc@xyz.com          |
+----------------------+

(iii)

SELECT LTRIM('    my country');

+---------------------+
| LTRIM('my country') |
+---------------------+
| my country          |
+---------------------+

(iv)

SELECT CURDATE();

+------------+
| CURDATE()  |
+------------+
| 2024-05-21 |
+------------+

(v)

SELECT POWER(10, 6);

+--------------+
| POWER(10, 6) |
+--------------+
|      1000000 |
+--------------+