SQL Challenge

 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

5. /*
https://www.hackerrank.com/challenges/weather-observation-station-20/problem
A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to  decimal places.
*/

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