Simply SQL - The Sequel

Q1 : Employee Names

Ans

SELECT name from employee

ORDER BY name;

Q2 : Employee Salaries

Ans

SELECT name FROM employee

WHERE salary > 2000 AND months < 10

ORDER BY employee_id;

Q3 : Top Earners

Ans

SELECT (salarymonths) AS total_earnings, COUNT() FROM employee

GROUP BY total_earnings

ORDER BY total_earnings DESC

LIMIT 1;

Q4 : New Companies

Ans

SELECT C.company_code, C.founder,

COUNT(DISTINCT E.lead_manager_code), COUNT(DISTINCT E.senior_manager_code), COUNT(DISTINCT E.manager_code), COUNT(DISTINCT E.employee_code)

FROM company AS C

JOIN employee AS E

ON C.company_code = E.company_code

GROUP BY C.company_code, C.founder;

Q5: Top Competitors*

Ans

SELECT H.hacker_id, H.name FROM challenges AS C

JOIN difficulty AS D ON C.difficulty_level = D.difficulty_level

JOIN submissions AS S ON C.hacker_id = S.hacker_id

JOIN hackers AS H ON C.hacker_id = H.hacker_id

WHERE (D.score = S.score)

GROUP BY C.hacker_id

HAVING COUNT(*) > 1

ORDER BY COUNT(*) DESC, C.hacker_id;

Q6 : Ollivander's Inventory*

Ans

SELECT W.id, WP.age, MIN(W.coins_needed), W.power FROM wands AS W JOIN wands_property AS WP

ON W.code = WP.code

WHERE WP.is_evil = 0

GROUP BY WP.age, W.power

ORDER BY W.power DESC, WP.age DESC;