Hackerank Q&A

HACKERANK SQL PROBLEMS.

Question:

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

Answer: 

select (case     when occupation= "Doctor" then concat(name,"(D)")
    when occupation= "Professor" then concat(name,"(P)")
    when occupation= "Singer" then concat(name,"(S)")
    when occupation= "Actor" then concat(name,"(A)")
end) 
from OCCUPATIONS
ORDER BY name;
select 'There are a total of',count(occupation),concat(lower(occupation),'s.')
from occupations
group by occupation 
order by count(occupation), occupation;

other approaches

select concat(name,'(',left(occupation,1),')') from occupations order by name;select concat('There are total ',count(name),' ',lower(occupation),'s.') from occupations 

group by occupation order by count(name);

Question :

Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

• Equilateral: It's a triangle with  sides of equal length.
• Isosceles: It's a triangle with  sides of equal length.
• Scalene: It's a triangle with  sides of differing lengths.
• Not A Triangle: The given values of A, B, and C don't form a triangle.

Answer:

SELECT(CASE    WHEN (A+B<=C)or(A+C<=B)or(B+C<=A) THEN "Not A Triangle"
    WHEN (A=B)and(B=C) THEN "Equilateral"
    WHEN ((A=B)and(B!=C))or((A=C)and(B!=C))or((B=C)and(B!=A)) and     
    ((A+B>C)and(A+C>B)and(B+C>A)) THEN "Isosceles"
    WHEN  ((A!=B)and(B!=C)and(A!=C)) and (A+B>C)and(A+C>B)and(B+C>A)
    THEN "Scalene"
    
END)
FROM triangles;

Question:

Given a string, , matching the regular expression [A-Za-z !,?._'@]+, split the string into tokens. We define a token to be one or more consecutive English alphabetic letters. Then, print the number of tokens, followed by each token on a new line.

Note: You may find the String.split method helpful in completing this challenge.

Input Format

A single string, .

Constraints

•  is composed of any of the following: English alphabetic letters, blank spaces, exclamation points (!), commas (,), question marks (?), periods (.), underscores (_), apostrophes ('), and at symbols (@).

Output Format

On the first line, print an integer, , denoting the number of tokens in string  (they do not need to be unique). Next, print each of the  tokens on a new line in the same order as they appear in input string .

Sample Input

He is a very very good boy, isn't he?

Sample Output

10
He
is
a
very
very
good
boy
isn
t
he

Explanation

We consider a token to be a contiguous segment of alphabetic characters. There are a total of  such tokens in string , and each token is printed in the same order in which it appears in string .

Answer:

public class Solution {

    public static void main(String[] args) {
        Scanner scan = new Scanner(System.in);       

        String s = scan.nextLine().trim();
        if (s.length() == 0) {
            System.out.println("None");        

        } else {
            String[] arrOfStr = s.split("[1-9 !,?._'@]+");           

            int strlength = arrOfStr.length;            

            if (s.length() < 400000) {
                System.out.println(strlength);               

                for (String i : arrOfStr) {
                    System.out.println(i);       

               }
            }
        }
        scan.close();    }
}

Question:

We define an employee's total earnings to be their monthly  worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as  space-separated integers.

Input Format

The Employee table containing employee data for a company is described as follows:

where employee_id is an employee's ID number, name is their name, months is the total number of months they've been working for the company, and salary is the their monthly salary.

Sample Input

Sample Output

69952 1

Explanation

The table and earnings data is depicted in the following diagram:

The maximum earnings value is . The only employee with earnings  is Kimberly, so we print the maximum earnings value () and a count of the number of employees who have earned  (which is ) as two space-separated values.

Answer:

select max(months*salary),count(name)
from employee
where months*salary=(select max(months*salary) from employee);