6.893 Problem Set 1 1Problem Set 1: SQL1 IntroductionThe purpose of this assignment is to introduce you to the SQL programming language. SQL is a declarative language, or a“relational calculus” in which you specify the data you are interested in in terms of logical expressions.This assignment is designed provide you with some hands on experience writing SQL queries. We will be using thePostgreSQL open source database, which provides a fairly standard implementation of SQL (in reality, there are slight variationsbetween the SQL dialects – especially with respect to some advanced features, built-in functions, and so on – between all majorvendors.) The SQL tutorial provided with the Postgres documentation at http://www.postgresql.org/docs/7.4/static/tutorial-sql.html provides a good introduction to the basic features of SQL; after following this tutorial youshould be able to answer most of the problems in this problem set (the last three questions are a bit tricky). We have set up aPostgres database server for you to use; you will need to download and install the Postgres client (psql) on a local machine –more details are given in Section 3 below.2 Sensor DataIn this problem set, you will write a series of queries using the SELECT statement over a table called expt table of lightand temperature readings collected from three wireless nodes with special sensing hardware. The data represents a day’s worthof light and temperature readings, collected every 30 seconds from each sensor node. Because these sensor that were used arelossy, there are some times when sensor readings are missing. The schema of the sensor data is as follows:result time : timestamp epoch : int nodeid : int light : int temp : int voltage : intWhere the fields are as follows:result time The time the record was inserted into the database.epoch The sensor-node sequence number of the record. Different results from differentsensors with the same epoch number should arrive in the database at about the same time.If they have different times, it can indicate a failure in the software that runs on thesensor – one of the queries below asks you to look for such failures.nodeid The id of the sensor node that produced this reading.There are three sensors in this data set, numbered 1, 2, and 3.light The light reading from the sensor, in raw units relative to the minimum and maximumbrightness the sensor can perceive. This is a 10-bit sensor, with a value of 0 correspondingto darkness 1024 representing maximum brightness. The calib light table maps rawunits into Lux, which is a commonly used measure of brightness.temp The temperature reading from the sensor, in raw units. The calib temp table mapsraw units into degrees Celsius.voltage The voltage on this device, in raw units.The two tables, calib light and calib temp each have two fields :raw : integer calib : integerWhere raw is the raw sensor value and calib is the calibrated value, in Lux (in the case of calib light) or degrees Celsius(in the case of calib temp.)3 Connecting to the DatabaseTo connect to the database, you will need the psql Postgres client. Some recent versions of Linux come with this tool pre-installed, and you can download binaries for many platforms from the Internet; if you are using a package manager such as rpm,6.893 Problem Set 1 2dpkg/apt, or yum, you should install the packages “postgresql-libs” and “postgresql”. We have made a binary version of thepsql tool available for Linux-based Athena machines at http://db.csail.mit.edu/6.893/psql.tar.gz. To useit, download it to your Athena directory, expand the archive (using a command like tar xvzf psql.tar.gz) and followthe instructions in the README file in the postgres client directory. There is also a Windows version of the Postgresclient available, though it is somewhat more involved to install; the easiest way is to install the Cygwin system, which can bedownloaded from http://www.cygwin.com.To connect to the database, type the following (assuming psql is in your Unix path):psql -h hancock.lcs.mit.edu 6.893 studentYou MUST do this from an MIT machine (e.g., with IP Address 18.x.x.x).You should now be able to type SQL queries directly. All queries in Postgres must be terminated with a semi-colon. Forexample, to get a list of all records in the expt table, you would type:SELECT * FROM expt table;You can use the \h and \? commands for help on SQL syntax and Postgres specific commands, respectively. For example, \hSELECT will give the syntax of the SELECT command.4 Questions1. Write a query (using the SELECT statement) that will compute times and ids when any sensor’s light reading was above550. Show both the query and the first few lines of the result.2. Write a query that will compute the average light reading at sensor 1 between 6 PM and 9 PM (inclusive of 6:00:00 PMand 9:00:00 PM). Show the query and the result.3. Write a single query that computes the average temperature and light reading at every sensor between 6 PM and 9 PM,but exclude any sensors whose maximum voltage was greater than 418 during that time period. Show both the query andthe result.4. Write a query that computes the average calibrated temperature readings from sensor 2 during each hour, inclusive,between 6 PM and 9 PM (i.e., your answer should consist of 4 rows of calibrated temperatures.)5. Write a query that computes all the epochs during which the results from sensors 1 and 2 arrived more than 1 secondapart. Show the query and the result. Note that you can use the difference (minus) operator on timestamps in Postgres,and that the string ’1 second’ refers to a period of 1 second.6. Write a query that determines epochs during which one or two of the sensors did not return results. Show your queryand the first few results, sorted in by epoch number. You may wish to use a nested query – that is, a SELECT statementwithin the FROM clause of another SELECT statement.7. Write a query that produces a temperature reading for each of the three sensors during any epoch in which any sensorproduced a reading. If a sensor is missing a value during a given epoch, your result should report the value of this sensoras the most recent previously reported value. If there is no such value (e.g., the first value for a particular sensor ismissing), you should return the special value ’null’. You may wish to read about the CASE and OUTER JOIN SQLstatements.8. Write a query that determines epochs during which all three
View Full Document
Unlocking...