This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE DATABASE devstronomy; | |
DROP TABLE IF EXISTS devstronomy.`planet`; | |
CREATE TABLE devstronomy.`planet` ( | |
`id` int(11) NOT NULL AUTO_INCREMENT, | |
`name` varchar(255) NOT NULL, | |
`mass` decimal(16,6) NOT NULL, | |
`diameter` decimal(16,6) NOT NULL, | |
`density` decimal(16,6) NOT NULL, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select max(p.mass) max_mass, | |
max(p.diameter) max_diameter, | |
max(p.density) max_density, | |
last_value(p.name) over (partition by null order by p.mass asc) lightest_planet, | |
p.name | |
from devstronomy.planet as p; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select p.name, | |
s.name, | |
s.gm, | |
row_number() over (partition by p.id order by s.gm) sat_rownum_by_sat_mass, | |
rank() over (partition by p.id order by s.gm) sat_rank_by_sat_mass, | |
dense_rank() over (partition by p.id order by s.gm) sat_dense_rank_by_sat_mass | |
from devstronomy.satellite as s right join | |
devstronomy.planet as p | |
on p.id = s.planet_id | |
where p.name = 'Jupiter'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select p.name, | |
row_number() over () rn, | |
rank() over () rnk, | |
dense_rank() over () dns_rnk, | |
lead(p.name) over () lead_name, | |
lag(p.name) over () lag_name, | |
first_value(p.name) over () fv_name, | |
last_value(p.name) over () lv_name, | |
ntile(4) over () quartile, | |
ntile(5) over () quintile |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select distinct p.name, | |
first_value(s.name) over (partition by p.id order by s.gm desc, s.radius desc) biggest_satellite, | |
last_value(s.name) over (partition by p.id order by s.gm desc, s.radius desc) smallest_satellite | |
from devstronomy.planet as p | |
left join devstronomy.satellite as s | |
on p.id = s.planet_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE `l_planet` ( | |
`id` int(11) NOT NULL DEFAULT '0', | |
`name` varchar(255) CHARACTER SET utf8 NOT NULL, | |
`mass` decimal(16,6) NOT NULL, | |
`diameter` decimal(16,6) NOT NULL, | |
`density` decimal(16,6) NOT NULL, | |
`gravity` decimal(16,6) NOT NULL | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; | |
CREATE TABLE `l_satellite` ( |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select * | |
from devstronomy.l_satellite | |
where planet_id = (select id | |
from devstronomy.l_planet | |
order by rand() | |
limit 1) | |
order by rand() | |
limit 1; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table devstronomy.temp_l_satellite like devstronomy.l_satellite; | |
alter table devstronomy.temp_l_satellite add primary key (id); | |
alter table devstronomy.temp_l_satellite add column ins_ts timestamp default current_timestamp not null; | |
insert ignore into devstronomy.temp_l_satellite | |
select ls.*, current_timestamp | |
from devstronomy.l_satellite ls | |
where planet_id = (select lp.id | |
from devstronomy.l_planet lp | |
order by rand() |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select distinct * | |
from (select * | |
from devstronomy.l_satellite | |
where rand() < .5 | |
union all | |
select * | |
from devstronomy.l_satellite | |
where rand() > .5 | |
) t | |
order by rand() |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select a, b, a = b | |
from ( | |
select 13 a, '13dfgs' b union all | |
select 13 a, '130dfgs' b union all | |
select 13 a, '13' b union all | |
select 13 a, '0013dfgs' b union all | |
select 13 a, ' 13dfgs' b union all | |
select 13 a, 'dfgs13' | |
) as t; |
OlderNewer