MySQL Chapter 4-Querying Data by Using Joins and Subqueries

use globaltoyz;

/*Exercise 1*/
select t.vToyName, b.cBrandName,c.cCategory from toys as t Join toybrand as b
on t.cBrandId=b.cBrandId JOIN category as c on t.cCategoryId=c.cCategoryId;

/*Exercise 2*/
SELECT toys.vToyName,shoppingcart.cCartId FROM toys 
LEFT OUTER JOIN shoppingcart ON toys.cToyID=shoppingcart.cToyId;/*'join''s left is full*/

/*Exercise 3*/
SELECT shopper.cshopperId, shopper.cState,orders.cOrderNo  FROM orders RIGHT OUTER JOIN 
shopper ON shopper.cShopperId=orders.cShopperId;/*joinis right is full*/

/*Exercise 4*/
SELECT s.cShopperId, s.vFirstName,s.vLastName,count(o.cShopperId) 
FROM shopper as s cross JOIN orders as o on s.cShopperId=o.cShopperId group by s.cshopperid;

/*Exercise 5*/
select o.cOrderNo, o.ctoyId, w.vDescription from orderdetail as o Join wrapper as w
on o.cWrapperId=w.cWrapperId;

/*Exercise 6*/
select t.cToyId,t.vToyName,s.siQty from 
shoppingcart as s inner join toys as t on s.cToyId=t.cToyId 
where cCartId=000001;

/*Exercise 7*/
SELECT * FROM toys RIGHT OUTER JOIN 
category ON toys.cCategoryId=category.cCategoryId;

/*Exercise 8*/
SELECT country.cCountryId, cModeId, mRatePerPound FROM country 
left OUTER JOIN shippingrate ON country.cCountryId=shippingrate.cCountryId;

/*Exercise 9*/
select cShopperId, vFirstName, vLastName from shopper join country on shopper.cCountryId=country.cCountryId 
where cCountry ='United States of America';

/*Exercise 10*/
select r.cCountryId,r.cModeId,m.cMode from shippingrate r Join shippingmode as m
on r.cModeId=m.cModeId;

/*Exercise 11*/
select * from orderdetail where cOrderNo IN (select cOrderNo from orders where dOrderDate='2001/05/20');

/*Exercise 12*/
select * from category WHERE EXISTS (
SELECT * FROM toys WHERE category.cCategoryId=toys.cCategoryId);

/*Exercise 13*/
select vToyName
from toys where cCategoryId IN (select cCategoryId from category where cCategory='Stuffed Toys');

/*Exercise 14*/
select cOrderNo from orders WHERE EXISTS (
SELECT * FROM shipment WHERE orders.cOrderNo=shipment.cOrderNo and cDeliveryStatus='d');

/*Exercise 15*/
SELECT cOrderNo, mTotalCost FROM orders WHERE mTotalCost > (SELECT Avg(mTotalCost) FROM orders);

/*Exercise 16*/
select cToyId,siMonth,iYear,iTotalSold from pickofmonth where iTotalSold>
all (select iTotalSold from pickofmonth where cToyId=0000015);

/*Exercise 17*/
SELECT cBrandName FROM toybrand Where cBrandId IN 
(SELECT cBrandId FROM toys Where 
cToyId IN (SELECT cToyId FROM orders Where cOrderNo=000005));

/*Exercise 18*/
select cOrderNo,dOrderDate, cShopperId from orders where mTotalCost>
all (select mTotalCost from orders where cCartId=000002);

/*Exercise 19*/
SELECT * FROM toys WHERE mToyRate> Any ( SELECT mToyRate from toys where cCategoryId=000002);

/*Exercise 20*/
select * from shoppingcart where cToyId=(select cToyId from toys where vToyName='Robby the whale');