CMIS 320

Joins, part II.

Use the product_vendor database to answer these queries.

1. Vendors from whom no products are gotten. Hint: outer join.

1b. Pairs of products that arrived on the same indate. Hint: self join

1c. Pairs of vendors that are in the same state and both have a Y order.



*******************************************************************
Using the world database.
world.sql creates the world database, with City, Country, and
CountryLanguage tables, and load them with data.  Note that MyISAM
tables are created and foreign keys are not defined and so referential
integrity is not enforced but that Country.Code, City.CountryCode, and
CountryLanguage.CountryCode are common values that link the tables
(i.e. City and CountryLanguage are child tables of Country).  Also, Country
and City are linked by Country.Capital and City.ID (Country is a child
table of City but each city parent can have only one child country;
this is a 1-to-1 relationship).

Use meaningful names for calculated or aggregated columns.

Hint: outer joins:
2. List the Cities that are not of any Country. i.e any orphan cities?
    Use City as child, Country as parent.

3. List the CountryLanguages that are not of any Country. i.e any orphan languages?
    Use CountryLanguage as child, Country as parent.

4. List the Countries that have no Cities.  i.e. any childless parent Country?

5. List the Countries that have no CountryLanguages.      ditto


Exercising the 1-to-1 relationship between City and Country:
6. How many Cities are not the capital of a Country.  
   City as parent that has no Country child.

7. List the Countries that have no Capital city. i.e. null capitals.
   Country as child that has no parent City.

8. List the Countries that do not have a valid non-null capital city.  i.e. orphan country 


Bonus:  this is not any kind of join
9. List the country names, (ISO) Code and (Internet TLD) Code2 of
the countries whose code2 is neither the first two, nor the last two,
nor the first and last characters of the Code.