Database: program_share

CREATE TABLE "program" (
"Program_ID" int,
"Name" text,
"Origin" text,
"Launch" real,
"Owner" text,
PRIMARY KEY ("Program_ID")
);
CREATE TABLE "channel" (
"Channel_ID" int,
"Name" text,
"Owner" text,
"Share_in_percent" real,
"Rating_in_percent" real,
PRIMARY KEY ("Channel_ID")
);
CREATE TABLE "broadcast" (
"Channel_ID" int,
"Program_ID" int,
"Time_of_day" text,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
);
CREATE TABLE "broadcast_share" (
"Channel_ID" int,
"Program_ID" int,
"Date" text,
"Share_in_percent" real,
PRIMARY KEY ("Channel_ID","Program_ID"),
FOREIGN KEY (`Channel_ID`) REFERENCES `channel`(`Channel_ID`),
FOREIGN KEY (`Program_ID`) REFERENCES `program`(`Program_ID`)
);

Question: Find the names of channels that are not owned by CCTV.
SQL: SELECT name FROM channel WHERE OWNER != 'CCTV'

Question: Which channels are not owned by CCTV? Give me the channel names.
SQL: SELECT name FROM channel WHERE OWNER != 'CCTV'

Question: List all channel names ordered by their rating in percent from big to small.
SQL: SELECT name FROM channel ORDER BY rating_in_percent DESC

Question: Give me a list of all the channel names sorted by the channel rating in descending order.
SQL: SELECT name FROM channel ORDER BY rating_in_percent DESC

Question: What is the owner of the channel that has the highest rating ratio?
SQL: SELECT OWNER FROM channel ORDER BY rating_in_percent DESC LIMIT 1

Question: Show me the owner of the channel with the highest rating.
SQL: SELECT OWNER FROM channel ORDER BY rating_in_percent DESC LIMIT 1

Question: how many programs are there?
SQL: SELECT count(*) FROM program

Question: Count the number of programs.
SQL: SELECT count(*) FROM program

Question: list all the names of programs, ordering by launch time.
SQL: SELECT name FROM program ORDER BY launch

Question: What is the list of program names, sorted by the order of launch date?
SQL: SELECT name FROM program ORDER BY launch

Question: List the name, origin and owner of each program.
SQL: SELECT name ,  origin ,  OWNER FROM program

Question: What are the name, origin and owner of each program?
SQL: SELECT name ,  origin ,  OWNER FROM program

Question: find the name of the program that was launched most recently.
SQL: SELECT name FROM program ORDER BY launch DESC LIMIT 1

Question: Which program was launched most recently? Return the program name.
SQL: SELECT name FROM program ORDER BY launch DESC LIMIT 1

Question: find the total percentage share of all channels owned by CCTV.
SQL: SELECT sum(Share_in_percent) FROM channel WHERE OWNER  =  'CCTV'

Question: What is the total share (in percent) of all the channels owned by CCTV?
SQL: SELECT sum(Share_in_percent) FROM channel WHERE OWNER  =  'CCTV'

Question: Find the names of the channels that are broadcast in the morning.
SQL: SELECT t1.name FROM channel AS t1 JOIN broadcast AS t2 ON t1.channel_id  =  t2.channel_id WHERE t2.time_of_day  =  'Morning'

Question: Which channels are broadcast in the morning? Give me the channel names.
SQL: SELECT t1.name FROM channel AS t1 JOIN broadcast AS t2 ON t1.channel_id  =  t2.channel_id WHERE t2.time_of_day  =  'Morning'

Question: what are the names of the channels that broadcast in both morning and night?
SQL: 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'

Question: Which channels broadcast both in the morning and at night? Give me the channel names.
SQL: 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'

Question: how many programs are broadcast in each time section of the day?
SQL: SELECT count(*) ,  time_of_day FROM broadcast GROUP BY time_of_day

Question: Count the number of programs broadcast for each time section of a day.
SQL: SELECT count(*) ,  time_of_day FROM broadcast GROUP BY time_of_day

Question: find the number of different programs that are broadcast during night time.
SQL: SELECT count(DISTINCT program_id) FROM broadcast WHERE time_of_day  =  'Night'

Question: How many distinct programs are broadcast at "Night" time?
SQL: SELECT count(DISTINCT program_id) FROM broadcast WHERE time_of_day  =  'Night'

Question: Find the names of programs that are never broadcasted in the morning.
SQL: 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"

Question: Which programs are never broadcasted in the morning? Give me the names of the programs.
SQL: 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"

Question: find the program owners that have some programs in both morning and night time.
SQL: 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"

Question: Who are the owners of the programs that broadcast both in the morning and at night?
SQL: 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"

Question: List all program origins in the alphabetical order.
SQL: SELECT origin FROM program ORDER BY origin

Question: What is the list of program origins ordered alphabetically?
SQL: SELECT origin FROM program ORDER BY origin

Question: what is the number of different channel owners?
SQL: SELECT count(DISTINCT OWNER) FROM channel

Question: Count the number of distinct channel owners.
SQL: SELECT count(DISTINCT OWNER) FROM channel

Question: find the names of programs whose origin is not in Beijing.
SQL: SELECT name FROM program WHERE origin != 'Beijing'

Question: Which programs' origins are not "Beijing"? Give me the program names.
SQL: SELECT name FROM program WHERE origin != 'Beijing'

Question: What are the names of the channels owned by CCTV or HBS?
SQL: SELECT name FROM channel WHERE OWNER  =  'CCTV' OR OWNER  =  'HBS'

Question: List the names of all the channels owned by either CCTV or HBS
SQL: SELECT name FROM channel WHERE OWNER  =  'CCTV' OR OWNER  =  'HBS'

Question: Find the total rating ratio for each channel owner.
SQL: SELECT sum(Rating_in_percent) ,  OWNER FROM channel GROUP BY OWNER

Question: What is the total rating of channel for each channel owner?
SQL: SELECT sum(Rating_in_percent) ,  OWNER FROM channel GROUP BY OWNER

Question: Find the name of the program that is broadcast most frequently.
SQL: 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

Question: Which program is broadcast most frequently? Give me the program name.
SQL: 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