SQL Queries for Car Park Management
Added on 2020-05-04
8 Pages752 Words113 Views
|
|
|
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:
End of preview
Want to access all the pages? Upload your documents or become a member.