mercury.txt file

-- Create a table with a CTemp column

CREATE TABLE climate_EU
(       
        location         char(32),
        temperature      CTemp,
        date             date    
);

-- Add some rows

INSERT INTO climate_EU
VALUES
(
        'Lisbon',
        0,
        '01/01/1990'
);


INSERT INTO climate_EU
VALUES
(
        'Zurich',
        -10,
        '01/01/1990'
);



INSERT INTO climate_EU
VALUES
(
        'London',
        10,
        '01/01/1990'
);

-- Create a table with an FTemp column

CREATE TABLE climate_USA
(       
        location        char(32),
        temperature     FTemp,
        date            date    
);

-- Add some rows

INSERT INTO climate_USA
VALUES
(
        'San Francisco',
        32.0,
        '01/01/1990'
);

INSERT INTO climate_USA
VALUES
(
        'Chicago',
        30,
        '01/01/1990'
);

INSERT INTO climate_USA
VALUES
(
        'New York',
        25,
        '01/01/1990'
);


-- Find the European and U.S. cities that have the same temperature
-- on the same date (the casting between temperature scales is
-- performed automatically): 

        select climate_EU.location, climate_USA.location 
                from climate_EU, climate_USA 
                where climate_USA.temperature = climate_EU.temperature
                and climate_USA.date = climate_EU.date;

-- Find the average temperature in London and New York, expressed in 
-- degrees Fahrenheit: 

        select climate_USA.date, (climate_USA.temperature + 
                climate_EU.temperature) / 2
                from climate_EU, climate_USA 
                where climate_EU.location = 'London' 
                and climate_USA.location = 'New York'
                and climate_USA.date = climate_EU.date;

-- Try to add a value that is below absolute zero:

        insert into climate_USA
        values  (
                'San Francisco',
                -1000,
                '01/01/1990'
                );

-- If you run this query in SQL Editor, it fails, but does not produce a 
-- meaningful error message. If you run this query in DB-Access, the error 
-- message you defined in the Mercury DataBlade module appears.