DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number INT,
name VARCHAR(20),
type VARCHAR(10),
attack INT,
defense INT,
capture_date DATE
);
INSERT INTO mypokemon (number, name, type, attack, defense, capture_date)
VALUES (10, 'caterpie', 'bug', 30, 35, '2019-10-14'),
(25, 'pikachu', 'electric', 55, 40, '2018-11-04'),
(26, 'raichu', 'electric', 90, 55, '2019-05-28'),
(125, 'electabuzz', 'electric', 83, 57, '2020-12-29'),
(133, 'eevee', 'normal', 55, 50, '2021-10-03'),
(137, 'porygon', 'normal', 60, 70, '2021-01-16'),
(152, 'chikoirita', 'grass', 49, 65, '2020-03-05'),
(153, 'bayleef', 'grass', 62, 80, '2022-01-01');
select * from mypokemon;
select name, rank()over(order by defense desc) as defense_rank from mypokemon;
select name, rank()over(order by defense desc) as defense_rank from mypokemon;
select name, datediff(now(), capture_date) as days from mypokemon;
select name, right(name,3) as last_char from mypokemon;
select name, left(name,2) as left2 from mypokemon;
select replace(name, 'o','O') as bigO from mypokemon where name like '%o%' order by name;
select name, concat(upper(left(type,1)),upper(right(type,1))) as type_code from mypokemon;
select * from mypokemon where length(name)>8;
select round(avg(attack),0) as avg_of_attack from mypokemon ;
select truncate(avg(defense),0) as avg_of_defense from mypokemon ;
select name, power(attack,2) as attack2 from mypokemon where length(name)<8;
select name, mod(attack,2) as div2 from mypokemon;
select name, abs(attack-defense) as diff from mypokemon where attack<=50;
select current_date() as now_date, current_time() as now_time;
select month(capture_date) as month_num, monthname(capture_date) as month_eng from mypokemon;
select dayofweek(capture_date) as day_num, dayname(capture_date) as day_eng from mypokemon;
select year(capture_date) as year, month(capture_date) as month, dayofmonth(capture_date) as day from mypokemon;
