Hackerrank: The Pads

-
Problem Statement:

Generate the following two result sets:
  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).
  2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

    There are a total of [occupation_count] [occupation]s.

    where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.
Note: There will be at least two entries in the table for each type of occupations.


SOLUTION:

here is what you have been looking for.. the query format is for the oracle SQL. The problem uses the concat() and count() functions and the query is devided into the two parts. part 1 is for generating the first result set and 2nd part is to generate 2nd result set..

select case when Occupation='Doctor' then concat(concat(concat(Name,'('),'D'),')') when Occupation='Actor' then concat(concat(concat(Name,'('),'A'),')') when Occupation='Professor' then concat(concat(concat(Name,'('),'P'),')') when Occupation='Singer' then concat(concat(concat(Name,'('),'S'),')')end as name
from OCCUPATIONS
order by name asc;

select concat(concat(concat(concat('There are a total of ',count(Occupation)),' '),lower(Occupation)),'s.')
from OCCUPATIONS
group by Occupation
order by Count(Occupation) asc,lower(Occupation); 

_______________________________The End__________________________________
Hoping this would be helpful to you, if not, post your problem in the comment section and i will try to get back to you as soon as possible.

tell me, how do you like this solution? and what is there that i can improve...

follow me on hackerrank: www.hackerrank.com/satyansh_sagar

Comments

Post a Comment

Popular posts from this blog

Contest Leaderboard: hackerrank problem (Level medium)

-
Problem Statement: You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too! The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id , name , and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id . Exclude all hackers with a total score of from your result. Tables: Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.  Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission.   Solutions: select h.hacker_id,h.name,sum(sscore) from Hackers h inner join (select s.hacker_id,max(score) as sscore from Submissions s g
Read more

Placements: SQL Advance join (Hackerrank Problem)

-
Image
Problem: You are given three tables:  Students , Friends and Packages.   Students contains two columns: ID  and Name . Friends contains two columns: ID and Friend_ID ( ID of the ONLY best friend). Packages  contains two columns: ID and Salary (offered salary in $ thousands per month). Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer. Solution: select temp1.sn from (select S.ID si,S.Name sn,P.Salary ps from Students S join Packages P on S.ID=P.ID) temp1 join (select FF.ID fi,FF.Friend_ID fd,PP.Salary pps from Friends FF join Packages PP on FF.Friend_ID=pp.ID) temp2 on temp1.si=temp2.fi and temp1.ps<temp2.pps order by temp2.pps asc ; Explanation: As the problem states that we have to select the name of the friend whose salary is lesser than what his/her friend is ha
Read more

The Occupation: hackerrank problem

-
Problem Statement: 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. Table:   'OCCUPATIONS' table contain the column 'Name' and 'Occupation' Solution: select doc,prof,singer,act from ( select row_number() over(partition by Occupation order by Name) ron,Name as n,Occupation as o from OCCUPATIONS ) pro pivot (max(n)  for o in ('Doctor' as doc,'Actor' as act,'Professor' as prof,'Singer' as singer))  order by ron asc; Explanation: in the above query statement i am using row_number() function with the 'partition by' clause to append one column that contains row_number for each row in the subquery. in the subquery while assigning row_number() we
Read more