MySQL Practice Exercises with Solutions – Country Database – Part 8

SELECT * 
FROM `cities` 
ORDER BY `population` DESC 
LIMIT 3;

MySQL SELECTSELECT statement is used to get data from a MySQL database. You can use this command at the mysql> command prompt as well as in…Read More MySQL ORDER BYThe ORDER BY keyword sorts the result set in ascending or descending order. To sort records in descending order, use DESC keyword. To sort in…Read More MySQL LIMITLIMIT keyword is used to limit the number of rows returned in the result of a query. It can be used in conjunction with SELECT,…Read More

SELECT * 
FROM `cities` 
ORDER BY `surface` ASC 
LIMIT 3;

MySQL ORDER BYThe ORDER BY keyword sorts the result set in ascending or descending order. To sort records in descending order, use DESC keyword. To sort in…Read More MySQL LIMITLIMIT keyword is used to limit the number of rows returned in the result of a query. It can be used in conjunction with SELECT,…Read More

SELECT * 
FROM `states` 
WHERE `state_code` LIKE '97%';

MySQL LIKEThe LIKE operator is a logical operator used in WHERE clause that tests whether a string contains a specified pattern or not. Two wildcards are…Read More

SELECT * 
FROM `cities` 
LEFT JOIN states ON state_code = city_state
ORDER BY `population` DESC 
LIMIT 3;

MySQL LEFT JOINIn the SQL language, the LEFT JOIN command (also called LEFT OUTER JOIN) is a type of join between 2 tables. It allows listing all…Read More

SELECT state_name, city_state, COUNT(*) AS nbr_items 
FROM `cities` 
LEFT JOIN states ON state_code = city_state
GROUP BY city_state
ORDER BY `nbr_items` DESC;

MySQL LEFT JOINIn the SQL language, the LEFT JOIN command (also called LEFT OUTER JOIN) is a type of join between 2 tables. It allows listing all…Read More MySQL GROUP BYThe GROUP BY clause is used to group rows with the same values, and is used in SELECT statement with aggregation functions such as COUNT,…Read More

SELECT state_name, city_state, SUM(`surface`) AS state_surface 
FROM `cities` 
LEFT JOIN states ON state_code = city_state
GROUP BY city_state
ORDER BY state_surface DESC
LIMIT 3;

MySQL LEFT JOINIn the SQL language, the LEFT JOIN command (also called LEFT OUTER JOIN) is a type of join between 2 tables. It allows listing all…Read More

SELECT COUNT(*) 
FROM `cities` 
WHERE `name` LIKE 'San%';

MySQL ORDER BYThe ORDER BY keyword sorts the result set in ascending or descending order. To sort records in descending order, use DESC keyword. To sort in…Read More MySQL LIKEThe LIKE operator is a logical operator used in WHERE clause that tests whether a string contains a specified pattern or not. Two wildcards are…Read More

SELECT *
FROM `cities` 
WHERE `surface` > (SELECT AVG(`surface`) FROM `cities`);

SQL Wildcards in StatementA “wildcard” character is used to substitute for any other character in a string. These wildcards are used in the “LIKE” operator. Here are the…Read More

SELECT city_state, SUM(`population`) AS city_population
FROM `cities`
GROUP BY `city_state`
HAVING city_population > 1000000
ORDER BY city_population DESC;

MySQL ORDER BYThe ORDER BY keyword sorts the result set in ascending or descending order. To sort records in descending order, use DESC keyword. To sort in…Read More MySQL HAVINGHAVING clause is used in SELECT statement to specify filter conditions for a group of rows or aggregates. HAVING clause is often used with GROUP…Read More

UPDATE `cities` 
SET name = REPLACE(name, '-', ' ') 
WHERE `name` LIKE 'SAN-%'

MySQL UPDATEThe Update command is used to modify rows of a table. The Update command can be used to modify or update one or more fields…Read More