MySQL Functions

Which function can be used to concatenate two strings ?

Answer

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

Which function(s) can be used for extracting a substrings ?

Answer

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

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.

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.

Which function returns the current date and current time ?

Answer

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

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.

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.

Which of the following is not a text function ?

1. TRIM()
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.

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.

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 mⁿ i.e., a number m raised to the n^th power, whereas MONTH(), YEAR(), and NOW() are date functions.

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.

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.

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

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.

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

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.

What will be returned by the given query ?

SELECT INSTR("INDIA", "DI");

1. 2
2. 3
3. -2
4. -3

Answer

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.

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.

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.

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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

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

The functions that work with one row at a time are called single row functions.

The functions that work with multiple rows and return aggregated result, are called multiple rows functions.

The other name of multiple rows functions is aggregate functions.

To get a substring of a string, other than Substr(), function mid() is also used.

To get the day part of a date, Day() function is used.

To get the day name from a date, Dayname() function is used.

To remove a character from the right side of a string, Rtrim() function is used.

To get the current date, Curdate() Sysdate function is used.

SQRT() is an aggregate function.

Answer

False

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

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.

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.

Length() is a numeric function.

Answer

False

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

Functions MID() and SUBSTR() do the same thing.

Answer

True

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

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.

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.

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.

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.

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.

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.

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.

Define a function.

Answer

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

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

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

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

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

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

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

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

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

Explain the POWER() SQL function using suitable examples.

Answer

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

What will be the output of following code ?

mysql> SELECT CONCAT(CONCAT('Inform', 'atics'), 'Practices');

Answer

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

What will be the output of following code ?

mysql> SELECT LCASE('INFORMATICS PRACTICES CLASS 11TH'); 

Answer

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

What will be the output of following code ?

mysql> SELECT UCASE('Computer studies');

Answer

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

What will be the output of following code ?

mysql> SELECT CONCAT(LOWER('Class'), UPPER('xii'));

Answer

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

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

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

Write commands to display the system date.

Answer

SELECT SYSDATE();

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

Write a command to display the name of current month.

Answer

SELECT MONTHNAME(CURDATE());

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

Write SQL statement to display

Today, the date is <current date>

Answer

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

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

Write command to print the day of the week of your Birthday in the year 1999.

Answer

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

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

Write a command to display the current time.

Answer

SELECT TIME(NOW());

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

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;

Find the output of the following SQL Query :

SELECT ROUND(7658.345, 2);

Answer

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

Find the output of the following SQL Query :

SELECT MOD(ROUND (13.9, 0), 3);

Answer

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

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

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

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

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

Based on the SQL table CAR_SALES, write suitable queries for the following :

(i) Display fuel wise average sales in the first quarter.

(ii) Display segment wise highest sales in the second quarter.

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

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

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

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

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

Given the following table :

Table : STUDENT1

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)

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

Given the table LIBRARY :

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 ;

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

Write a query to show the current date and time.

Answer

SELECT NOW();

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

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

Show via query how many days remain until Christmas. Round fractional days up using the numeric function ROUND.

Answer

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

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

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 ABC@XYZ.com to lowercase.

(iii) To remove leading spaces from a string 'my country'.

(iv) To display current date.

(v) To display the value of 10⁶.

Answer

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

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;

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;

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.

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;