Database schema:
CREATE TABLE Ref_Age_Categories (
  age_category_code TEXT PRIMARY KEY,
  age_category_description TEXT
);
CREATE TABLE Ref_Property_Types (
  property_type_code TEXT PRIMARY KEY,
  property_type_description TEXT
);
CREATE TABLE Ref_Room_Types (
  room_type_code TEXT PRIMARY KEY,
  room_type_description TEXT
);
CREATE TABLE Ref_User_Categories (
  user_category_code TEXT PRIMARY KEY,
  user_category_description TEXT
);
CREATE TABLE Addresses (
  address_id NUMBER PRIMARY KEY,
  line_1_number_building TEXT,
  line_2_number_street TEXT,
  line_3_area_locality TEXT,
  town_city TEXT,
  zip_postcode TEXT,
  county_state_province TEXT,
  country TEXT,
  other_address_details TEXT
);
CREATE TABLE Features (
  feature_id NUMBER PRIMARY KEY,
  feature_name TEXT,
  feature_description TEXT
);
CREATE TABLE Users (
  user_id NUMBER PRIMARY KEY,
  age_category_code TEXT,
  user_category_code TEXT,
  user_address_id NUMBER,
  is_buyer TEXT,
  is_seller TEXT,
  login_name TEXT,
  password TEXT,
  date_registered TIME,
  first_name TEXT,
  middle_name TEXT,
  last_name TEXT,
  other_user_details TEXT
);
CREATE TABLE Properties (
  property_id NUMBER PRIMARY KEY,
  property_address_id NUMBER,
  owner_user_id NUMBER,
  property_type_code TEXT,
  date_on_market TIME,
  date_off_market TIME,
  property_name TEXT,
  property_description TEXT,
  garage_yn TEXT,
  parking_lots TEXT,
  room_count TEXT,
  vendor_requested_price NUMBER,
  price_min NUMBER,
  price_max NUMBER,
  other_property_details TEXT,
  FOREIGN KEY (property_type_code) REFERENCES Ref_Property_Types(property_type_code),
  FOREIGN KEY (property_address_id) REFERENCES Addresses(address_id),
  FOREIGN KEY (owner_user_id) REFERENCES Users(user_id)
);
CREATE TABLE Property_Features (
  property_id NUMBER,
  feature_id NUMBER,
  feature_value TEXT,
  property_feature_description TEXT,
  FOREIGN KEY (property_id) REFERENCES Properties(property_id),
  FOREIGN KEY (feature_id) REFERENCES Features(feature_id)
);
CREATE TABLE Property_Photos (
  property_id NUMBER,
  photo_seq NUMBER,
  photo_title TEXT,
  photo_description TEXT,
  photo_filename TEXT,
  FOREIGN KEY (property_id) REFERENCES Properties(property_id)
);
CREATE TABLE Rooms (
  property_id NUMBER,
  room_number TEXT,
  room_type_code TEXT,
  room_size TEXT,
  other_room_details TEXT,
  FOREIGN KEY (room_type_code) REFERENCES Ref_Room_Types(room_type_code),
  FOREIGN KEY (property_id) REFERENCES Properties(property_id)
);
CREATE TABLE User_Property_History (
  user_id NUMBER,
  property_id NUMBER,
  datestamp TIME,
  FOREIGN KEY (property_id) REFERENCES Properties(property_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
CREATE TABLE User_Searches (
  user_id NUMBER,
  search_seq NUMBER,
  search_datetime TIME,
  search_string TEXT,
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

Question: What are the last names and ids of users who have searched two or fewer times, and own two or more properties?
SQL: SELECT T1.last_name ,  T1.user_id FROM Users AS T1 JOIN User_Searches AS T2 ON T1.user_id  =  T2.user_id GROUP BY T1.user_id HAVING count(*)  <=  2 INTERSECT SELECT T3.last_name ,  T3.user_id FROM Users AS T3 JOIN Properties AS T4 ON T3.user_id  =  T4.owner_user_id GROUP BY T3.user_id HAVING count(*)  >=  2;

Question: Where do the Senior Citizens live? List building, street, and the city.
SQL: SELECT T1.line_1_number_building ,  T1.line_2_number_street ,  T1.town_city FROM Addresses AS T1 JOIN Users AS T2 ON T1.address_id  =  T2.user_address_id WHERE T2.user_category_code  =  'Senior Citizen';

Question: What is the average number of rooms in a property?
SQL: SELECT avg(room_count) FROM Properties;

Question: List the first name, middle name and last name, and log in name of all the seller users, whose seller value is 1.
SQL: SELECT first_name ,  middle_name ,  last_name ,  login_name FROM Users WHERE is_seller  =  1;

Question: Count the number of properties that have 1 parking lot or 1 garage.
SQL: SELECT count(*) FROM Properties WHERE parking_lots  =  1 OR garage_yn  =  1;

Question: What is the first name of the user who owns the greatest number of properties?
SQL: SELECT T1.first_name FROM Users AS T1 JOIN Properties AS T2 ON T2.owner_user_id  =  T1.User_id GROUP BY T1.User_id ORDER BY count(*) DESC LIMIT 1;

Question: Count the number of searches made by buyers.
SQL: SELECT count(*) FROM Users AS T1 JOIN User_Searches AS T2 ON T1.user_id  =  T2.user_id WHERE T1.is_buyer  =  1;

Question: For users whose description contain the string 'Mother', which age categories are they in?
SQL: SELECT T2.age_category_code FROM Ref_User_Categories AS T1 JOIN Users AS T2 ON T1.user_category_code  =  T2.user_category_code WHERE T1.User_category_description LIKE "%Mother";

Question: What are the date stamps and property names for each item of property history, ordered by date stamp?
SQL: SELECT T1.datestamp ,  T2.property_name FROM User_Property_History AS T1 JOIN Properties AS T2 ON T1.property_id  =  T2.property_id ORDER BY datestamp;

Question: How many searches do buyers make in total?
SQL: SELECT count(*) FROM Users AS T1 JOIN User_Searches AS T2 ON T1.user_id  =  T2.user_id WHERE T1.is_buyer  =  1;

Question: What search strings were entered by users who do not own any properties?
SQL: SELECT search_string FROM User_Searches EXCEPT SELECT T1.search_string FROM User_Searches AS T1 JOIN Properties AS T2 ON T1.user_id  =  T2.owner_user_id;

Question: What is the description of the most common property type? List the description and code.
SQL: SELECT T1.property_type_description ,  T1.property_type_code FROM Ref_Property_Types AS T1 JOIN Properties AS T2 ON T1.property_type_code  =  T2.property_type_code GROUP BY T1.property_type_code ORDER BY count(*) DESC LIMIT 1;

Question: Return the age category for the user who made the earliest search.
SQL: SELECT T1.age_category_code FROM Users AS T1 JOIN User_Searches AS T2 ON T1.user_id  =  T2.user_id ORDER BY T2.search_datetime LIMIT 1;

Question: What are the first, middle, last, and login names for all users who are sellers?
SQL: SELECT first_name ,  middle_name ,  last_name ,  login_name FROM Users WHERE is_seller  =  1;

Question: Give the states or provinces corresponding to each property.
SQL: SELECT DISTINCT T1.county_state_province FROM Addresses AS T1 JOIN Properties AS T2 ON T1.address_id  =  T2.property_address_id;

Question: What are the users making only one search? List both category and user id.
SQL: SELECT T1.user_category_code ,  T1.user_id FROM Users AS T1 JOIN User_Searches AS T2 ON T1.user_id  =  T2.user_id GROUP BY T1.user_id HAVING count(*)  =  1;

Question: Return the zip codes for properties not belonging to users who own two or fewer properties.
SQL: SELECT T1.zip_postcode FROM Addresses AS T1 JOIN Properties AS T2 ON T1.address_id  =  T2.property_address_id WHERE T2.owner_user_id NOT IN ( SELECT owner_user_id FROM Properties GROUP BY owner_user_id HAVING count(*)  <=  2 );

Question: What is the total max price of the properties owned by single mothers or students?
SQL: SELECT sum(T1.price_max) FROM Properties AS T1 JOIN Users AS T2 ON T1.owner_user_id  =  T2.user_id WHERE T2.user_category_code  =  'Single Mother' OR T2.user_category_code  =  'Student';

Question: How is the feature rooftop described?
SQL: SELECT feature_description FROM Features WHERE feature_name  =  'rooftop';

Question: What was the registration date for the user whose login name is ratione?
SQL: SELECT date_registered FROM Users WHERE login_name  =  'ratione';

Question: What are all the user searches time and content? Sort the result descending by content.
SQL: SELECT search_datetime ,  search_string FROM User_Searches ORDER BY search_string DESC;