system echo '1. (5 points)'

SELECT firstName,lastName,phone,city,state,zip FROM customer WHERE zip=75012 OR zip=48751;


system echo
system echo '2. (5 points)'

SELECT orderID,productID FROM order_item WHERE quantity BETWEEN 20 AND 30;


system echo
system echo '3. (5 points)'

SELECT orderID,employeeID,orderDate,customerID FROM order_ WHERE EXTRACT(YEAR FROM orderDate)<=2012;


system echo
system echo '4. (5 points)'

SELECT firstName,lastName,phone FROM customer WHERE customerID IN (SELECT customerID FROM order_ WHERE EXTRACT(YEAR FROM orderDate)=2015 AND (EXTRACT(MONTH FROM orderDate)=11 OR EXTRACT(MONTH FROM orderDate)=12));


system echo
system echo '5. (5 points)'

SELECT firstName,lastName,city,state,zip FROM customer WHERE customerID IN (SELECT customerID FROM order_ WHERE employeeID=7);


system echo
system echo '6. (5 points)'

SELECT firstName,lastName FROM customer WHERE customerID IN (SELECT customerID FROM order_ WHERE employeeID IN (SELECT employeeID FROM employee WHERE firstName='Vin'));


system echo
system echo '7. (5 points)'

SELECT COUNT(customerID) FROM order_ WHERE customerID IN (13,10,7);


system echo
system echo '8. (5 points)'

SELECT COUNT(customerID) FROM order_ WHERE customerID=13 AND employeeID IN (SELECT employeeID FROM employee WHERE lastName='Scully');


system echo
system echo '9. (5 points)'

SELECT SUM(quantity) FROM order_item WHERE productID=10;


system echo
system echo '10. (5 points)'

SELECT SUM(quantity) FROM order_item WHERE productID IN (SELECT productID FROM product WHERE productName='One Finger Death Punch');


system echo
system echo '11. (7 points)'

SELECT customer.firstName,customer.lastName,customer.state,order_.orderDate FROM order_ INNER JOIN customer ON order_.customerID=customer.customerID WHERE orderID IN (SELECT orderID FROM order_item WHERE productID IN (SELECT productID FROM product WHERE productName='Overwatch' OR productName='The Elder Scrolls V: Skyrim'));


system echo
system echo '12. (8 points)'

SELECT firstName,lastName,employeeID FROM employee WHERE employeeID IN (SELECT employeeID FROM order_ WHERE orderID IN (SELECT orderID FROM order_item WHERE productID IN (SELECT productID FROM product WHERE productName='Call of Duty: Black Ops III')));


system echo
system echo '13. (10 points)'

INSERT INTO product (productName,productVendor,cost,price) VALUES ('Diablo III','Blizzard Entertainment',10,39.99);
SELECT productName FROM product WHERE SUBSTRING(productVendor,1,1)='A' OR SUBSTRING(productvendor,1,1)='B';


system echo
system echo '14. (10 points)'

UPDATE product SET price=ROUND((price*0.9),2) WHERE productVendor='Blizzard Entertainment';
SELECT productName,cost,price FROM product WHERE productVendor='Blizzard Entertainment';