All Questions Combined in mysql

-- Easy:
-- Question 1:

SELECT * FROM city WHERE countrycode='JPN';





-- Question 2:

SELECT DISTINCT city FROM station WHERE RIGHT(city, 1) NOT IN ('a', 'e', 'i', 'o', 'u');






-- Question 3:


SELECT name FROM students WHERE marks > 75 ORDER BY RIGHT(name, 3), ID ASC;





-- Question 4:


SELECT 
    CASE 
        WHEN A >= (B + C) OR B >= (A + C) OR C >= (A + B) THEN 'Not A Triangle'
        WHEN A = B AND A = C THEN 'Equilateral'
        WHEN A = B OR B = C OR A = C THEN 'Isosceles'
        ELSE 'Scalene'
    END
FROM triangles;







--  ===================================== --

-- Medium:

-- Question 1 (The PADS):


SELECT CONCAT(name,'(',LEFT(occupation, 1),')') 
FROM occupations 
ORDER BY name;

SELECT CONCAT('There are a total of ', COUNT(*), ' ', LOWER(occupation), 's.') 
FROM occupations 
GROUP BY occupation 
ORDER BY COUNT(*), occupation;








-- Question 2 (New Companies):

SELECT comp.Company_code,
       comp.Founder,
  (SELECT COUNT(DISTINCT Lead_Manager_Code)
   FROM Lead_Manager LEAD
   WHERE LEAD.Company_code = comp.Company_code),
  (SELECT COUNT(DISTINCT Senior_Manager_Code)
   FROM Senior_Manager SENIOR
   WHERE SENIOR.Company_code = comp.Company_code),
  (SELECT COUNT(DISTINCT Manager_Code)
   FROM Manager MANAGER
   WHERE MANAGER.Company_code = comp.Company_code),
  (SELECT COUNT(DISTINCT Employee_Code)
   FROM Employee EMPLYEE
   WHERE EMPLYEE.Company_code = comp.Company_code)
FROM Company comp
ORDER BY comp.Company_code ASC;






-- Question 3 (The Report):


SELECT IF(grades.grade>=8, students.name, NULL),grades.grade, students.marks
FROM grades, students

WHERE students.marks BETWEEN grades.min_mark AND grades.max_mark
ORDER BY grades.grade DESC, students.name;








-- Question 4 (Top Competitors):


SELECT hack.hacker_id, hack.name
FROM Hackers hack
JOIN Submissions subs ON hack.hacker_id = subs.hacker_id
JOIN Challenges chal ON chal.challenge_id = subs.challenge_id
JOIN Difficulty diff ON chal.difficulty_level = diff.difficulty_level
WHERE subs.score = diff.score
GROUP BY 1,2
HAVING COUNT(DISTINCT chal.challenge_id) > 1
ORDER BY COUNT(DISTINCT chal.challenge_id) DESC, hack.hacker_id ASC;






-- Question 5 (SQL Project Planning):


SELECT Start_Date, min(End_Date) FROM 
 (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) temp1 ,
 (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) temp2

WHERE Start_Date < End_Date
GROUP BY Start_Date
ORDER BY DATEDIFF(min(End_Date), Start_Date) ASC, Start_Date ASC;






--  Question 6 (Symmetric Pairs):


SELECT f1.x AS X, f1.y AS Y

FROM   functions f1, functions f2

WHERE  f1.x = f2.y AND f1.y = f2.x AND f1.x < f1.y

UNION
SELECT X,Y

FROM   functions WHERE  X = Y GROUP  BY X,Y

HAVING Count(X) > 1
ORDER  BY X ASC;





-- Question 7 (Ollivander's Inventory):


SELECT wand.id, property.age, wand.coins_needed, wand.power 
FROM wands wand JOIN wands_property property ON wand.code = property.code 

WHERE  property.is_evil = 0 AND wand.coins_needed = 

(SELECT Min(wandTemp.coins_needed) FROM wands wandTemp  JOIN wands_property wandTempProperty ON wandTemp.code = wandTempProperty.code WHERE  property.age = wandTempProperty.age AND wand.power = wandTemp.power) 

ORDER  BY wand.power DESC, property.age DESC;






-- Question 8 (Placements):


Select stud.name FROM students stud 
JOIN friends Friend ON stud.id = Friend.id
JOIN packages temp1 ON stud.id = temp1.id
JOIN packages temp2 ON Friend.friend_id = temp2.id
WHERE temp2.salary > temp1.salary
ORDER BY temp2.salary;





--  ===================================== --

Hard:


-- Question 1 (Interviews):


SELECT contest.contest_id, contest.hacker_id, contest.name, sum(total_submissions), sum(total_accepted_submissions), sum(total_views),sum(total_unique_views)

FROM contests Contest 
join colleges College ON contest.contest_id = College.contest_id 
join challenges Challenge ON  College.college_id = Challenge.college_id 
left join

(SELECT challenge_id, sum(total_views) AS total_views, sum(total_unique_views) AS total_unique_views FROM view_stats group by challenge_id) temp1 ON Challenge.challenge_id = temp1.challenge_id 
left join

(SELECT challenge_id, sum(total_submissions) AS total_submissions, sum(total_accepted_submissions) AS total_accepted_submissions FROM submission_stats group by challenge_id) temp2 ON Challenge.challenge_id = temp2.challenge_id group by contest.contest_id, contest.hacker_id, contest.name

HAVING sum(total_submissions)!=0 OR sum(total_accepted_submissions)!=0 OR sum(total_views)!=0 OR sum(total_unique_views)!=0 

order by contest_id;






-- Question 2 (15 Days of Learning SQL):



SELECT submission_date, (SELECT COUNT(distinct hacker_id) FROM Submissions temp2  
 WHERE temp2.submission_date = temp1.submission_date AND (SELECT COUNT(DISTINCT temp3.submission_date) FROM Submissions temp3 WHERE temp3.hacker_id = temp2.hacker_id 
 AND temp3.submission_date < temp1.submission_date) = dateDIFF(temp1.submission_date , '2016-03-01')),
 
(SELECT hacker_id FROM submissions temp2 WHERE temp2.submission_date = temp1.submission_date group by hacker_id order by count(submission_id) DESC , hacker_id limit 1) as temporary,
(SELECT name FROM hackers WHERE hacker_id = temporary) FROM (SELECT DISTINCT submission_date FROM submissions) temp1 

group by submission_date;