8.6 Exercises
| Question 1: | What kind of join can find matches (values that are present in both tables involved in the join)? | | Question 2: | What kind of join or joins find mismatches (values that are present in only one of the tables involved in the join)? | | Question 3: | Here's the structure and contents for two tables, client and project, which will be used for the next two questions.
mysql> DESCRIBE client; DESCRIBE project;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| cid | smallint(5) unsigned | | PRI | 0 | |
| name | char(20) | | | | |
+-------+----------------------+------+-----+---------+-------+
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| pid | int(10) unsigned | | PRI | 0 | |
| cid | smallint(5) unsigned | | | 0 | |
| name | char(30) | | | | |
| start | date | YES | | NULL | |
| end | date | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
mysql> SELECT * FROM client; SELECT * FROM project;
+-----+---------------+
| cid | name |
+-----+---------------+
| 101 | Seamen's |
| 103 | Lennart AG |
| 110 | MySQL AB |
| 115 | Icoaten & Co. |
| 125 | Nittboad Inc |
+-----+---------------+
+-------+-----+-------------+------------+------------+
| pid | cid | name | start | end |
+-------+-----+-------------+------------+------------+
| 10000 | 103 | New CMS | 2003-01-00 | 2003-05-00 |
| 10010 | 110 | Texi2XML | 2002-04-00 | 2003-09-00 |
| 10020 | 100 | Studyguides | 2002-09-00 | 2003-03-30 |
| 10030 | 115 | PDC Server | 2003-01-00 | 2003-01-00 |
| 10040 | 103 | Intranet | 2009-02-00 | NULL |
| 10050 | 101 | Intranet | NULL | NULL |
| 10060 | 115 | SMB Server | 2003-05-00 | NULL |
| 10070 | 115 | WLAN | NULL | 2003-07-00 |
+-------+-----+-------------+------------+------------+
How many rows will the following join statements return?
mysql> SELECT client.name, project.name, project.start, project.end
-> FROM client, project
-> ;
mysql> SELECT client.name, project.name, project.start, project.end
-> FROM client, project
-> WHERE project.cid = client.cid
-> ;
| | Question 4: | Refer to the client and project tables shown in the previous question. How many rows will the following join statements return?
mysql> SELECT client.name, project.name, project.start, project.end
-> FROM client, project
-> WHERE project.cid = client.cid
-> AND project.start IS NOT NULL
-> ;
mysql> SELECT client.name, project.name, project.start, project.end
-> FROM client, project
-> WHERE project.cid = client.cid
-> AND project.start IS NOT NULL
-> AND project.end IS NOT NULL
-> ;
| | Question 5: | Here's the structure and sample data for two tables, client and project, which will be used for the next three questions.
mysql> DESCRIBE client; DESCRIBE project;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | | PRI | 0 | |
| name | char(20) | | | | |
+-------+----------------------+------+-----+---------+-------+
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| pid | int(10) unsigned | | PRI | 0 | |
| id | smallint(5) unsigned | | | 0 | |
| name | char(30) | | | | |
| start | date | YES | | NULL | |
| end | date | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
mysql> SELECT * FROM client; SELECT * FROM project;
+-----+---------------+
| id | name |
+-----+---------------+
| 101 | Seamen's |
| 103 | Lennart AG |
| 110 | MySQL AB |
| 115 | Icoaten & Co. |
| 125 | Nittboad Inc |
+-----+---------------+
+-------+-----+-------------+------------+------------+
| pid | id | name | start | end |
+-------+-----+-------------+------------+------------+
| 10000 | 103 | New CMS | 2003-01-00 | 2003-05-00 |
| 10010 | 110 | Texi2XML | 2002-04-00 | 2003-09-00 |
| 10020 | 100 | Studyguides | 2002-09-00 | 2003-03-30 |
| 10030 | 115 | PDC Server | 2003-01-00 | 2003-01-00 |
| 10040 | 103 | Intranet | 2009-02-00 | NULL |
| 10050 | 101 | Intranet | NULL | NULL |
| 10060 | 115 | SMB Server | 2003-05-00 | NULL |
| 10070 | 115 | WLAN | NULL | 2003-07-00 |
+-------+-----+-------------+------------+------------+
Using the client and project tables, you want to retrieve a list of clients that have no projects for a report with these headings:
+----------+---------+-------+------+
| CLIENT | PROJECT | START | END |
+----------+---------+-------+------+
What SQL statement will you issue? | | Question 6: | Refer to the structure and sample data for the client and project tables, shown in the previous question. Using these two tables, you want to retrieve a list of clients that have projects starting in the year 2003, sorted by start date, for a report with these headings:
+----------+---------+-------+------+
| CLIENT | PROJECT | START | END |
+----------+---------+-------+------+
What SQL statement will you issue? | | Question 7: | Refer to the structure and sample data for the client and project tables, shown two questions earlier. Using these two tables, you want to retrieve a list of clients that have intranet projects, using the LEFT JOIN syntax, for a report with these headings:
+----------+---------+-------+------+
| CLIENT | PROJECT | START | END |
+----------+---------+-------+------+
What SQL statement will you issue? | | Question 8: | Here's the structure and sample data for two tables, client and project, which will be used for the next three questions.
mysql> DESCRIBE client; DESCRIBE project;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | | PRI | 0 | |
| name | char(20) | | | | |
+-------+----------------------+------+-----+---------+-------+
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| pid | int(10) unsigned | | PRI | 0 | |
| id | smallint(5) unsigned | | | 0 | |
| name | char(30) | | | | |
| start | date | YES | | NULL | |
| end | date | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
mysql> SELECT * FROM client; SELECT * FROM project;
+-----+---------------+
| id | name |
+-----+---------------+
| 101 | Seamen's |
| 103 | Lennart AG |
| 110 | MySQL AB |
| 115 | Icoaten & Co. |
| 125 | Nittboad Inc |
+-----+---------------+
+-------+-----+-------------+------------+------------+
| pid | id | name | start | end |
+-------+-----+-------------+------------+------------+
| 10000 | 103 | New CMS | 2003-01-00 | 2003-05-00 |
| 10010 | 110 | Texi2XML | 2002-04-00 | 2003-09-00 |
| 10020 | 100 | Studyguides | 2002-09-00 | 2003-03-30 |
| 10030 | 115 | PDC Server | 2003-01-00 | 2003-01-00 |
| 10040 | 103 | Intranet | 2009-02-00 | NULL |
| 10050 | 101 | Intranet | NULL | NULL |
| 10060 | 115 | SMB Server | 2003-05-00 | NULL |
| 10070 | 115 | WLAN | NULL | 2003-07-00 |
+-------+-----+-------------+------------+------------+
Using the client and project tables, you want to retrieve a list of clients that have intranet projects, using the INNER JOIN syntax, for a report with these headings:
+----------+---------+-------+------+
| CLIENT | PROJECT | START | END |
+----------+---------+-------+------+
What SQL statement will you issue? | | Question 9: | Refer to the structure and sample data for the client and project tables, shown in the previous question. Using these two tables, you want to retrieve a list of clients that have intranet projects, using an inner join with the WHERE clause, for a report with these headings:
+----------+---------+-------+------+
| CLIENT | PROJECT | START | END |
+----------+---------+-------+------+
What SQL statement will you issue? | | Question 10: | Refer to the structure and sample data for the client and project tables, shown two questions earlier. Using these two tables, you want to retrieve a list of clients and their projects, sorted by client name, and within client name, sorted by start date, for a report with these headings:
+----------+---------+-------+------+
| CLIENT | PROJECT | START | END |
+----------+---------+-------+------+
What SQL statement will you issue? Clients without projects should also be displayed. | | Question 11: | Here's the structure and sample data for two tables, client and project.
mysql> DESCRIBE client; DESCRIBE project;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | | PRI | 0 | |
| name | char(20) | | | | |
+-------+----------------------+------+-----+---------+-------+
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| pid | int(10) unsigned | | PRI | 0 | |
| id | smallint(5) unsigned | | | 0 | |
| name | char(30) | | | | |
| start | date | YES | | NULL | |
| end | date | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
mysql> SELECT * FROM client; SELECT * FROM project;
+-----+---------------+
| id | name |
+-----+---------------+
| 101 | Seamen's |
| 103 | Lennart AG |
| 110 | MySQL AB |
| 115 | Icoaten & Co. |
| 125 | Nittboad Inc |
+-----+---------------+
+-------+-----+-------------+------------+------------+
| pid | id | name | start | end |
+-------+-----+-------------+------------+------------+
| 10000 | 103 | New CMS | 2003-01-00 | 2003-05-00 |
| 10010 | 110 | Texi2XML | 2002-04-00 | 2003-09-00 |
| 10020 | 100 | Studyguides | 2002-09-00 | 2003-03-30 |
| 10030 | 115 | PDC Server | 2003-01-00 | 2003-01-00 |
| 10040 | 103 | Intranet | 2009-02-00 | NULL |
| 10050 | 101 | Intranet | NULL | NULL |
| 10060 | 115 | SMB Server | 2003-05-00 | NULL |
| 10070 | 115 | WLAN | NULL | 2003-07-00 |
+-------+-----+-------------+------------+------------+
Using the client and project tables, you want to retrieve a list of clients and their projects, sorted by client name, and within client name, sorted by start date, for a report with these headings:
+----------+---------+-------+------+
| CLIENT | PROJECT | START | END |
+----------+---------+-------+------+
What SQL statement will you issue? Clients without projects should be displayed, but projects that don't have a start date and projects that don't have an end date should not be displayed. | | Question 12: | The following sample data from two tables, client and project, will be used for the next three questions.
mysql> SELECT * FROM client; SELECT * FROM project;
+-----+---------------+
| id | name |
+-----+---------------+
| 101 | Seamen's |
| 103 | Lennart AG |
| 110 | MySQL AB |
| 115 | Icoaten & Co. |
| 125 | Nittboad Inc |
+-----+---------------+
+-------+-----+-------------+------------+------------+
| pid | id | name | start | end |
+-------+-----+-------------+------------+------------+
| 10000 | 103 | New CMS | 2003-01-00 | 2003-05-00 |
| 10010 | 110 | Texi2XML | 2002-04-00 | 2003-09-00 |
| 10020 | 100 | Studyguides | 2002-09-00 | 2003-03-30 |
| 10030 | 115 | PDC Server | 2003-01-00 | 2003-01-00 |
| 10040 | 103 | Intranet | 2009-02-00 | NULL |
| 10050 | 101 | Intranet | NULL | NULL |
| 10060 | 115 | SMB Server | 2003-05-00 | NULL |
| 10070 | 115 | WLAN | NULL | 2003-07-00 |
| 10080 | 135 | Intranet | 2003-08-00 | NULL |
| 10090 | 145 | PDC Server | NULL | NULL |
+-------+-----+-------------+------------+------------+
The client and project tables are related through their common column (id). Thus, the following statement could be used to determine which projects have clients (that is, which projects have an id whose value is the same as one of the id values found in the client table):
SELECT ... FROM project WHERE project.id IN
(SELECT client.id FROM client)
Prior to version 4.1, MySQL doesn't support subqueries such as this. How would you rewrite the statement using the LEFT JOIN operator? Your output should have headers like this:
+------------+--------------+-----------+---------------+
| Project ID | Project Name | Client No | Client Name |
+------------+--------------+-----------+---------------+
| | Question 13: | Recall that the client and project tables shown in the previous question are related through their common column (id). This SQL statement produces a list of projects that have an id whose value is the same as one of the id values found in the client table:
SELECT ... FROM project WHERE project.id IN
(SELECT client.id FROM client)
Prior to version 4.1, MySQL doesn't support subqueries such as this. How would you rewrite the statement using the RIGHT JOIN operator? Your output should have headers like this:
+------------+--------------+-----------+---------------+
| Project ID | Project Name | Client No | Client Name |
+------------+--------------+-----------+---------------+
| | Question 14: | Recall that the client and project tables shown two questions earlier are related through their common column (id). This SQL statement produces a list of projects that have an id whose value is the same as one of the id values found in the client table:
SELECT ... FROM project WHERE project.id IN
(SELECT client.id FROM client)
Prior to version 4.1, MySQL doesn't support subqueries such as this. How would you rewrite the statement using the INNER JOIN operator? Your output should have headers like this:
+------------+--------------+-----------+---------------+
| Project ID | Project Name | Client No | Client Name |
+------------+--------------+-----------+---------------+
| | Question 15: | The following sample data from two tables, client and project, will be used for the next three questions.
mysql> SELECT * FROM client; SELECT * FROM project;
+-----+---------------+
| id | name |
+-----+---------------+
| 101 | Seamen's |
| 103 | Lennart AG |
| 110 | MySQL AB |
| 115 | Icoaten & Co. |
| 125 | Nittboad Inc |
+-----+---------------+
+-------+-----+-------------+------------+------------+
| pid | id | name | start | end |
+-------+-----+-------------+------------+------------+
| 10000 | 103 | New CMS | 2003-01-00 | 2003-05-00 |
| 10010 | 110 | Texi2XML | 2002-04-00 | 2003-09-00 |
| 10020 | 100 | Studyguides | 2002-09-00 | 2003-03-30 |
| 10030 | 115 | PDC Server | 2003-01-00 | 2003-01-00 |
| 10040 | 103 | Intranet | 2009-02-00 | NULL |
| 10050 | 101 | Intranet | NULL | NULL |
| 10060 | 115 | SMB Server | 2003-05-00 | NULL |
| 10070 | 115 | WLAN | NULL | 2003-07-00 |
| 10080 | 135 | Intranet | 2003-08-00 | NULL |
| 10090 | 145 | PDC Server | NULL | NULL |
+-------+-----+-------------+------------+------------+
The client and project tables are related through their common column (id). Thus, the following statement could be used to determine which projects don't have clients (that is, which projects have an id whose value is not the same as one of the id values found in the client table):
SELECT ... FROM project WHERE project.id NOT IN
(SELECT client.id FROM client)
Prior to version 4.1, MySQL doesn't support subqueries such as this. How would you rewrite the statement using the LEFT JOIN operator? Your output should have headers like this:
+------------+--------------+-----------+---------------+
| Project ID | Project Name | Client No | Client Name |
+------------+--------------+-----------+---------------+
| | Question 16: | Recall that the client and project tables shown in the previous question are related through their common column (id). This SQL statement produces a list of projects that have an id whose value is not the same as one of the id values found in the client table:
SELECT ... FROM project WHERE project.id NOT IN
(SELECT client.id FROM client)
Prior to version 4.1, MySQL doesn't support subqueries such as this. How would you rewrite the statement using the RIGHT JOIN operator? Your output should have headers like this:
+------------+--------------+-----------+---------------+
| Project ID | Project Name | Client No | Client Name |
+------------+--------------+-----------+---------------+
| | Question 17: | Recall that the client and project tables shown two questions earlier are related through their common column (id). This SQL statement produces a list of projects that have an id whose value is not the same as one of the id values found in the client table:
SELECT ... FROM project WHERE project.id NOT IN
(SELECT client.id FROM client)
Prior to version 4.1, MySQL doesn't support subqueries such as this. How would you rewrite the statement using the INNER JOIN operator? Your output should have headers like this:
+------------+--------------+-----------+---------------+
| Project ID | Project Name | Client No | Client Name |
+------------+--------------+-----------+---------------+
| | Question 18: | Consider the following record from the Country table:
mysql> SELECT
-> Name, Region, Continent, SurfaceArea, Population
-> FROM Country
-> WHERE Name = 'Paraguay'
-> ;
+----------+---------------+---------------+-------------+------------+
| Name | Region | Continent | SurfaceArea | Population |
+----------+---------------+---------------+-------------+------------+
| Paraguay | South America | South America | 406752.00 | 5496000 |
+----------+---------------+---------------+-------------+------------+
What statement would you issue to retrieve a list of countries whose surface area is larger than that of Paraguay, when you consider countries on the same continent (South America) only? The headings of the result should look like this:
+----------+-----------------+---------------+--------------+
| Country | Other Countries | Continent | Surface Area |
+----------+-----------------+---------------+--------------+
| | Question 19: | Consider the following record from the Country table:
mysql> SELECT
-> Name, Region, Continent, SurfaceArea, Population
-> FROM Country
-> WHERE Name = 'Germany'
-> ;
+---------+----------------+-----------+-------------+------------+
| Name | Region | Continent | SurfaceArea | Population |
+---------+----------------+-----------+-------------+------------+
| Germany | Western Europe | Europe | 357022.00 | 82164700 |
+---------+----------------+-----------+-------------+------------+
What statement would you issue to retrieve a list of the countries worldwide with a population at least as large as that of Germany? Germany should be included in the list, which should be sorted by descending population. The headings of the result should look like this:
+---------+--------------------+------------+
| Country | Other Countries | Population |
+---------+--------------------+------------+
| | Question 20: | Consider the following record from the Country table:
mysql> SELECT
-> Name, Region, Continent, SurfaceArea, Population
-> FROM Country
-> WHERE Name = 'Nepal'
-> ;
+-------+---------------------------+-----------+-------------+------------+
| Name | Region | Continent | SurfaceArea | Population |
+-------+---------------------------+-----------+-------------+------------+
| Nepal | Southern and Central Asia | Asia | 147181.00 | 23930000 |
+-------+---------------------------+-----------+-------------+------------+
What statement would you issue to retrieve a list of countries in the same region with a population and surface area at least as large as that of Nepal? Nepal should be included in the list, which should be sorted by descending population. The headings of the result should look like this, with all region names cut to a maximum length of 10 characters:
+---------+-----------------+----------------+------------+------------+
| Country | Other Countries | Region | Population | Surface |
+---------+-----------------+----------------+------------+------------+
|
Answers to Exercises | | | Answer 1: | Any kind of join can find matches between tables. | | | | Answer 2: | Outer joins, that is, LEFT JOIN and RIGHT JOIN. | | | | Answer 3: | The statements will return the following result sets:
+---------------+-------------+------------+------------+
| name | name | start | end |
+---------------+-------------+------------+------------+
| Seamen's | New CMS | 2003-01-00 | 2003-05-00 |
| Lennart AG | New CMS | 2003-01-00 | 2003-05-00 |
| MySQL AB | New CMS | 2003-01-00 | 2003-05-00 |
| Icoaten & Co. | New CMS | 2003-01-00 | 2003-05-00 |
| Nittboad Inc | New CMS | 2003-01-00 | 2003-05-00 |
| Seamen's | Texi2XML | 2002-04-00 | 2003-09-00 |
| Lennart AG | Texi2XML | 2002-04-00 | 2003-09-00 |
| MySQL AB | Texi2XML | 2002-04-00 | 2003-09-00 |
| Icoaten & Co. | Texi2XML | 2002-04-00 | 2003-09-00 |
| Nittboad Inc | Texi2XML | 2002-04-00 | 2003-09-00 |
| Seamen's | Studyguides | 2002-09-00 | 2003-03-30 |
| Lennart AG | Studyguides | 2002-09-00 | 2003-03-30 |
| MySQL AB | Studyguides | 2002-09-00 | 2003-03-30 |
| Icoaten & Co. | Studyguides | 2002-09-00 | 2003-03-30 |
| Nittboad Inc | Studyguides | 2002-09-00 | 2003-03-30 |
| Seamen's | PDC Server | 2003-01-11 | 2003-01-00 |
| Lennart AG | PDC Server | 2003-01-11 | 2003-01-00 |
| MySQL AB | PDC Server | 2003-01-11 | 2003-01-00 |
| Icoaten & Co. | PDC Server | 2003-01-11 | 2003-01-00 |
| Nittboad Inc | PDC Server | 2003-01-11 | 2003-01-00 |
| Seamen's | Intranet | 2009-02-00 | NULL |
| Lennart AG | Intranet | 2009-02-00 | NULL |
| MySQL AB | Intranet | 2009-02-00 | NULL |
| Icoaten & Co. | Intranet | 2009-02-00 | NULL |
| Nittboad Inc | Intranet | 2009-02-00 | NULL |
| Seamen's | Intranet | NULL | NULL |
| Lennart AG | Intranet | NULL | NULL |
| MySQL AB | Intranet | NULL | NULL |
| Icoaten & Co. | Intranet | NULL | NULL |
| Nittboad Inc | Intranet | NULL | NULL |
| Seamen's | SMB Server | 2003-05-00 | NULL |
| Lennart AG | SMB Server | 2003-05-00 | NULL |
| MySQL AB | SMB Server | 2003-05-00 | NULL |
| Icoaten & Co. | SMB Server | 2003-05-00 | NULL |
| Nittboad Inc | SMB Server | 2003-05-00 | NULL |
| Seamen's | WLAN | NULL | 2003-07-00 |
| Lennart AG | WLAN | NULL | 2003-07-00 |
| MySQL AB | WLAN | NULL | 2003-07-00 |
| Icoaten & Co. | WLAN | NULL | 2003-07-00 |
| Nittboad Inc | WLAN | NULL | 2003-07-00 |
+---------------+-------------+------------+------------+
40 rows in set
+---------------+------------+------------+------------+
| name | name | start | end |
+---------------+------------+------------+------------+
| Lennart AG | New CMS | 2003-01-00 | 2003-05-00 |
| MySQL AB | Texi2XML | 2002-04-00 | 2003-09-00 |
| Icoaten & Co. | PDC Server | 2003-01-00 | 2003-01-00 |
| Lennart AG | Intranet | 2009-02-00 | NULL |
| Seamen's | Intranet | NULL | NULL |
| Icoaten & Co. | SMB Server | 2003-05-00 | NULL |
| Icoaten & Co. | WLAN | NULL | 2003-07-00 |
+---------------+------------+------------+------------+
7 rows in set
| | | | Answer 4: | The statements will return the following result sets:
+---------------+------------+------------+------------+
| name | name | start | end |
+---------------+------------+------------+------------+
| Lennart AG | New CMS | 2003-01-00 | 2003-05-00 |
| MySQL AB | Texi2XML | 2002-04-00 | 2003-09-00 |
| Icoaten & Co. | PDC Server | 2003-01-00 | 2003-01-00 |
| Lennart AG | Intranet | 2009-02-00 | NULL |
| Icoaten & Co. | SMB Server | 2003-05-00 | NULL |
+---------------+------------+------------+------------+
5 rows in set
+---------------+------------+------------+------------+
| name | name | start | end |
+---------------+------------+------------+------------+
| Lennart AG | New CMS | 2003-01-00 | 2003-05-00 |
| MySQL AB | Texi2XML | 2002-04-00 | 2003-09-00 |
| Icoaten & Co. | PDC Server | 2003-01-00 | 2003-01-00 |
+---------------+------------+------------+------------+
3 rows in set
| | | | Answer 5: | Note: For this exercise, as for several of the following exercises, there might be more than one way to retrieve the required information. But here's one SQL statement that accomplishes the task:
mysql> SELECT
-> c.name AS CLIENT, p.name AS PROJECT,
-> p.start AS START, p.end AS END
-> FROM client AS c
-> LEFT JOIN project AS p
-> USING (id)
-> WHERE p.id IS NULL
-> ORDER BY CLIENT
-> ;
+--------------+---------+-------+------+
| CLIENT | PROJECT | START | END |
+--------------+---------+-------+------+
| Nittboad Inc | NULL | NULL | NULL |
+--------------+---------+-------+------+
1 row in set
| | | | Answer 6: | Here's one SQL statement that accomplishes the task:
mysql> SELECT
-> c.name AS CLIENT, p.name AS PROJECT,
-> p.start AS START, p.end AS END
-> FROM client AS c
-> LEFT JOIN project AS p
-> USING (id)
-> WHERE START BETWEEN '2003-01-00' AND '2003-12-31'
-> ORDER BY START
-> ;
+---------------+------------+------------+------------+
| CLIENT | PROJECT | START | END |
+---------------+------------+------------+------------+
| Lennart AG | New CMS | 2003-01-00 | 2003-05-00 |
| Icoaten & Co. | PDC Server | 2003-01-00 | 2003-01-00 |
| Icoaten & Co. | SMB Server | 2003-05-00 | NULL |
+---------------+------------+------------+------------+
3 rows in set
| | | | Answer 7: | This statement accomplishes the task:
mysql> SELECT
-> c.name AS CLIENT, p.name AS PROJECT,
-> p.start AS START, p.end AS END
-> FROM client AS c
-> LEFT JOIN project AS p
-> USING (id) /* or: ON c.id = p.id */
-> WHERE p.name = 'Intranet'
-> ;
+------------+----------+------------+------+
| CLIENT | PROJECT | START | END |
+------------+----------+------------+------+
| Seamen's | Intranet | NULL | NULL |
| Lennart AG | Intranet | 2009-02-00 | NULL |
+------------+----------+------------+------+
2 rows in set
| | | | Answer 8: | This statement accomplishes the task:
mysql> SELECT
-> c.name AS CLIENT, p.name AS PROJECT,
-> p.start AS START, p.end AS END
-> FROM client AS c
-> INNER JOIN project AS p
-> USING (id) /* or: ON c.id = p.id */
-> WHERE p.name = 'Intranet'
-> ;
+------------+----------+------------+------+
| CLIENT | PROJECT | START | END |
+------------+----------+------------+------+
| Seamen's | Intranet | NULL | NULL |
| Lennart AG | Intranet | 2009-02-00 | NULL |
+------------+----------+------------+------+
2 rows in set
| | | | Answer 9: | This statement accomplishes the task:
mysql> SELECT
-> c.name AS CLIENT, p.name AS PROJECT,
-> p.start AS START, p.end AS END
-> FROM client AS c, project AS p
-> WHERE c.id = p.id
-> AND p.name = 'Intranet'
-> ;
+------------+----------+------------+------+
| CLIENT | PROJECT | START | END |
+------------+----------+------------+------+
| Seamen's | Intranet | NULL | NULL |
| Lennart AG | Intranet | 2009-02-00 | NULL |
+------------+----------+------------+------+
2 rows in set
| | | | Answer 10: | Here's one SQL statement that accomplishes the task:
mysql> SELECT
-> c.name AS CLIENT, p.name AS PROJECT,
-> p.start AS START, p.end AS END
-> FROM client AS c
-> LEFT JOIN project AS p
-> USING (id) /* or: ON c.id = p.id */
-> ORDER BY c.name ASC, p.start ASC
-> ;
+---------------+------------+------------+------------+
| CLIENT | PROJECT | START | END |
+---------------+------------+------------+------------+
| Icoaten & Co. | WLAN | NULL | 2003-07-00 |
| Icoaten & Co. | PDC Server | 2003-01-00 | 2003-01-00 |
| Icoaten & Co. | SMB Server | 2003-05-00 | NULL |
| Nittboad Inc | NULL | NULL | NULL |
| Lennart AG | New CMS | 2003-01-00 | 2003-05-00 |
| Lennart AG | Intranet | 2009-02-00 | NULL |
| MySQL AB | Texi2XML | 2002-04-00 | 2003-09-00 |
| Seamen's | Intranet | NULL | NULL |
+---------------+------------+------------+------------+
8 rows in set
| | | | Answer 11: | Here's one SQL statement that accomplishes the task:
mysql> SELECT
-> c.name AS CLIENT, p.name AS PROJECT,
-> p.start AS START, p.end AS END
-> FROM client AS c
-> LEFT JOIN project AS p
-> USING (id) /* or: ON c.id = p.id */
-> WHERE p.start IS NOT NULL
-> AND p.end IS NOT NULL
-> ORDER BY c.name ASC, p.start ASC
-> ;
+---------------+------------+------------+------------+
| CLIENT | PROJECT | START | END |
+---------------+------------+------------+------------+
| Icoaten & Co. | PDC Server | 2003-01-00 | 2003-01-00 |
| Lennart AG | New CMS | 2003-01-00 | 2003-05-00 |
| MySQL AB | Texi2XML | 2002-04-00 | 2003-09-00 |
+---------------+------------+------------+------------+
3 rows in set
| | | | Answer 12: | Subquery converted to a LEFT JOIN:
mysql> SELECT
-> p.pid AS `Project ID`,
-> p.name AS `Project Name`,
-> c.id AS `Client No`,
-> c.name AS `Client Name`
-> FROM project AS p
-> LEFT JOIN client AS c
-> USING (id) /* or: ON p.id = c.id */
-> WHERE c.name IS NOT NULL
-> ;
+------------+--------------+-----------+---------------+
| Project ID | Project Name | Client No | Client Name |
+------------+--------------+-----------+---------------+
| 10000 | New CMS | 103 | Lennart AG |
| 10010 | Texi2XML | 110 | MySQL AB |
| 10020 | Studyguides | 110 | MySQL AB |
| 10030 | PDC Server | 115 | Icoaten & Co. |
| 10040 | Intranet | 103 | Lennart AG |
| 10050 | Intranet | 101 | Seamen's |
| 10060 | SMB Server | 115 | Icoaten & Co. |
| 10070 | WLAN | 115 | Icoaten & Co. |
+------------+--------------+-----------+---------------+
8 rows in set
| | | | Answer 13: | Subquery converted to a RIGHT JOIN:
mysql> SELECT
-> p.pid AS `Project ID`,
-> p.name AS `Project Name`,
-> c.id AS `Client No`,
-> c.name AS `Client Name`
-> FROM client AS c
-> RIGHT JOIN project AS p
-> USING (id) /* or: ON c.id = p.id */
-> WHERE c.name IS NOT NULL
-> ;
+------------+--------------+-----------+---------------+
| Project ID | Project Name | Client No | Client Name |
+------------+--------------+-----------+---------------+
| 10000 | New CMS | 103 | Lennart AG |
| 10010 | Texi2XML | 110 | MySQL AB |
| 10020 | Studyguides | 110 | MySQL AB |
| 10030 | PDC Server | 115 | Icoaten & Co. |
| 10040 | Intranet | 103 | Lennart AG |
| 10050 | Intranet | 101 | Seamen's |
| 10060 | SMB Server | 115 | Icoaten & Co. |
| 10070 | WLAN | 115 | Icoaten & Co. |
+------------+--------------+-----------+---------------+
8 rows in set
| | | | Answer 14: | Subquery converted to an INNER JOIN:
mysql> SELECT
-> p.pid AS `Project ID`,
-> p.name AS `Project Name`,
-> c.id AS `Client No`,
-> c.name AS `Client Name`
-> FROM project AS p
-> INNER JOIN client AS c
-> USING (id) /* or: ON p.id = c.id */
-> ;
+------------+--------------+-----------+---------------+
| Project ID | Project Name | Client No | Client Name |
+------------+--------------+-----------+---------------+
| 10050 | Intranet | 101 | Seamen's |
| 10000 | New CMS | 103 | Lennart AG |
| 10040 | Intranet | 103 | Lennart AG |
| 10010 | Texi2XML | 110 | MySQL AB |
| 10020 | Studyguides | 110 | MySQL AB |
| 10030 | PDC Server | 115 | Icoaten & Co. |
| 10060 | SMB Server | 115 | Icoaten & Co. |
| 10070 | WLAN | 115 | Icoaten & Co. |
+------------+--------------+-----------+---------------+
8 rows in set
| | | | Answer 15: | Subquery converted to a LEFT JOIN:
mysql> SELECT
-> p.pid AS `Project ID`,
-> p.name AS `Project Name`,
-> c.id AS `Client No`,
-> c.name AS `Client Name`
-> FROM project AS p
-> LEFT JOIN client AS c
-> USING (id) /* or: ON p.id = c.id */
-> WHERE c.name IS NULL
-> ;
+------------+--------------+-----------+-------------+
| Project ID | Project Name | Client No | Client Name |
+------------+--------------+-----------+-------------+
| 10080 | Intranet | NULL | NULL |
| 10090 | PDC Server | NULL | NULL |
+------------+--------------+-----------+-------------+
2 rows in set
| | | | Answer 16: | Subquery converted to a RIGHT JOIN:
mysql> SELECT
-> p.pid AS `Project ID`,
-> p.name AS `Project Name`,
-> c.id AS `Client No`,
-> c.name AS `Client Name`
-> FROM client AS c
-> RIGHT JOIN project AS p
-> USING (id) /* or: ON c.id = p.id */
-> WHERE c.name IS NULL
-> ;
+------------+--------------+-----------+-------------+
| Project ID | Project Name | Client No | Client Name |
+------------+--------------+-----------+-------------+
| 10080 | Intranet | NULL | NULL |
| 10090 | PDC Server | NULL | NULL |
+------------+--------------+-----------+-------------+
2 rows in set
| | | | Answer 17: | This subquery cannot be converted into an inner join because an inner join will find only matching combinations, not rows that do not match. | | | | Answer 18: | This statement retrieves a list of the countries in South America that have a larger surface area than Paraguay:
mysql> SELECT
-> c1.Name AS 'Country',
-> c2.Name AS 'Other Countries',
-> c2.Continent AS 'Continent',
-> c2.SurfaceArea AS 'Surface Area'
-> FROM Country AS c1
-> INNER JOIN Country AS c2
-> USING (Continent)
-> WHERE c2.SurfaceArea > c1.SurfaceArea
-> AND c1.Name = 'Paraguay'
-> ;
+----------+-----------------+---------------+--------------+
| Country | Other Countries | Continent | Surface Area |
+----------+-----------------+---------------+--------------+
| Paraguay | Argentina | South America | 2780400.00 |
| Paraguay | Bolivia | South America | 1098581.00 |
| Paraguay | Brazil | South America | 8547403.00 |
| Paraguay | Chile | South America | 756626.00 |
| Paraguay | Colombia | South America | 1138914.00 |
| Paraguay | Peru | South America | 1285216.00 |
| Paraguay | Venezuela | South America | 912050.00 |
+----------+-----------------+---------------+--------------+
| | | | Answer 19: | This statement retrieves a list of all countries whose population is greater than or equal to that of Germany:
mysql> SELECT
-> c1.Name AS 'Country',
-> c2.Name AS 'Other Countries',
-> c2.Population AS 'Population'
-> FROM Country AS c1, Country AS c2
-> WHERE c2.Population >= c1.Population
-> AND c1.Name = 'Germany'
-> ORDER BY c2.Population DESC
-> ;
+---------+--------------------+------------+
| Country | Other Countries | Population |
+---------+--------------------+------------+
| Germany | China | 1277558000 |
| Germany | India | 1013662000 |
| Germany | United States | 278357000 |
| Germany | Indonesia | 212107000 |
| Germany | Brazil | 170115000 |
| Germany | Pakistan | 156483000 |
| Germany | Russian Federation | 146934000 |
| Germany | Bangladesh | 129155000 |
| Germany | Japan | 126714000 |
| Germany | Nigeria | 111506000 |
| Germany | Mexico | 98881000 |
| Germany | Germany | 82164700 |
+---------+--------------------+------------+
| | | | Answer 20: | This statement retrieves a list of all countries whose population and surface area are greater than or equal to that of Nepal:
mysql> SELECT
-> c1.Name AS 'Country',
-> c2.Name AS 'Other Countries',
-> LEFT(c2.Region,10) AS 'Region',
-> c2.Population AS 'Population',
-> c2.SurfaceArea AS 'Surface'
-> FROM Country AS c1, Country AS c2
-> WHERE c1.Region = c2.Region
-> AND c2.SurfaceArea >= c1.SurfaceArea
-> AND c2.Population >= c1.Population
-> AND c1.Name = 'Nepal'
-> ORDER BY c2.Population DESC
-> ;
+---------+-----------------+------------+------------+------------+
| Country | Other Countries | Region | Population | Surface |
+---------+-----------------+------------+------------+------------+
| Nepal | India | Southern a | 1013662000 | 3287263.00 |
| Nepal | Pakistan | Southern a | 156483000 | 796095.00 |
| Nepal | Iran | Southern a | 67702000 | 1648195.00 |
| Nepal | Uzbekistan | Southern a | 24318000 | 447400.00 |
| Nepal | Nepal | Southern a | 23930000 | 147181.00 |
+---------+-----------------+------------+------------+------------+
|
|
