Ah, makes sense. Finishing off my sql section then I have a week to finish Database design stuff in between packing boxes and cycling to work, the guy who was giving me a lift seems to think it was cool to tell me to go back to China with my slant eyes slut and Amazon are fine with him saying this :glare:
DO include SQL statements as part of your description of the business processes. This scenario provides scope for the use of queries with multiple 'where' criteria, 'and'/'or' constructs, 'like' '%' & 'between' criteria, use of 'group by'. Try to optimize your interaction with the database by making use of nested queries, joins and aggregate values (e.g.' sum', 'avg', 'max').
For the administrator business processes, write SQL that would handle:
- editing and deleting customer details
- adding, updating and deleting properties
- acquiring summary information about orders
a. the total number of bookings placed between any two dates
b. Which properties have the highest occupancy
c. The total revenue from bookings by region
d. Which properties have not been booked over a specified time period
e. How much business is repeat business
- editing and deleting customer details
a. editing
Should it be required to update a user’s card details UPDATE user SET card=123456 WHERE userid=1;
b. deleting
- adding, updating and deleting properties
a. adding
b. updating
c. deleting
Since location address are stored within address table it is necessary to run a query that is joined to prevent orphaned data, the below query will delete locationid 1
DELETE location , address FROM location INNER JOIN address
WHERE location.locationid= address.locationid and location.locationid = '1'
- acquiring summary information about orders
a. the total number of bookings placed between any two dates
select COUNT(orderId)
from rentals
where startDate between '2016-10-01' and '2016-10-31'
b. Which properties have the highest occupancy
SELECT MAX(maxOccupants) AS 'Highest capacity', address, city, postcode, region
FROM location l
LEFT JOIN address a
ON l.locationid=a.locationid;
c. The total revenue from bookings by region
SELECT region, SUM(locationPrice) AS 'Region revenue' FROM rentals r
RIGHT JOIN location l
ON l.locationid=r.locationid
RIGHT JOIN address a
ON a.locationid=r.locationid
GROUP BY region
 
d. Which properties have not been booked over a specified time period
e. How much business is repeat business
SELECT COUNT(*) AS 'Repeat business' FROM rentals r
RIGHT JOIN location l
ON r.locationid=r.locationId
WHERE r.locationId = 1
GROUP BY
userId
HAVING
COUNT(*) > 1
