sql substring_分享hackrank上SQL相关问题的答案

前段时间准备找工作，把hackrank上的SQL相关题目都做了一遍，五十多道，基本上涵盖了SQL transaction的所有知识点了，找工作前准备SQL相关的话，可以看看。

题目在

https://www.hackerrank.com/domains/sql?badge_type=sql

1 Revising the Select Query I
Query all columns for all American cities in CITY with populations larger than 100000. The CountryCode for America is USA.
select * from CITY where COUNTRYCODE='USA' and population>100000;
2 Revising the Select Query II  
SELECT NAME from CITY where COUNTRYCODE='USA' and POPULATION>120000;
3 Select All
select * from CITY;

4 Select By ID
select * from CITY where ID=1661;

5 Japanese Cities' Attributes
select * from CITY where COUNTRYCODE='JPN';

6 Japanese Cities' Names
select NAME from CITY where COUNTRYCODE='JPN';

7  Weather Observation Station 1
select CITY, STATE from STATION;

8  Weather Observation Station 3
select DISTINCT CITY from STATION where MOD(ID,2)=0;

9  Weather Observation Station 4
select count(CITY) -    count( distinct CITY ) from STATION

10  Weather Observation Station 5
(select CITY, length(CITY) from STATION order by length(CITY), CITY limit 1)
UNION
(select CITY, length(CITY) from STATION order by length(CITY) DESC, CITY limit 1)

11  Weather Observation Station 6
select distinct CITY
from STATION
where city regexp "^[aeiou].*"


Or
where city regexp "^[aeiou]."

12  Weather Observation Station 7
select distinct CITY from STATION where CITY REGEXP ".*[aeiou]$"

13  Weather Observation Station 8
select distinct CITY from STATION where CITY REGEXP "^[aeiou].*[aeiou]$"

14  Weather Observation Station 9
select distinct CITY from STATION where CITY REGEXP "^[^aeiou].*"

15  Weather Observation Station 10
select distinct CITY from STATION where CITY REGEXP ".*[^aeiou]$"

16  Weather Observation Station 11
select distinct CITY from STATION where CITY REGEXP "^[^aeiou].*" or CITY REGEXP ".*[^aeiou]$"

17  Weather Observation Station 12
select distinct CITY from STATION where CITY REGEXP "^[^aeiou].*[^aeiou]$"


18  Higher Than 75 Marks
select Name
from STUDENTS
where Marks>75
order by  RIGHT(Name, 3), ID asc


19  Employee Names
SELECT Name from Employee order by Name

20  Employee Salaries
select Name from Employee where salary>2000 and months<10 order by employee_id


21  Type of Triangle  **

SELECT
CASE
    WHEN A + B > C AND A+C>B AND B+C>A THEN
        CASE
            WHEN A = B AND B = C THEN 'Equilateral'
            WHEN A = B OR B = C OR A = C THEN 'Isosceles'
            /*WHEN A != B OR B != C OR A != C THEN 'Scalene' */
            ELSE 'Scalene'
        END
    ELSE 'Not A Triangle'
END
FROM TRIANGLES;

22  The PADS
select concat(Name,'(',Substring(Occupation,1,1),')') as N
from occupations
Order by N;
select concat('There are a total of',' ',count(occupation),' ',lower(occupation),'s.') as total
from occupations
group by occupation
order by total;


23  Occupations??
set @r1=0, @r2=0, @r3=0, @r4=0;
select min(Doctor), min(Professor), min(Singer), min(Actor)
from(
  select case when Occupation='Doctor' then (@r1:=@r1+1)
            when Occupation='Professor' then (@r2:=@r2+1)
            when Occupation='Singer' then (@r3:=@r3+1)
            when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
    case when Occupation='Doctor' then Name end as Doctor,
    case when Occupation='Professor' then Name end as Professor,
    case when Occupation='Singer' then Name end as Singer,
    case when Occupation='Actor' then Name end as Actor
  from OCCUPATIONS
  order by Name
) Temp
group by RowNumber

24   Binary Tree Nodes
select
N, case
when P is NULL then "Root"
when (select count(*) from BST where P =B.N)>0 then "Inner"
ELSE "Leaf"
end
from BST as B
order by N

25  New Companies
select c.company_code, c.founder, count(distinct lm.lead_manager_code),
count(distinct sm.senior_manager_code), count(distinct m.manager_code),
count(distinct e.employee_code)
from Company c, Lead_Manager lm, Senior_Manager sm, Manager m, Employee e
where c.company_code = lm.company_code
    and lm.lead_manager_code = sm.lead_manager_code
    and sm.senior_manager_code = m.senior_manager_code
    and m.manager_code = e.manager_code
group by c.company_code, c.founder
order by c.company_code

26  Revising Aggregations - The Count Function
select count(ID) from CITY where POPULATION>100000;

27  Revising Aggregations - The Sum Function
select sum(POPULATION) from CITY where DISTRICT="California"

28  Revising Aggregations - Averages
select AVG(POPULATION) from CITY where DISTRICT="California";

29  Average Population
select round(AVG(POPULATION), 0) from CITY

30  Japan Population
select sum(POPULATION) from CITY where COUNTRYCODE="JPN";

31  Population Density Difference
select max(POPULATION)-min(POPULATION) from CITY;


32  The Blunder
select round(avg(Salary), 0) -  round(avg(replace(Salary, '0', '')), 0) from EMPLOYEES;

33  Top Earners
select salary*months as earnings, count(*) from employee
group by earnings
order by earnings desc limit 1;

34  Weather Observation Station 2
select round(sum(LAT_N),2) as lat, round(sum(LONG_W),2) as lon
from STATION;

35  Weather Observation Station 13
select round(sum(LAT_N), 4)
from STATION
where LAT_N<137.2345 and LAT_N>38.7880

36   Weather Observation Station 14
select round(max(LAT_N),4)
from STATION
where LAT_N<137.2345;

=====From Here on==========

37  Weather Observation Station 15
select round(LONG_W, 4)
from STATION
where LAT_N<137.2345
order by LAT_N desc limit 1

38  Weather Observation Station 16
select round(min(LAT_N), 4)
from STATION
where LAT_N>38.7780

or:
select round(LAT_N, 4) as v
from STATION
where LAT_N>38.7780
order by v
limit 1

39  Weather Observation Station 17
select round(LONG_W, 4)
from STATION
where LAT_N>38.7780
order by LAT_N asc
limit 1;

40  Weather Observation Station 18
select round(max(LAT_N)-min(LAT_N) + max(LONG_W) -min(LONG_W), 4)
from STATION as result

41  Weather Observation Station 19
select round(SQRT(power(max(LAT_N)-min(LAT_N),2) + power(max(LONG_W)-min(LONG_W),2) ), 4)
from STATION;

42  Weather Observation Station 20  找中位数 利用大于它和小于它的数目相同
Select round(S.LAT_N,4) mediam
from station S
where
(select count(Lat_N) from station where Lat_N < S.LAT_N ) = (select count(Lat_N) from station where Lat_N > S.LAT_N)

43  Asian Population
select sum(CITY.POPULATION)
from COUNTRY, CITY
where COUNTRY.CONTINENT="Asia" and COUNTRY.CODE=CITY.COUNTRYCODE

44  African Cities
select CITY.NAME
from CITY, COUNTRY
where CITY.COUNTRYCODE=COUNTRY.CODE and COUNTRY.CONTINENT='Africa'

45  Average Population of Each Continent
select COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION))
from COUNTRY, CITY
where CITY.COUNTRYCODE=COUNTRY.CODE
group by COUNTRY.CONTINENT


46  The Report
SELECT (CASE g.grade>=8 WHEN TRUE THEN s.name ELSE null END),g.grade,s.marks
FROM students s INNER JOIN grades g ON s.marks BETWEEN min_mark AND max_mark
ORDER BY g.grade DESC,s.name,s.marks;


47  Top Competitors
select h.hacker_id, h.name
from submissions s
inner join challenges c
on s.challenge_id = c.challenge_id
inner join difficulty d
on c.difficulty_level = d.difficulty_level
inner join hackers h
on s.hacker_id = h.hacker_id
where s.score = d.score
group by h.hacker_id, h.name
having count(s.hacker_id) > 1
order by count(s.hacker_id) desc, s.hacker_id asc

=======From Here on=====
48  Ollivander's Inventory
select w.id, p.age, w.coins_needed, w.power
from Wands as w join Wands_Property as p
on (w.code = p.code)
where p.is_evil = 0 and w.coins_needed = (select min(coins_needed) from Wands as w1 join Wands_Property as p1 on (w1.code = p1.code) where w1.power = w.power and p1.age = p.age)
order by w.power desc, p.age desc

49  Challenges
select h.hacker_id, h.name ,count(c.hacker_id) as c_count
from Hackers as h inner join Challenges as c on c.hacker_id = h.hacker_id
group by h.hacker_id, h.name
having c_count = (SELECT MAX(temp1.cnt) from (SELECT COUNT(hacker_id) as cnt from Challenges group by hacker_id   order by hacker_id) temp1) or c_count in (select t.cnt from (select count(*) as cnt  from challenges group by hacker_id) t  group by t.cnt   having count(t.cnt) = 1)
order by c_count DESC, c.hacker_id;


50  Contest Leaderboard
select Hackers.hacker_id, name, sum(score) as total_score
from Hackers, (select max(score) as score, hacker_id from submissions group by hacker_id, challenge_id  ) as  s
where Hackers.hacker_id= s.hacker_id
group by Hackers.hacker_id, name
having total_score <> 0
order by total_score desc, Hackers.hacker_id


51  SQL Project Planning
select Start_Date, MIN(End_Date)
from
(SELECT Start_Date from Projects where Start_Date not in (select End_Date from Projects) ) a,
(SELECT End_Date from Projects where End_Date not in (select Start_Date from Projects ) ) b
where Start_Date < End_Date
group by Start_Date
order by DATEDIFF(MIN(End_Date), Start_Date) ASC, Start_Date ASC
Explanation of MySQL code:
First, understand what the two subqueries are doing and why: 1. Select all projects that have a start date not in the set of end dates. If there is no end date that matches a start date, this means that these start dates mark the beginning of the projects. (There is no end date 'consecutive with' the end date that corresponds to the start date.) 2. Select all end dates not in the set of start dates. By a symetric argument to the one above, all of these dates mark the end of projects.
Now do a cartesian join on these two tables (note that there is no 'on' clause for the join). Now, 'trim' this new joined table by not allowing rows where the end date is passed the start date. Then, group by start date so that the start date of each row is unique. Finally, select each unique start date and the min(end_date) from each group of start dates as your respective start and end date values for each project. Order by length of project (datediff) and then start date.

52  Placements
select s.Name
from (Students s join Packages p1 on s.ID=p1.ID join Friends f on s.ID=f.ID join Packages p2 on f.Friend_ID=p2.ID)
where p2.Salary>P1.Salary
Order by P2.Salary



53  Symmetric Pairs
SELECT f1.X, f1.Y
FROM Functions f1 INNER JOIN Functions f2 ON f1.X=f2.Y AND f1.Y=f2.X
GROUP BY f1.X, f1.Y
HAVING COUNT(f1.X)>1 or f1.X<f1.Y
ORDER BY f1.X

#Using having sentence to delete one columns
#============Till here==========
54  Interviews
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
            order by contest_id;

55  15 Days of Learning SQL
SELECT sd, cnt, h.hacker_id, h.name
FROM(

SELECT sd, (SELECT COUNT(DISTINCT h.hacker_id) FROM hackers h JOIN submissions s ON s.hacker_id = h.hacker_id WHERE s.submission_date <= sd AND
            (SELECT COUNT(DISTINCT submission_date) FROM hackers hh JOIN submissions ss ON ss.hacker_id = hh.hacker_id WHERE hh.hacker_id = h.hacker_id
            AND ss.submission_date <= sd
            )
            = 1+DATEDIFF(sd,'2016-03-01')) AS cnt,
(SELECT h.hacker_id FROM hackers h JOIN submissions s ON s.hacker_id = h.hacker_id WHERE s.submission_date = sd GROUP BY h.hacker_id ORDER BY COUNT(s.submission_id) DESC, h.hacker_id LIMIT 1) AS hacker_id,
    (SELECT COUNT(s.submission_id) FROM hackers h JOIN submissions s ON s.hacker_id = h.hacker_id WHERE s.submission_date = sd GROUP BY h.hacker_id ORDER BY COUNT(s.submission_id) DESC, h.hacker_id LIMIT 1) AS hacker_cnt

FROM
    (SELECT submission_date sd
     FROM submissions
     GROUP BY submission_date
    ) AS date_tbl

) AS TBL2
JOIN hackers h ON h.hacker_id = TBL2.hacker_id
ORDER BY sd

56  Draw The Triangle 1
SELECT REPEAT('* ', @NUMBER := @NUMBER - 1)
FROM information_schema.tables,(SELECT @NUMBER:=21) t
LIMIT 20


57  Draw The Triangle 2
select repeat("* ", @NUMBER:=@NUMBER+1)
from information_schema.tables, (SELECT @NUMBER:=0) as t
limit 20

58  Print Prime Numbers
SELECT GROUP_CONCAT(NUMB SEPARATOR '&')
FROM (
    SELECT @num:=@num+1 as NUMB FROM
    information_schema.tables t1,
    information_schema.tables t2,
    (SELECT @num:=1) tmp
) tempNum
WHERE NUMB<=1000 AND NOT EXISTS(
        SELECT * FROM (
            SELECT @nu:=@nu+1 as NUMA FROM
                information_schema.tables t1,
                information_schema.tables t2,
                (SELECT @nu:=1) tmp1
                LIMIT 1000
            ) tatata
        WHERE FLOOR(NUMB/NUMA)=(NUMB/NUMA) AND NUMA<NUMB AND NUMA>1
    )