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
find the total percentage share of all channels owned by CCTV.
assistant
SELECT sum(Share_in_percent) FROM channel WHERE OWNER = 'CCTV'
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
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 channels that are not owned by CCTV.
assistant
SELECT name 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" ;