SELECT CONCAT(Name, "(", LEFT(Occupation, 1), ")") FROM OCCUPATIONS ORDER BY Name; SELECT CONCAT("There are a total of ", COUNT(Occupation), " ", LCASE(Occupation) , "s.") FROM OCCUPATIONS GROUP BY Occupation ORDER BY COUNT(Occupation), Occupation;
31 January 2018
The PADS
My solution:
Type of Triangle
My solution:
SELECT IF(( A + B > C AND ABS(A - B) < C ) AND ( A + C > B AND ABS(A - C) < B ) AND ( B + C > A AND ABS(B - C) < A ), IF (A = B AND A = C AND B = C, "Equilateral", IF(A = B OR B = C OR A = C, "Isosceles", "Scalene")), "Not A Triangle") FROM TRIANGLES;
Employee Salaries
My solution:
SELECT name FROM Employee WHERE salary > 2000 AND months < 10 ORDER BY employee_id;
Higher Than 75 Marks
My solution:
SELECT Name FROM STUDENTS WHERE Marks > 75 ORDER BY RIGHT(Name, 3), ID;
Weather Observation Station 12
My solution:
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP "^[^AEIOU].*[^aeiou]$";
Weather Observation Station 5
My solution:
(SELECT CITY, LENGTH(CITY) FROM STATION GROUP BY CITY HAVING LENGTH(CITY) <= (SELECT MIN(LENGTH(CITY)) FROM STATION) LIMIT 1) UNION (SELECT CITY, LENGTH(CITY) FROM STATION GROUP BY CITY HAVING LENGTH(CITY) >= (SELECT MAX(LENGTH(CITY)) FROM STATION) LIMIT 1);
Weather Observation Station 11
My solution:
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP "^.*[^aeiou]$" OR CITY REGEXP "^[^AEIOU].*$";
Weather Observation Station 10
My solution:
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP "^.*[^aeiou]$";
Weather Observation Station 9
My solution:
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP "^[^AEIOU].*$";
Weather Observation Station 8
My solution:
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP "^(A|E|I|O|U).*(a|e|i|o|u)$";
Weather Observation Station 7
My solution:
SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE "%A" OR CITY LIKE "%E" OR CITY LIKE "%I" OR CITY LIKE "%O" OR CITY LIKE "%U";
Weather Observation Station 6
My solution:
SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE "A%" OR CITY LIKE "E%" OR CITY LIKE "I%" OR CITY LIKE "O%" OR CITY LIKE "U%";
30 January 2018
Revising the Select Query II
My solution:
SELECT NAME FROM CITY WHERE POPULATION > 120000 AND COUNTRYCODE = "USA";
Revising the Select Query I
My solution:
SELECT * FROM CITY WHERE POPULATION > 100000 AND COUNTRYCODE = "USA";
24 January 2018
legsCount
My solution:
DROP PROCEDURE IF EXISTS legsCount; CREATE PROCEDURE legsCount() SELECT SUM(IF(type = "human", 2, 4)) AS summary_legs FROM creatures ORDER BY id;
Labels:
Arcade,
CodeSignal,
Databases,
MySQL,
Time for Tricks
23 January 2018
websiteHacking
My solution:
CREATE PROCEDURE websiteHacking() SELECT id, login, name FROM users WHERE type = 'user' OR type != 'user' ORDER BY id
Labels:
Arcade,
CodeSignal,
Databases,
MySQL,
Time for Tricks
marketReport
My solution:
CREATE PROCEDURE marketReport() BEGIN SELECT DISTINCT country, COUNT(*) AS competitors FROM foreignCompetitors GROUP BY country UNION SELECT "Total:" AS country, COUNT(competitor) FROM foreignCompetitors; END
Labels:
Arcade,
CodeSignal,
Databases,
GROUP Dishes BY Type,
MySQL
22 January 2018
soccerPlayers
My solution:
CREATE PROCEDURE soccerPlayers() BEGIN SELECT GROUP_CONCAT(first_name, ' ', surname, ' #', player_number ORDER BY player_number ASC SEPARATOR '; ') AS players FROM soccer_team; END
Labels:
Arcade,
CodeSignal,
Databases,
GROUP Dishes BY Type,
MySQL
movieDirectors
My solution:
CREATE PROCEDURE movieDirectors() BEGIN SELECT director FROM (SELECT director, SUM(oscars) AS n_oscars FROM moviesInfo WHERE year > 2000 AND oscars > 0 GROUP BY director) x WHERE x.n_oscars > 2; END
Labels:
Arcade,
CodeSignal,
Databases,
GROUP Dishes BY Type,
MySQL
travelDiary
My solution:
CREATE PROCEDURE travelDiary() BEGIN SELECT GROUP_CONCAT(DISTINCT country ORDER BY country ASC SEPARATOR ';') AS countries FROM diary; END
Labels:
Arcade,
CodeSignal,
Databases,
GROUP Dishes BY Type,
MySQL
21 January 2018
usersByContinent
My solution:
CREATE PROCEDURE usersByContinent() BEGIN SELECT continent, SUM(users) AS users FROM countries GROUP BY continent ORDER BY users DESC; END
Labels:
Arcade,
CodeSignal,
Databases,
GROUP Dishes BY Type,
MySQL
20 January 2018
itemCounts
My solution:
CREATE PROCEDURE itemCounts() BEGIN SELECT item_name, item_type, COUNT(*) AS item_count FROM availableItems GROUP BY item_name, item_type ORDER BY item_type, item_name; END
Labels:
Arcade,
CodeSignal,
Databases,
GROUP Dishes BY Type,
MySQL
countriesInfo
My solution:
CREATE PROCEDURE countriesInfo() BEGIN SELECT COUNT(*) AS number, AVG(population) AS average, SUM(population) AS total FROM countries; END
Labels:
Arcade,
CodeSignal,
Databases,
GROUP Dishes BY Type,
MySQL
19 January 2018
testCheck
My solution:
CREATE PROCEDURE testCheck() SELECT id, IF (given_answer IS NULL, "no answer", CASE WHEN given_answer = correct_answer THEN "correct" ELSE "incorrect" END) AS checks FROM answers ORDER BY id;
Labels:
Arcade,
CodeSignal,
Databases,
MySQL,
Would you LIKE the Second Meal?
newsSubscribers
My solution:
CREATE PROCEDURE newsSubscribers() BEGIN SELECT DISTINCT subscriber FROM full_year WHERE full_year.newspaper LIKE "%Daily%" UNION SELECT DISTINCT subscriber FROM half_year WHERE half_year.newspaper LIKE "%Daily%" ORDER BY subscriber ASC; END
Labels:
Arcade,
CodeSignal,
Databases,
MySQL,
Would you LIKE the Second Meal?
Subscribe to:
Posts (Atom)