Test Your MySQL DB Function Knowledge with Our SQL Quiz

Progress

True / False

MySQL, the NOW() function returns the current date and time.

True False

The NOW() function in MySQL returns the current date and time in the format YYYY-MM-DD HH:MM:SS

True / False

The CONCAT() function in MySQL can be used to combine two or more strings into one string.

True False

The CONCAT() function in MySQL concatenates two or more strings together.

True / False

The COUNT() function in MySQL is used to calculate the total number of rows in a table.

True False

The COUNT() function returns the number of rows that match a specified criterion or the total number of rows in a table if no criterion is specified.

True / False

The ROUND() function in MySQL can be used to round a number to a specified number of decimal places.

True False

The ROUND() function rounds a number to the number of decimal places specified by its second argument

True / False

The SUBSTRING() function in MySQL extracts a specified number of characters from a string starting at a specified position.

True False

The SUBSTRING() function extracts part of a string from a specified start position for a specified length.

True / False

The DATE_ADD() function in MySQL subtracts a time interval from a date.

True False

The DATE_ADD() function adds a time interval to a date. To subtract a time interval, you should use the DATE_SUB() function.

True / False

The IFNULL() function in MySQL replaces NULL values with a specified replacement value.

True False

The IFNULL() function returns the specified replacement value if the original value is NULL.

True / False

The CAST() function in MySQL converts a value from one data type to another.

True False

The CAST() function allows for the conversion of a value from one data type to another, such as from a string to an integer.

True / False

The GROUP_CONCAT() function in MySQL concatenates values from multiple rows into a single string.

True False

The GROUP_CONCAT() function concatenates values from multiple rows into a single string, separated by a specified delimiter.

Q10

True / False

MySQL, the DATE_FORMAT() function allows you to format a date value according to a specified format string.

True False

The DATE_FORMAT() function formats a date value according to the format string provided, allowing for various date and time representations.

Q11

Single Choice

What is the purpose of the NOW() function in MySQL?

To return the current time To return the current date and time To return the current dateTo return the current yea

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

Q12

Single Choice

Which function in MySQL is used to return the length of a string?

LENGTH() CHAR_LENGTH() STR_LEN()STRING_LENGTH()

The LENGTH() function returns the length of a string in MySQL.

Q13

Single Choice

How do you convert a string to lowercase in MySQL?

LOWERCASE() TOLOWER() LOWER()DOWNCASE()

The LOWER() function converts a string to lowercase in MySQL.

Q14

Single Choice

Which function in MySQL can be used to extract a substring from a string?

SUBSTR() EXTRACT() SUBSTRING()MIDSTRING()

The SUBSTRING() function is used to extract a substring from a string in MySQL.

Q15

Single Choice

What does the CONCAT() function do in MySQL?

Joins two or more strings into one string Reverses a string Splits a string into substringsFinds a substring in a string

The CONCAT() function joins two or more strings into one string in MySQL

Q16

Single Choice

How do you find the number of rows in a table using a function in MySQL?

COUNT(*) NUMROWS() ROWCOUNT()TCOUNT()

The COUNT(*) function returns the number of rows in a table in MySQL.

Q17

Single Choice

Which MySQL function can be used to find the difference in days between two dates?

DATE_DIFF() DATEDIFF() DATE_SUBTRACT()DATEGAP()

The DATEDIFF() function returns the difference in days between two dates in MySQL

Q18

Single Choice

What does the COALESCE() function do in MySQL?

Returns the first non-null value in a list Concatenates multiple strings Finds the maximum value in a listFinds the minimum value in a list

The COALESCE() function returns the first non-null value in a list of expressions in MySQL.

Q19

Single Choice

Which MySQL function is used to calculate the standard deviation of a set of values?

STDDEV() STDEV() STD()SD()

The STDDEV() function calculates the standard deviation of a set of values in MySQL.

Q20

Single Choice

Which MySQL function can be used to perform a full-text search on a table?

MATCH() AGAINST() SEARCH() IN() TEXTSEARCH() IN()FULLTEXT() AGAINST()

The MATCH() AGAINST() function is used to perform a full-text search on a table in MySQL.

Q21

Multiple Choice

Identify the correct SQL statement to create a user-defined function that returns the full name of a user.

SQL Code

CREATE FUNCTION get_full_name (first_name VARCHAR(100), last_name VARCHAR(100))
RETURNS VARCHAR(200)
BEGIN
RETURN CONCAT(first_name, ' ', last_name);
END;

CREATE FUNCTION get_full_name (first_name VARCHAR(100), last_name VARCHAR(100)) RETURNS VARCHAR(200) BEGIN RETURN CONCAT(first_name, ' ', last_name); END; CREATE FUNCTION get_full_name (first_name VARCHAR(100), last_name VARCHAR(100)) RETURNS VARCHAR(200) BEGIN RETURN first_name + ' ' + last_name; END; CREATE FUNCTION get_full_name (first_name VARCHAR(100), last_name VARCHAR(100)) RETURNS VARCHAR(200) BEGIN RETURN first_name || ' ' || last_name; END;CREATE FUNCTION get_full_name (first_name VARCHAR(100), last_name VARCHAR(100)) RETURNS VARCHAR(200) BEGIN RETURN CONCAT(first_name, last_name); END;

This function concatenates the first name and last name with a space in between to return the full name.

Q22

Multiple Choice

Choose the correct SQL statement to create a function that calculates the square of a number.

SQL Code

CREATE FUNCTION calculate_square (number INT)
RETURNS INT
BEGIN
RETURN number * number;
END;

CREATE FUNCTION calculate_square (number INT) RETURNS INT BEGIN RETURN number * number; END; CREATE FUNCTION calculate_square (number INT) RETURNS INT BEGIN RETURN POW(number, 2); END; CREATE FUNCTION calculate_square (number INT) RETURNS INT BEGIN RETURN SQUARE(number); END;CREATE FUNCTION calculate_square (number INT) RETURNS INT BEGIN RETURN number ** 2; END;

This function takes an integer as input and returns the square of that number by multiplying it by itself.

Q23

Multiple Choice

Determine the correct SQL statement to create a function that returns the average salary from the employees table.

SQL Code

CREATE FUNCTION get_average_salary ()
RETURNS DECIMAL(10,2)
BEGIN
DECLARE avg_salary DECIMAL(10,2);
SELECT AVG(salary) INTO avg_salary
FROM employees;
RETURN avg_salary;
END;

CREATE FUNCTION get_average_salary () RETURNS DECIMAL(10,2) BEGIN DECLARE avg_salary DECIMAL(10,2); SELECT AVG(salary) INTO avg_salary FROM employees; RETURN avg_salary; END; CREATE FUNCTION get_average_salary () RETURNS DECIMAL(10,2) BEGIN RETURN (SELECT AVG(salary) FROM employees); END; CREATE FUNCTION get_average_salary () RETURNS DECIMAL(10,2) BEGIN SELECT AVG(salary) FROM employees; END;CREATE FUNCTION get_average_salary () RETURNS DECIMAL(10,2) BEGIN DECLARE avg_salary DECIMAL(10,2); SELECT AVG(salary) INTO avg_salary FROM employees; RETURN avg_salary; END IF;

This function calculates and returns the average salary of all employees by using the AVG() aggregate function.

Q24

Multiple Choice

Which SQL statement correctly creates a function that checks if a user is active based on the last login date?

SQL Code

CREATE FUNCTION is_user_active (last_login DATE)
RETURNS BOOLEAN
BEGIN
RETURN last_login >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
END;

CREATE FUNCTION is_user_active (last_login DATE) RETURNS BOOLEAN BEGIN RETURN last_login >= DATE_SUB(CURDATE(), INTERVAL 30 DAY); END; CREATE FUNCTION is_user_active (last_login DATE) RETURNS BOOLEAN BEGIN RETURN last_login > DATE_SUB(CURDATE(), INTERVAL 30 DAY); END; CREATE FUNCTION is_user_active (last_login DATE) RETURNS BOOLEAN BEGIN RETURN last_login >= CURDATE() - INTERVAL 30 DAY; END;CREATE FUNCTION is_user_active (last_login DATE) RETURNS BOOLEAN BEGIN RETURN last_login >= ADDDATE(CURDATE(), INTERVAL -30 DAY); END;

This function checks if the last login date is within the last 30 days, returning TRUE if the user is active and FALSE otherwise.

Q25

Multiple Choice

Select the correct SQL statement to create a function that returns the number of orders placed by a customer.

SQL Code

CREATE FUNCTION get_order_count (customer_id INT)
RETURNS INT
BEGIN
DECLARE order_count INT;
SELECT COUNT(*) INTO order_count
FROM orders
WHERE customer_id = customer_id;
RETURN order_count;
END;

CREATE FUNCTION get_order_count (customer_id INT) RETURNS INT BEGIN DECLARE order_count INT; SELECT COUNT(*) INTO order_count FROM orders WHERE customer_id = customer_id; RETURN order_count; END; CREATE FUNCTION get_order_count (customer_id INT) RETURNS INT BEGIN RETURN (SELECT COUNT(*) FROM orders WHERE customer_id = customer_id); END; CREATE FUNCTION get_order_count (customer_id INT) RETURNS INT BEGIN DECLARE order_count INT; SELECT COUNT(*) INTO order_count FROM orders WHERE customer_id = customer_id; RETURN order_count; END IF;CREATE FUNCTION get_order_count (customer_id INT) RETURNS INT BEGIN DECLARE order_count INT; SELECT COUNT(*) INTO order_count FROM orders WHERE customer_id = customer_id; RETURN order_count; END;

This function returns the total number of orders placed by a customer, identified by their customer_id.

Q26

Multiple Choice

Determine the correct SQL statement to create a function that calculates the discount for an order based on the order amount.

SQL Code

CREATE FUNCTION calculate_discount (order_amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
DECLARE discount DECIMAL(10,2);
IF order_amount > 100 THEN
SET discount = order_amount * 0.1;
ELSE
SET discount = 0;
END IF;
RETURN discount;
END;

CREATE FUNCTION calculate_discount (order_amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) BEGIN DECLARE discount DECIMAL(10,2); IF order_amount > 100 THEN SET discount = order_amount * 0.1; ELSE SET discount = 0; END IF; RETURN discount; END; CREATE FUNCTION calculate_discount (order_amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) BEGIN RETURN IF(order_amount > 100, order_amount * 0.1, 0); END; CREATE FUNCTION calculate_discount (order_amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) BEGIN DECLARE discount DECIMAL(10,2); SET discount = IF(order_amount > 100, order_amount * 0.1, 0); RETURN discount; END;CREATE FUNCTION calculate_discount (order_amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) BEGIN IF order_amount > 100 THEN RETURN order_amount * 0.1; ELSE RETURN 0; END IF; END;

This function calculates a 10% discount for orders over 100 and returns the discount amount.

Q27

Multiple Choice

Which SQL statement correctly creates a function that returns the number of days between two dates?

SQL Code

CREATE FUNCTION days_between (start_date DATE, end_date DATE)
RETURNS INT
BEGIN
RETURN DATEDIFF(end_date, start_date);
END;

CREATE FUNCTION days_between (start_date DATE, end_date DATE) RETURNS INT BEGIN RETURN DATEDIFF(end_date, start_date); END; CREATE FUNCTION days_between (start_date DATE, end_date DATE) RETURNS INT BEGIN RETURN TIMESTAMPDIFF(DAY, start_date, end_date); END; CREATE FUNCTION days_between (start_date DATE, end_date DATE) RETURNS INT BEGIN RETURN TO_DAYS(end_date) - TO_DAYS(start_date); END;CREATE FUNCTION days_between (start_date DATE, end_date DATE) RETURNS INT BEGIN RETURN end_date - start_date; END;

This function calculates the difference in days between two dates using the DATEDIFF() function.

Q28

Multiple Choice

Identify the correct SQL statement to create a function that returns the first day of the month for a given date.

SQL Code

CREATE FUNCTION first_day_of_month (input_date DATE)
RETURNS DATE
BEGIN
RETURN DATE_FORMAT(input_date, '%Y-%m-01');
END;

CREATE FUNCTION first_day_of_month (input_date DATE) RETURNS DATE BEGIN RETURN DATE_FORMAT(input_date, '%Y-%m-01'); END; CREATE FUNCTION first_day_of_month (input_date DATE) RETURNS DATE BEGIN RETURN DATE_SUB(input_date, INTERVAL DAY(input_date)-1 DAY); END; CREATE FUNCTION first_day_of_month (input_date DATE) RETURNS DATE BEGIN RETURN MAKEDATE(YEAR(input_date), MONTH(input_date)); END;CREATE FUNCTION first_day_of_month (input_date DATE) RETURNS DATE BEGIN RETURN CONCAT_WS('-', YEAR(input_date), MONTH(input_date), '01'); END;

This function returns the first day of the month for the given input date by formatting the date to the first day.

Q29

Multiple Choice

Select the correct SQL statement to create a function that checks if a string is a valid email format.

SQL Code

CREATE FUNCTION is_valid_email (email VARCHAR(255))
RETURNS BOOLEAN
BEGIN
RETURN email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$';
END;

CREATE FUNCTION is_valid_email (email VARCHAR(255)) RETURNS BOOLEAN BEGIN RETURN email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'; END; CREATE FUNCTION is_valid_email (email VARCHAR(255)) RETURNS BOOLEAN BEGIN RETURN email RLIKE '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'; END; CREATE FUNCTION is_valid_email (email VARCHAR(255)) RETURNS BOOLEAN BEGIN RETURN email LIKE '%_@__%.__%'; END;CREATE FUNCTION is_valid_email (email VARCHAR(255)) RETURNS BOOLEAN BEGIN RETURN REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'); END;

This function uses a regular expression to validate the format of an email address, returning TRUE if the format is correct.

Q30

Multiple Choice

Determine the correct SQL statement to create a function that converts a string to title case (capitalizing the first letter of each word).

SQL Code

CREATE FUNCTION to_title_case (input_string VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE result VARCHAR(255);
SET result = CONCAT(UCASE(LEFT(input_string, 1)), LCASE(SUBSTRING(input_string, 2)));
RETURN result;
END;

CREATE FUNCTION to_title_case (input_string VARCHAR(255)) RETURNS VARCHAR(255) BEGIN DECLARE result VARCHAR(255); SET result = CONCAT(UCASE(LEFT(input_string, 1)), LCASE(SUBSTRING(input_string, 2))); RETURN result; END; CREATE FUNCTION to_title_case (input_string VARCHAR(255)) RETURNS VARCHAR(255) BEGIN DECLARE result VARCHAR(255); SET result = CONCAT(UPPER(LEFT(input_string, 1)), LOWER(SUBSTRING(input_string, 2))); RETURN result; END; CREATE FUNCTION to_title_case (input_string VARCHAR(255)) RETURNS VARCHAR(255) BEGIN RETURN CONCAT(UCASE(LEFT(input_string, 1)), LCASE(SUBSTRING(input_string, 2))); END;CREATE FUNCTION to_title_case (input_string VARCHAR(255)) RETURNS VARCHAR(255) BEGIN RETURN CONCAT(UPPER(SUBSTRING(input_string, 1, 1)), LOWER(SUBSTRING(input_string, 2))); END;

This function capitalizes the first letter of the input string and converts the rest of the string to lowercase.

MySQL Database Quiz Questions