Essential MYSQL functions

Question 1

round 5.73

Answer 1

select round(5.73)
ROUND(n,d, f) -- If f is not zero, then the ROUND() function rounds n to the d number of decimal places.

Question 2

round 5.7345 within two digits from the decimal point

Answer 2

select round(5.7345, 2)

Question 3

truncate 5.7345 two digits past the decimal

Answer 3

select truncate(5.7345, 2)

Question 4

what is round is sql?

Answer 4

rounds a number to a specified number of decimal places

Question 5

what is truncate in sql?

Answer 5

select truncate(5.7345, 2)
TRUNCATE(n, d) -- function returns n truncated to d decimal places.

Question 6

what is the ceiling of 5.2

Answer 6

select ceiling(5.3) #6
returns the smallest integer value that is larger than or equal to a number.

Question 7

what is the floor of 5.2?

Answer 7

select floor(5.2) # 5
returns the largest integer value that is smaller than or equal to a number

Question 8

what is the absolute value of -5.2 in sql?

Answer 8

select abs(-5.2) # 5.2
returns the absolute value of a number

Question 9

write a random value between 0 and 1 in sql?

Answer 9

select rand()

Question 10

find the length of string ‘karen’

Answer 10

select length(‘karen’) # 5

Question 11

change string ‘karen’ to upper-case

Answer 11

select upper(‘karen’) # KAREN

Question 12

change string ‘KAREN’ to lowercase

Answer 12

select lower(‘KAREN’) # karen

Question 13

remove the space from the string ‘ kelvin’

Answer 13

select ltrim(‘ kelvin’) # kelvin

Question 14

remove the space from the string ‘kelvin ‘

Answer 14

select rtrim(‘kelvin ‘) # kelvin

Question 15

remove any leading or trailing spaces from a string

Answer 15

select trim(‘ kelvin ‘) # kelvin

Question 16

return the first four characters of string Kindergarten

Answer 16

select left(‘Kindergarten’, 4)

Question 17

select the last for characters in string Kindergarten

Answer 17

select right(‘Kindergarten’, 4)

Question 18

obtain the substring wat from kelvinwaters

Answer 18

select substring(‘kelvinwaters’, 7, 3) # start at 7 then highlights 3 more values

Question 19

what is the first position of ‘n’ in ‘kindergarten’?

Answer 19

select locate(‘n’, ‘kindergarten’) # 3

Question 20

True or False

when using the locate clause, capitalization isn’t valid

Answer 20

True

‘N’ is the same as ‘n’

Question 21

replace string Kindergarten with Kindergarden

Answer 21

select replace('Kindergarten', 'ten', 'den')
select replace('Kindergarten', 't', 'd')

Question 22

combine first and last name

Answer 22

select concat(‘kelvin’, ‘waters’)

Question 23

what is this query doing?

select concat(
first_name, ‘ ‘, last_name) as full_name
from customers

Answer 23

combining first and last name with a space between them

Question 24

what is the current date and time using mysql?

Answer 24

select now()

Question 25

what is the current time in mysql?

Answer 25

select curtime()

Question 26

how do you determine current year in mysql?

Answer 26

select year(now())

Question 27

how to determine the day name in mysql?

Answer 27

select dayname(now())

Question 28

what is the current day using the EXTRACT clause?

Answer 28

select extract(day from now())

Question 29

extract the year from today

Answer 29

select extract(year from now())

Question 30

refactor this code to return from the current year

select *
from orders
where order_date >= ‘2019-01-01’

Answer 30

select \*
from orders
where year(order\_date) = year(now())

Question 31

what is wrong or not advisable with this query?

select *
from orders
where order_date >= ‘2019-01-01’

Answer 31

you should not hard code the date

Question 32

use the date_format clause to return the date as

Answer 32

select date_format(now(), ‘%M %D %Y’)

Question 33

display the current time in format

13:11 PM

Answer 33

select time_format(now(), ‘%H:%i %p’)

Question 34

add a day to the current date time

Answer 34

select date_add(now(), interval(1) day)

Question 35

True or False

You can easily manipulate day and time queries by just changing the unit

Answer 35

True

Question 36

determine how many days I’ve been breathing

Answer 36

select datediff(now(), ‘1967-08-02’)

19789

Question 37

how would you change missing null values to something else, like not assigned?

Answer 37

select
order_id,
ifnull(shipper_id, ‘Not assigned’) as shipper
from orders

Question 38

what is the difference between ifnull and coalesce

Answer 38

ifnull we can select and alternative value

coalesce will return the first value that isn’t null, if both are null you can the alternative is selected.

Question 39

write this query

Answer 39

select
concat(first_name, ‘ ‘, last_name) as customer,
coalesce(phone, ‘Unknown’) as phone
from customers

Question 40

explain this query using the if clause

if(expression, first, second)

Answer 40

the expression is a boolean if True the first will be selected if False the second will be chosen

think if(expression, True, False)

Question 41

explain this query

select
order_id,
order_date,
if(
year(order_date) = year(now()),
‘active’,
‘archived’) as category
from orders

Answer 41

if order_date openeration is True, ‘active’ will be selected, if False ‘archived’ both under the category alias column

Question 42

write a query to produce this result

Answer 42

select
product_id,
name,
count(*) as orders,
if(count(*) > 1, ‘Many times’, ‘Once’) as frequency
from products
join order_items using (product_id)
group by product_id, name

Question 43

True or False

A CASE statement must end with the END clause

Answer 43

True

Question 44

In this query what is the order of operation on the case statement

Answer 44

the first WHEN boolean returns True before proceeding to the next WHEN clause

Question 45

If you have multiple test expression what is the sql statement to use?

Answer 45

the CASE statement

Question 46

True or False

It’s good practice to name an alias to a CASE statement

Answer 46

True

Question 47

what is the sql syntax to end a CASE statement and assign it an alias?

Answer 47

END AS ‘alias_name’

Question 48

write a query using the CASE statement to produce this result

Answer 48

select
concat(first_name,’ ‘, last_name) as customer,
points,
case
when points > 3000 then ‘Gold’
when points >= 2000 then ‘Silver’
else ‘Bronze’
end as category
from customers