CT230 Lab 6

http://geminga.it.nuigalway.ie/~gettrick/courses/CT230/labs/lab6.html

• if any of this lab is unclear, raise your hand or go up to ask the TA
• your lab report (hardcopy/printout) should be written during the lab and given to the TA at the end of the lab for grading [make sure your ID & name are on the cover]
• if there is a problem with your computer, try rebooting. if this does not work, go to another computer, and send an e-mail to joe.oconnell@nuigalway.ie to report the problem

This is the last Practical for CT230. We will work with subqueries, UPDATEs DELETEs and some operators from set theory such as UNION. Open the database at

Click on the Queries tab (next to the Tables tab) and then click on New. Open the Query under Design View. In the resulting Show Table window that appears just click on Close. Now from the View menu in the top title bar select SQL View. You should now have a single Select; Query. Delete this. Now for each of the following type in the SQL query given and run it to see the result.

• We've decided the codes in the Offence table are wrong and should be one greater. Try the query

  UPDATE Offence
  SET Code = Code + 1;

  and check the new entries in the Offence table.

• Suppose the Traffic Officers decide to give a once off amnesty to all Cars that are registered, i.e. that are in the Cars table. This could be accomplished with an SQL query of the form

  DELETE FROM Offence
  WHERE CarReg = ANY
    (SELECT RegNum
    FROM Cars);

  Execute this and view the result.

• To look at all the numbers we are using to identify Officers (Officer.RegNum) and Offences (Offence.Code) we can use a UNION:

  SELECT RegNum
  FROM Officer
    UNION
    SELECT Code
    FROM Offence;

  This will give a list of numbers that appear as either Officer.RegNum or Offence.Code. N.B. This is not an example of a subquery: the two select statements here are completely independent (in particular we could interchange them since A UNION B = B UNION A). For subqueries, we cannot in general interchange the (two) statements.

1. output the Codes for Offences in descending date order (i.e. code for most recent offence first)

2. delete from the table of Officers any Officers whose surname comes (alphabetically) after "Conroy"

3. correct an error whereby after January 1st, 1998, Offences that were in fact reported by Officer 567 were recorded instead as having been reported by Officer 513.

4. update all the entries in Offence.Code by multiplying them by 10.

5. output the names of Car Owners and Surnames of Officers in one long list.