SELECT *
FROM CITY
WHERE POPULATION > 100000 AND COUNTRYCODE = 'USA'
SELECT NAME
FROM CITY
WHERE POPULATION > 120000 AND COUNTRYCODE = 'USA'
SELECT *
FROM CITY
SELECT *
FROM CITY
WHERE ID = '1661'
SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN'
SELECT NAME
FROM CITY
WHERE COUNTRYCODE = 'JPN'
SELECT CITY, STATE
FROM STATION
SELECT DISTINCT CITY
FROM STATION
WHERE (ID%2 = 0)
SELECT COUNT(CITY) - COUNT(DISTINCT CITY)
FROM STATION
(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)
SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY,1) IN ('a','e','i','o','u')
SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY,1) IN('u','e','o','a','i')
SELECT CITY
FROM STATION
WHERE (LEFT(CITY,1) IN('a','e','o','u','i')) AND (RIGHT(CITY,1) IN('a','e','o','u','i'))
SELECT DISTINCT CITY
FROM STATION
WHERE NOT (LEFT(CITY,1) IN('a','e','o','u','i'))
SELECT DISTINCT CITY
FROM STATION
WHERE NOT (RIGHT(CITY,1) IN('a','e','o','u','i'))
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'))
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'))
SELECT NAME
FROM STUDENTS
WHERE MARKS > 75
ORDER BY RIGHT(NAME,3), ID ASC
select name from Employee
order by name asc
select name from Employee
where salary > 2000 and
months < 10
order by employee_id asc
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
(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)
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
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
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
select sum(population) from CITY
where district = 'California'
select avg(population) from CITY
where district = 'California'
select round(avg(population)) from CITY
select sum(population) from CITY
where countrycode = 'JPN'
select max(population) - min(population) from CITY
select round(avg(salary)) - round(avg(replace(salary,'0',''))) from employees
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 1To use WHERE:
select salary*months, count(*) from employee
where salary*months = (select max(salary*months) from employee)
select round(sum(lat_n),2),' ', round(sum(long_w),2) from STATION
select round(sum(lat_n),4) from STATION
where lat_n > 38.7880 and lat_n < 137.2345
select round(max(lat_n),4) from STATION
where lat_n < 137.2345;
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
select round(min(lat_n),4) from STATION
where lat_n > 38.7780
select round(long_w,4) from STATION
where lat_n = (select min(lat_n) from STATION where lat_n > 38.7780)
select round(abs(min(lat_n) - min(long_w))+abs(max(lat_n)-max(long_w)),4) from STATION
select round(sqrt(power(min(lat_n) - min(long_w),2) + power(max(lat_n)-max(long_w),2)),4) from STATION
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)/2Solution 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;
select sum(city.population) from city
inner join country on city.countrycode = country.code
where country.continent = 'Asia'
select city.name from city
inner join country on city.countrycode = country.code
where country.continent = 'Africa'
select country.continent, round(avg(city.population)-0.5) from city
inner join country on city.countrycode = country.code
group by country.continent
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
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-SUMMARY:
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
Summary:
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_idSolution 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