Solution - Discussion 49 - MySQL Basics Exercise: Exploring the World Database

[akash-coded]

Below are the solutions for each task, along with explanations and possible alternatives to enhance your understanding and skills in SQL querying.

1. Basic Data Selection

Objective: Learn to retrieve data from a single table.
Task: Query the country table to select the Name, Continent, and Population.
SQL Solution:

SELECT Name, Continent, Population FROM country;

Explanation: This SQL statement uses the SELECT keyword to specify which columns to retrieve from the country table. It's a straightforward query that introduces you to basic data selection.

2. Using Aliases for Columns and Tables

Objective: Improve query readability and manage column names in the result set.
Task: Use aliases to rename the Name column to Country Name and the country table as c in your queries.
SQL Solution:

SELECT Name AS 'Country Name' FROM country AS c;

Explanation: The AS keyword is used to create aliases, which are alternative names for columns or tables. This makes queries easier to read and can simplify complex SQL scripts. The column Name is aliased to Country Name, and the table country is referred to as c.

Alternative:

SELECT c.Name AS 'Country Name' FROM country c;

Using the table alias c to qualify the column name, which can help avoid ambiguity in queries involving multiple tables.

3. Filtering Data with Basic WHERE Clauses

Objective: Learn to filter data based on specific criteria.
Task: Find all countries in the 'Europe' continent with a population greater than 10 million.
SQL Solution:

SELECT Name, Population FROM country WHERE Continent = 'Europe' AND Population > 10000000;

Explanation: The WHERE clause is used to filter records. This SQL command retrieves the names and populations of countries in Europe with a population over 10 million. The AND operator ensures both conditions must be true.

4. Combining Conditions with AND, OR

Objective: Use logical operators to combine multiple conditions in a WHERE clause.
Task: Retrieve all cities in 'Poland' or 'Belgium'.
SQL Solution:

SELECT Name, CountryCode FROM city WHERE CountryCode = 'POL' OR CountryCode = 'BEL';

Explanation: This query uses the OR operator to combine conditions, selecting cities either in Poland (POL) or Belgium (BEL). This is useful for conditions where any one of multiple criteria can be true.

5. Sorting Results with ORDER BY

Objective: Order your query results based on one or more columns.
Task: List all countries in 'South America' by their LifeExpectancy in descending order.
SQL Solution:

SELECT Name, LifeExpectancy FROM country WHERE Continent = 'South America' ORDER BY LifeExpectancy DESC;

Explanation: The ORDER BY clause is used to sort the results by LifeExpectancy in descending order. This is valuable for seeing data in a structured form, such as when looking for the highest or lowest values.

6. Limiting Results with LIMIT

Objective: Restrict the number of rows returned by a query.
Task: Get the top 5 largest cities by population in the database.
SQL Solution:

SELECT Name, Population FROM city ORDER BY Population DESC LIMIT 5;

Explanation: The LIMIT clause restricts the number of rows returned in a query result. This query returns only the top 5 cities sorted by population in descending order. It's particularly useful for top-n queries.

Additional Practice:

• Experiment with different WHERE conditions to filter data in various ways.
• Try sorting data by multiple columns using ORDER BY column1, column2 to see how it affects the order of the results.
• Use LIMIT in conjunction with OFFSET to paginate through results, such as LIMIT 10 OFFSET 20 to start from the 21st row.

These tasks and their solutions are foundational, and mastering them will significantly boost your proficiency in SQL querying for data retrieval and manipulation.