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.
Highlight the table cars and click
on Add. Then click on Close. We will
now do a SELECT on the table cars. In
Design View, double click on Address so it
gets added to the Field: box below. Now from View in the
menu on the top title bar, choose SQL View. You should
see
SELECT cars.Address
FROM cars;
Now from the Query menu at the top choose Run.
You should get the following table:
Save the previous Query. Now re-open it and choose SQL View.
Edit the code to get
SELECT *
FROM cars;
and Run this code as before. Since *
is the wildcard operator, we
get all the fields from the cars table, i.e. the complete
table. Now open this Query under Design View to see the
correspondence.
Under SQL View type
SELECT *
FROM cars,officer;
and Run this Query. Note that the output is the same as the
Cartesian Product cars X vans since we are selecting
all attributes from both tables.
TO BE HANDED IN
Develop BOTH Queries in SQL View and the result (table)
from running the Query corresponding to the following:
-
List the First Names of all Officers who registered an Offence with
Code 18.
-
List the Owners and Addresses of Cars that committed Offences that
were NOT registered by the Officer with surname White.
-
List the Owners of all cars that committed offences on the fourth
of April, 1998.
TO BE HANDED IN
Write down SQL code that carries out the same operation as the
following Relational Algebra Expressions:
-
sAddress='Clonmel'(Cars).
-
pRegNum,Owner
sAddress='Clonmel'(Cars).
-
sOfficer.Surname='Burke' OR
Officer.Firstname='John'
p
Officer.Surname,Officer.Firstname,Cars.Owner(Cars X Officer).