Car Park Management System: SQL Queries for Ticketing and Parking Data

The assignment requires students to perform a range of SQL query exercises focusing on the management and analysis of car park data. These tasks encompass listing season ticket holder information across different parking blocks, calculating spaces assigned to both seasonal and ordinary cu...Read More

i. List all the details of season ticket holders for each of the seven car park. (1 mark)
Query:
select ParkingBlock.idParkingBlock, AddressParking, idSeasonalCustomer, CustomerName
from (SeasonalCustomers inner join Control_Pillar on
SeasonalCustomers.idControl_Pillar=Control_Pillar.idControl_Pillar) inner join ParkingBlock
on ParkingBlock.idControl_Pillar=Control_Pillar.idControl_Pillar order by
ParkingBlock.idParkingBlock;
Output:
ii. List total number of car parking spaces assigned to season ticket holders and ordinary
customers for each car park. (1 mark)
Query:
select idParkingBlock, (0.1*spaceCount) as SeasonTicketSpace, (0.9*spaceCount) as
OrdinaryTicketSpace from ParkingBlock;
Output:

iii. List the total amount of money (payment) collected by each of the seven car park (from all
payment machines) for a given day. (1 mark) (You may select any day.)
Query:
select PayStation.idParkingBlock, sum(AmountPaid) from PayStation inner join Transaction on
PayStation.idPayStation=Transaction.idPayStation where DateIssued='20160315' group by
(PayStation.idParkingBlock)
Output:

iv. List all records of payments for each payment machines in a car park. ( 0.5 mark) (You may
select any ONE of the sever car parks)
Query:
select PayStation.idPayStation, idTransaction, TimeIssued, DateIssued ,AmountPaid from
PayStation inner join Transaction on PayStation.idPayStation=Transaction.idPayStation where
PayStation.idParkingBlock =1order by PayStation.idPayStation;
Output:
v. List the records of security guard visit for a week for all car parks. ( 0.5 mark) (You may
select any calendar week- Sunday to Saturday.)
Query:
select idVisit, DateVisit, Visit.idSecurityStaff, NameSecurity from Visit inner join SecurityStaff
on Visit.idSecurityStaff=SecurityStaff .idSecurityStaff where DayName(DateVisit )='Saturday'
Output:

vi. List all records of fault tickets for a selected car park for a selected week. (1 mark) (You may
select any calendar week- Sunday to Saturday.)
Query:
select FaultTicketID, FaultTicket.idAttendant, Attendant.NameAttendant from FaultTicket inner
join Attendant on FaultTicket.idAttendant=attendant.idAttendant where
DayName(CheckDate)='Saturday' and idParkingBlock=2;
Output:

vii. List the total number of cars parked in one selected car park on every Saturday in January
2017 sort in descending (ie. List the Saturday with highest first, and the lowest last in the list.
The dates for Saturday in January 2017 are 7, 14, 21, 28) (1 mark)
Query:
Select Parking_Ticket.DateParking, count(*) as CarParkedCount From Parking_Ticketinner join
ParkingBlock on ParkingBlock.idControl_Pillar=Parking_Ticket.idControl_Pillar where
year(Parking_Ticket.DateParking)=2017 and
monthname(Parking_Ticket.DateParking)='January' and dayname(Pa
rking_Ticket.DateParking)='Saturday' and ParkingBlock.idParkingBlock=1 group by
Parking_Ticket.DateParking order by CarParkedCount;
Output:
viii. list the maximum and minimum number of cars parked in each car park by ordinary
customers on a day. (1 mark) (You may select any day, and group by hour, and group by car
park. This query allows you show the time (hour, e.g 1:00pm-2pm) when the car parks have the
maximum of cars, and the time when the car parks has least number of cars)
Query:
Select temp. ParkingBlockID, max(temp. CarParkedCount), min(temp. CarParkedCount) From
(select ParkingBlock. idParkingBlock as ParkingBlockID, Parking_Ticket.DateParking,
count(*) as CarParkedCount From Control_Pillar inner join Parking_Ticket on
Control_Pillar.idControl_Pillar= Parking_Ticket .idControl_Pillar inner join ParkingBlock on
ParkingBlock. idControl_Pillar = Control_Pillar.idControl_Pillar Where parkingType='Ordinary'

group by ParkingBlock. idParkingBlock , Parking_Ticket.DateParking) as temp group by temp.
ParkingBlockID;
Output:
ix. Print the details of one ordinary parking ticket: ticket no., issue date, issue time, issuing
machine. (0.5mark)
Query:
Create view view1 as
Select idTicket, DateParking, TimeParking, Control_Pillar. MachineName from Parking_Ticket
inner join Control_Pillar on Control_Pillar.idControl_Pillar = Parking_Ticket .idControl_Pillar
where idTicket=1;
Output:

x. Print the details of one receipt shows the Council address, address of the car park, VAT
number, date and amount paid. (0.5 mark)
Query:
Create view view2 as
Select AddressCouncil, AddressParking, VAT, DateIssued , AmountPaid From (ParkingBlock
inner join CityCouncil on CityCouncil. idCityCouncil= ParkingBlock.idCityCouncil) inner join
PayStation on PayStation. idParkingBlock= ParkingBlock.idParkingBlock inner join Transaction
on Transaction. idPayStation= PayStation. idPayStation where Transaction. idTransaction=2;
Output: