Introduction to MySQL Practice 

The best way we learn anything is by practice and exercise questions. Hope, these exercises help you to improve your SQL skills. 

For the practice we use the subcounty_population_density table that was in the intro_to_mysql reference. 

The other practice database is the employees schema, which is an organized database with related tables. 

 

Number 1 is compulsory. Then choose between subcounty_population_density and employee schema 

---

 

 

 

 

 

Explain What is SQL how it is useful 

 

 

---

Subcounty_population_density 

• Write a SQL statement to display specific columns the county name, subcounty name, total population, square kilometers and population density for all records.  

• Write an SQL Query to display the county_name, subcounty_name, total, male, female sorted by female population. Subcounty with highest female population is on top. 

• Write an SQL query to calculate total population of all sub counties. (Use SQL aggregate functions) 

• Write an SQL query that retrieves the county_name, the average population density of the subcounties. Order the retrieved results by county name first and then population density descending. (Group by aggregate function) 

• Write an SQL query to retrieve rows where the subcounty is in Nyandarua county. Notice some of these subcounties have typing mistakes. Update the names of the subcounties without typing mistakes. (SQL UPDATE query) 

 

 

 

 

Employees schema

---

All material (code) for the employee schema is provided in reference design (2). 

 

• Create the necessary tables for the employee schema. 

• Identify which are the primary keys and foreign keys per table. 

• Insert records as needed. Notice I used my name as the first record I inserted in the employees table. Replace my name with your name. 

• Insert multiple records as provided in the reference. 

• Write an SQL query that retrieves all employees and the devices they own. If they do not own a device, exclude them from results. (INNER JOIN) 

• Write an SQL query that retrieves all employees and the devices they own. If they do not own a device, include them and show null for devices (LEFT JOIN) 

• Write a query that retrieves all employees and their job title. 

 

 

REFERENCES 

1. https://dasclab.uonbi.ac.ke/dstraining/intro_to_mysql.html 

2. https://dasclab.uonbi.ac.ke/dstraining/design.html