human
Schema:
Customer_Master_Index (master_customer_id, cmi_details)
CMI_Cross_References (cmi_cross_ref_id, master_customer_id, source_system_code)
Council_Tax (council_tax_id, cmi_cross_ref_id)
Business_Rates (business_rates_id, cmi_cross_ref_id)
Benefits_Overpayments (council_tax_id, cmi_cross_ref_id)
Parking_Fines (council_tax_id, cmi_cross_ref_id)
Rent_Arrears (council_tax_id, cmi_cross_ref_id)
Electoral_Register (electoral_register_id, cmi_cross_ref_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Which cmi cross reference id is not related to any parking taxes?
assistant
SELECT cmi_cross_ref_id FROM cmi_cross_references EXCEPT SELECT cmi_cross_ref_id FROM parking_fines
human
How many business rates are related to each cmi cross reference? List cross reference id, master customer id and the n
assistant
SELECT T2.cmi_cross_ref_id , T2.master_customer_id , count(*) FROM Business_Rates AS T1 JOIN CMI_Cross_References AS T2 ON T1.cmi_cross_ref_id = T2.cmi_cross_ref_id GROUP BY T2.cmi_cross_ref_id
human
What are the distinct cross reference source system codes which are related to the master customer details 'Gottlieb, Becker and Wyman'?
assistant
SELECT DISTINCT T2.source_system_code FROM customer_master_index AS T1 JOIN cmi_cross_references AS T2 ON T1.master_customer_id = T2.master_customer_id WHERE T1.cmi_details = 'Gottlieb , Becker AND Wyman'
human
What is the cmi cross reference id that is related to at least one council tax entry? List the cross reference id and source system code.
assistant
SELECT T1.cmi_cross_ref_id , T1.source_system_code FROM CMI_Cross_References AS T1 JOIN Council_Tax AS T2 ON T1.cmi_cross_ref_id = T2.cmi_cross_ref_id GROUP BY T1.cmi_cross_ref_id HAVING count(*) >= 1
human
what are the details of the cmi masters that have the cross reference code 'Tax'?
assistant
SELECT T1.cmi_details FROM Customer_Master_Index AS T1 JOIN CMI_Cross_References AS T2 ON T1.master_customer_id = T2.master_customer_id WHERE T2.source_system_code = 'Tax'
human
How many different source system code for the cmi cross references are there?
assistant
SELECT COUNT( DISTINCT source_system_code ) FROM CMI_cross_references ;