https://www.hackerrank.com/challenges/revising-the-select-query/problem
Easy
1. Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.
select * from City where population > 100000 and CountryCode='USA'
2. Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.
The CITY table is described as follows:
select Name from City where population > 120000 and CountryCode='USA'
3. Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.
select distinct City from Station where mod(id,2)=0
Medium
1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
select * from (select concat('There are a total of ',count(occupation),' ',lower(occupation),'s.') as stat from occupations group by occupation) as innertable order by innertable.stat;
2. Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.
Note: Print NULL when there are no more names corresponding to an occupation.
https://codingsight.com/pivot-tables-in-mysql/
User nobuh:
select
max(if(occupation='Doctor', name, null)) as docname,
max(case when occupation='Professor' then name else null end) as profname,
max(case when occupation='Singer' then name else null end) as singername,
max(case when occupation='Actor' then name else null end) as actorname
from (
select
a.occupation,
a.name,
(select count(name) from occupations b where a.occupation=b.occupation and a.name > b.name) as name_rank
from occupations a
) as rank_summary
group by name_rank
order by name_rank
name_rank:
select
a.occupation,
a.name,
-- get the name rank by comparing the names
(select count(name) from occupations b where a.occupation=b.occupation and a.name > b.name) as name_rank
from occupations a
order by name_rank;
Output:
Doctor Aamina 0
Actor Eve 0
Singer Christeen 0
Professor Ashley 0
Doctor Julia 1
Singer Jane 1
Professor Belvet 1
Actor Jennifer 1
Actor Ketty 2
Singer Jenny 2
Doctor Priya 2
Professor Britney 2
Singer Kristeen 3
Professor Maria 3
Actor Samantha 3
Professor Meera 4
Professor Naomi 5
Professor Priyanka 6
3. You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.
Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:
Root: If node is root node.
Leaf: If node is leaf node.
Inner: If node is neither root nor leaf node.
Answer
select * from bst;
select distinct '------' from bst;
select * from (
select n,'Root' from bst bo where bo.p is null
union all
select n,'Inner' from bst bo where bo.p is not null and bo.n in (select bi.p from bst bi where p is not null)
union all
select n,'Leaf' from bst bo where bo.p is not null and bo.n not in (select bi.p from bst bi where p is not null)
) tmp
order by n;
Output
1 2
3 2
5 6
7 6
2 4
6 4
4 15
8 9
10 9
12 13
14 13
9 11
13 11
11 15
15 NULL
------
1 Leaf
2 Inner
3 Leaf
4 Inner
5 Leaf
6 Inner
7 Leaf
8 Leaf
9 Inner
10 Leaf
11 Inner
12 Leaf
13 Inner
14 Leaf
15 Root
4. Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.
https://www.hackerrank.com/challenges/the-company/problem
SQL
select c.company_code,founder
,count(distinct lead_manager_code)
,count(distinct senior_manager_code)
,count(distinct manager_code)
,count(distinct employee_code)
from company c left join employee e on c.company_code=e.company_code group by c.company_code,founder
order by cast(substr(c.company_code,2,5) as UNSIGNED);
Output
C1 Monika 1 2 1 2
C2 Samantha 1 1 2 2
SQL
select format(lat_n,4) from (
select s1.id,s1.lat_n,(select count(*) from station),(select cast(count(id)/2 as unsigned) from station) as mid_rank,(select count(s2.id) from station s2 where s1.lat_n > s2.lat_n) as latn_rank from station s1 order by s1.lat_n
) as rank_table
where (latn_rank +1 )= mid_rank;
or from
user vinaychinni1998
select round(s.lat_n,4) from station s where (select round(count(s.id)/2)-1 from station) = (select count(s1.id) from station s1 where s1.lat_n > s.lat_n);
Output
83.8913
6. https://www.hackerrank.com/challenges/interviews/problem
/*
Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are .
Note: A specific contest can be used to screen candidates at more than one college, but each college only holds screening contest.
Sample output
66406 17973 Rose 111 39 156 56
66556 79153 Angela 0 0 11 10
94828 80275 Frank 150 38 41 15
*/
Note: View_Stats and Submission_Stats have duplicate challenge_id's !
from user dongyuzhang
select con.contest_id,
con.hacker_id,
con.name,
sum(total_submissions),
sum(total_accepted_submissions),
sum(total_views), sum(total_unique_views)
from contests con
join colleges col on con.contest_id = col.contest_id
join challenges cha on col.college_id = cha.college_id
left join
(select challenge_id, sum(total_views) as total_views, sum(total_unique_views) as total_unique_views
from view_stats group by challenge_id) vs on cha.challenge_id = vs.challenge_id
left join
(select challenge_id, sum(total_submissions) as total_submissions, sum(total_accepted_submissions) as total_accepted_submissions from submission_stats group by challenge_id) ss on cha.challenge_id = ss.challenge_id
group by con.contest_id, con.hacker_id, con.name
-- having sum(total_submissions)!=0 or
-- sum(total_accepted_submissions)!=0 or
-- sum(total_views)!=0 or
-- sum(total_unique_views)!=0
having ( sum(total_submissions) + sum(total_accepted_submissions) + sum(total_views) + sum(total_unique_views)) > 0
order by contest_id;
Output
845 579 Rose 1987 580 1635 566
858 1053 Angela 703 160 1002 384
883 1055 Frank 1121 319 1217 338
1793 2655 Patrick 1337 360 1216 412
https://www.w3schools.com/sql/func_mysql_date_add.asp
SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); # returns 2017-06-25
Importance of being uncertain
Error Bars
Significance,
Power and sample size
Visualizing samples with box plots
Comparing samples—part I
Comparing samples—part II
Nonparametric tests
Designing comparative experiments
Analysis of variance and blocking
Replication
Nested designs
Two-factor designs
Sources of variation