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
What is the owner of the channel that has the highest rating ratio?
assistant
SELECT OWNER FROM channel ORDER BY rating_in_percent DESC LIMIT 1
human
what is the number of different channel owners?
assistant
SELECT count(DISTINCT OWNER) FROM channel
human
find the total percentage share of all channels owned by CCTV.
assistant
SELECT sum(Share_in_percent) FROM channel WHERE OWNER = 'CCTV'
human
What are the names of the channels owned by CCTV or HBS?
assistant
SELECT name FROM channel WHERE OWNER = 'CCTV' OR OWNER = 'HBS'
human
Which channels are not owned by CCTV? Give me the channel names.
assistant
SELECT name FROM channel WHERE OWNER != "CCTV" ;