Congestion Hard2

Congestion Charges: hard questions

11 and 12 concern charging days. Saturday and Sunday are not charging days. In addition the following public holidays are not charging days: March 21st 2003 March 31st 2003 May 1st 2003 May 20th 2003 To answer 11 or 12 you will need to record these non-charging days using a table or view You may need to create views to complete these questions - but you do not have permission to create tables or views in the default schema. Your SQL commands are executed by user scott in schema gisq - you may create or drop views and tables in schema scott but not in gisq.

When creating a view in scott you must specify the schema name of the sources and the destination.

DROP VIEW scott.high_value;
CREATE VIEW scott.high_value AS
SELECT * FROM gisq.tprod
  WHERE pric>50;

SELECT * FROM scott.high_value

Anomalous daily permits. Daily permits should not be issued for non-charging days. Find a way to represent non-charging days. Identify the anomalous daily permits.

Permit duration. You can pay your congestion charge daily, weekly (representing 5 consecutive charging days), monthly (20 consecutive charging days), or annually (252 consecutive charging days). The field lDate is intended to hold the last date for each permit - it is currently null. Create a query to calculate the correct lDate for each permit and change each record. Note that you do not have permission to execute such as query on the share table so you should take your own copy. Demonstrate your answer by showing the start date, chargeType and last last Date for permits with a start date in Jan 2003. A question from Jim

Issue fines. Vehicles using the zone during the charge period, on charging days must be issued with fine notices unless they have a permit covering that day. List the name and address of such culprits, give the camera and the date and time of the first offence. The charging period is from 7am to 6:30pm

Copied plates. Cars may enter and leave the charging zone any number of times however certain patterns of behaviour are regarded as suspicious: Cars which enter the zone twice between 07:00 and 09:00 Cars which enter the zone three times but leave only once We are suspicious that the same registration number plate is on more than one vehicle. Define suspicious behaviour and create a query that will identify suspected registration plates.

Speeders. The fastest legal time from camera 18 to camera 9 is 2 minutes. Car SO 02 RSP did the trip in 1 minute and should be fined for speeding. Find a way to record the fastest legal time between pairs of cameras, make up five such values and identify the speeders.