Database Reference
In-Depth Information
• What are the smallest and largest US state populations?
mysql>
SELECT MIN(pop) AS 'fewest people', MAX(pop) AS 'most people'
->
FROM states;
+---------------+-------------+
| fewest people | most people |
+---------------+-------------+
| 563626 | 37253956 |
+---------------+-------------+
• What are the first and last state names, lexically speaking? The shortest and longest
names?
mysql>
SELECT
->
MIN(name) AS first,
->
MAX(name) AS last,
->
MIN(CHAR_LENGTH(name)) AS shortest,
->
MAX(CHAR_LENGTH(name)) AS longest
->
FROM states;
+---------+---------+----------+---------+
| first | last | shortest | longest |
+---------+---------+----------+---------+
| Alabama | Wyoming | 4 | 14 |
+---------+---------+----------+---------+
The final query illustrates that
MIN()
and
MAX()
need not be applied directly to column
values; they're also useful for expressions or values derived from column values.
Summarizing with SUM() and AVG()
SUM()
and
AVG()
produce the total and average (mean) of a set of values:
• What is the total amount of mail traffic in bytes and the average size of each message?
mysql>
SELECT
->
SUM(size) AS 'total traffic',
->
AVG(size) AS 'average message size'
->
FROM mail;
+---------------+----------------------+
| total traffic | average message size |
+---------------+----------------------+
| 3798185 | 237386.5625 |
+---------------+----------------------+
• How many miles did the drivers in the
driver_log
table travel? What was the
average number of miles traveled per day?
mysql>
SELECT
->
SUM(miles) AS 'total miles',
->
AVG(miles) AS 'average miles/day'
->
FROM driver_log;
+-------------+-------------------+
| total miles | average miles/day |
