Added Aggregate functions

ybg345 · Jun 8, 2019 · c497c2d · c497c2d
1 parent da6330d
commit c497c2d
Show file tree

Hide file tree

Showing 20 changed files with 204 additions and 0 deletions.
diff --git a/...5_Aggregate functions, Group by, Having/01_Aggregate Functions_Total world population.sql b/...5_Aggregate functions, Group by, Having/01_Aggregate Functions_Total world population.sql
@@ -0,0 +1,9 @@
+/* Problem Statement:
+
+Show the total population of the world.
+
+*/
+
+-- Solution: 
+
+SELECT SUM(population) FROM world;
diff --git a/...Zoo/5_Aggregate functions, Group by, Having/02_Aggregate Functions_List of continents.sql b/...Zoo/5_Aggregate functions, Group by, Having/02_Aggregate Functions_List of continents.sql
@@ -0,0 +1,9 @@
+/* Problem Statement:
+
+List all the continents - just once each.
+
+*/
+
+-- Solution: 
+
+SELECT DISTINCT(continent) FROM world;
diff --git a/SQL-Zoo/5_Aggregate functions, Group by, Having/03_Aggregate Functions_GDP of Africa.sql b/SQL-Zoo/5_Aggregate functions, Group by, Having/03_Aggregate Functions_GDP of Africa.sql
@@ -0,0 +1,10 @@
+/* Problem Statement:
+
+Give the total GDP of Africa.
+
+*/
+
+-- Solution: 
+
+SELECT SUM(gdp) FROM world 
+WHERE continent = 'Africa';
diff --git a/..._Aggregate functions, Group by, Having/04_Aggregate Functions_Count the big countries.sql b/..._Aggregate functions, Group by, Having/04_Aggregate Functions_Count the big countries.sql
@@ -0,0 +1,9 @@
+/* Problem Statement:
+
+How many countries have an area of at least 1000000.
+
+*/
+
+-- Solution: 
+
+SELECT COUNT(name) FROM world WHERE area >= 1000000;
diff --git a/...Aggregate functions, Group by, Having/05_Aggregate Functions_Baltic states population.sql b/...Aggregate functions, Group by, Having/05_Aggregate Functions_Baltic states population.sql
@@ -0,0 +1,10 @@
+/* Problem Statement:
+
+What is the total population of ('Estonia', 'Latvia', 'Lithuania').
+
+*/
+
+-- Solution: 
+
+SELECT SUM(population) FROM world 
+WHERE name IN ('Estonia', 'Latvia', 'Lithuania');
diff --git a/...roup by, Having/06_Using GROUP BY and HAVING_Counting the countries of each continent.sql b/...roup by, Having/06_Using GROUP BY and HAVING_Counting the countries of each continent.sql
@@ -0,0 +1,10 @@
+/* Problem Statement:
+
+For each continent show the continent and number of countries.
+
+*/
+
+-- Solution: 
+
+SELECT continent, COUNT(name) FROM world
+GROUP BY continent;
diff --git a/...roup by, Having/07_Using GROUP BY and HAVING_Counting big countries in each continent.sql b/...roup by, Having/07_Using GROUP BY and HAVING_Counting big countries in each continent.sql
@@ -0,0 +1,12 @@
+/* Problem Statement:
+
+For each continent show the continent and number of countries with populations of at least 10 million.
+
+*/
+
+-- Solution: 
+
+SELECT continent, COUNT(name)
+FROM world
+WHERE population >= 10000000
+GROUP BY continent;
diff --git a/...gate functions, Group by, Having/08_Using GROUP BY and HAVING_Counting big continents.sql b/...gate functions, Group by, Having/08_Using GROUP BY and HAVING_Counting big continents.sql
@@ -0,0 +1,11 @@
+/* Problem Statement:
+
+List the continents that have a total population of at least 100 million.
+
+*/
+
+-- Solution: 
+
+SELECT continent FROM world
+GROUP BY continent
+HAVING SUM(population) >= 100000000;
diff --git a/SQL-Zoo/5_Aggregate functions, Group by, Having/09_Aggregate Function_Nobel Table_1.sql b/SQL-Zoo/5_Aggregate functions, Group by, Having/09_Aggregate Function_Nobel Table_1.sql
@@ -0,0 +1,9 @@
+/* Problem Statement:
+
+Show the total number of prizes awarded.
+
+*/
+
+-- Solution: 
+
+SELECT COUNT(winner) FROM nobel;
diff --git a/SQL-Zoo/5_Aggregate functions, Group by, Having/10_Aggregate Function_Nobel Table_2.sql b/SQL-Zoo/5_Aggregate functions, Group by, Having/10_Aggregate Function_Nobel Table_2.sql
@@ -0,0 +1,9 @@
+/* Problem Statement:
+
+List each subject - just once.
+
+*/
+
+-- Solution: 
+
+SELECT DISTINCT(subject) FROM nobel;
diff --git a/SQL-Zoo/5_Aggregate functions, Group by, Having/11_Aggregate Function_Nobel Table_3.sql b/SQL-Zoo/5_Aggregate functions, Group by, Having/11_Aggregate Function_Nobel Table_3.sql
@@ -0,0 +1,9 @@
+/* Problem Statement:
+
+Show the total number of prizes awarded for Physics.
+
+*/
+
+-- Solution: 
+
+SELECT COUNT(winner) FROM nobel WHERE subject = 'Physics';
diff --git a/SQL-Zoo/5_Aggregate functions, Group by, Having/12_GROUP BY and HAVING_Nobel Table_4.sql b/SQL-Zoo/5_Aggregate functions, Group by, Having/12_GROUP BY and HAVING_Nobel Table_4.sql
@@ -0,0 +1,11 @@
+/* Problem Statement:
+
+For each subject show the subject and the number of prizes.
+
+*/
+
+-- Solution: 
+
+SELECT subject, COUNT(winner) 
+FROM nobel
+GROUP BY subject;
diff --git a/SQL-Zoo/5_Aggregate functions, Group by, Having/13_GROUP BY and HAVING_Nobel Table_5.sql b/SQL-Zoo/5_Aggregate functions, Group by, Having/13_GROUP BY and HAVING_Nobel Table_5.sql
@@ -0,0 +1,10 @@
+/* Problem Statement:
+
+For each subject show the first year that the prize was awarded.
+
+*/
+
+-- Solution: 
+
+SELECT subject, MIN(yr) FROM nobel
+GROUP BY subject;
diff --git a/SQL-Zoo/5_Aggregate functions, Group by, Having/14_GROUP BY and HAVING_Nobel Table_6.sql b/SQL-Zoo/5_Aggregate functions, Group by, Having/14_GROUP BY and HAVING_Nobel Table_6.sql
@@ -0,0 +1,11 @@
+/* Problem Statement:
+
+For each subject show the number of prizes awarded in the year 2000.
+
+*/
+
+-- Solution: 
+
+SELECT subject, COUNT(winner) FROM nobel 
+WHERE yr = 2000
+GROUP BY subject;
diff --git a/...Zoo/5_Aggregate functions, Group by, Having/15_aggregates with DISTINCT_Nobel Table_7.sql b/...Zoo/5_Aggregate functions, Group by, Having/15_aggregates with DISTINCT_Nobel Table_7.sql
@@ -0,0 +1,10 @@
+/* Problem Statement:
+
+Show the number of different winners for each subject.
+
+*/
+
+-- Solution: 
+
+SELECT subject, COUNT(DISTINCT(winner)) FROM nobel
+GROUP BY subject;
diff --git a/...Zoo/5_Aggregate functions, Group by, Having/16_aggregates with DISTINCT_Nobel Table_8.sql b/...Zoo/5_Aggregate functions, Group by, Having/16_aggregates with DISTINCT_Nobel Table_8.sql
@@ -0,0 +1,10 @@
+/* Problem Statement:
+
+For each subject show how many years have had prizes awarded.
+
+*/
+
+-- Solution: 
+
+SELECT subject, COUNT(DISTINCT yr) FROM nobel
+GROUP BY subject;
diff --git a/SQL-Zoo/5_Aggregate functions, Group by, Having/17_using Having_Nobel Table_9.sql b/SQL-Zoo/5_Aggregate functions, Group by, Having/17_using Having_Nobel Table_9.sql
@@ -0,0 +1,12 @@
+/* Problem Statement:
+
+Show the years in which three prizes were given for Physics.
+
+*/
+
+-- Solution: 
+
+SELECT yr FROM nobel
+WHERE subject = 'Physics'
+GROUP BY yr
+HAVING COUNT(winner) = 3;
diff --git a/SQL-Zoo/5_Aggregate functions, Group by, Having/18_using Having_Nobel Table_10.sql b/SQL-Zoo/5_Aggregate functions, Group by, Having/18_using Having_Nobel Table_10.sql
@@ -0,0 +1,11 @@
+/* Problem Statement:
+
+Show winners who have won more than once.
+
+*/
+
+-- Solution: 
+
+SELECT winner FROM nobel 
+GROUP BY winner
+HAVING COUNT(winner) > 1;
diff --git a/SQL-Zoo/5_Aggregate functions, Group by, Having/19_using Having_Nobel Table_11.sql b/SQL-Zoo/5_Aggregate functions, Group by, Having/19_using Having_Nobel Table_11.sql
@@ -0,0 +1,11 @@
+/* Problem Statement:
+
+Show winners who have won more than one subject.
+
+*/
+
+-- Solution: 
+
+SELECT winner FROM nobel
+GROUP BY winner
+HAVING COUNT(DISTINCT subject) > 1;
diff --git a/SQL-Zoo/5_Aggregate functions, Group by, Having/20_Group by AND Having_Nobel Table_12.sql b/SQL-Zoo/5_Aggregate functions, Group by, Having/20_Group by AND Having_Nobel Table_12.sql
@@ -0,0 +1,11 @@
+/* Problem Statement:
+
+Show winners who have won more than one subject.
+
+*/
+
+-- Solution: 
+
+SELECT yr, subject FROM nobel
+GROUP BY yr, subject
+HAVING COUNT(subject) = 3 AND yr >= 2000;