How can I find the initials
of the most active video maker, the one that made the most videos considering the following tables using only one query?
CREATE TABLE IF NOT EXISTS Videos ( title TEXT PRIMARY KEY, video_description TEXT NOT NULL, ); CREATE TABLE IF NOT EXISTS VideosMaker ( full_name TEXT PRIMARY KEY NOT NULL, initials TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS VideosMaker_Videos ( video_title TEXT NOT NULL, video_maker_name TEXT NOT NULL, PRIMARY KEY (video_title, video_maker_name), FOREIGN KEY (video_title) REFERENCES Videos(title), FOREIGN KEY (video_maker_name) REFERENCES VideosMaker(full_name) );
NOTE: It is guaranteed that there will be only one video_maker who has contributed to a maximum number of news items. For this problem, foreign_keys is ON
Advertisement
Answer
You can get the name of the video maker who made the most videos with this query:
SELECT video_maker_name FROM VideosMaker_Videos GROUP BY video_maker_name ORDER BY COUNT(*) DESC LIMIT 1
and to get the initials you must use it as a subquery in the WHERE clause:
SELECT initials FROM VideosMaker WHERE full_name = ( SELECT video_maker_name FROM VideosMaker_Videos GROUP BY video_maker_name ORDER BY COUNT(*) DESC LIMIT 1 )