CT230 Lab 5

http://geminga.it.nuigalway.ie/~gettrick/courses/CT230/labs/lab5.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

We investigate more advanced queries, SQL INSERT and DELETE commands and the use of subqueries. We will work with the same database as last week at

• Open the database with name cars.mdb. 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.

• Enter the following SELECT Query:

  SELECT Surname
  FROM Officer
  WHERE RegNum IN
    (SELECT Officer
     FROM Offence
     WHERE CarReg IS NULL);

  Then from the View menu choose Datasheet View and you should obtain the table

  Surname

  Burke

  This is an example of a subquery. The last three lines (in round brackets) form one query (which is evaluated first). The result of this query is used in the main query in the first 3 lines. This is often also called a nested query. The operation can also be carried out using various JOIN commands, but subqueries are popular since they are often easier for humans to read.

• TO BE HANDED IN
  Write SQL subqueries for each of the following tasks. Hand up both the SQL (nested) subqueries and the resulting table.
  1. Retrieve the Surnames of all Officers who reported Offences on known dates (i.e. where the Offence date was non NULL)
  2. Retrieve the Surnames and Firstnames of all Officers who reported Offences with Codes between 10 and 19
  3. Retrieve the Surnames of all Officers who reported Offences whose Code was one of 19,17,15,13
  4. Retrieve the Surnames and Firstnames of all Officers who reported Offences involving Cars whose owners address (Town) is not Maynooth

• In SQL View create a new query with the following text

  INSERT INTO Officer
  VALUES(319,'Thumb','Tom');
  

  If you view this query under Datasheet View you should see something like

  Expr1000	Expr1001	Expr1002
  319	Thumb	Tom

  Save this Query (choose "Save As" under "File" menu), picking a name yourself. This INSERT Query should insert one extra row in the Officer table. Close the Query, then re-open it by double clicking on the icon. It will warn you that it is about to add to the table: click Yes, and then Yes again. Now open the Officer table separately and check the row has indeed been added. Once again double click on the INSERT Query, and answer Yes to the questions. Does INSERT add the row this time? Why not?

• Form a new Query under SQL View with the following text

  DELETE *
  FROM Officer
  WHERE Officer.Firstname = 'Tom';
  

  Under Datasheet View this should look like

  RegNum	Surname	Firstname
  319	Thumb	Tom

  Save this query, and then re-open it by double clicking. Answer Yes to the questions (ACCESS will warn you it is about to delete records). Check the Officer table to verify the record has been removed. Try to run the same query a second time. Do any deletions occur this time?

• TO BE HANDED IN
  (a) Write an SQL DELETE Query to remove from the Offence table any records which have unknown values in both Offence.Officer AND Offence.CarReg fields. Display the result.
  (b) Lets say we decide to lay off the Officers who did not give the CarReg when they were reporting an Offence (a rather extreme measure!). Write an SQL Query (using DELETE with a SELECT subquery) to remove from the Officer table all officers who reported an Offence without reporting the CarReg. Display the output of this Query.