All Questions Combined in mysql
Rishabh Goswami Assignment 4 DBMS Rishabh_Goswami_DBMS_A4user_6227789376
mysql
4 years ago
5.4 kB
6
Indexable
-- 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;
Editor is loading...