Database: tvshow
Schema: CREATE TABLE IF NOT EXISTS "TV_Channel" ( "id" text, "series_name" text, "Country" text, "Language" text, "Content" text, "Pixel_aspect_ratio_PAR" text, "Hight_definition_TV" text, "Pay_per_view_PPV" text, "Package_Option" text, PRIMARY KEY ("id") ); CREATE TABLE IF NOT EXISTS "TV_series" ( "id" real, "Episode" text, "Air_Date" text, "Rating" text, "Share" real, "18_49_Rating_Share" text, "Viewers_m" text, "Weekly_Rank" real, "Channel" text, PRIMARY KEY ("id"), FOREIGN KEY (`Channel`) REFERENCES `TV_Channel`(`id`) ); CREATE TABLE IF NOT EXISTS "Cartoon" ( "id" real, "Title" text, "Directed_by" text, "Written_by" text, "Original_air_date" text, "Production_code" real, "Channel" text, PRIMARY KEY ("id"), FOREIGN KEY (`Channel`) REFERENCES `TV_Channel`(`id`) );

Question (easy): List the title of all cartoons in alphabetical order.
SQL: SELECT Title FROM Cartoon ORDER BY title

Question (easy): What are the titles of the cartoons sorted alphabetically?
SQL: SELECT Title FROM Cartoon ORDER BY title

Question (easy): List all cartoon directed by "Ben Jones".
SQL: SELECT Title FROM Cartoon WHERE Directed_by = "Ben Jones";

Question (easy): What are the names of all cartoons directed by Ben Jones?
SQL: SELECT Title FROM Cartoon WHERE Directed_by = "Ben Jones";

Question (easy): How many cartoons were written by "Joseph Kuhr"?
SQL: SELECT count(*) FROM Cartoon WHERE Written_by = "Joseph Kuhr";

Question (easy): What is the number of cartoones written by Joseph Kuhr?
SQL: SELECT count(*) FROM Cartoon WHERE Written_by = "Joseph Kuhr";

Question (medium): list all cartoon titles and their directors ordered by their air date
SQL: SELECT title ,  Directed_by FROM Cartoon ORDER BY Original_air_date

Question (medium): What is the name and directors of all the cartoons that are ordered by air date?
SQL: SELECT title ,  Directed_by FROM Cartoon ORDER BY Original_air_date

Question (medium): List the title of all cartoon directed by "Ben Jones" or "Brandon Vietti".
SQL: SELECT Title FROM Cartoon WHERE Directed_by = "Ben Jones" OR Directed_by = "Brandon Vietti";

Question (medium): What are the titles of all cartoons directed by Ben Jones or Brandon Vietti?
SQL: SELECT Title FROM Cartoon WHERE Directed_by = "Ben Jones" OR Directed_by = "Brandon Vietti";

Question (hard): Which country has the most of TV Channels? List the country and number of TV Channels it has.
SQL: SELECT Country ,  count(*) FROM TV_Channel GROUP BY Country ORDER BY count(*) DESC LIMIT 1;

Question (hard): What is the country with the most number of TV Channels and how many does it have?
SQL: SELECT Country ,  count(*) FROM TV_Channel GROUP BY Country ORDER BY count(*) DESC LIMIT 1;

Question (medium): List the number of different series names and contents in the TV Channel table.
SQL: SELECT count(DISTINCT series_name) ,  count(DISTINCT content) FROM TV_Channel;

Question (medium): How many different series and contents are listed in the TV Channel table?
SQL: SELECT count(DISTINCT series_name) ,  count(DISTINCT content) FROM TV_Channel;

Question (easy): What is the content of TV Channel with serial name "Sky Radio"?
SQL: SELECT Content FROM TV_Channel WHERE series_name = "Sky Radio";

Question (easy): What is the content of the series Sky Radio?
SQL: SELECT Content FROM TV_Channel WHERE series_name = "Sky Radio";

Question (easy): What is the Package Option of TV Channel with serial name "Sky Radio"?
SQL: SELECT Package_Option FROM TV_Channel WHERE series_name = "Sky Radio";

Question (easy): What are the Package Options of the TV Channels whose series names are Sky Radio?
SQL: SELECT Package_Option FROM TV_Channel WHERE series_name = "Sky Radio";

Question (easy): How many TV Channel using language English?
SQL: SELECT count(*) FROM TV_Channel WHERE LANGUAGE = "English";

Question (easy): How many TV Channels use the English language?
SQL: SELECT count(*) FROM TV_Channel WHERE LANGUAGE = "English";

Question (hard): List the language used least number of TV Channel. List language and number of TV Channel.
SQL: SELECT LANGUAGE ,  count(*) FROM TV_Channel GROUP BY LANGUAGE ORDER BY count(*) ASC LIMIT 1;

Question (hard): What are the languages used by the least number of TV Channels and how many channels use it?
SQL: SELECT LANGUAGE ,  count(*) FROM TV_Channel GROUP BY LANGUAGE ORDER BY count(*) ASC LIMIT 1;

Question (medium): List each language and the number of TV Channels using it.
SQL: SELECT LANGUAGE ,  count(*) FROM TV_Channel GROUP BY LANGUAGE

Question (medium): For each language, list the number of TV Channels that use it.
SQL: SELECT LANGUAGE ,  count(*) FROM TV_Channel GROUP BY LANGUAGE

Question (medium): What is the TV Channel that shows the cartoon "The Rise of the Blue Beetle!"? List the TV Channel's series name.
SQL: SELECT T1.series_name FROM TV_Channel AS T1 JOIN Cartoon AS T2 ON T1.id = T2.Channel WHERE T2.Title = "The Rise of the Blue Beetle!";

Question (medium): What is the series name of the TV Channel that shows the cartoon "The Rise of the Blue Beetle"?
SQL: SELECT T1.series_name FROM TV_Channel AS T1 JOIN Cartoon AS T2 ON T1.id = T2.Channel WHERE T2.Title = "The Rise of the Blue Beetle!";

Question (medium): List the title of all  Cartoons showed on TV Channel with series name "Sky Radio".
SQL: SELECT T2.Title FROM TV_Channel AS T1 JOIN Cartoon AS T2 ON T1.id = T2.Channel WHERE T1.series_name = "Sky Radio";

Question (medium): What is the title of all the cartools that are on the TV Channel with the series name "Sky Radio"?
SQL: SELECT T2.Title FROM TV_Channel AS T1 JOIN Cartoon AS T2 ON T1.id = T2.Channel WHERE T1.series_name = "Sky Radio";

Question (easy): List the Episode of all TV series sorted by rating.
SQL: SELECT Episode FROM TV_series ORDER BY rating

Question (easy): What are all of the episodes ordered by ratings?
SQL: SELECT Episode FROM TV_series ORDER BY rating

Question (medium): List top 3 highest Rating  TV series. List the TV series's Episode and Rating.
SQL: SELECT Episode ,  Rating FROM TV_series ORDER BY Rating DESC LIMIT 3;

Question (medium): What are 3 most highly rated episodes in the TV series table and what were those ratings?
SQL: SELECT Episode ,  Rating FROM TV_series ORDER BY Rating DESC LIMIT 3;

Question (medium): What is minimum and maximum share of TV series?
SQL: SELECT max(SHARE) , min(SHARE) FROM TV_series;

Question (medium): What is the maximum and minimum share for the TV series?
SQL: SELECT max(SHARE) , min(SHARE) FROM TV_series;

Question (easy): What is the air date of TV series with Episode "A Love of a Lifetime"?
SQL: SELECT Air_Date FROM TV_series WHERE Episode = "A Love of a Lifetime";

Question (easy): When did the episode "A Love of a Lifetime" air?
SQL: SELECT Air_Date FROM TV_series WHERE Episode = "A Love of a Lifetime";

Question (easy): What is Weekly Rank of TV series with Episode "A Love of a Lifetime"?
SQL: SELECT Weekly_Rank FROM TV_series WHERE Episode = "A Love of a Lifetime";

Question (easy): What is the weekly rank for the episode "A Love of a Lifetime"?
SQL: SELECT Weekly_Rank FROM TV_series WHERE Episode = "A Love of a Lifetime";

Question (medium): What is the TV Channel of TV series with Episode "A Love of a Lifetime"? List the TV Channel's series name.
SQL: SELECT T1.series_name FROM TV_Channel AS T1 JOIN TV_series AS T2 ON T1.id = T2.Channel WHERE T2.Episode = "A Love of a Lifetime";

Question (medium): What is the name of the series that has the episode "A Love of a Lifetime"?
SQL: SELECT T1.series_name FROM TV_Channel AS T1 JOIN TV_series AS T2 ON T1.id = T2.Channel WHERE T2.Episode = "A Love of a Lifetime";

Question (medium): List the Episode of all  TV series showed on TV Channel with series name "Sky Radio".
SQL: SELECT T2.Episode FROM TV_Channel AS T1 JOIN TV_series AS T2 ON T1.id = T2.Channel WHERE T1.series_name = "Sky Radio";

Question (medium): What is the episode for the TV series named "Sky Radio"?
SQL: SELECT T2.Episode FROM TV_Channel AS T1 JOIN TV_series AS T2 ON T1.id = T2.Channel WHERE T1.series_name = "Sky Radio";

Question (medium): Find the number of cartoons directed by each of the listed directors.
SQL: SELECT count(*) ,  Directed_by FROM cartoon GROUP BY Directed_by

Question (medium): How many cartoons did each director create?
SQL: SELECT count(*) ,  Directed_by FROM cartoon GROUP BY Directed_by

Question (medium): Find the production code and channel of the most recently aired cartoon .
SQL: select production_code ,  channel from cartoon order by original_air_date desc limit 1

Question (medium): What is the produdction code and channel of the most recent cartoon ?
SQL: select production_code ,  channel from cartoon order by original_air_date desc limit 1

Question (medium): Find the package choice and series name of the TV channel that has high definition TV.
SQL: SELECT package_option ,  series_name FROM TV_Channel WHERE hight_definition_TV  =  "yes"

Question (medium): What are the package options and the name of the series for the TV Channel that supports high definition TV?
SQL: SELECT package_option ,  series_name FROM TV_Channel WHERE hight_definition_TV  =  "yes"

Question (medium): which countries' tv channels are playing some cartoon written by Todd Casey?
SQL: SELECT T1.country FROM TV_Channel AS T1 JOIN cartoon AS T2 ON T1.id = T2.Channel WHERE T2.written_by  =  'Todd Casey'

Question (medium): What are the countries that have cartoons on TV that were written by Todd Casey?
SQL: SELECT T1.country FROM TV_Channel AS T1 JOIN cartoon AS T2 ON T1.id = T2.Channel WHERE T2.written_by  =  'Todd Casey'

Question (hard): which countries' tv channels are not playing any cartoon written by Todd Casey?
SQL: SELECT country FROM TV_Channel EXCEPT SELECT T1.country FROM TV_Channel AS T1 JOIN cartoon AS T2 ON T1.id = T2.Channel WHERE T2.written_by  =  'Todd Casey'

Question (hard): What are the countries that are not playing cartoons written by Todd Casey?
SQL: SELECT country FROM TV_Channel EXCEPT SELECT T1.country FROM TV_Channel AS T1 JOIN cartoon AS T2 ON T1.id = T2.Channel WHERE T2.written_by  =  'Todd Casey'

Question (extra): Find the series name and country of the tv channel that is playing some cartoons directed by Ben Jones and Michael Chang?
SQL: SELECT T1.series_name ,  T1.country FROM TV_Channel AS T1 JOIN cartoon AS T2 ON T1.id = T2.Channel WHERE T2.directed_by  =  'Michael Chang' INTERSECT SELECT T1.series_name ,  T1.country FROM TV_Channel AS T1 JOIN cartoon AS T2 ON T1.id = T2.Channel WHERE T2.directed_by  =  'Ben Jones'

Question (extra): What is the series name and country of all TV channels that are playing cartoons directed by Ben Jones and cartoons directed by Michael Chang?
SQL: SELECT T1.series_name ,  T1.country FROM TV_Channel AS T1 JOIN cartoon AS T2 ON T1.id = T2.Channel WHERE T2.directed_by  =  'Michael Chang' INTERSECT SELECT T1.series_name ,  T1.country FROM TV_Channel AS T1 JOIN cartoon AS T2 ON T1.id = T2.Channel WHERE T2.directed_by  =  'Ben Jones'

Question (medium): find the pixel aspect ratio and nation of the tv channels that do not use English.
SQL: SELECT Pixel_aspect_ratio_PAR ,  country FROM tv_channel WHERE LANGUAGE != 'English'

Question (medium): What is the pixel aspect ratio and country of origin for all TV channels that do not use English?
SQL: SELECT Pixel_aspect_ratio_PAR ,  country FROM tv_channel WHERE LANGUAGE != 'English'

Question (easy): find id of the tv channels that from the countries where have more than two tv channels.
SQL: SELECT id FROM tv_channel GROUP BY country HAVING count(*)  >  2

Question (easy): What are the ids of all tv channels that have more than 2 TV channels?
SQL: SELECT id FROM tv_channel GROUP BY country HAVING count(*)  >  2

Question (hard): find the id of tv channels that do not play any cartoon directed by Ben Jones.
SQL: SELECT id FROM TV_Channel EXCEPT SELECT channel FROM cartoon WHERE directed_by  =  'Ben Jones'

Question (hard): What are the ids of the TV channels that do not have any cartoons directed by Ben Jones?
SQL: SELECT id FROM TV_Channel EXCEPT SELECT channel FROM cartoon WHERE directed_by  =  'Ben Jones'

Question (hard): find the package option of the tv channel that do not have any cartoon directed by Ben Jones.
SQL: SELECT package_option FROM TV_Channel WHERE id NOT IN (SELECT channel FROM cartoon WHERE directed_by  =  'Ben Jones')

Question (hard): What are the package options of all tv channels that are not playing any cartoons directed by Ben Jones?
SQL: SELECT package_option FROM TV_Channel WHERE id NOT IN (SELECT channel FROM cartoon WHERE directed_by  =  'Ben Jones')