Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

bin/mysqldump –p -t –-fields-terminated –by=, Meet_A_Geek Orders >> Report.txt

Day 9

1. Translate the following into SQL statements:
   
   
   1. View all the records of customers from the Customers table who have
      placed an order.
   
   
   
   

SELECT * FROM Customers WHERE Customer_ID = Orders.Customer_ID

2. View all shippers that have been used in the past month.

SELECT Shipper_Name FROM Shipper as S, Orders as O

WHERE O.Order_Date >= '2000-01-01'

AND O.Order_Date <= '2001-01-31'

AND O.Shipper_ID = S.Shipper_ID

3. View all customers.

SELECT * FROM Customers

2. Create an SQL statement that accomplishes the following:
   Add the following information to the Customers table:

First Name: Brent

Last Name: Jacobs

Address: 123 N. Hampton Lane

INSERT INTO Customers (First_Name, Last_Name, Address1)

VALUES('Brent', 'Jacobs', '123 N. Hampton Lane')

3. Change the name of the last record you added from "Jerry" to "Sam."


4. UPDATE Customers SET First_Name = 'Sam'
   WHERE Customer_ID = LAST_INSERT_ID()


5. Delete all records from the Customers table whose first name is Jerry.
   DELETE FROM Customers WHERE First_Name = 'Jerry'


6. Create an SQL statement that returns the number of customers whose names begin
   with M.


7. SELECT COUNT(Customer_ID) as Cust_Count


8. FROM Customers
   WHERE First_Name LIKE "M%"


9. Create an SQL statement that returns the average number of products each
   customer purchases. (Hint: use the quantity column.)
   SELECT AVG(Quantity) FROM Orders


10. Create an SQL statement that returns all the customers who have placed an order in
    the past month, sort it in ascending alphabetic order, and group the records by date.


11. SELECT C.* FROM Orders as O, Customers as C


12. WHERE O.Order_Date >='2000-01-01'


13. AND O.Order_Date <= '2000-01-31'


14. AND C.Customer_ID = O.Customer_ID


15. ORDER BY C.Last_Name
    GROUP BY O.Order_Date

Day 10

1. Use some of the functions you learned today in queries using the Meet_A_Geek
   database.

The following are some examples:

SELECT SUM(Amount_Paid) AS Total FROM Transactions;

SELECT MAX(Customer_ID) AS Top FROM Customers;