Essential MYSQL functions Flashcards
round 5.73
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.
round 5.7345 within two digits from the decimal point
select round(5.7345, 2)
truncate 5.7345 two digits past the decimal
select truncate(5.7345, 2)
what is round is sql?
rounds a number to a specified number of decimal places
what is truncate in sql?
select truncate(5.7345, 2) TRUNCATE(n, d) -- function returns n truncated to d decimal places.
what is the ceiling of 5.2
select ceiling(5.3) #6 returns the smallest integer value that is larger than or equal to a number.
what is the floor of 5.2?
select floor(5.2) # 5 returns the largest integer value that is smaller than or equal to a number
what is the absolute value of -5.2 in sql?
select abs(-5.2) # 5.2 returns the absolute value of a number
write a random value between 0 and 1 in sql?
select rand()
find the length of string ‘karen’
select length(‘karen’) # 5
change string ‘karen’ to upper-case
select upper(‘karen’) # KAREN
change string ‘KAREN’ to lowercase
select lower(‘KAREN’) # karen
remove the space from the string ‘ kelvin’
select ltrim(‘ kelvin’) # kelvin
remove the space from the string ‘kelvin ‘
select rtrim(‘kelvin ‘) # kelvin
remove any leading or trailing spaces from a string
select trim(‘ kelvin ‘) # kelvin
return the first four characters of string Kindergarten
select left(‘Kindergarten’, 4)
select the last for characters in string Kindergarten
select right(‘Kindergarten’, 4)
obtain the substring wat from kelvinwaters
select substring(‘kelvinwaters’, 7, 3) # start at 7 then highlights 3 more values
what is the first position of ‘n’ in ‘kindergarten’?
select locate(‘n’, ‘kindergarten’) # 3
True or False
when using the locate clause, capitalization isn’t valid
True
‘N’ is the same as ‘n’
replace string Kindergarten with Kindergarden
select replace('Kindergarten', 'ten', 'den')
select replace('Kindergarten', 't', 'd')combine first and last name
select concat(‘kelvin’, ‘waters’)
what is this query doing?
select concat(
first_name, ‘ ‘, last_name) as full_name
from customers
combining first and last name with a space between them
what is the current date and time using mysql?
select now()
what is the current time in mysql?
select curtime()
how do you determine current year in mysql?
select year(now())
how to determine the day name in mysql?
select dayname(now())
what is the current day using the EXTRACT clause?
select extract(day from now())
extract the year from today
select extract(year from now())
refactor this code to return from the current year
select *
from orders
where order_date >= ‘2019-01-01’
select \* from orders where year(order\_date) = year(now())
what is wrong or not advisable with this query?
select *
from orders
where order_date >= ‘2019-01-01’
you should not hard code the date
use the date_format clause to return the date as
select date_format(now(), ‘%M %D %Y’)
display the current time in format
13:11 PM
select time_format(now(), ‘%H:%i %p’)
add a day to the current date time
select date_add(now(), interval(1) day)
True or False
You can easily manipulate day and time queries by just changing the unit
True
determine how many days I’ve been breathing
select datediff(now(), ‘1967-08-02’)
19789
how would you change missing null values to something else, like not assigned?
select
order_id,
ifnull(shipper_id, ‘Not assigned’) as shipper
from orders
what is the difference between ifnull and coalesce
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.
write this query
select
concat(first_name, ‘ ‘, last_name) as customer,
coalesce(phone, ‘Unknown’) as phone
from customers
explain this query using the if clause
if(expression, first, second)
the expression is a boolean if True the first will be selected if False the second will be chosen
think if(expression, True, False)
explain this query
select
order_id,
order_date,
if(
year(order_date) = year(now()),
‘active’,
‘archived’) as category
from orders
if order_date openeration is True, ‘active’ will be selected, if False ‘archived’ both under the category alias column
write a query to produce this result
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
True or False
A CASE statement must end with the END clause
True
In this query what is the order of operation on the case statement
the first WHEN boolean returns True before proceeding to the next WHEN clause
If you have multiple test expression what is the sql statement to use?
the CASE statement
True or False
It’s good practice to name an alias to a CASE statement
True
what is the sql syntax to end a CASE statement and assign it an alias?
END AS ‘alias_name’
write a query using the CASE statement to produce this result
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
