2023年11月25日

数据库代写｜CEGE0052 – Preparation for SQL Quiz 1

本次英国代写是一个数据库相关的quiz

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
building_condition table

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
order

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
and
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-
10-20_and-2020-11-25