5.8 Exercises
| Question 1: | Assume that a table t contains a DATE column d. How would you find the oldest date? How would you find the most recent date? | | Question 2: | Assume that a table t contains a date-valued column d as well as other columns. How would you find the row that contains the oldest date? How would you find the row containing the most recent date? (Note that these are different problems than finding the oldest and most recent date.) | | Question 3: | Consider the following table structure and partial listing of its contents:
mysql> DESCRIBE CountryList;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Code | char(3) | | | | |
| Name | char(52) | | | | |
| IndepYear | smallint(6) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
mysql> SELECT * FROM CountryList;
+------+-------------+-----------+
| Code | Name | IndepYear |
+------+-------------+-----------+
| AFG | Afghanistan | 1919 |
| AGO | Angola | 1975 |
| ALB | Albania | 1912 |
...
How can you find out which country was the last to become independent? How can you find out which country was the first to become independent?
| | Question 4: | Here's an alphabetical list of some basic clauses for the SELECT statement:
FROM GROUP BY HAVING LIMIT ORDER BY WHERE
These clauses must be used in a specific order. What's this order? | | Question 5: | In general, which of the SELECT statement clauses shown in the previous question are optional? Which of them are optional when retrieving data from a table? | | Question 6: | You want to retrieve data from a table users in the project1 database. Your current database is test. How can you refer to the users table in the FROM clause of SELECT statements without changing the default database beforehand? | | Question 7: | The City table looks like this:
mysql> DESCRIBE City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | | PRI | NULL | auto_increment |
| Name | char(35) | | | | |
| CountryCode | char(3) | | | | |
| District | char(20) | | | | |
| Population | int(11) | | | 0 | |
+-------------+----------+------+-----+---------+----------------+
You want to find cities whose names start with letters B to F and K to M, and that have more than a million inhabitants. The output should be sorted in ascending name order. What query will produce this result? | | Question 8: | The table petbirth has the following structure and contents:
mysql> DESCRIBE petbirth; SELECT * FROM petbirth;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1995-07-29 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puffball | 1999-03-30 |
| Lucy | 1988-05-08 |
| Macie | 1997-05-08 |
| Myra | 1997-06-09 |
| Cheep | 1998-05-08 |
+----------+------------+
Using the SQL functions MONTH() and YEAR(), you want to produce an ordered list that's sorted by year and month of birth:
+----------+-------+------+
| Pet | Month | Year |
+----------+-------+------+
| Puffball | 3 | 1999 |
| Chirpy | 9 | 1998 |
| Cheep | 5 | 1998 |
| Whistler | 12 | 1997 |
| Myra | 6 | 1997 |
| Macie | 5 | 1997 |
| Slim | 4 | 1996 |
| Bowser | 7 | 1995 |
| Claws | 3 | 1994 |
| Fluffy | 2 | 1993 |
| Fang | 8 | 1990 |
| Buffy | 5 | 1989 |
| Lucy | 5 | 1988 |
+----------+-------+------+
What's the appropriate SQL statement to produce this result? | | Question 9: | The table sortorder has the following structure and contents:
mysql> DESCRIBE sortorder; SELECT * FROM sortorder;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| cs | tinyblob | YES | | NULL | |
| ci | tinytext | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
+-------+-------+
| cs | ci |
+-------+-------+
| Anton | Anton |
| anton | anton |
| Berta | Berta |
| berta | berta |
+-------+-------+
What's the output of this statement?
SELECT cs FROM sortorder ORDER BY cs;
| | Question 10: | Refer to the structure and contents shown for table sortorder in the previous question. What is the output of these two statements?
SELECT ci FROM sortorder ORDER BY ci;
SELECT ci FROM sortorder ORDER BY BINARY ci;
| | Question 11: | The table petbirth has the following structure and contents:
mysql> DESCRIBE petbirth; SELECT * FROM petbirth;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1995-07-29 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puffball | 1999-03-30 |
| Lucy | 1988-05-08 |
| Macie | 1997-05-08 |
| Myra | 1997-06-09 |
| Cheep | 1998-05-08 |
+----------+------------+
You want to display name and birthday of the oldest pet. What's the appropriate SQL statement? | | Question 12: | The table pet has the following structure and contents:
mysql> DESCRIBE pet; SELECT * FROM pet;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| owner | char(20) | YES | | NULL | |
| species | char(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
+----------+--------+---------+--------+
| name | owner | species | gender |
+----------+--------+---------+--------+
| Fluffy | Harold | cat | f |
| Claws | Gwen | cat | m |
| Buffy | Harold | dog | f |
| Fang | Benny | dog | m |
| Bowser | Diane | dog | m |
| Chirpy | Gwen | bird | f |
| Whistler | Gwen | bird | NULL |
| Slim | Benny | snake | m |
| Puffball | Diane | hamster | f |
+----------+--------+---------+--------+
What statements would you use to produce the following results?
The number of male and female pets (discarding the pets whose gender is unknown) The number of pets of each species, with the species having the highest number of individuals appearing first The number of dogs and cats, with the species that has the highest number of individuals to appear first, using the WHERE clause The number of dogs and cats, with the species which has the highest number of individuals to appear first, using the HAVING clause
The table headings for the results should be Species, Gender, and Total. | | Question 13: | The table personnel has the following structure and contents:
mysql> DESCRIBE personnel; SELECT * FROM personnel;
+--------+----------------------+
| Field | Type |
+--------+----------------------+
| pid | smallint(5) unsigned |
| unit | tinyint(3) unsigned |
| salary | decimal(9,2) |
+--------+----------------------+
+-----+------+---------+
| pid | unit | salary |
+-----+------+---------+
| 1 | 42 | 1500.00 |
| 2 | 42 | 1700.00 |
| 3 | 42 | 1950.00 |
| 4 | 42 | 2300.00 |
| 5 | 42 | 1900.00 |
| 6 | 23 | 850.00 |
| 7 | 23 | 1250.00 |
| 8 | 23 | 1450.00 |
| 9 | 23 | 1920.00 |
| 10 | 42 | 2200.00 |
| 11 | 23 | 2900.00 |
| 12 | 23 | 1000.00 |
| 13 | 42 | 2850.00 |
+-----+------+---------+
What statements would you use to retrieve the following information?
Find the number of employees, the salary total, and the average salary per employee for the two company units, with the highest total salary appearing first. The output should have headings that should look like this:
+------+-----------+----------+-------------+
| Unit | Employees | Total | Average |
+------+-----------+----------+-------------+
Identify the employees with the highest and the lowest salary per unit. The output should have headings that should look like this:
+------+---------+---------+
| Unit | High | Low |
+------+---------+---------+
| | Question 14: | The table pet has the following structure and contents:
mysql> DESCRIBE pet; SELECT * FROM pet;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| owner | char(20) | YES | | NULL | |
| species | char(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
+----------+--------+---------+--------+
| name | owner | species | gender |
+----------+--------+---------+--------+
| Fluffy | Harold | cat | f |
| Claws | Gwen | cat | m |
| Buffy | Harold | dog | f |
| Fang | Benny | dog | m |
| Bowser | Diane | dog | m |
| Chirpy | Gwen | bird | f |
| Whistler | Gwen | bird | NULL |
| Slim | Benny | snake | m |
| Puffball | Diane | hamster | f |
+----------+--------+---------+--------+
How many rows will the following statements return?
SELECT COUNT(*) FROM pet;
SELECT COUNT(gender) FROM pet;
SELECT COUNT(DISTINCT gender) FROM pet;
SELECT COUNT(DISTINCT species) FROM pet;
| | Question 15: | The table pet has the following structure and contents:
mysql> DESCRIBE pet; SELECT * FROM pet;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| owner | char(20) | YES | | NULL | |
| species | char(20) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
+----------+--------+---------+--------+
| name | owner | species | gender |
+----------+--------+---------+--------+
| Fluffy | Harold | cat | f |
| Claws | Gwen | cat | m |
| Buffy | Harold | dog | f |
| Fang | Benny | dog | m |
| Bowser | Diane | dog | m |
| Chirpy | Gwen | bird | f |
| Whistler | Gwen | bird | NULL |
| Slim | Benny | snake | m |
| Puffball | Diane | hamster | f |
+----------+--------+---------+--------+
Write an SQL statement to produce the following output:
+---------+--------+-------+
| Species | Gender | Total |
+---------+--------+-------+
| bird | NULL | 1 |
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+--------+-------+
| | Question 16: | An e-commerce company has a different database for each project. For each project, it has a table that holds the team members. Unluckily, this information is not organized consistently, so there are three tables in three different databases that look like this:
mysql> SELECT * FROM project1.user;
+-------+-------------+
| name | job |
+-------+-------------+
| John | Manager |
| Steve | Programmer |
| Andy | Webdesigner |
+-------+-------------+
mysql> SELECT * FROM project2.users;
+-------+------------+
| nick | task |
+-------+------------+
| Jim | Manager |
| Steve | Programmer |
+-------+------------+
mysql> SELECT * FROM project3.members;
+--------+-------------+
| member | job |
+--------+-------------+
| John | Manager |
| Steve | Programmer |
| Carol | Webdesigner |
+--------+-------------+
Assume that you want output with headings that look like this:
+------------+-------------+
| TeamMember | TeamTask |
+------------+-------------+
What SQL statement will give you a list of all team members, sorted by their names? What would the statement be if you don't want team members to appear more than once in the list? |
Answers to Exercises | | | Answer 1: | The following queries find the oldest and most recent dates in the column d:
SELECT MIN(d) FROM t;
SELECT MAX(d) FROM t;
| | | | Answer 2: | To find the rows containing the oldest and most recent dates in the column d, sort the dates in ascending or descending date order and return the first row of the result:
SELECT * FROM t ORDER BY d LIMIT 1;
SELECT * FROM t ORDER BY d DESC LIMIT 1;
| | | | Answer 3: |
Here's the last country that became independent:
mysql> SELECT * FROM CountryList ORDER BY IndepYear DESC LIMIT 1;
+------+-------+-----------+
| Code | Name | IndepYear |
+------+-------+-----------+
| PLW | Palau | 1994 |
+------+-------+-----------+
Here's the first country that became independent:
mysql> SELECT * FROM CountryList ORDER BY IndepYear ASC LIMIT 1;
+------+-------+-----------+
| Code | Name | IndepYear |
+------+-------+-----------+
| CHN | China | -1523 |
+------+-------+-----------+
| | | | Answer 4: | The clauses must be used in this order:
FROM WHERE GROUP BY HAVING ORDER BY LIMIT
| | | | Answer 5: | All the SELECT clauses shown in the previous question are optional. When you retrieve data from a table, the FROM clause is mandatory; all other clauses are optional. | | | | Answer 6: | You can use a fully qualified table name. That is, precede the table name with the database name as a qualifier. The users table would be referred to as project1.users. | | | | Answer 7: | The following query retrieves cities having names that begin with B, C, D, E, F, K, L, or M and that have more than a million inhabitants:
mysql> SELECT Name, Population FROM City
-> WHERE (
-> (Name >= 'B' AND Name < 'G')
-> OR
-> (Name >= 'K' AND Name < 'N')
-> )
-> AND Population > 1000000
-> ORDER BY Name ASC
-> ;
Other solutions are possible. | | | | Answer 8: | Birthdays of pets (months and years only) in descending order:
mysql> SELECT
-> name AS Pet,
-> MONTH(birth) AS Month,
-> YEAR(birth) AS Year
-> FROM petbirth
-> ORDER BY Year DESC, Month DESC
-> ;
| | | | Answer 9: |
mysql> SELECT cs FROM sortorder ORDER BY cs;
+-------+
| cs |
+-------+
| Anton |
| Berta |
| anton |
| berta |
+-------+
For string columns, the sort order depends on whether the strings are binary or nonbinary. The TINYBLOB column cs is binary, so it sorts in a case-sensitive manner. The byte values of A and B are lower than the byte values of a and b in the server's default character set. | | | | Answer 10: |
mysql> SELECT ci FROM sortorder ORDER BY ci;
+-------+
| ci |
+-------+
| Anton |
| anton |
| Berta |
| berta |
+-------+
mysql> SELECT ci FROM sortorder ORDER BY BINARY ci;
+-------+
| ci |
+-------+
| Anton |
| Berta |
| anton |
| berta |
+-------+
For string columns, the sort order depends on whether the strings are binary or nonbinary. In the first query, the TINYTEXT column ci is nonbinary, so it sorts in the lexical order defined by the server's default character set. In the second query, the keyword BINARY makes the sort order for ci case-sensitive, so the result is the same as for the case-sensitive column cs in the previous question. | | | | Answer 11: | To retrieve information for the oldest animal, sort the table in birth order and limit the result to the first row:
mysql> SELECT
-> name, birth
-> FROM petbirth
-> ORDER BY birth ASC
-> LIMIT 1
-> ;
+-------+------------+
| name | birth |
+-------+------------+
| Lucy | 1988-05-08 |
+-------+------------+
| | | | Answer 12: | The following queries produce the desired results:
Number of pets by gender:
mysql> SELECT
-> gender AS Gender,
-> COUNT(*) AS Total
-> FROM pet
-> WHERE gender IS NOT NULL
-> GROUP BY Gender;
+--------+-------+
| Gender | Total |
+--------+-------+
| f | 4 |
| m | 4 |
+--------+-------+
Number of pets by species:
mysql> SELECT
-> species AS Species,
-> COUNT(*) AS Total
-> FROM pet
-> GROUP BY Species
-> ORDER BY Total DESC;
+---------+-------+
| Species | Total |
+---------+-------+
| dog | 3 |
| cat | 2 |
| bird | 2 |
| snake | 1 |
| hamster | 1 |
+---------+-------+
Number of dogs and cats, using a WHERE clause:
mysql> SELECT
-> species AS Species,
-> COUNT(*) AS Total
-> FROM pet
-> WHERE Species='dog' OR Species='cat'
-> GROUP BY Species
-> ORDER BY Total DESC;
+---------+-------+
| Species | Total |
+---------+-------+
| dog | 3 |
| cat | 2 |
+---------+-------+
Number of dogs and cats, using a HAVING clause:
mysql> SELECT
-> species AS Species,
-> COUNT(*) AS Total
-> FROM pet
-> GROUP BY Species
-> HAVING Species='dog' OR Species='cat'
-> ORDER BY Total DESC;
+---------+-------+
| Species | Total |
+---------+-------+
| dog | 3 |
| cat | 2 |
+---------+-------+
| | | | Answer 13: |
Number of employees, total, and average salary per unit:
mysql> SELECT
-> unit AS Unit,
-> COUNT(*) AS Employees,
-> SUM(salary) AS Total,
-> AVG(salary) AS Average
-> FROM personnel
-> GROUP BY Unit
-> ORDER BY Total DESC
-> ;
+------+-----------+----------+-------------+
| Unit | Employees | Total | Average |
+------+-----------+----------+-------------+
| 42 | 7 | 14400.00 | 2057.142857 |
| 23 | 6 | 9370.00 | 1561.666667 |
+------+-----------+----------+-------------+
Highest and lowest salary per unit:
mysql> SELECT
-> unit AS Unit,
-> MAX(salary) AS High,
-> MIN(salary) AS Low
-> FROM personnel
-> GROUP BY Unit
-> ;
+------+---------+---------+
| Unit | High | Low |
+------+---------+---------+
| 23 | 2900.00 | 850.00 |
| 42 | 2850.00 | 1500.00 |
+------+---------+---------+
| | | | Answer 14: |
This statement produces the total number of pets in the table:
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
COUNT(gender) counts only non-NULL values, so the statement counts only those rows containing a known gender. Because there's one NULL value in the gender column, that row isn't counted:
mysql> SELECT COUNT(gender) FROM pet;
+---------------+
| COUNT(gender) |
+---------------+
| 8 |
+---------------+
The statement counts the number of distinct known genders. The NULL gender of the bird isn't counted:
mysql> SELECT COUNT(DISTINCT gender) FROM pet;
+------------------------+
| COUNT(DISTINCT gender) |
+------------------------+
| 2 |
+------------------------+
The statement counts the number of distinct known species:
mysql> SELECT COUNT(DISTINCT species) FROM pet;
+-------------------------+
| COUNT(DISTINCT species) |
+-------------------------+
| 5 |
+-------------------------+
| | | | Answer 15: | The output counts the number of individuals for each combination of species and gender. It's displayed sorted by species and by gender within species. The following statement produces the output:
mysql> SELECT
-> species AS Species,
-> gender AS Gender,
-> COUNT(*) AS Total
-> FROM pet
-> GROUP BY Species, Gender
-> ;
| | | | Answer 16: | To display all team members, issue this statement:
mysql> SELECT
-> name AS TeamMember,
-> job AS TeamTask
-> FROM project1.user
-> UNION ALL
-> SELECT * FROM project2.users
-> UNION
-> SELECT * FROM project3.members
-> ORDER BY TeamMember
-> ;
+------------+-------------+
| TeamMember | TeamTask |
+------------+-------------+
| Andy | Webdesigner |
| Carol | Webdesigner |
| Jim | Manager |
| John | Manager |
| John | Manager |
| Steve | Programmer |
| Steve | Programmer |
| Steve | Programmer |
+------------+-------------+
To remove duplicates of team members, the statement is the same, with the exception that you omit the ALL keyword:
mysql> SELECT
-> name AS TeamMember,
-> job AS TeamTask
-> FROM project1.user
-> UNION
-> SELECT * FROM project2.users
-> UNION
-> SELECT * FROM project3.members
-> ORDER BY TeamMember
-> ;
+------------+-------------+
| TeamMember | TeamTask |
+------------+-------------+
| Andy | Webdesigner |
| Carol | Webdesigner |
| Jim | Manager |
| John | Manager |
| Steve | Programmer |
+------------+-------------+
|
|
