generate an sql query for this question- What is the average duration of the most popular videos? 

a. This question involves understanding video duration trends, which can be essential for content creators and consumers alike. It helps creators optimize their video length for engagement and viewers to manage their time effectively. 

 

2)- generate an sql query for this- What day of the week sees the highest number of new video uploads?

                 a.  Involves analyzing temporal patterns, helping both content creators and users              

                      know the most active days for new content

 

THE FOLLOWING TABLES AND COLUMNS ARE THE ONLY ONES TO BE USED. I DONT ANY COLUMN CALLED DURATION OR ANY TABLE CALLED VIDEO DURATION. MAKE THE QUERIES WORK ONLY USING THE FOLLOWING TABLES AND COLUMNS.
 

use the following tables:-

 

CREATE TABLE COMMENTS (
   commentid NUMBER(10),
   videoid VARCHAR2(255),
   commentcontent NVARCHAR2(1000),
   likes NUMBER(15,2),
   sentiment NUMBER(15,2)
);
 

 

CREATE TABLE VIDEOSTATS (
   recordid NUMBER(10),
   title VARCHAR2(255),
   videoid VARCHAR2(255),
   publishedat DATE,
   keyword VARCHAR2(20),
   likes NUMBER(20,2),
   comments NUMBER(20,2),
   views NUMBER(20,2)
);
 

CREATE TABLE GLOBALSTATS (
   rank VARCHAR2(20),
   youtuber VARCHAR2(100),
   subscribers VARCHAR2(20),
   videoveiws VARCHAR2(20),
   category VARCHAR2(100),
   title VARCHAR2(255),
   uploads VARCHAR2(20),
   country VARCHAR2(20),
   abbrevcountry VARCHAR2(5),
   channeltype VARCHAR2(50),
   videoviewsrank VARCHAR2(20),
   countryrank VARCHAR2(20),
   channeltyperank VARCHAR2(20),
   views30days VARCHAR2(20),
   lowestmonthlyearnings VARCHAR2(20),
   highestmonthlyearnings VARCHAR2(20),
   lowestyearlyearnings VARCHAR2(20),
   highestyearlyearnings VARCHAR2(20),
   subs30days VARCHAR2(20),
   yearcreated VARCHAR2(20),
   monthcreated VARCHAR2(15),
   datecreated VARCHAR2(20),
   grosstertiaryenrollment NUMBER(10,2),
   population VARCHAR2(20),
   unemploymentrate NUMBER(10,2),
   urbanpop VARCHAR2(20),
   latitude NUMBER(10,10),
   longitude NUMBER(10,10)
); 

 

 

DROP TABLE COMMENTS; 

 

DROP TABLE GLOBALSTATS; 

 

DROP TABLE VIDEOSTATS; 

 

Package.sql:-

CREATE OR REPLACE PACKAGE video_package AS
 PROCEDURE get_num_video_types(p_num_video_types OUT NUMBER);

 PROCEDURE get_top_video_by_average_views(p_title OUT VARCHAR2, p_average_views OUT NUMBER);

 PROCEDURE get_most_liked_comment(p_commentid OUT NUMBER, p_videoid OUT NUMBER, p_commentcontent OUT VARCHAR2, p_likes OUT NUMBER, p_sentiment OUT VARCHAR2);

 PROCEDURE get_total_watch_time(p_videoid OUT NUMBER, p_total_watch_time OUT NUMBER);

 PROCEDURE get_genre_count(p_keyword OUT VARCHAR2, p_genre_count OUT NUMBER);

 PROCEDURE get_youtuber_stats(p_youtuber OUT VARCHAR2, p_comment_count OUT NUMBER, p_like_count OUT NUMBER);

 PROCEDURE get_country_view_count(p_videoid OUT NUMBER, p_country OUT VARCHAR2, p_view_count OUT NUMBER);

 PROCEDURE get_max_likes(p_max_likes OUT NUMBER);

END video_package;

CREATE OR REPLACE PACKAGE BODY video_package AS
 PROCEDURE get_num_video_types(p_num_video_types OUT NUMBER) IS
 BEGIN
   SELECT COUNT(DISTINCT video_type) INTO p_num_video_types FROM VIDEOSTATS;
 END get_num_video_types;

 PROCEDURE get_top_video_by_average_views(p_title OUT VARCHAR2, p_average_views OUT NUMBER) IS
 BEGIN
   SELECT title, AVG(views) INTO p_title, p_average_views
   FROM VIDEOSTATS
   GROUP BY title
   ORDER BY average_views DESC
   FETCH FIRST 1 ROWS ONLY;
 END get_top_video_by_average_views;

 PROCEDURE get_most_liked_comment(p_commentid OUT NUMBER, p_videoid OUT NUMBER, p_commentcontent OUT VARCHAR2, p_likes OUT NUMBER, p_sentiment OUT VARCHAR2) IS
 BEGIN
   SELECT commentid, videoid, commentcontent, likes, sentiment INTO p_commentid, p_videoid, p_commentcontent, p_likes, p_sentiment
   FROM COMMENTS
   ORDER BY likes DESC
   FETCH FIRST 1 ROWS ONLY;
 END get_most_liked_comment;

 PROCEDURE get_total_watch_time(p_videoid OUT NUMBER, p_total_watch_time OUT NUMBER) IS
 BEGIN
   SELECT videoid, SUM(views) INTO p_videoid, p_total_watch_time
   FROM VIDEOSTATS
   GROUP BY videoid;
 END get_total_watch_time;

 PROCEDURE get_genre_count(p_keyword OUT VARCHAR2, p_genre_count OUT NUMBER) IS
 BEGIN
   SELECT keyword, COUNT(*) INTO p_keyword, p_genre_count
   FROM VIDEOSTATS
   GROUP BY keyword
   ORDER BY genre_count DESC;
 END get_genre_count;

 PROCEDURE get_youtuber_stats(p_youtuber OUT VARCHAR2, p_comment_count OUT NUMBER, p_like_count OUT NUMBER) IS
 BEGIN
   SELECT g.youtuber, COUNT(c.commentid), SUM(v.likes) INTO p_youtuber, p_comment_count, p_like_count
   FROM GLOBALSTATS g
   LEFT JOIN VIDEOSTATS v ON g.title = v.title
   LEFT JOIN COMMENTS c ON v.videoid = c.videoid
   GROUP BY g.youtuber
   ORDER BY comment_count DESC, like_count DESC;
 END get_youtuber_stats;

 PROCEDURE get_country_view_count(p_videoid OUT NUMBER, p_country OUT VARCHAR2, p_view_count OUT NUMBER) IS
 BEGIN
   SELECT v.videoid, COALESCE(g.country, 'United States'), COUNT(*) INTO p_videoid, p_country, p_view_count
   FROM VIDEOSTATS v
   LEFT JOIN GLOBALSTATS g ON v.title = g.title
   GROUP BY v.videoid, g.country
   ORDER BY view_count DESC;
 END get_country_view_count;

 PROCEDURE get_max_likes(p_max_likes OUT NUMBER) IS
 BEGIN
   SELECT MAX(likes) INTO p_max_likes FROM VIDEOSTATS;
 END get_max_likes;

END video_package;