Connor: Created page with "

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 ch..."

2012-07-19T14:56:56Z

Created page with "<h1>Congestion Charges: hard questions</h1> <p>11 and 12 concern charging days. Saturday and Sunday are not charging days. In addition the following public holidays are not ch..."

New page

<h1>Congestion Charges: hard questions</h1>
<p>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'''.</p>

<div class='qu'>
When creating a view in scott you must specify the schema name of the sources and the destination.
<source lang='sql' class='def'>
DROP VIEW scott.high_value;
CREATE VIEW scott.high_value AS
SELECT * FROM gisq.tprod
WHERE pric>50;

SELECT * FROM scott.high_value
</source>
</div>

<div class='qu'>
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.
<source lang='sql' class='def'>
</source>
</div>

<div class='qu'>
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]]
<source lang='sql' class='def'>
</source>
</div>

<div class='qu'>
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
<source lang='sql' class='def'>
</source>
</div>

<div class='qu'>
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.
<source lang='sql' class='def'>
</source>
</div>

<div class='qu'>
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.
<source lang='sql' class='def'>
</source>
</div>

Congestion Hard2 - Revision history

Connor: Created page with "

Congestion Charges: hard questions