Home

Project Deliverable #5: Sample DML Queries

These queries were answer business related questions relevant to the operation of the Databeast site.

SELECT Statement Queries

Q1: What is the most expensive card?

This is important to know since it will allow us to make this card more rare, and produce less of it.

Query

SELECT * 
FROM   Card_T 
WHERE  Price = 
       (SELECT MAX(Price) 
        FROM Card_T);

ResultSet

+----------+-------------------+-------+------------+-------------+-----------+
| CardID   | Name              | Price | PowerLevel | HealthLevel | FactoryID |
+----------+-------------------+-------+------------+-------------+-----------+
| 32489878 | Legendary Paladin |   101 |         14 |          11 |         2 | 
+----------+-------------------+-------+------------+-------------+-----------+
1 row in set (0.01 sec)

Q2: Where does one of our trucks park?

This will be useful when we need to identify a unique truck and determine where it is parked so we can reference it later on.

Query

SELECT * 
FROM   Truck_T AS t 
       JOIN Factory_T AS f 
WHERE  t.StorageLocation = f.Location;

ResultSet

+----+-------------------+-----------+-------------------+
| ID | StorageLocation   | FactoryID | Location          |
+----+-------------------+-----------+-------------------+
|  1 | Cheyenne, Wyoming |         2 | Cheyenne, Wyoming | 
+----+-------------------+-----------+-------------------+
1 row in set (0.00 sec)

Q3: Which players have won games?

This will be useful in helping to display rankings for other users; also, it may help us target certain users for different marketing campaigns depending on their skill level.

Query

SELECT * 
FROM   Customer_T AS c 
       JOIN Game_T AS g 
WHERE  g.Winner = c.ID;

ResultSet

+----+-------+----------+--------------+---------------+----------------------------+--------+--------+-----------+-----------+
| ID | First | Last     | PhoneNo      | Email         | Address                    | GameID | Winner | Player1ID | Player2ID |
+----+-------+----------+--------------+---------------+----------------------------+--------+--------+-----------+-----------+
|  1 | Joe   | Deringer | 998-997-4123 | JDer123@g.com | 345 Square Ln, Oakland, CA |      1 |      1 |         1 |         2 | 
+----+-------+----------+--------------+---------------+----------------------------+--------+--------+-----------+-----------+
1 row in set (0.00 sec)

Q4: What Player has the most wins?

This will also have a lot of the same implications as above; however this will also allow us to determine the very strongest player in order to know who the best player of the game is.

Query

SELECT * 
FROM   Customer_T AS c 
       JOIN Player_T AS p 
WHERE  c.ID = p.ID 
       AND p.Wins = 
       (SELECT MAX(Wins) 
       FROM Player_T);

ResultSet

+----+------------+--------+--------------+---------------+-------------------------+---------+------+--------+----+
| ID | First      | Last   | PhoneNo      | Email         | Address                 | Ranking | Wins | Losses | ID |
+----+------------+--------+--------------+---------------+-------------------------+---------+------+--------+----+
|  3 | Shinnosuke | Nohara | 456-986-0876 | oshiri@yo.com | 34 H St Kasukabe, Japan |       1 |    7 |      0 |  3 | 
+----+------------+--------+--------------+---------------+-------------------------+---------+------+--------+----+
1 row in set (0.00 sec)

Q5: Who is the richest Customer (Who has the most money in their account)?

This will help us try to market more expensive cards to those who have more money in their account, so that we can sell more to them.

Query

SELECT * 
FROM   Customer_T AS c 
       JOIN Account_T AS a 
WHERE  c.ID = a.ID 
       AND a.Balance = 
           (SELECT MAX(Balance) 
           FROM Account_T);

ResultSet

+----+------------+--------+--------------+---------------+-------------------------+--------+---------+----+
| ID | First      | Last   | PhoneNo      | Email         | Address                 | Number | Balance | ID |
+----+------------+--------+--------------+---------------+-------------------------+--------+---------+----+
|  3 | Shinnosuke | Nohara | 456-986-0876 | oshiri@yo.com | 34 H St Kasukabe, Japan |      1 |     800 |  3 | 
+----+------------+--------+--------------+---------------+-------------------------+--------+---------+----+
1 row in set (0.00 sec)

Q6: What Card has the highest Power Level?

This is extremely important in determining the strength of our cards and figuring out the upper limit which we want to have our cards reach.

Query

SELECT * 
FROM   Card_T 
WHERE  PowerLevel = 
       (SELECT MAX(PowerLevel) 
       FROM Card_T);

ResultSet

+----------+--------------------+-------+------------+-------------+-----------+
| CardID   | Name               | Price | PowerLevel | HealthLevel | FactoryID |
+----------+--------------------+-------+------------+-------------+-----------+
| 11134555 | Legendary Marksman |    87 |         15 |           5 |         2 | 
+----------+--------------------+-------+------------+-------------+-----------+
1 row in set (0.00 sec)

Changes from the Previous Deliverables

There are no changes to the previous deliverables.
Home