Q1. What customers have made RECENT purchases?

A simple purchase question.

Query

The query throws in the purchase history of the customer.

SELECT  C.CustomerID, C.Email, A.History

FROM    Customer_QVC C, Account_QVC A

WHERE   C.CustomerID = A.AccountNumber

HAVING  A.History = ‘Recent Purchases’;

Resultset

Only one customer has made a recent  purchase.

+------------+--------------------+------------------+

| CustomerID | Email              | History          |

+------------+--------------------+------------------+

|          2 | lgriffin@gmail.com | Recent Purchases |

+------------+--------------------+------------------+

1 row in set (0.00 sec)

Q2. For each customer, which customer has a QCard Balance of over $75?

Given that Swapaholics.com is all about swaps, it stands to reason that someone would want to know which items are currently being swapped.

Query

The query throws in the QCard balance on the QCard of the customer.

SELECT  C.CustomerID, C.Email, Q.Balance

FROM    Customer_QVC C, QCard_QVC Q

WHERE   C.CustomerID = Q.QCardNumber

HAVING  Q.Balance > 75;

Resultset

The Griffins have balances on their QCards that are over $75

+------------+----------------------+---------+

| CustomerID | Email                | Balance |

+------------+----------------------+---------+

|          1 | pgriffin@verizon.net |     100 |

|          2 | lgriffin@gmail.com   |     500 |

+------------+----------------------+---------+

2 rows in set (0.00 sec)

Q3. What Products were made in the USA and have an arrival date earlier than January 1, 2011?

This query should be interesting.  This could be very important for any business for whatever reason.

Query

I have selected everything to show that the ProductID and the OrderID do match

SELECT *

FROM    Order_QVC O, Product_QVC P

WHERE   O.ArrivalDate < ‘2011-01-01’ AND P.ProductID = O.OrderID

HAVING  P.Description = ‘Made In USA’;

Resultset

We see that there is only one result.

+---------+--------------+-------------+-----------+------------------+------+-------------+

| OrderID | ShippingDate | ArrivalDate | ProductID | Name             | Cost | Description |

+---------+--------------+-------------+-----------+------------------+------+-------------+

|       2 | 2010-05-10   | 2010-07-10  |         2 | Diamond Necklace |  299 | Made In USA |

+---------+--------------+-------------+-----------+------------------+------+-------------+

1 row in set (0.00 sec)

Q4. Which Customers live in the United States, have NO RECENT Purchases, and have a QCard Balance of greater than $75?

QVC would like to know where there customers are located and if they have or have NOT made a recent purchase to think of different business strategies to make customers have more recent purchases.

Query

These quesies will be joined with a comma.  We could  also use the reserved word JOIN.

SELECT *

FROM    Customer_QVC C, QCard_QVC Q, Account_QVC A

WHERE   C.Country = ‘United States’ AND Q.Balance < 75 AND C.CustomerID = Q.QCardNumber AND Q.QCardNumber = A.AccountNumber

HAVING  A.History = ‘No Recent Purchases’;

Resultset

As you can see, only Homer Simpson answers the question.

+--------------------+---------------+---------+---------------------+

| Email              | Country       | Balance | History             |

+--------------------+---------------+---------+---------------------+

| hsimpson@yahoo.com | United States |      50 | No Recent Purchases |

+--------------------+---------------+---------+---------------------+

1 row in set (0.00 sec)

Q5. Count the number of Customers that are in each country?

This is another simple Query

Query

This one needs to use COUNT(*)

SELECT  Country, Count(*)

FROM    Customer_QVC

GROUP BY Country;

Resultset

This would be a good thing to know especially if you want to draw in more customers from a particular county.

+---------------+----------+

| Country       | Count(*) |

+---------------+----------+

| Jamaica       |        1 |

| United States |        3 |

+---------------+----------+

2 rows in set (0.00 sec)

Q6. What is the maximum QCard balance?

For this query, we need to use MAX(Balance)

Query

Very simple query.

SELECT  MAX(Balance)

FROM    QCard_QVC;

 

Resultset

This is good information to know.

+--------------+

| MAX(Balance) |

+--------------+

|          500 |

+--------------+

1 row in set (0.00 sec)

 

Q7. What products have been ordered?

For this query, we use IN

Query

SELECT P.Name, P.Cost

FROM Product_QVC P

WHERE ProductId

IN (

SELECT OrderID

FROM Order_QVC

);

Resultset

This is very good information to know for many reasons.  Maybe those products will be promoted

+------------------+------+

| Name             | Cost |

+------------------+------+

| Glasses Case     |    5 |

| Diamond Necklace |  299 |

| Toy Car          |   12 |

+------------------+------+

3 rows in set (0.01 sec)

Q8. What is the revenue for this month?

For this query, we need to use Sum(P.Cost)

Query

Simple IN query.

SELECT SUM(P.Cost) AS cost_total

FROM Product_QVC P

WHERE ProductId

IN (

SELECT OrderID

FROM Order_QVC

);

Resultset

This is good information to know.

+------------+

| cost_total |

+------------+

|        316 |

+------------+

1 row in set (0.01 sec)