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
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
Find the pixels of the screen modes that are used by both phones with full accreditation types and phones with Provisional accreditation types.
assistant
SELECT t1.pixels FROM screen_mode AS t1 JOIN phone AS t2 ON t1.Graphics_mode = t2.screen_mode WHERE t2.Accreditation_type = 'Provisional' INTERSECT SELECT t1.pixels FROM screen_mode AS t1 JOIN phone AS t2 ON t1.Graphics_mode = t2.screen_mode WHERE t2.Accreditation_type = 'Full'
human
List the hardware model name for the phones that have screen mode type 'Text' or RAM size greater than 32.
assistant
SELECT T2.Hardware_Model_name FROM chip_model AS T1 JOIN phone AS T2 ON T1.Model_name = T2.chip_model JOIN screen_mode AS T3 ON T2.screen_mode = T3.Graphics_mode WHERE T3.Type = 'Text' OR T1.RAM_MiB > 32;
human
List the hardware model name and company name for the phone whose screen mode type is 'Graphics.'
assistant
SELECT T2.Hardware_Model_name , T2.Company_name FROM screen_mode AS T1 JOIN phone AS T2 ON T1.Graphics_mode = T2.screen_mode WHERE T1.Type = 'Graphics';
human
List the phone hardware model and company name for the phones whose screen usage in kb is between 10 and 15.
assistant
SELECT DISTINCT T2.Hardware_Model_name , T2.Company_name FROM screen_mode AS T1 JOIN phone AS T2 ON T1.Graphics_mode = T2.screen_mode WHERE T1.used_kb BETWEEN 10 AND 15;
human
List the maximum, minimum and average number of used kb in screen mode.
assistant
SELECT MAX( used_kb ) , MIN( used_kb ) , avg ( used_kb ) FROM screen_mode ;