Scribd
Upload
46 views2 pages

Demo

The document outlines the creation of a table 'd' for storing video data with various attributes, followed by loading data into it. It provides SQL queries that retrieve the top categories by count, the top videos by number of ratings, and the most viewed videos. The results show 'Entertainment' as the leading category and a video with over 65 million views.

Uploaded by

VIDHYA HK
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
46 views2 pages

Demo

The document outlines the creation of a table 'd' for storing video data with various attributes, followed by loading data into it. It provides SQL queries that retrieve the top categories by count, the top videos by number of ratings, and the most viewed videos. The results show 'Entertainment' as the leading category and a video with over 65 million views.

Uploaded by

VIDHYA HK
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

CREATE TABLE d(id STRING, uploader STRING, interv INT, category STRING, length INT,

views INT, rating float, no_of_ratings INT, comments INT, related_id STRING) ROW
FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE

LOAD DATA INPATH "/y" INTO TABLE d;

select category,count(*) as cat_count from d group by category order by cat_count


desc limit 5

+-----------------+-----------+
| category | cat_count |
+-----------------+-----------+
| Entertainment | 908 |
| Music | 862 |
| Comedy | 414 |
| People & Blogs | 398 |
| News & Politics | 333 |
+-----------------+-----------+

select id,no_of_ratings from d where no_of_ratings IS NOT NULL order by


no_of_ratings desc limit 10;

+-------------+---------------+
| id | no_of_ratings |
+-------------+---------------+
| kHmvkRoEowc | 122129 |
| EwTZ2xpQwpA | 83514 |
| rZBA0SKmQy8 | 75004 |
| 4DC4Rb9quKk | 73257 |
| LU8DDYz68kM | 58850 |
| Qit3ALTelOo | 56767 |
| irp8CNj9qBI | 43774 |
| 3QL97xldoXc | 37247 |
| LTxO_pgMqys | 35352 |
| Md6rURKhZmA | 34802 |
+-------------+---------------+

select id,views from d where views IS NOT NULL order by views desc limit 1

+-------------+----------+
| id | views |
+-------------+----------+
| 12Z3J1uzd0Q | 65341925 |
+-------------+----------+

select id,views from d where views IS NOT NULL order by views desc limit 5

+-------------+----------+
| id | views |
+-------------+----------+
| 12Z3J1uzd0Q | 65341925 |
| 4DC4Rb9quKk | 33754615 |
| LU8DDYz68kM | 27721690 |
| kHmvkRoEowc | 18235463 |
| Md6rURKhZmA | 18141492 |
+-------------+----------+
select id,views from d where views IS NOT NULL order by views desc limit 10

+-------------+----------+
| id | views |
+-------------+----------+
| 12Z3J1uzd0Q | 65341925 |
| 4DC4Rb9quKk | 33754615 |
| LU8DDYz68kM | 27721690 |
| kHmvkRoEowc | 18235463 |
| Md6rURKhZmA | 18141492 |
| EwTZ2xpQwpA | 16841569 |
| A2f3cuUXXRs | 13038204 |
| rZBA0SKmQy8 | 11007201 |
| irp8CNj9qBI | 10172172 |
| ZCYaw5tGYAs | 8944331 |
+-------------+----------+

You might also like