insert into sailors(sid, sname, rating, age) values
(22,'Chris',7,50.0);

update sailors
set sname = 'Chris', rating = 7, age = 50.0
where sid = 22;

replace into sailors(sid, sname, rating, age) values
(22, 'Chris',7, 50.0);

-- views
create view topratedsailors as
select *
from sailors
order by rating desc
limit 3;

select sname
from topratedsailors
where rating >= ALL (
	select rating from topratedsailors
);

-- functions
-- numeric
select round(age)  -- https://dev.mysql.com/doc/refman/8.0/en/numeric-functions.html
from sailors;

-- string 
-- https://dev.mysql.com/doc/refman/8.0/en/string-functions.html
select ucase(concat(bname, ' - ', color))
from boats;

-- date
-- https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
select datediff(current_date(), rday), date_format(rday, '%W, %D %M %Y')
from reserves;

-- comparison https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html


-- control flow
select rating,
case
when rating < 3 then 'bad'
when rating < 7 then 'good'
when rating < 9 then 'excellent'
when rating <= 10 then 'best'
else 'don\'t know'
end as ratingexplained
from sailors
order by rating desc;