human
Schema:
chip_model (Model_name, Launch_year, RAM_MiB, ROM_MiB, Slots, WiFi, Bluetooth)
screen_mode (Graphics_mode, Char_cells, Pixels, Hardware_colours, used_kb, map, Type)
phone (Company_name, Hardware_Model_name, Accreditation_type, Accreditation_level, Date, chip_model, screen_mode)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the average ram mib size of the chip models that are never used by any phone.
assistant
SELECT avg(RAM_MiB) FROM chip_model WHERE model_name NOT IN (SELECT chip_model FROM phone)
human
List the hardware model name and company name for all the phones that were launched in year 2002 or have RAM size greater than 32.
assistant
SELECT T2.Hardware_Model_name , T2.Company_name FROM chip_model AS T1 JOIN phone AS T2 ON T1.Model_name = T2.chip_model WHERE T1.Launch_year = 2002 OR T1.RAM_MiB > 32;
human
List the hardware model name for the phones that were produced by 'Nokia Corporation' or whose screen mode type is 'Graphics.'
assistant
SELECT DISTINCT T2.Hardware_Model_name FROM screen_mode AS T1 JOIN phone AS T2 ON T1.Graphics_mode = T2.screen_mode WHERE T1.Type = 'Graphics' OR t2.Company_name = 'Nokia Corporation'
human
How many phone hardware models are produced by the company named 'Nokia Corporation'?
assistant
SELECT count(*) FROM phone WHERE Company_name = 'Nokia Corporation';
human
What is maximum and minimum RAM size of phone produced by company named 'Nokia Corporation'?
assistant
SELECT max(T1.RAM_MiB) , min(T1.RAM_MiB) FROM chip_model AS T1 JOIN phone AS T2 ON T1.Model_name = T2.chip_model WHERE T2.Company_name = 'Nokia Corporation';
human
What is the average ROM size of phones produced by the company named 'Nokia Corporation'?
assistant
SELECT avg ( T1.ROM_MiB ) FROM chip_model AS T1 JOIN phone AS T2 ON T1.Model_name = T2.chip_model WHERE T2.Company_name = "Nokia Corporation" ;