Practice Questions Flashcards
Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn’t want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade – i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use “NULL” as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.
SELECT (CASE WHEN GRADE < 8 THEN NULL ELSE NAME END), GRADE, MARKS FROM STUDENTS JOIN GRADES ON MARKS BETWEEN MIN_MARK AND MAX_MARK ORDER BY GRADE DESC, NAME ASC;
Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.
https://www.hackerrank.com/challenges/full-score/problem
SELECT H.Hacker_Id, H.Name FROM Submissions S
INNER JOIN Hackers H ON H.Hacker_Id = S.Hacker_Id
INNER JOIN Challenges C ON C.Challenge_Id = S.Challenge_Id
INNER JOIN Difficulty D ON D.Difficulty_level = C.Difficulty_level
WHERE D.Score = S.Score
GROUP BY H.Hacker_Id, H.Name
HAVING COUNT() >= 2 ORDER BY COUNT() DESC, H.Hacker_Id ASC;
Harry Potter and his friends are at Ollivander’s with Ron, finally replacing Charlie’s old broken wand.
Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron’s interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.
https://www.hackerrank.com/challenges/harry-potter-and-wands/problem
SELECT W1.Id, WT.Age, W1.Coins_Needed, W1.Power FROM Wands W1
JOIN Wands_Property WT ON W1.Code = WT.Code
WHERE WT.Is_Evil != 1 AND W1.Coins_Needed = (
SELECT min(W2.Coins_Needed) FROM Wands W2 JOIN Wands_Property WP ON W2.code = WP.code WHERE WT.age = WP.Age AND W1.power = W2.power)
ORDER BY W1.Power DESC, WT.Age DESC;
Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.
https://www.hackerrank.com/challenges/challenges/problem
SELECT H.Hacker_Id, H.Name, COUNT(C.Challenge_Id)
FROM Hackers H JOIN Challenges C
ON H.Hacker_Id = C.Hacker_Id
GROUP BY H.Hacker_Id, H.Name
HAVING COUNT(*) IN (
SELECT MAX(Total)
FROM (
SELECT COUNT(Challenge_Id) Total
FROM Challenges
GROUP BY Hacker_Id
)
) OR COUNT (*) IN (
SELECT Total
FROM (
SELECT COUNT(Challenge_Id) Total
FROM Challenges
GROUP BY Hacker_Id
)
GROUP BY Total
HAVING COUNT(Total) = 1
)
ORDER BY COUNT(C.Challenge_Id) DESC, H.Hacker_Id;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 0 from your result.
SELECT Temp.Hacker_Id, Temp.Name, SUM (Temp.score)
FROM (
(
SELECT S.Challenge_Id, S.Hacker_Id, H.Name, MAX(Score) score
FROM Submissions S, Hackers H
WHERE H.Hacker_Id = S.Hacker_Id
GROUP BY S.Challenge_Id, S.Hacker_Id, H.Name
) Temp
)
GROUP BY Temp.Hacker_Id, Temp.Name
HAVING SUM(Temp.score) > 0
ORDER BY SUM(Temp.score) DESC, Temp.Hacker_Id;
Query the average population for all cities in CITY, rounded down to the nearest integer.
SELECT ROUND(AVG(Population)) FROM City;
Query the difference between the maximum and minimum populations in CITY.
SELECT MAX(Population) - MIN(Population) FROM City;
Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard’s key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeroes removed), and the actual average salary.
Write a query calculating the amount of error (i.e.: “actual-miscalculated” average monthly salaries), and round it up to the next integer.
SELECT CEIL(AVG(Salary) - AVG(REPLACE(Salary, ‘0’, ‘’))) FROM Employees;
Query the following two values from the STATION table:
The sum of all values in LAT_N rounded to a scale of 2 decimal places.
The sum of all values in LONG_W rounded to a scale of 2 decimal places.
SELECT ROUND(SUM(Lat_N), 2), ROUND(SUM(Long_W), 2) FROM Station;
Query the sum of Northern Latitudes (LAT_N) from STATION having values greater than 38.7880 and less than 137.2345 . Truncate your answer to 4 decimal places.
SELECT TRUNC(SUM(Lat_N), 4) FROM Station WHERE Lat_N BETWEEN 38.7880 AND 137.2345;
Query the Western Longitude (LONG_W) where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780. Round your answer to 4 decimal places.
SELECT ROUND(Long_W, 4) FROM Station WHERE Lat_N = (
SELECT MIN(Lat_N) FROM Station WHERE Lat_N > 38.7780
);Consider P1(a,c) and P2(b,d) to be two points on a 2D plane where (a,b) are the respective minimum and maximum values of Northern Latitude (LAT_N) and (c,d) are the respective minimum and maximum values of Western Longitude (LONG_W) in STATION. Query the Euclidean Distance between points P1 and P2 and format your answer to display 4 decimal digits.
SELECT TRUNC(SQRT(POWER(MIN(Lat_N) - MAX(Lat_N), 2) + POWER(MIN(Long_W) - MAX(Long_W), 2)), 4) FROM Station;
A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to 4 decimal places.
SELECT ROUND(MEDIAN(Lat_N), 4) FROM STATION;
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.
https://www.hackerrank.com/challenges/placements/problem
SELECT S.Name FROM Students S JOIN Friends F ON S.Id = F.Id JOIN Packages SP ON S.Id = SP.Id JOIN Packages FP ON F.Friend_Id = FP.Id WHERE FP.Salary > SP.Salary ORDER BY FP.Salary;
You are given a table, Functions, containing two columns: X and Y. Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.
Write a query to output all such symmetric pairs in ascending order by the value of X.
SELECT f1.X, f1.Y FROM Functions f1
WHERE f1.X = f1.Y GROUP BY f1.X, f1.Y HAVING COUNT(*) > 1
UNION
SELECT f1.X, f1.Y FROM Functions f1, Functions f2
WHERE f1.X <> f1.Y AND f1.X = f2.Y AND f1.Y = f2.X AND f1.X < f1.Y
ORDER BY X;
https://nifannn.github.io/2017/10/24/SQL-Notes-Hackerrank-Symmetric-Pairs/
