ybg345 / sql-hands-on

This repository contains SQL queries from various popular online learning resources e.g. Vertabelo Academy, SQLZoo etc.

Welcome to this SQL Repository

Repository Description:

This repository contains SQL queries from various popular online learning resources e.g. SQLZoo, Vertabelo Academy etc.

SQL-Zoo:

Topics	# Total Problem	# of Solved
0 SELECT basics	03	03
1 SELECT name	15	15
2 SELECT from World	13	13
3 SELECT from Nobel	14	14
4 SELECT within SELECT	10	10
5 SUM and COUNT	08	08
6 JOIN	13	13
7 More JOIN operations	15	15
8 Using Null	10	10
8+ Numeric Examples	08	08
9 Self join	10	09

Vertabelo Academy:

Topics	# Total Problem (Free)	# of Solved
1_Select From Table Section	06	06
2_Aggregation and grouping	08	08
3_SQL Join Types	16	16

More Details (Questions & Solutions) of all Sections: SQL-Zoo -

0 SELECT basics section:

Problem Statement + MySQL Solution
Introducing the world table of countries
Scandinavia
Just the right size

1 SELECT name section:

Problem Statement + MySQL Solution
Problem 1
Problem 2
Problem 3
Problem 4
Problem 5
Problem 6
Problem 7
Problem 8
Problem 9
Problem 10
Problem 11
Problem 12
Problem 13
Problem 14
Problem 15

2_Select from world section:

Problem Statement + MySQL Solution
Introduction
Large Countries
Per Capita GDP
South America in Millions
France, Germany, Italy
United
Two ways to be big
One or the other (but not both)
Rounding
Trillion dollar economies
Name and capital have the same length
Matching name and capital
All the vowels

3_SELECT from Nobel section:

Problem Statement + MySQL Solution
Nobel_Winners from 1950
Nobel_1962 Literature
Nobel_Albert Einstein
Nobel_Recent Peace Prizes
Nobel_Literature in the 1980's
Nobel_Only Presidents
Nobel_John
Nobel_Chemistry and Physics from different years
Nobel_Exclude Chemists and Medics
Nobel_Early Medicine, Late Literature
Nobel_Find all details of the prize won by PETER GRÜNBERG
Nobel_Apostrophe
Nobel_Knights of the realm
Nobel_Chemistry and Physics last

4_SELECT within SELECT section:

Problem Statement + MySQL Solution
Bigger than Russia
Richer than UK
Neighbours of Argentina and Australia
Between Canada and Poland
Percentages of Germany
Bigger than every country in Europe
Largest in each continent
First country of each continent (alphabetically)
Difficult Questions That Utilize Techniques Not Covered In Prior Sections
More population than any neighbours

5_Aggregate functions, Group by, Having section:

Problem Statement + MySQL Solution
Aggregate Functions: Total world population
Aggregate Functions: List of continents
Aggregate Functions: GDP of Africa
Aggregate Functions: Count the big countries
Aggregate Functions: Baltic states population
Using GROUP BY and HAVING: Counting the countries of each continent
Using GROUP BY and HAVING: Counting big countries in each continent
Using GROUP BY and HAVING: Counting big continents
Aggregate Function: Nobel Table 1
Aggregate Function: Nobel Table 2
Aggregate Function: Nobel Table 3
GROUP BY and HAVING: Nobel Table 4
GROUP BY and HAVING: Nobel Table 5
GROUP BY and HAVING: Nobel Table 6
aggregates with DISTINCT: Nobel Table 7
aggregates with DISTINCT: Nobel Table 8
using Having: Nobel Table 9
using Having: Nobel Table 10
using Having: Nobel Table 11
Group by AND Having: Nobel Table 12

6_Join Operations section:

Problem Statement + MySQL Solution
UEFA EURO 2012_1
UEFA EURO 2012_2
UEFA EURO 2012_3
UEFA EURO 2012_4
UEFA EURO 2012_5
UEFA EURO 2012_6
UEFA EURO 2012_7
UEFA EURO 2012_8
UEFA EURO 2012_9
UEFA EURO 2012_10
UEFA EURO 2012_11
UEFA EURO 2012_12
UEFA EURO 2012_13
Table Tennis Olympics Database_1
Table Tennis Olympics Database_2
Table Tennis Olympics Database_3
Table Tennis Olympics Database_4
Table Tennis Olympics Database_5
Table Tennis Olympics Database_6
Table Tennis Olympics Database_7
Table Tennis Olympics Database_8
Table Tennis Olympics Database_9
Music database_1
Music database_2
Music database_3
Music database_4
Music database_5
Music database_6
Music database_7
Music database_8
Music database_9
Music database_10

7_More Join Operation section:

Problem Statement + MySQL Solution
1962 movies
When was Citizen Kane released
Star Trek movies
id for actor Glenn Close
id for Casablanca
Cast list for Casablanca
Alien cast list
Harrison Ford movies
Harrison Ford as a supporting actor
Lead actors in 1962 movies
Busy years for John Travolta
Lead actor in Julie Andrews movies
Actors with 30 leading roles
Films released in the year 1978
Worked with 'Art Garfunkel'

8_Using Null section:

Problem Statement + MySQL Solution
Teachers and Departments_NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN
Teachers and Departments_NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN
Teachers and Departments_NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN
Teachers and Departments_NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN
Teachers and Departments_COALESCE function
Teachers and Departments_COALESCE function
Teachers and Departments_Count Function
Teachers and Departments_Count and Group by Function
Teachers and Departments_Case Function
Teachers and Departments_Case Function
Scottish Parliament_1
Scottish Parliament_2
Scottish Parliament_3
Scottish Parliament_4
Scottish Parliament_5
Scottish Parliament_6
Scottish Parliament_7

8+_Numeric Example section:

Problem Statement + MySQL Solution
Check out one row
Calculate how many agree or strongly agree
Unhappy Computer Students
More Computing or Creative Students
Strongly Agree Numbers
Strongly Agree, Percentage
Scores for Institutions in Manchester
Number of Computing Students in Manchester

9_Self Join section:

Problem Statement + MySQL Solution
Edinburgh Buses_Summary1
Edinburgh Buses_Summary2
Edinburgh Buses_Summary3
Edinburgh Buses_Routes and stops_1
Edinburgh Buses_Routes and stops_2
Edinburgh Buses_Routes and stops_3
Edinburgh Buses_Using a self join_1
Edinburgh Buses_Using a self join_2
Edinburgh Buses_Using a self join_3
Edinburgh Buses_Using a self join_4

More Details (Questions & Solutions) of different Sections: Vertabelo Academy -

1_Select From Table Section section:

Problem Statement + MySQL Solution
Kittens
Young and old ragdoll cats
Which cats like to play with balls
The most bored cat
Elder cats have some fun
Adult cat that love teaser toys

2_Aggregation and grouping section:

Problem Statement + MySQL Solution
Cost effective games
Average production cost of games
Revenue for recent good shooter games
Games production statistics by year
Profitable games by type
Gross profit by company
Production statistics
Identify good games

3_SQL Join Types section:

Problem Statement + MySQL Solution
Get to know the author table
Get to know the book table
Get to know the adaptation table
Joins recap
Join and filtering
Joins ambogius columns
Inner Join
Left Join
Full Join
Cross joins
Filtering with joins
Filtering with joins continued
on and where condition difference
Additional Exercise1
Additional Exercise2
Additional Exercise3