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 name of the company that produced more than one phone model.
assistant
SELECT Company_name FROM phone GROUP BY Company_name HAVING count(*) > 1;
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
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 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
Find the number of phones for each accreditation type.
assistant
SELECT Accreditation_type , count(*) FROM phone GROUP BY Accreditation_type
human
Find the accreditation level that more than 3 phones use.
assistant
SELECT Accreditation_level FROM phone GROUP BY Accreditation_level HAVING COUNT( * ) > 3 ;