hackerrank刷題總結

標籤：hacker   sql文法   scale   apr   tee   說明   friend   orm   equal   

 

1.You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.

If the End_Date of the tasks are consecutive, then they are 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 days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.

答案：

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;

分析：參考討論區大佬的答案，是mysql。首先通過範例表發現，只要開始日期不等於結束日期，說明上一個結束日期不是這個的開始日期也就是說這不是一個連續的，不是一個項目。同理還要保證結束日期不是開始日期。並以開始日期作為分類標準，用datediff（資料1，資料2）返回兩個日期之間的參數。set sql_mode=‘‘：它定義了你MySQL應該支援的sql文法，對資料的校正等等

2.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.

答案：

SELECT s.NAME
FROM Students s JOIN Friends f ON s.ID=f.ID JOIN Packages p1 ON s.ID=p1.ID JOIN Packages p2 ON f.Friend_ID=p2.ID
WHERE p2.Salary>p1.Salary
ORDER BY p2.Salary;

分析：學生的id與朋友表的id是關聯的，學生的id與工資表的id是關聯的，朋友表的id與工資表的id是關聯的，所以選擇三表關聯，與學生表關聯的工資小於與朋友表關聯的工資。

3.

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).

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

答案：

SELECT CONCAT(Name,‘(‘,SUBSTR(Occupation,1,1),‘)‘)
FROM OCCUPATIONS
ORDER BY Name;
SELECT CONCAT(‘There are a total of ‘,COUNT(Occupation),‘ ‘,LOWER(Occupation),‘s.‘)
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation),Occupation;

解析：concat函數是將多個字串連成一個字串。substr是字串的截取：substr（列名，開始點，長度）截取指定範圍長度的子字串

4.   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

答案：

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 OR A=C OR B=C THEN ‘Isosceles‘
ELSE ‘Scalene‘
END
FROM TRIANGLES;

解析：使用case搜尋函數

 

hackerrank刷題總結