Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

11 cards

()

Section 1

Preview this deck

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.

Front

1 / 11

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (11)

Section 1

(11 cards)

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.

Front

select h.hacker_id,h.name from hackers h,challenges c ,difficulty d,submissions s where h.hacker_id=s.hacker_id and c.challenge_id=s.challenge_id and c.difficulty_level=d.difficulty_level and s.score=d.score group by h.hacker_id,h.name having count(h.hacker_id)>1 order by count(h.hacker_id) desc,h.hacker_id;

Back

FLOOR AND CEIL FUNCTIONS

Front

FLOOR function in sql rounds up the positive or negative value to the next least integer value. ceil function used to get the smallest integer valuewhich is greater than or equal to the given value.

Back

2.Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

Front

SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,LENGTH(CITY),1)) IN ('a','e','i','o','u');

Back

all city names from tables city and country

Front

SELECT CITY.NAME from CITY,COUNTRY WHERE CITY.COUNTRYCODE=COUNTRY.CODE AND COUNTRY.CONTINENT="Africa";

Back

Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.

Front

SELECT DISTINCT CITY FROM STATION WHERE CITY RLIKE '^[^aeiou].*.[^aeiou]$';

Back

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer. Note: CITY.CountryCode and COUNTRY.Code are matching key columns. Do not include continents without cities in your output. Input Format The CITY and COUNTRY tables are described as follows:

Front

SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM CITY , COUNTRY WHERE CITY.COUNTRYCODE = COUNTRY.CODE GROUP BY COUNTRY.CONTINENT; round(avg(City.Population)-0.5) to round it down to nearest Integer. - also works

Back

.Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

Front

SELECT distinct FROM STATION where CITY regexp '^[aeiou]'; SELECT distinct CITY FROM STATION WHERE CITY LIKE 'A%' OR CITY LIKE 'E%' OR CITY LIKE 'O%' OR CITY LIKE 'I%' OR CITY LIKE 'U%';

Back

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks. Grades contains the following data: 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. Write a query to help Eve.

Front

SELECT (CASE WHEN GRADE > 7 THEN STUDENTS.NAME ELSE 'NULL' END), GRADES.GRADE, STUDENTS.MARKS FROM STUDENTS INNER JOIN GRADES ON STUDENTS.MARKS BETWEEN GRADES.MIN_MARK AND GRADES.MAX_MARK ORDER BY GRADES.GRADE DESC, STUDENTS.NAME; SELECT IF (S.Marks < 70, 'NULL', S.Name), G.Grade, S.Marks FROM Students AS S, Grades AS G WHERE S.Marks BETWEEN G.Min_Mark AND G.Max_Mark ORDER BY G.GRADE DESC, S.NAME;

Back

Query the Name of any student in STUDENTS who scored higher than Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

Front

SELECT NAME FROM STUDENTS WHERE MARKS>75 ORDER BY SUBSTR(NAME,-3),ID;

Back

Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.

Front

UPDATE SALARY SET SEX = (CASE WHEN sex = 'F' THEN 'M' ELSE 'F' END); UPDATE salary SET sex = IF(sex = 'm', 'f', 'm')

Back

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.

Front

SELECT W.ID, P.AGE, W.COINS_NEEDED, W.POWER FROM WANDS W, WANDS_PROPERTY P WHERE W.CODE = P.CODE AND P.IS_EVIL = '0' AND COINS_NEEDED = (SELECT MIN(COINS_NEEDED) FROM WANDS WHERE CODE = W.CODE AND POWER =W.POWER) GROUP BY P.AGE, W.COINS_NEEDED, W.ID, W.POWER ORDER BY W.POWER DESC, P.AGE DESC;

Back