Examples of SQL Queries

The following are examples of queries designed to return specific subsets of data. These examples can be copied into the Custom Query window and modified as required. For more guidance on using the SQL query language, visit the Microsoft website.
 

  1. This query returns all records in the mux table.


    select * from mux
     
  2. This query returns the following fields from the mux table: sensorname, location_[x,y,z], for the sensor whose sensorid field equals 1.


    select sensorname, location_x, location_y, location_z
    from mux
    where sensorid=1
     
  3. This query returns all temperature data for January 1, 2000.


    select * from static_data
    where convert(varchar,date_time,111)='2000/01/01'
    and sensorid in
    (select sensorid from mux where sensortype='temperature')
    order by sensorid
     
  4. This query returns all dynamic data for the event whose event number (eventno) is 6.


    select * from dynamic_data
    where eventno=6
     
  5. This query returns all dynamic data for February 14, 2000.


    select * from dynamic_data
    where year(spureftime)=2000
    and month(spureftime)=2
    and day(spureftime)=14
     
  6. This query returns only the peak values of concrete strain gage responses from February 14, 2000, and is further restricted to those events for which the test vehicle was positioned along wander track no. 1:


    select eventno, sensorid, peak1 
    from dynamic_data 
    where year(spureftime)=2000 
    and month(spureftime)=2 
    and day(spureftime)=14
    and sensorid in
    (select sensorid from spus where sensortype='Concrete Strain')
    and eventno in
    (select eventno from traffic where
    trackno=1)
    order by eventno

    Last Update: 01/21/03