Statement of Completion#bd348558
Argjenta Topalli has successfully completed this project.
Intermediate SQL
medium
LIKE Operator project using Python with World Database
Resolution
Activities
Project.ipynb
In [3]:
#Importing the pymysql library with cursors module for MySQL database connectivity
import pymysql.cursors
In [4]:
# Database connection details for connecting to the `world` database
HOST = "192.22.162.2"
USER = "datawars"
DATABASE = "world"
PASSWORD = ""
PORT = 3306
In [5]:
# Create connection
conn = pymysql.connect(
host=HOST, user=USER, port=PORT,
password=PASSWORD, database=DATABASE,
cursorclass=pymysql.cursors.DictCursor)
In [6]:
# Create cursor
cursor = conn.cursor()
Activities¶
1. Write a query to select all cities whose name starts with the letter S.¶
In [8]:
query = "SELECT * from city where Name like 'S%';"
cursor.execute(query)
S_cities = cursor.fetchall()
S_cities[:3]
Out[8]:
[{'ID': 40,
'Name': 'Sétif',
'CountryCode': 'DZA',
'District': 'Sétif',
'Population': 179055},
{'ID': 41,
'Name': 'Sidi Bel Abbès',
'CountryCode': 'DZA',
'District': 'Sidi Bel Abbès',
'Population': 153106},
{'ID': 42,
'Name': 'Skikda',
'CountryCode': 'DZA',
'District': 'Skikda',
'Population': 128747}]
2. Select all cities whose name contains the substring ton (e.g., Houston, Boston).¶
In [10]:
query = "select * from city where Name like '%ton%'"
cursor.execute(query)
ton_cities = cursor.fetchall()
ton_cities[:3]
Out[10]:
[{'ID': 186,
'Name': 'Cotonou',
'CountryCode': 'BEN',
'District': 'Atlantique',
'Population': 536827},
{'ID': 191,
'Name': 'Hamilton',
'CountryCode': 'BMU',
'District': 'Hamilton',
'Population': 1200},
{'ID': 377,
'Name': 'Teófilo Otoni',
'CountryCode': 'BRA',
'District': 'Minas Gerais',
'Population': 124489}]
3. Query all cities whose name contains exactly five characters.¶
In [14]:
query = "select * from city where Name like '_____';"
cursor.execute(query)
five_char = cursor.fetchall()
five_char[:3]
Out[14]:
[{'ID': 1,
'Name': 'Kabul',
'CountryCode': 'AFG',
'District': 'Kabol',
'Population': 1780000},
{'ID': 3,
'Name': 'Herat',
'CountryCode': 'AFG',
'District': 'Herat',
'Population': 186800},
{'ID': 12,
'Name': 'Breda',
'CountryCode': 'NLD',
'District': 'Noord-Brabant',
'Population': 160398}]
4. Find all cities whose names contain es anywhere.¶
In [17]:
query = "select * from city where Name like '%es%'"
cursor.execute(query)
es_cities = cursor.fetchall()
es_cities[:3]
Out[17]:
[{'ID': 41,
'Name': 'Sidi Bel Abbès',
'CountryCode': 'DZA',
'District': 'Sidi Bel Abbès',
'Population': 153106},
{'ID': 47,
'Name': 'Tébessa',
'CountryCode': 'DZA',
'District': 'Tébessa',
'Population': 112007},
{'ID': 69,
'Name': 'Buenos Aires',
'CountryCode': 'ARG',
'District': 'Distrito Federal',
'Population': 2982146}]
5. Querying Countries by Consecutive Characters: Find Countries Containing oo.¶
In [19]:
query = "Select Name, Continent, SurfaceArea, GovernmentForm from country where Name like '%oo%'"
cursor.execute(query)
oo_countries = cursor.fetchall()
6. Identify Countries Without a in Their Names¶
In [21]:
query = "select Name, SurfaceArea from country where Name not like '%a%' and SurfaceArea < 1000000"
cursor.execute(query)
not_a = cursor.fetchall()
not_a[:3]
Out[21]:
[{'Name': 'French Southern territories', 'SurfaceArea': Decimal('7780.00')},
{'Name': 'Burundi', 'SurfaceArea': Decimal('27834.00')},
{'Name': 'Belgium', 'SurfaceArea': Decimal('30518.00')}]
7. Query for cities whose names start with G and end with y.¶
In [23]:
query = "SELECT * FROM country WHERE Name LIKE 'G%y' AND LifeExpectancy BETWEEN 70 AND 80;"
cursor.execute(query)
gy_state = cursor.fetchall()
gy_state
Out[23]:
[{'Code': 'DEU',
'Name': 'Germany',
'Continent': 'Europe',
'Region': 'Western Europe',
'SurfaceArea': Decimal('357022.00'),
'IndepYear': 1955,
'Population': 82164700,
'LifeExpectancy': Decimal('77.4'),
'GNP': Decimal('2133367.00'),
'GNPOld': Decimal('2102826.00'),
'LocalName': 'Deutschland',
'GovernmentForm': 'Federal Republic',
'HeadOfState': 'Johannes Rau',
'Capital': 3068,
'Code2': 'DE'}]
8. Extract Countries Containing Land in Their Name¶
In [25]:
query = "SELECT Name, LifeExpectancy FROM country WHERE Name LIKE '%land%' AND LifeExpectancy BETWEEN 60 AND 80;"
cursor.execute(query)
land_countries = cursor.fetchall()
land_countries[:3]
Out[25]:
[{'Name': 'Netherlands Antilles', 'LifeExpectancy': Decimal('74.7')},
{'Name': 'Switzerland', 'LifeExpectancy': Decimal('79.6')},
{'Name': 'Cook Islands', 'LifeExpectancy': Decimal('71.1')}]
9. Retrieve Countries with High Population and Specific Name Patterns¶
In [29]:
query = "SELECT Name, Population FROM country WHERE Name LIKE '%ia%' AND Population > 50000000 ORDER BY Population DESC;"
cursor.execute(query)
ia_countries = cursor.fetchall()
ia_countries
Out[29]:
[{'Name': 'India', 'Population': 1013662000},
{'Name': 'Indonesia', 'Population': 212107000},
{'Name': 'Russian Federation', 'Population': 146934000},
{'Name': 'Nigeria', 'Population': 111506000},
{'Name': 'Ethiopia', 'Population': 62565000}]
10. Extract Countries Satisfying Multiple Conditions from Database¶
In [31]:
query = "SELECT Name, Continent FROM country WHERE (Name LIKE 'B%a' OR Name LIKE 'M%a') LIMIT 10;"
cursor.execute(query)
B_countries = cursor.fetchall()
B_countries
Out[31]:
[{'Name': 'Bulgaria', 'Continent': 'Europe'},
{'Name': 'Bosnia and Herzegovina', 'Continent': 'Europe'},
{'Name': 'Bermuda', 'Continent': 'North America'},
{'Name': 'Bolivia', 'Continent': 'South America'},
{'Name': 'Botswana', 'Continent': 'Africa'},
{'Name': 'Moldova', 'Continent': 'Europe'},
{'Name': 'Macedonia', 'Continent': 'Europe'},
{'Name': 'Malta', 'Continent': 'Europe'},
{'Name': 'Mongolia', 'Continent': 'Asia'},
{'Name': 'Mauritania', 'Continent': 'Africa'}]
11. Find cities where the second character is a.¶
In [33]:
query = "SELECT * FROM city WHERE Name LIKE '_a%';"
cursor.execute(query)
_a_cities = cursor.fetchall()
_a_cities[:3]
Out[33]:
[{'ID': 1,
'Name': 'Kabul',
'CountryCode': 'AFG',
'District': 'Kabol',
'Population': 1780000},
{'ID': 2,
'Name': 'Qandahar',
'CountryCode': 'AFG',
'District': 'Qandahar',
'Population': 237500},
{'ID': 4,
'Name': 'Mazar-e-Sharif',
'CountryCode': 'AFG',
'District': 'Balkh',
'Population': 127800}]
12. Write a Python function that finds all Countries whose name starts with the following vowels : A, E, O, U.¶
In [35]:
vowels = ['A', 'E', 'O', 'U']
for vowel in vowels:
query = f"select Name from country where Name like '{vowel}%'"
cursor.execute(query)
vowel_country = cursor.fetchall()
print(f"Countries starting with {vowel}: {vowel_country}")
Countries starting with A: [{'Name': 'Aruba'}, {'Name': 'Afghanistan'}, {'Name': 'Angola'}, {'Name': 'Anguilla'}, {'Name': 'Albania'}, {'Name': 'Andorra'}, {'Name': 'Argentina'}, {'Name': 'Armenia'}, {'Name': 'American Samoa'}, {'Name': 'Antarctica'}, {'Name': 'Antigua and Barbuda'}, {'Name': 'Australia'}, {'Name': 'Austria'}, {'Name': 'Azerbaijan'}, {'Name': 'Algeria'}]
Countries starting with E: [{'Name': 'Ecuador'}, {'Name': 'Egypt'}, {'Name': 'Eritrea'}, {'Name': 'Estonia'}, {'Name': 'Ethiopia'}, {'Name': 'Equatorial Guinea'}, {'Name': 'El Salvador'}, {'Name': 'East Timor'}]
Countries starting with O: [{'Name': 'Oman'}]
Countries starting with U: [{'Name': 'United Arab Emirates'}, {'Name': 'United Kingdom'}, {'Name': 'Uganda'}, {'Name': 'Ukraine'}, {'Name': 'United States Minor Outlying Islands'}, {'Name': 'Uruguay'}, {'Name': 'United States'}, {'Name': 'Uzbekistan'}]
13. Find countries Starting with a Specific Letter and Containing a¶
In [37]:
def find_countries_with_a_after_letter(letter):
query = f"SELECT Name FROM country WHERE Name LIKE '{letter}%a%';"
cursor.execute(query)
return cursor.fetchall()
ba_countries = find_countries_with_a_after_letter('B')
print(f"Countries starting with 'B' and containing 'a': {ba_countries}")
Countries starting with 'B' and containing 'a': [{'Name': 'Burkina Faso'}, {'Name': 'Bangladesh'}, {'Name': 'Bulgaria'}, {'Name': 'Bahrain'}, {'Name': 'Bahamas'}, {'Name': 'Bosnia and Herzegovina'}, {'Name': 'Belarus'}, {'Name': 'Bermuda'}, {'Name': 'Bolivia'}, {'Name': 'Brazil'}, {'Name': 'Barbados'}, {'Name': 'Bhutan'}, {'Name': 'Bouvet Island'}, {'Name': 'Botswana'}, {'Name': 'British Indian Ocean Territory'}]
14. Finding Official Languages Containing the Letter i.¶
In [39]:
query = "Select * from countrylanguage where IsOfficial = 'T' and Language like '%i%' limit 10"
cursor.execute(query)
i_language = cursor.fetchall()
i_language[:3]
Out[39]:
[{'CountryCode': 'AFG',
'Language': 'Dari',
'IsOfficial': 'T',
'Percentage': Decimal('32.1')},
{'CountryCode': 'AIA',
'Language': 'English',
'IsOfficial': 'T',
'Percentage': Decimal('0.0')},
{'CountryCode': 'ALB',
'Language': 'Albaniana',
'IsOfficial': 'T',
'Percentage': Decimal('97.9')}]
Great Job!! 🎉🎈✨
