Part 1 – The SELECT statement
1. Write a query to select all the rows and columns from the users table.
2. Write a query to just select the building_id and roof_condition columns from the
3. Write a query to count the number of room condition reports, using num_reports as an alias
for the count column.
4. Write a query to select just the room_id, room_use and room_number columns from the
rooms table, and sort the result first by room_use and then by room_number
5. Write a query to select just the room_id, room_use and room_number columns from the
rooms table, and sort the result first by room_use and then by room_number in descending
Part 2 – The WHERE clause
1. Write a query to select the building condition reports inserted by user_id = 1
2. Write a query to select the window condition reports inserted by user_id = 1 which also have
condition = 4
3. Write a query to select the parameters where the parameter_type is cost or the
parameter_type is energy saving or the parameter name is wndows (be careful – you will
need single straight quotes around any text)
4. Write a query to find parameters that meet the following criteria
a. parameter_type is either cost or safety
b. parameter_name is either temperature_sensor or ethernet_cables
Use brackets around the two statements that are separated with AND so that you have a
WHERE clause in the form ( ) AND ( ).
5. Write a query to find all the rooms where at least one condition indicator is above 2
Part 3 – Basic Analysis – Non-Spatial Operators
1. Write a query to find the average of all the condition values for each row in the buildings
_condition table. Don’t forget to use ::decimal to make sure you get a floating point
number. Use an alias for the average condition avg_bld_condition. The result should have 2
columns – the average condition and the building_id.
2. Write a query to list all the room condition reports submitted between 2020-10-25 and
2020-11-25. our result should list all the columns in the table and also the additional column
that states whether the reports are between these dates, with alias reports_between_2020-
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: firstname.lastname@example.org 微信:itcsdx