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 all phones that have word 'Full' in their accreditation types. List the Hardware Model name and Company name.
assistant
SELECT Hardware_Model_name , Company_name FROM phone WHERE Accreditation_type LIKE 'Full';
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
Which model has the least amount of RAM? List the model name and the amount of RAM.
assistant
SELECT Model_name , RAM_MiB FROM chip_model ORDER BY RAM_MiB ASC LIMIT 1;
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
Find the names of the chip models that are not used by any phone with full accreditation type.
assistant
SELECT model_name FROM chip_model EXCEPT SELECT chip_model FROM phone WHERE Accreditation_type = 'Full'
human
Find the details for all chip models.
assistant
SELECT * FROM chip_model ;