SELECT

Question 1:

image

SELECT *
FROM CITY
WHERE POPULATION > 100000 AND COUNTRYCODE = 'USA'

Question 2:

image

SELECT NAME
FROM CITY
WHERE POPULATION > 120000 AND COUNTRYCODE = 'USA'

Question 3:

image

SELECT *
FROM CITY

Question 4:

image

SELECT *
FROM CITY
WHERE ID = '1661'

Question 5:

image

SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN'

Question 6:

image

SELECT NAME
FROM CITY
WHERE COUNTRYCODE = 'JPN'

Question 7:

image

SELECT CITY, STATE
FROM STATION

Question 8:

image

SELECT DISTINCT CITY
FROM STATION
WHERE (ID%2 = 0)

Question 9:

image

SELECT COUNT(CITY) - COUNT(DISTINCT CITY)
FROM STATION

Question 10:

image

(SELECT CITY ,LENGTH(CITY)
FROM STATION
WHERE 
       LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION)
ORDER BY CITY
LIMIT 1)
UNION
(SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE
       LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION)
ORDER BY CITY
LIMIT 1)

Question 11:

image

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY,1) IN ('a','e','i','o','u')

Question 12:

image

SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY,1) IN('u','e','o','a','i')

Question 13:

image

SELECT CITY
FROM STATION
WHERE (LEFT(CITY,1) IN('a','e','o','u','i')) AND (RIGHT(CITY,1) IN('a','e','o','u','i'))

Question 14:

image

SELECT DISTINCT CITY
FROM STATION
WHERE  NOT (LEFT(CITY,1) IN('a','e','o','u','i'))

Question 15:

image

SELECT DISTINCT CITY
FROM STATION
WHERE  NOT (RIGHT(CITY,1) IN('a','e','o','u','i'))

Question 16:

image

SELECT DISTINCT CITY
FROM STATION
WHERE NOT(LEFT(CITY,1) IN('a','e','o','u','i')) OR  NOT(RIGHT(CITY,1) IN('a','e','o','u','i'))

Question 17:

image

SELECT DISTINCT CITY
FROM STATION
WHERE
    NOT (LEFT(CITY,1) IN('a','e','o','u','i'))
    AND
    NOT (RIGHT(CITY,1) IN('a','e','o','u','i'))

Question 18:

image

SELECT NAME
FROM STUDENTS
WHERE MARKS > 75
ORDER BY RIGHT(NAME,3), ID ASC

Question 19:

image

select name from Employee
order by name asc

Question 20:

image

select name from Employee
where salary > 2000 and
        months < 10
order by employee_id asc

ADVANCE SELECT

Question 1: (Type of Triangle)

image

image

select  
    case when (A + B <= C) or (A + C <= B) or (B + C <= A) then 'Not A Triangle'
         when (A = B) and (B = C) then 'Equilateral'
         when ((A = B) and (A != C))
              or ((A = C) and (A != B)) 
              or ((B = C) and (B != A)) then 'Isosceles'
         when (A != B) and (B != C) and (A != C) then 'Scalene'
    end
from TRIANGLES

Reference 1

Reference 2

Question 2:

image

image

image

(SELECT CONCAT(NAME,'(',LEFT(OCCUPATION,1),')')
FROM OCCUPATIONS
ORDER BY NAME);
(SELECT CONCAT('There are total ',COUNT(NAME),' ',LCASE(OCCUPATION),'s.')
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(NAME) ASC, OCCUPATION);
(SELECT CONCAT(NAME,'(',LEFT(OCCUPATION,1),')')
FROM OCCUPATIONS
ORDER BY NAME ASC LIMIT 1000)
UNION
(SELECT CONCAT('There are total ',COUNT(NAME),' ',LCASE(OCCUPATION),'s.')
FROM OCCUPATIONS
GROUP BY OCCUPATION
ORDER BY COUNT(NAME) ASC, OCCUPATION ASC LIMIT 100)

Question 3: (Transpose Table)

image

image

set @r1=0, @r2=0, @r3=0, @r4=0;
select min(Doctor), min(Professor), min(Singer), min(Actor)
from(
  select case when Occupation='Doctor' then (@r1:=@r1+1)
            when Occupation='Professor' then (@r2:=@r2+1)
            when Occupation='Singer' then (@r3:=@r3+1)
            when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
    case when Occupation='Doctor' then Name end as Doctor,
    case when Occupation='Professor' then Name end as Professor,
    case when Occupation='Singer' then Name end as Singer,
    case when Occupation='Actor' then Name end as Actor
  from OCCUPATIONS
  order by Name
) as Temp
group by RowNumber

Question 4:

image

image

select N, case when P is NULL then 'Root'
               when N IN (select P from BST) then 'Inner'
               else 'Leaf'
        end
from BST
order by N

Question 5:

image

image

image

AGGREGATION:

Question 1:

image

Create Input Table:

create table CITY(
id int,
name varchar(17),
countrycode varchar(3),
district varchar(20),
population int,
primary key (id)
);


Insert Into CITY (id, name, countrycode, district, population)
Values (6,'Rotterdam','NLD','Zuid-Holland',593321),
(3878,'Scottsdale','USA','Arizona',202705),
(3965,'Corona','USA','California',124966),
(3973,'Concord','USA','California',121780),
(3977,'Cedar Rapids','USA','Iowa',120758),
(3982,'Coral Springs','USA','Florida',117549),
(4054,'Fairfield','USA','California',92256),
(4058,'Boulder','USA','Colorado',91238),
(4061,'Fall River','USA','Massachusetts',90555);

Answer:

select count(name) from CITY
where population > 100000

Question 2:

image

select sum(population) from CITY
where district = 'California'

Question 3:

image

select avg(population) from CITY
where district = 'California'

Question 4:

image

select round(avg(population)) from CITY

Question 5:

image

select sum(population) from CITY
where countrycode = 'JPN'

Question 6:

image

select max(population) - min(population) from CITY

Question 7:

image

image

select round(avg(salary)) - round(avg(replace(salary,'0',''))) from employees

Question 8:

image

image

image

For more literal code:

select salary*months as earnings, count(*) from employee
group by earnings
order by earnings desc limit 1;

For more lazy:

select salary*months as earnings, count(*) from employee
group by 1
order by 1 desc limit 1

To use WHERE:

select salary*months, count(*) from employee
where salary*months = (select max(salary*months) from employee)

Question 9:

image

select round(sum(lat_n),2),' ', round(sum(long_w),2) from STATION

Question 10:

image

select round(sum(lat_n),4) from STATION
where lat_n > 38.7880 and lat_n < 137.2345

Question 11:

image

select round(max(lat_n),4) from STATION
where lat_n < 137.2345;

Question 12:

image

select round(long_w,4) from STATION
where lat_n = (select max(lat_n) from STATION where lat_n < 137.2345)

or

select round(long_w,4) from STATION
where lat_n < 137.2345
order by lat_n desc limit 1

Question 13:

image

select round(min(lat_n),4) from STATION
where lat_n > 38.7780

Question 14:

image

select round(long_w,4) from STATION
where lat_n = (select min(lat_n) from STATION where lat_n > 38.7780)

Question 15:

image

select round(abs(min(lat_n) - min(long_w))+abs(max(lat_n)-max(long_w)),4) from STATION

Question 16:

image

select round(sqrt(power(min(lat_n) - min(long_w),2) + power(max(lat_n)-max(long_w),2)),4) from STATION

Question 17:

image

Solution 1:

SELECT round(x.lat_n,4) from station x, station y
GROUP BY x.lat_n
HAVING SUM(SIGN(1-SIGN(y.lat_n-x.lat_n))) = (COUNT(*)+1)/2

Solution 2:

set @row_num = 0;
select round(k.lat_n,4) from
    (select @row_num := @row_num + 1 as row_index, p.lat_n from
        (select lat_n from station order by lat_n) as p
        having row_index = (select round((COUNT(*)+1)/2) from station)) as k;

BASIC JOIN:

Question 1 :

image

image

select sum(city.population) from city
inner join country on city.countrycode = country.code
where country.continent = 'Asia'

Question 2 :

image

image

select city.name from city
inner join country on city.countrycode = country.code
where country.continent = 'Africa'

Question 3 :

image

image

select country.continent, round(avg(city.population)-0.5) from city
inner join country on city.countrycode = country.code
group by country.continent

Question 4 :

image

image

image

select
    if(grades.grade < 8, 'NULL', students.name),
    grades.grade,
    students.marks
from students
inner join grades 
    on students.marks >= grades.min_mark and students.marks <= grades.max_mark
order by grades.grade desc, students.name asc, students.marks asc

Question 5 :

image

image

image

image

image

image

image

select m.hacker_id, m.name from(
    select p.hacker_id, p.name, count(case when 
                                p.score = p.full_score then 1 else NULL end) as count_codition from
        (select submissions.hacker_id, hackers.name, submissions.challenge_id,
            challenges.difficulty_level, submissions.score as score,
            difficulty.score as full_score
        from submissions
            inner join challenges
                on submissions.challenge_id = challenges.challenge_id
            inner join difficulty
                on challenges.difficulty_level = difficulty.difficulty_level
            inner join hackers
                on  submissions.hacker_id = hackers.hacker_id) as p
    group by p.hacker_id) as m
where m.count_codition > 1
order by m.count_codition desc, m.hacker_id asc 

Question 6 :

image

image

image

image

image

image

image

QUESTION-SUMMARY:

image

image

SELECT W.id, P.age, W.coins_needed, W.power
FROM Wands W
JOIN Wands_Property P USING (code)
JOIN
    (SELECT W.power, MIN(W.coins_needed) AS min_coins,P.age
    FROM Wands W
    JOIN Wands_Property P USING(code)
    WHERE P.is_evil = 0
    GROUP BY W.power,P.age) AS mins
    ON W.power = mins.power AND W.coins_needed = mins.min_coins AND P.age=mins.age
WHERE p.is_evil=0
ORDER BY W.power DESC, P.age DESC

Question 7 :

image

image

image

image

image

image

image

Summary:

image

Solution 1:

select hackers.hacker_id, hackers.name, new_challenges.challenges_created
from hackers
join
    (select c.hacker_id, c.challenges_created
    from
        (select challenges.hacker_id, count(challenges.challenge_id) as challenges_created
        from challenges
        group by challenges.hacker_id) as c
    left join 
        (select m.challenges_created
            from
                (select t.challenges_created, count(t.challenges_created) as count_challenges_created
                from
                    (select count(challenge_id) as challenges_created
                    from challenges
                    group by hacker_id) as t
                group by t.challenges_created) as m
            where m.challenges_created != (select max(t.challenges_created)
                                           from 
                                                (select count(challenge_id) as challenges_created
                                                from challenges
                                                group by hacker_id) as t) 
                 and m.count_challenges_created >1) as exclude
    on c.challenges_created = exclude.challenges_created
    where exclude.challenges_created IS NULL) as new_challenges
using (hacker_id)
order by new_challenges.challenges_created desc, hackers.hacker_id

Solution 2:

select hackers.hacker_id, hackers.name, new_challenges.challenges_created
from hackers
join
    (select c.hacker_id, c.challenges_created
    from
        (select challenges.hacker_id, count(challenges.challenge_id) as challenges_created
        from challenges
        group by challenges.hacker_id) as c
    where not (c.challenges_created in 
            (select m.challenges_created
            from
                (select t.challenges_created, count(t.challenges_created) as count_challenges_created
                from
                    (select count(challenge_id) as challenges_created
                    from challenges
                    group by hacker_id) as t
                group by t.challenges_created) as m
            where m.challenges_created != (select max(t.challenges_created)
                                           from 
                                                (select count(challenge_id) as challenges_created
                                                from challenges
                                                group by hacker_id) as t) 
                 and m.count_challenges_created >1))) as new_challenges
using (hacker_id)
order by new_challenges.challenges_created desc, hackers.hacker_id

Question 8 :

Question 9 :

Question 10 :

Question 11 :

Question 12 :

Question 13 :

Question 14 :

Question 15 :

Question 16: