Database: local_govt_and_lot

CREATE TABLE Customers (
customer_id INTEGER NOT NULL,
customer_details VARCHAR(255),
PRIMARY KEY (customer_id)
);
CREATE TABLE Properties (
property_id INTEGER NOT NULL,
property_type_code CHAR(15) NOT NULL,
property_address VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (property_id)
);
CREATE TABLE Residents (
resident_id INTEGER NOT NULL,
property_id INTEGER NOT NULL,
date_moved_in DATETIME NOT NULL,
date_moved_out DATETIME NOT NULL,
other_details VARCHAR(255),
PRIMARY KEY (resident_id, property_id, date_moved_in),
FOREIGN KEY (property_id) REFERENCES Properties (property_id)
);
CREATE TABLE Organizations (
organization_id INTEGER NOT NULL,
parent_organization_id INTEGER,
organization_details VARCHAR(255),
PRIMARY KEY (organization_id)
);
CREATE TABLE Services (
service_id INTEGER NOT NULL,
organization_id INTEGER NOT NULL,
service_type_code CHAR(15) NOT NULL,
service_details VARCHAR(255),
PRIMARY KEY (service_id),
FOREIGN KEY (organization_id) REFERENCES Organizations (organization_id)
);
CREATE TABLE Residents_Services (
resident_id INTEGER NOT NULL,
service_id INTEGER NOT NULL,
date_moved_in DATETIME,
property_id INTEGER,
date_requested DATETIME,
date_provided DATETIME,
other_details VARCHAR(255),
PRIMARY KEY (resident_id, service_id),
FOREIGN KEY (service_id) REFERENCES Services (service_id),
FOREIGN KEY (resident_id, property_id, date_moved_in) REFERENCES Residents (resident_id,property_id,date_moved_in)
);
CREATE TABLE Things (
thing_id INTEGER NOT NULL,
organization_id INTEGER NOT NULL,
Type_of_Thing_Code CHAR(15) NOT NULL,
service_type_code CHAR(10) NOT NULL,
service_details VARCHAR(255),
PRIMARY KEY (thing_id),
FOREIGN KEY (organization_id) REFERENCES Organizations (organization_id)
);
CREATE TABLE Customer_Events (
Customer_Event_ID INTEGER NOT NULL,
customer_id INTEGER,
date_moved_in DATETIME,
property_id INTEGER,
resident_id INTEGER,
thing_id INTEGER NOT NULL,
PRIMARY KEY (Customer_Event_ID),
FOREIGN KEY (thing_id) REFERENCES Things (thing_id),
FOREIGN KEY (customer_id) REFERENCES Customers (customer_id),
FOREIGN KEY (resident_id, property_id, date_moved_in) REFERENCES Residents (resident_id,property_id,date_moved_in)
);
CREATE TABLE Customer_Event_Notes (
Customer_Event_Note_ID INTEGER NOT NULL,
Customer_Event_ID INTEGER NOT NULL,
service_type_code CHAR(15) NOT NULL,
resident_id INTEGER NOT NULL,
property_id INTEGER NOT NULL,
date_moved_in DATETIME NOT NULL,
PRIMARY KEY (Customer_Event_Note_ID),
FOREIGN KEY (Customer_Event_ID) REFERENCES Customer_Events (Customer_Event_ID)
);
CREATE TABLE Timed_Status_of_Things (
thing_id INTEGER NOT NULL,
Date_and_Date DATETIME NOT NULL,
Status_of_Thing_Code CHAR(15) NOT NULL,
PRIMARY KEY (thing_id, Date_and_Date, Status_of_Thing_Code),
FOREIGN KEY (thing_id) REFERENCES Things (thing_id)
);
CREATE TABLE Timed_Locations_of_Things (
thing_id INTEGER NOT NULL,
Date_and_Time DATETIME NOT NULL,
Location_Code CHAR(15) NOT NULL,
PRIMARY KEY (thing_id, Date_and_Time, Location_Code),
FOREIGN KEY (thing_id) REFERENCES Things (thing_id));

Question: How many residents does each property have? List property id and resident count.
SQL: SELECT T1.property_id ,  count(*) FROM properties AS T1 JOIN residents AS T2 ON T1.property_id  =  T2.property_id GROUP BY T1.property_id

Question: What is the distinct service types that are provided by the organization which has detail 'Denesik and Sons Party'?
SQL: SELECT DISTINCT T1.service_type_code FROM services AS T1 JOIN organizations AS T2 ON T1.organization_id  =  T2.organization_id WHERE T2.organization_details  =  'Denesik and Sons Party'

Question: How many services has each resident requested? List the resident id, details, and the count in descending order of the count.
SQL: SELECT T1.resident_id ,  T1.other_details ,  count(*) FROM Residents AS T1 JOIN Residents_Services AS T2 ON T1.resident_id  =  T2.resident_id GROUP BY T1.resident_id ORDER BY count(*) DESC

Question: What is the maximum number that a certain service is provided? List the service id, details and number.
SQL: SELECT T1.service_id ,  T1.service_details ,  count(*) FROM Services AS T1 JOIN Residents_Services AS T2 ON T1.service_id  =  T2.service_id GROUP BY T1.service_id ORDER BY count(*) DESC LIMIT 1

Question: List the id and type of each thing, and the details of the organization that owns it.
SQL: SELECT T1.thing_id ,  T1.type_of_Thing_Code ,  T2.organization_details FROM Things AS T1 JOIN Organizations AS T2 ON T1.organization_id  =  T2.organization_id

Question: What are the id and details of the customers who have at least 3 events?
SQL: SELECT T1.customer_id ,  T1.customer_details FROM Customers AS T1 JOIN Customer_Events AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id HAVING count(*)  >=  3

Question: What is each customer's move in date, and the corresponding customer id and details?
SQL: SELECT T2.date_moved_in ,  T1.customer_id ,  T1.customer_details FROM Customers AS T1 JOIN Customer_Events AS T2 ON T1.customer_id  =  T2.customer_id

Question: Which events have the number of notes between one and three? List the event id and the property id.
SQL: SELECT T1.Customer_Event_ID  ,  T1.property_id FROM Customer_Events AS T1 JOIN Customer_Event_Notes AS T2 ON T1.Customer_Event_ID  =  T2.Customer_Event_ID GROUP BY T1.customer_event_id HAVING count(*) BETWEEN 1 AND 3

Question: What are the distinct id and type of the thing that has the status 'Close' or has a status record before the date '2017-06-19 02:59:21'
SQL: SELECT DISTINCT T2.thing_id ,  T2.Type_of_Thing_Code FROM Timed_Status_of_Things AS T1 JOIN Things AS T2 ON T1.thing_id  =  T2.thing_id WHERE T1.Status_of_Thing_Code  =  'Close' OR T1.Date_and_Date  <  '2017-06-19 02:59:21'

Question: How many distinct locations have the things with service detail 'Unsatisfied' been located in?
SQL: SELECT count(DISTINCT T2.Location_Code) FROM Things AS T1 JOIN Timed_Locations_of_Things AS T2 ON T1.thing_id  =  T2.thing_id WHERE T1.service_details  =  'Unsatisfied'

Question: How many different status codes of things are there?
SQL: SELECT count(DISTINCT Status_of_Thing_Code) FROM Timed_Status_of_Things

Question: Which organizations are not a parent organization of others? List the organization id.
SQL: SELECT organization_id FROM organizations EXCEPT SELECT parent_organization_id FROM organizations

Question: When is the last day any resident moved in?
SQL: SELECT max(date_moved_in) FROM Residents

Question: What are the resident details containing the substring 'Miss'?
SQL: SELECT other_details FROM Residents WHERE other_details LIKE '%Miss%'

Question: List the customer event id and the corresponding move in date and property id.
SQL: SELECT customer_event_id ,  date_moved_in ,  property_id FROM customer_events

Question: How many customers did not have any event?
SQL: SELECT count(*) FROM customers WHERE customer_id NOT IN ( SELECT customer_id FROM customer_events )

Question: What are the distinct move in dates of the residents?
SQL: SELECT DISTINCT date_moved_in FROM residents