Hackerrank Brush Problem Summary

1.You is given a table, Projects, containing three columns: task_id, start_date and End_da Te. It is guaranteed that the difference between the end_date and the start_date are equal to 1 Day For each row in the table.

If the end_date of the tasks is consecutive, then they is part of the same project. Samantha is interested in finding, the total number of different projects completed.

Write a query to output the start and end dates of projects listed by the number of th it took to complete the project I n Ascending order. If there is more than one project that has the same number of completion days and then order by the start date of the Projec T.

Answer:

SET sql_mode = ';
SELECT start_date, 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 (end_date, start_date), start_date;

Analysis: The answer to the big guy in the discussion area is MySQL. First, the sample table shows that the previous end date is not the start date, as long as the start date is not equal to the end date. That is, this is not a sequential, not a project. Also, make sure that the end date is not the start date. And the start date as the classification standard, using DATEDIFF (data 1, data 2) to return the parameters between two dates. Set Sql_mode=: It defines the SQL syntax that you should support for MySQL, the checksum of the data, and so on.

2.You is given three tables: Students, Friends and Packages. Students contains-columns: ID and Name. Friends contains-columns: ID and friend_id (ID of the only best Friend). Packages contains-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 is ordered by the salary amount offered to the best friends. It is guaranteed that no, students got same salary offer.

Answer:

SELECT S.name
From Students S joins Friends F on S.id=f.id joins Packages P1 on S.id=p1.id joins Packages P2 on f.friend_id=p2.id
WHERE P2. Salary>p1. Salary
ORDER by P2. Salary;

Analysis: The student ID is associated with the ID of the friend table, the ID of the student is associated with the ID of the payroll table, the ID of the friend table is associated with the ID of the payroll table, so select the three Tables association, and the salary associated with the student table is less than the salary associated with the friend table.

3.

Query an alphabetically ordered list of all names in occupations, immediately followed by the first Lett Er of each profession as a parenthetical (i.e.: enclosed in parentheses). For example:,,, AnActorName(A) ADoctorName(D) AProfessorName(P) and ASingerName(S) .

Query the number of ocurrences of each occupation in occupations. Sort the occurrences in ascending order, and output them in the following format:

Answer:

SELECT CONCAT (Name, ' (', SUBSTR (occupation,1,1), ') ')
From occupations
ORDER by Name;
SELECT CONCAT (' There is a total of ', COUNT (Occupation), ', LOWER (Occupation), ' s. ')
From occupations
GROUP by occupation
ORDER by COUNT (occupation), occupation;

Parsing: The Concat function is a string that is linked to multiple strings. SUBSTR is a string intercept: substr (column name, start point, length) intercepts substrings of the specified range length

4. Write a query identifying the type of each record in the Triangles table using its three side lengt Hs. 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 if 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 or a=c or b=c then ' isosceles '
ELSE ' Scalene '
END
from triangles;

Parsing: Using Case search function

Hackerrank Brush Problem Summary