human
Schema:
program (Program_ID, Name, Origin, Launch, Owner)
channel (Channel_ID, Name, Owner, Share_in_percent, Rating_in_percent)
broadcast (Channel_ID, Program_ID, Time_of_day)
broadcast_share (Channel_ID, Program_ID, Date, Share_in_percent)
Please answer with SQL.
assistant
Sure, I understand.
human
what are the names of the channels that broadcast in both morning and night?
assistant
SELECT t1.name FROM channel AS t1 JOIN broadcast AS t2 ON t1.channel_id = t2.channel_id WHERE t2.time_of_day = 'Morning' INTERSECT SELECT t1.name FROM channel AS t1 JOIN broadcast AS t2 ON t1.channel_id = t2.channel_id WHERE t2.time_of_day = 'Night'
human
Find the names of the channels that are broadcast in the morning.
assistant
SELECT t1.name FROM channel AS t1 JOIN broadcast AS t2 ON t1.channel_id = t2.channel_id WHERE t2.time_of_day = 'Morning'
human
List all channel names ordered by their rating in percent from big to small.
assistant
SELECT name FROM channel ORDER BY rating_in_percent DESC
human
find the total percentage share of all channels owned by CCTV.
assistant
SELECT sum(Share_in_percent) FROM channel WHERE OWNER = 'CCTV'
human
Find the names of channels that are not owned by CCTV.
assistant
SELECT name FROM channel WHERE OWNER != 'CCTV'
human
List the names of all the channels owned by either CCTV or HBS
assistant
SELECT name FROM channel WHERE OWNER = "CCTV" OR OWNER = "HBS" ;