I'm stuck on this...

[proggR]

I thought I knew MySQL until I started this assignment and now I know I just plain don't. I have 10 questions and have managed to figure out 6 of them but now I can't figure out the rest.

I normally don't ask for homework help but if anyone could help me out I'd appreciate it. We're given output and have to figure out the input that would make it. Its a fun idea but I'm so confused now I just can't seem to figure out the remaining questions.

 

#5 list a customer name ( last name and first name) with another
   customer name provided both customers live in the same city. 
   Do not show customers paired to themselves, or reversed pairs. (2 marks)

+-------------+-------------+-------------+-------------+
| CustSurname | CustGivname | CustSurname | CustGivname |
+-------------+-------------+-------------+-------------+
| Adams       | Amy         | Baker       | Bill        |
| Adams       | Amy         | Evans       | Ellen       |
| Baker       | Bill        | Evans       | Ellen       |
| Dove        | Dennis      | Grant       | Gail        |
+-------------+-------------+-------------+-------------+
4 rows in set (0.04 sec)

#6 List all Reservation IDs, the room fee (property rate times number of guests times length of stay), a 15% hospitality fee, the total fees due, the reservation amount paid, and the balance owing for each show.  Order the list
to have the largest balance owing first.  (2 marks)


+--------+---------+---------+-----------+-------------+----------+
| ResvID | RoomFee | HospFee | TotalFees | ResvAmtPaid | BalOwing |
+--------+---------+---------+-----------+-------------+----------+
| R10    | 1520.00 |  228.00 |   1748.00 |      760.00 |   988.00 |
| R12    | 1200.00 |  180.00 |   1380.00 |      560.00 |   820.00 |
| R13    |  900.00 |  135.00 |   1035.00 |      420.00 |   615.00 |
| R6     | 1040.00 |  156.00 |   1196.00 |      900.00 |   296.00 |
| R4     | 1500.00 |  225.00 |   1725.00 |     1500.00 |   225.00 |
| R3     |  600.00 |   90.00 |    690.00 |      520.00 |   170.00 |
| R7     |  720.00 |  108.00 |    828.00 |      720.00 |   108.00 |
| R8     |  576.00 |   86.40 |    662.40 |      576.00 |    86.40 |
| R9     |  520.00 |   78.00 |    598.00 |      520.00 |    78.00 |
| R5     |  420.00 |   63.00 |    483.00 |      420.00 |    63.00 |
| R1     |  420.00 |   63.00 |    483.00 |      420.00 |    63.00 |
| R11    |  384.00 |   57.60 |    441.60 |      384.00 |    57.60 |
| R2     |  180.00 |   27.00 |    207.00 |      360.00 |  -153.00 |
+--------+---------+---------+-----------+-------------+----------+
13 rows in set (0.04 sec)

#9 List customer names ( last name and first name) for customers who have
   more than two reservations or has a reservation at the Green Farm.
   (2 marks)

+-------------+-------------+
| CustSurname | CustGivname |
+-------------+-------------+
| Adams       | Amy         |
| Baker       | Bill        |
| Franks      | Fern        |
+-------------+-------------+
3 rows in set (0.17 sec)


#10 To help planning, create the SQL statement that will print the number 
    of reservations that end ( start date plus length ) on a weekday, and
    print the number that end on a weekend. (2 marks)


+---------+---------+
| Weekend | Weekday |
+---------+---------+
|       1 |      12 |
+---------+---------+
1 row in set (0.05 sec)

 

The tables we're working with are:

 

Reservation
+--------+------------+------------+-------------+------------+----------+----------+
| ResvID | ResvStart  | ResvLength | ResvAmtPaid | ResvGuests | FKPropID | FKCustID |
+--------+------------+------------+-------------+------------+----------+----------+
| R2     | 2008-07-11 |          2 |      360.00 |          2 | P2       | C4       |
| R3     | 2008-07-14 |          2 |      520.00 |          4 | P5       | C5       |
| R4     | 2008-07-19 |          5 |     1500.00 |          4 | P8       | C2       |
| R5     | 2008-07-10 |          6 |      420.00 |          2 | P1       | C3       |
| R6     | 2008-07-05 |          4 |      900.00 |          4 | P3       | C6       |
| R7     | 2008-07-17 |          4 |      720.00 |          4 | P2       | C1       |
| R8     | 2008-07-05 |          6 |      576.00 |          2 | P4       | C4       |
| R9     | 2008-07-21 |          2 |      520.00 |          4 | P3       | C1       |
| R10    | 2008-07-05 |          4 |      760.00 |          4 | P6       | C2       |
| R11    | 2008-07-16 |          2 |      384.00 |          4 | P4       | C5       |
| R1     | 2008-07-14 |          3 |      420.00 |          4 | P1       | C1       |
| R12    | 2008-07-10 |          4 |      560.00 |          4 | P5       | C6       |
| R13    | 2008-07-14 |          3 |      420.00 |          4 | P5       | C2       |
+--------+------------+------------+-------------+------------+----------+----------+
13 rows in set (0.00 sec)

Customer
+--------+-------------+-------------+----------+------------+
| CustID | CustSurname | CustGivname | CustCity | CustPhone  |
+--------+-------------+-------------+----------+------------+
| C1     | Adams       | Amy         | Toronto  | 4165551212 |
| C2     | Baker       | Bill        | Toronto  | 9057771212 |
| C3     | Cook        | Chuck       | Westport | 6133219876 |
| C4     | Dove        | Dennis      | Ottawa   | 6137893322 |
| C5     | Evans       | Ellen       | Toronto  | 4167774432 |
| C6     | Franks      | Fern        | Kiosk    | 7059876543 |
| C7     | Grant       | Gail        | Ottawa   | 6139873254 |
+--------+-------------+-------------+----------+------------+
7 rows in set (0.00 sec)

Property
+--------+--------------+-----------+----------+----------+----------+
| PropID | PropName     | PropCity  | PropRate | PropBeds | PropType |
+--------+--------------+-----------+----------+----------+----------+
| P1     | Echo Lake    | Timmins   |    35.00 |        4 | Cottage  |
| P2     | Fish Heaven  | Upsala    |    45.00 |       12 | Resort   |
| P3     | Green Farm   | Tweed     |    65.00 |        4 | BB       |
| P4     | Rustic Retrt | Schutt    |    48.00 |        4 | Cabin    |
| P5     | Toms Place   | North Bay |    75.00 |       16 | Cottage  |
| P6     | Maries House | Stratford |    95.00 |        4 | BB       |
| P7     | Mitchel Ldng | Westport  |    35.00 |        6 | Cabin    |
| P8     | Pine Lodge   | Kiosk     |    75.00 |       22 | Resort   |
| P9     | SL Salon     | Kingston  |    10.00 |        6 | BB       |
+--------+--------------+-----------+----------+----------+----------+
9 rows in set (0.00 sec)

 

Thanks again for any help/answers. I'm going to keep working away at these and I'll check back later.

Thanks in advance.

[proggR]

I understand if nobody wants to do my homework but could someone lend a hand with the functions and with the logic of the queries perhaps?

I'm working on number 9 right now and so far have:

SELECT CustSurname, CustGivname,COUNT(FKCustID) AS Total FROM Customer, Reservation HAVING Total > 2;

which returns:

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

| CustSurname | CustGivname | Total |

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

| Adams      | Amy        |    91 |

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

I'm not sure how its getting 91. I'm sure I'm not understanding the Count function.

Any help would be great. I really have no idea what I'm doing.

[proggR]

For number 10 I have:

 

mysql> SELECT COUNT(*) AS Weekday FROM Reservation WHERE WEEKDAY(ADDDATE(ResvStart,INTERVAL ResvLength DAY)) < 6 UNION SELECT COUNT(*) AS Weekend  FROM Reservation WHERE WEEKDAY(ADDDATE(ResvStart,INTE
RVAL ResvLength DAY)) = 6
    -> ;
+---------+
| Weekday |
+---------+
|      12 |
|       1 |
+---------+
2 rows in set (0.01 sec)

 

I don't know why it isn't showing as two columns. maybe union but how else can I do two select statements?

[fenway]

I understand if nobody wants to do my homework but could someone lend a hand with the functions and with the logic of the queries perhaps?

I'm working on number 9 right now and so far have:

SELECT CustSurname, CustGivname,COUNT(FKCustID) AS Total FROM Customer, Reservation HAVING Total > 2;

which returns:

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

| CustSurname | CustGivname | Total |

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

| Adams      | Amy        |    91 |

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

I'm not sure how its getting 91. I'm sure I'm not understanding the Count function.

Any help would be great. I really have no idea what I'm doing.

 

You don't have a join condition.