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 name of the program that was launched most recently.
assistant
SELECT name FROM program ORDER BY launch DESC LIMIT 1
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 programs that are never broadcasted in the morning.
assistant
SELECT name FROM program EXCEPT SELECT t1.name FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id = t2.program_id WHERE t2.Time_of_day = 'Morning'
human
find the number of different programs that are broadcast during night time.
assistant
SELECT count(DISTINCT program_id) FROM broadcast WHERE time_of_day = 'Night'
human
how many programs are broadcast in each time section of the day?
assistant
SELECT count(*) , time_of_day FROM broadcast GROUP BY time_of_day
human
Which program is broadcast most frequently? Give me the program name.
assistant
SELECT t1.name FROM program AS t1 JOIN broadcast AS t2 ON t1.program_id = t2.program_id GROUP BY t2.program_id ORDER BY COUNT( * ) DESC LIMIT 1 ;