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
List the names of all programs.
assistant
SELECT name FROM program;
human
Include their broadcast times.
assistant
SELECT t1.name , t2.time_of_day FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id = t2.program_id;
human
Which ones are broadcasted in both morning and night?
assistant
SELECT t1.name FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id = t2.program_id WHERE t2.time_of_day = 'Morning' INTERSECT SELECT t1.name FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id = t2.program_id WHERE t2.time_of_day = 'Night';
human
Who are the owners of these programs?
assistant
SELECT t1.owner FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id = t2.program_id WHERE t2.Time_of_day = "Morning" INTERSECT SELECT t1.owner FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id = t2.program_id WHERE t2.Time_of_day = "Night" ;