MySQL Certification Practice Questions

Question

i am wondering how should i assume what these questions are actually asking?

part of the question

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.

answer

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 (cid) /* or: ON c.cid = p.cid */
->  WHERE p.start IS NOT NULL
->  AND p.end IS NOT NULL
->  ORDER BY c.name ASC, p.start ASC
-> ;

i think according to the answer, the question should be phrased as "projects that dont have a start and end date" rather than "... and projects that ... " that seem to imply exclude projects that dont have a start or end date? or isit only me? UPDATE:

mysql> select c.name, p.name, p.start, p.end
    -> from client c left join project p
    -> using (cid);
+------------+--------------+------------+------------+
| name       | name         | start      | end        |
+------------+--------------+------------+------------+
| Seamen's   | Intranet     | 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 |
| MySQL AB   | Study Guides | 2002-09-00 | 2003-03-30 |
| Icoaten    | PDC Server   | 2003-01-00 | 2003-01-00 |
| Icoaten    | SMB Server   | 2003-05-00 | NULL       |
| Icoaten    | WLAN         | NULL       | 2003-08-00 |
| Nittboad   | NULL         | NULL       | NULL       |
+------------+--------------+------------+------------+
9 rows in set (0.04 sec)

mysql> select c.name, p.name, p.start, p.end
    -> from client c left join project p
    -> using (cid)
    -> where p.start is not null
    -> and p.end is not null;
+------------+--------------+------------+------------+
| name       | name         | start      | end        |
+------------+--------------+------------+------------+
| Lennart AG | New CMS      | 2003-01-00 | 2003-05-00 |
| MySQL AB   | Texi2XML     | 2002-04-00 | 2003-09-00 |
| MySQL AB   | Study Guides | 2002-09-00 | 2003-03-30 |
| Icoaten    | PDC Server   | 2003-01-00 | 2003-01-00 |
+------------+--------------+------------+------------+
4 rows in set (0.00 sec)

mysql> select c.name, p.name, p.start, p.end
    -> from client c left join project p
    -> using (cid)
    -> where p.start is not null
    -> or p.end is not null ;
+------------+--------------+------------+------------+
| name       | name         | start      | end        |
+------------+--------------+------------+------------+
| 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 |
| MySQL AB   | Study Guides | 2002-09-00 | 2003-03-30 |
| Icoaten    | PDC Server   | 2003-01-00 | 2003-01-00 |
| Icoaten    | SMB Server   | 2003-05-00 | NULL       |
| Icoaten    | WLAN         | NULL       | 2003-08-00 |
+------------+--------------+------------+------------+
7 rows in set (0.00 sec)

update 3:

"projects that don't have a start and end date" is looking for both fields missing, mathematically: Start IS NULL AND End IS NULL.

"projects that don't have a start date and projects that don't have an end date" is, as you say, really "OR" (Start IS NULL OR End IS NULL). Note: This is equivalent to "NOT (Start IS NOT NULL AND End IS NOT NULL)".

Answer 1

WHERE p.start IS NOT NULL
AND p.end IS NOT NULL

excludes both cases. So projects that don't have a start date are excluded, as are projects that don't have an end date. Of course, projects with neither a start date nor an end date are excluded as well.

Their wording, at least to me, very unambiguously specifies that behavior.

Note, however, that their answer has another problem: A client with one project, but that one project has a missing start or end date (or both), will not be displayed. Oops.

---

edit: try #2

Questions about English are heading towards offtopic, but I read that sentences as:

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.

In other words, as a two-item list. Grammatically identical to:

Clients without projects should be displayed, but Seamen's and MySQL AB should not not be displayed.

Note that clearly means that a client shouldn't be displayed if its name is Seamen's or if its name is MySQL AB, not that it shouldn't be displayed if its name is both!