/*
Question 8: Which items (note items, not parts) have been delivered by a supplier called Fisher-Price? Formulate without a subquery.
*/

select jbitem.name from jbitem, jbsupplier where jbitem.supplier=jbsupplier.id and jbsupplier.name='Fisher-Price';

/*
    +-----------------+
	| name            |
	+-----------------+
	| Maze            |
	| The 'Feel' Book |
	| Squeeze Ball    |
	+-----------------+

Selects the item name and shows the names for where supplier from item and id from supplier matches has correct supplier name.
*/


/*
Question 9: Show all cities that have suppliers located in them. Formulate this query using a subquery in the where-clause.
*/

select name from jbcity where id IN (SELECT city from jbsupplier);

/*
    +----------------+
	| name           |
	+----------------+
	| Amherst        |
	| Boston         |
	| New York       |
	| White Plains   |
	| Hickville      |
	| Atlanta        |
	| Madison        |
	| Paxton         |
	| Dallas         |
	| Denver         |
	| Salt Lake City |
	| Los Angeles    |
	| San Diego      |
	| San Francisco  |
	| Seattle        |
	+----------------+

	Select name from city database and shows cities where city id exists as city name in supplier
*/


/*
Question 10: What is the name and color of the parts that are heavier than a card reader? 
Formulate this query using a subquery in the where-clause. (The SQL query must not contain the weight as a constant.)
*/

select name, color from jbparts where weight > (Select weight from jbparts where name = 'card reader');

/*
    +--------------+--------+
	| name         | color  |
	+--------------+--------+
	| disk drive   | black  |
	| tape drive   | black  |
	| line printer | yellow |
	| card punch   | gray   |
	+--------------+--------+
	
Select names and color from parts db and conditionals weight on where weight is bigger than what it is for the card reader column
*/