|
| 1 | +#SQL |
| 2 | +Solutions for the tasks located at:https://www.hackerrank.com/domains/sql |
| 3 | + |
| 4 | +##CITY table used for all below |
| 5 | + |
| 6 | + |
| 7 | +###Revising the Select Query I |
| 8 | +Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA. |
| 9 | +```sql |
| 10 | +SELECT*FROM cityWHERE countrycode='USA'AND population>100000; |
| 11 | +``` |
| 12 | + |
| 13 | +###Revising the Select Query II |
| 14 | +Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA. |
| 15 | +```sql |
| 16 | +SELECT nameFROM cityWHERE countrycode='USA'AND population>120000; |
| 17 | +``` |
| 18 | + |
| 19 | +###Select All |
| 20 | +Query all columns (attributes) for every row in the CITY table. |
| 21 | +```sql |
| 22 | +SELECT*FROM city; |
| 23 | +``` |
| 24 | + |
| 25 | +###Select By ID |
| 26 | +Query all columns for a city in CITY with the ID 1661. |
| 27 | +```sql |
| 28 | +SELECT*FROM cityWHERE id=1661; |
| 29 | +``` |
| 30 | + |
| 31 | +###Japanese Cities' Attributes |
| 32 | +Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN. |
| 33 | +```sql |
| 34 | +SELECT*FROM cityWHERE countrycode='JPN'; |
| 35 | +``` |
| 36 | + |
| 37 | +###Japanese Cities' Names |
| 38 | +Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN. |
| 39 | +```sql |
| 40 | +SELECT nameFROM cityWHERE countrycode='JPN'; |
| 41 | +``` |
| 42 | + |
| 43 | + |
| 44 | +##STATION table used for all below |
| 45 | + |
| 46 | + |
| 47 | +###Weather Observation Station 1 |
| 48 | +Query a list of CITY and STATE FROM the STATION table. |
| 49 | +```sql |
| 50 | +SELECT city, stateFROM station; |
| 51 | +``` |
| 52 | + |
| 53 | +###Weather Observation Station 2 |
| 54 | +Query the following two values FROM the STATION table: |
| 55 | + |
| 56 | +The sum of all values in LAT_N rounded to a scale of decimal places. |
| 57 | +The sum of all values in LONG_W rounded to a scale of decimal places. |
| 58 | +```sql |
| 59 | +/* mysql*/ |
| 60 | +SELECT round(sum(LAT_N),2), round(sum(LONG_W),2)FROM station; |
| 61 | +``` |
| 62 | + |
| 63 | +###Weather Observation Station 3 |
| 64 | +Query a list of CITY names FROM STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates FROM the answer. |
| 65 | +```sql |
| 66 | +SELECT DISTINCT cityFROM stationWHERE id %2=0; |
| 67 | +``` |
| 68 | + |
| 69 | +###Weather Observation Station 4 |
| 70 | +Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table. |
| 71 | +```sql |
| 72 | +SELECTCOUNT(city)-COUNT(DISTINCT city)FROM station; |
| 73 | +``` |
| 74 | + |
| 75 | +###Weather Observation Station 5 |
| 76 | +Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically. |
| 77 | +```sql |
| 78 | +-- mysql |
| 79 | +SELECT city, LENGTH(city)AS name_length |
| 80 | +FROM ( |
| 81 | +-- Shortest city name |
| 82 | +SELECT city, LENGTH(city)AS name_length |
| 83 | +FROM station |
| 84 | +ORDER BY LENGTH(city)ASC, cityASC |
| 85 | +LIMIT1 |
| 86 | +)AS shortest |
| 87 | + |
| 88 | +UNION ALL |
| 89 | + |
| 90 | +SELECT city, LENGTH(city)AS name_length |
| 91 | +FROM ( |
| 92 | +-- Longest city name |
| 93 | +SELECT city, LENGTH(city)AS name_length |
| 94 | +FROM station |
| 95 | +ORDER BY LENGTH(city)DESC, cityASC |
| 96 | +LIMIT1 |
| 97 | +)AS longest; |
| 98 | +``` |
| 99 | + |
| 100 | +### |
| 101 | + |
| 102 | +```sql |
| 103 | +-- mysql |
| 104 | + |
| 105 | +``` |
| 106 | + |
| 107 | +### |
| 108 | + |
| 109 | +```sql |
| 110 | +-- mysql |
| 111 | + |
| 112 | +``` |
| 113 | + |
| 114 | +### |
| 115 | + |
| 116 | +```sql |
| 117 | +-- mysql |
| 118 | + |
| 119 | +``` |
| 120 | + |
| 121 | +### |
| 122 | + |
| 123 | +```sql |
| 124 | +-- mysql |
| 125 | + |
| 126 | +``` |
| 127 | + |
| 128 | +### |
| 129 | + |
| 130 | +```sql |
| 131 | +-- mysql |
| 132 | + |
| 133 | +``` |
| 134 | + |
| 135 | +### |
| 136 | + |
| 137 | +```sql |
| 138 | +-- mysql |
| 139 | + |
| 140 | +``` |
| 141 | + |
| 142 | +### |
| 143 | + |
| 144 | +```sql |
| 145 | +-- mysql |
| 146 | + |
| 147 | +``` |
| 148 | + |
| 149 | +### |
| 150 | + |
| 151 | +```sql |
| 152 | +-- mysql |
| 153 | + |
| 154 | +``` |
| 155 | + |
| 156 | +### |
| 157 | + |
| 158 | +```sql |
| 159 | +-- mysql |
| 160 | + |
| 161 | +``` |
| 162 | + |
| 163 | +### |
| 164 | + |
| 165 | +```sql |
| 166 | +-- mysql |
| 167 | + |
| 168 | +``` |
| 169 | + |
| 170 | +### |
| 171 | + |
| 172 | +```sql |
| 173 | +-- mysql |
| 174 | + |
| 175 | +``` |
| 176 | + |
| 177 | +### |
| 178 | + |
| 179 | +```sql |
| 180 | +-- mysql |
| 181 | + |
| 182 | +``` |