-
Notifications
You must be signed in to change notification settings - Fork 0
/
HackerrankQuestion-Answer.sql
157 lines (84 loc) · 3.31 KB
/
HackerrankQuestion-Answer.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
/* database schema
table city (
id int ,
name varchar2(17) ,
countrycode varchar2(3),
district varchar2(20),
population number
)
*/
/*
Question : 0 - 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.
Answer : */
select distinct(city) from station
where id % 2 = 0
/*
Question : 1 - Query the difference between the maximum and minimum populations in CITY.
Answer : */
select abs(max(population) - min(population)) from city
/*
Question : 2 - Query the average population of all cities in CITY where District is California.
Answer : */
select avg(population) from city
where District = 'California';
/*
Question : 3 - Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.
Answer : */
select NAME from CITY where COUNTRYCODE='USA' and POPULATION > 120000;
/*
Question : 4 - Query the total population of all cities in CITY where District is California.
Answer : */
select sum(population) from city
where district = 'California'
/* database schema
table station (
id int ,
city varchar2(21) ,
state varchar2(2),
lat_n int,
long_w int
)
*/
/*
Question : 5 Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.
For example, if there are three records in the table with CITY values 'New York', 'New York', 'Bengalaru', there are 2 different city names: 'New York' and 'Bengalaru'.
The query returns 1 , because total number of records - number of unique city names = 3 - 2 = 1
*/
select abs(count(DISTINCT city) - count(city)) from station
/*
Question : 6 Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.
*/
select distinct city from station
where city not like '[aeiouAEIOU]%[aeiouAEIOU]'
/*
Question : 7 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.
*/
select name from students
where marks > 75
order by right(name,3) , ID
/*
Question : 8 Query the smallest Northern Latitude (LAT_N) from STATION that is greater than 38.7780. Round your answer to 4 decimal places.
*/
select format(min(lat_n ),'N4') from station
where lat_n > 38.7780
/*
Question : 9 Query the Western Longitude (LONG_W)where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780. Round your answer to 4 decimal places.
*/
select format(round(long_w , 4 ) , 'N4') from station
where lat_n = (select min(lat_n) from station
where lat_n > 38.7780)
order by lat_n asc
/*
Question : 10 Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345 . Round your answer to 4 decimal places.
*/
select round(LONG_W,4)
from station
where lat_n < 137.2345
order by lat_n desc
limit 1
/*
Question : 11 Weather Observation Station 18
*/
select ROUND(ABS(Max(LAT_N) - MIN(LAT_N))
+ ABS(MAX(LONG_W)- MIN(LONG_W)),4 )
FROM STATION