Databases | SQL | Oracle | MS Access Answers

Find the sum of durations of the videos published by each channel in hours.

Note:

• The output must contain the duration as number of hours.
• Sort the output in the descending order of the no_of_hours

Table: Video

video_id	name	duration_in_secs	published_datetime	no_of_views	   channelid
1002   Victoria Beckham
        Gives Strangers 
        Fashion Advice      836	         2021-01-19 20:12	    75609	      353
       for $2 in Central 
       Park | Vanity Fair	
	
1007	DIY - Simon's Cat  
       |NEW BLACK & WHITE!	 1365	     2012-09-07 11:32	    106271	      355

1008	You, but in emojis.
        (YIAY #375)	         3874	     2021-01-19 20:12	    16469	      352

1009	100 People Hold 
       Their Breath for as   2885	     2015-05-17 19:32	    272102	      354
       Long as They Can	     

1010	Luke Bryan - Hooked
          On It (Audio)	     2106	     2016-02-10 12:32	    184471	      366

1011	The perfect ice - 
          Ice boats on       2549	     2020-07-10 15:32	    108047	      352
           Lake Geneva	

1012	5 Days Of Wearing 
        DIY Makeup | Try     3172	     2018-11-05 08:32	    178623	      358
       Living With Lucie 
        | Refinery29	

1013	Idiot's Guide to 
      Japanese Squat Toilets  709	     2009-08-05 11:32	    44293	      356

1019	Vostok Brompton Beach 
                Ride	        2022	 2016-04-05 19:00	    204704	      367

1020	SHOPPING FOR NEW FISH!! 3496	 2021-01-19 20:12	    294429	      359

1021	Paramore: Fake Happy 
           [OFFICIAL VIDEO]	    1397	 2015-07-05 19:00	    245138	      368

1022	Howard Stern: George 
      Takei Talks About Harvey 
        Weinstein and Sexually  776	     2021-01-19 20:12	    35875	      360
                Assault	

1023	Tony Hawk Answers 
      Skateboarding Questions   4476	 2017-02-17 19:32	    143404	      354
           From Twitter |
        Tech Support | WIRED	

1024	Kimbra - Top of the 
        World (Official Music   3243	 2015-01-05 19:00	    220673	      368
               Video)	

1025	Aly Raisman On Dr. 
      Larry Nassar’s Medical 
      Treatment: I Didn’t Know  2978	 2014-12-17 19:32	     93272	      354
      It Was Abuse | Megyn Kelly 
             TODAY	

1026	When Someone Has A 
     Crush on You | Lilly Singh	 959	 2021-01-19 20:12	     139481	      360

1051	Demi Lovato - Sorry 
   Not Sorry in the Live Lounge	 3929	 2021-01-19 20:12	     265864	      365

Expected Output Format

  channel_id	no_of_hours
      ...	       ...

What are dependencies in DBMS and how it will impact data? How normalization can remove dependencies in DBMS?

What are anomalies of data and how anomalies will remove from the Database?

Why we use joins in Database Management System? Define any four joins use in SQL with examples?

Define Database Management system? Why we use a Database Management system and how it is better than File System?

For all the videos, represent the number of views in multiples of thousands.

For example, if the number of views of a video is 17,200, it is represented as 17.2 in the output.

Note:

• Sort the output in the descending order ofno_of_views_in_thousands, and then in the alphabetical order ofname

Table: video

video_id	name	no_of_views_in_thousands
    ...	     ...	        ...

just tell me the form of converting the views of videos for EX 17,200 into 17.2.

and the code i had written for this is:-

SELECT

 video_id,

 name,

 (no_of_views) AS no_of_views_in_thousands

FROM

 video

WHERE

 cast(strftime("%d", no_of_views) AS float)

ORDER BY

 no_of_views_in_thousands DESC,

 name ASC;

Get the number of reactions (likes/dislikes) generated in each hour of the day in the year 2020.

Note:

• For this question, convert the hour_of_dayin string datatype to INT datatype.
• Sort the output in the ascending order of hour_of_day

Table:

user_id	video_id	reaction_type	reacted_at
2141	1529	LIKE	2012-04-12 04:46
2234	1529	LIKE	2012-04-30 17:47
2245	1529	DISLIKE	2012-05-03 21:24
2570	1529	LIKE	2012-04-14 23:08
2525	1529	LIKE	2012-04-26 23:12
2223	1529	DISLIKE	2012-04-08 12:42
2696	1529	LIKE	2012-04-28 01:53
2192	1529	LIKE	2012-04-21 02:39
2773	1529	LIKE	2012-04-25 16:02
2072	1529	LIKE	2012-04-07 15:00
2215	1529	DISLIKE	2012-04-14 03:12
2033	1529	LIKE	2012-04-16 13:54
2039	1529	DISLIKE	2012-04-25 19:14
2259	1529	LIKE	2012-05-03 14:07
2102	1529	LIKE	2012-04-29 11:44
2254	1529	DISLIKE	2012-04-17 14:15
2446	1529	DISLIKE	2012-04-17 07:03
2861	1529	DISLIKE	2012-04-29 08:38
2930	1529	DISLIKE	2012-04-25 03:46
2022	1529	LIKE	2012-04-25 15:45
2795	1529	LIKE	2012-05-01 22:17
2067	1529	LIKE	2012-04-24 13:27
2783	1529	LIKE	2012-04-23 15:23
2053	1529	DISLIKE	2012-04-13 06:26
2045	1529	DISLIKE	2012-04-20 16:47
2265	1529	LIKE	2012-04-11 12:39
2164	1529	LIKE	2012-04-16 02:58
2190	1529	LIKE	2012-04-11 15:45
2551	1529	LIKE	2012-05-04 04:27
2167	1529	LIKE	2012-05-01 07:55
2925	1529	LIKE	2012-04-11 00:54
2367	1529	LIKE	2012-04-17 17:36

Table:

hour_of_day	 no_of_reactions
     0	          500
     1	          2450
     ..	           ..
     ..	           ..
     23	          400

For all the videos, represent the number of views in multiples of thousands.

For example, if the number of views of a video is 17,200, it is represented as 17.2 in the output.

Note:

• Sort the output in the descending order ofno_of_views_in_thousands, and then in the alphabetical order ofname

Table: video

video_id	name	duration_in_secs	published_datetime	no_of_views	   channelid
1000	Getting My 
         Driver's          3652           2011-12-05 19:00       10619        367
        |Lele Pons  

1001	Apple iPhone 
        X Review:          4556	         2021-01-19 20:12	    140012	      362
       The Best Yet!	

1002   Victoria Beckham
        Gives Strangers 
        Fashion Advice      836	         2021-01-19 20:12	    75609	      353
       for $2 in Central 
       Park | Vanity Fair	

1008	You, but in emojis.
        (YIAY #375)	         3874	     2021-01-19 20:12	    16469	      352

1009	100 People Hold 
       Their Breath for as   2885	     2015-05-17 19:32	    272102	      354
       Long as They Can	     

1010	Luke Bryan - Hooked
          On It (Audio)	     2106	     2016-02-10 12:32	    184471	      366

1011	The perfect ice - 
          Ice boats on       2549	     2020-07-10 15:32	    108047	      352
           Lake Geneva	

1012	5 Days Of Wearing 
        DIY Makeup | Try     3172	     2018-11-05 08:32	    178623	      358
       Living With Lucie 
        | Refinery29	

1013	Idiot's Guide to 
      Japanese Squat Toilets  709	     2009-08-05 11:32	    44293	      356

1014	UCLA men's basketball
         players discuss       661	     2017-04-17 17:32	    76223	      359
          arrests in China	

1015	Surprise After 
      Surprise | October Vlog  3288	     2012-08-07 11:32	    132277	      355

1016	Canada Soccer's Women's
          National Team v USA 
      International Friendly    1661	 2010-11-05 11:32	    239292	      356
      LIVE from Avaya Stadium, 
             San Jose	

1017	F The Prom (2017) 
       |Official Trailer HD	    3327	 2019-12-17 17:32	    51052	      359
 
1018	Walking while black	    2868	 2016-02-10 15:32	    281085	      352

1019	Vostok Brompton Beach 
                Ride	        2022	 2016-04-05 19:00	    204704	      367

1020	SHOPPING FOR NEW FISH!! 3496	 2021-01-19 20:12	    294429	      359

1021	Paramore: Fake Happy 
           [OFFICIAL VIDEO]	    1397	 2015-07-05 19:00	    245138	      368

1022	Howard Stern: George 
      Takei Talks About Harvey 
        Weinstein and Sexually  776	     2021-01-19 20:12	    35875	      360
                Assault	

1023	Tony Hawk Answers 
      Skateboarding Questions   4476	 2017-02-17 19:32	    143404	      354
           From Twitter |
        Tech Support | WIRED	

1024	Kimbra - Top of the 
        World (Official Music   3243	 2015-01-05 19:00	    220673	      368
               Video)	

Expected Output Format

video_id	name	no_of_views_in_thousands
   ...	     ...	         ...

Get the ids of all the channels that have uploaded at least 50 videos.

Note:

• Sort the output in the ascending order of the channel_id

Table: Movies

video_id	name	duration_in_secs	published_datetime	no_of_views	   channelid
1002   Victoria Beckham
        Gives Strangers 
        Fashion Advice      836	         2021-01-19 20:12	    75609	      353
       for $2 in Central 
       Park | Vanity Fair	
	
1007	DIY - Simon's Cat  
       |NEW BLACK & WHITE!	 1365	     2012-09-07 11:32	    106271	      355

1008	You, but in emojis.
        (YIAY #375)	         3874	     2021-01-19 20:12	    16469	      352

1009	100 People Hold 
       Their Breath for as   2885	     2015-05-17 19:32	    272102	      354
       Long as They Can	     

1010	Luke Bryan - Hooked
          On It (Audio)	     2106	     2016-02-10 12:32	    184471	      366

1011	The perfect ice - 
          Ice boats on       2549	     2020-07-10 15:32	    108047	      352
           Lake Geneva	

1012	5 Days Of Wearing 
        DIY Makeup | Try     3172	     2018-11-05 08:32	    178623	      358
       Living With Lucie 
        | Refinery29	

1013	Idiot's Guide to 
      Japanese Squat Toilets  709	     2009-08-05 11:32	    44293	      356

1019	Vostok Brompton Beach 
                Ride	        2022	 2016-04-05 19:00	    204704	      367

1020	SHOPPING FOR NEW FISH!! 3496	 2021-01-19 20:12	    294429	      359

1021	Paramore: Fake Happy 
           [OFFICIAL VIDEO]	    1397	 2015-07-05 19:00	    245138	      368

1022	Howard Stern: George 
      Takei Talks About Harvey 
        Weinstein and Sexually  776	     2021-01-19 20:12	    35875	      360
                Assault	

1023	Tony Hawk Answers 
      Skateboarding Questions   4476	 2017-02-17 19:32	    143404	      354
           From Twitter |
        Tech Support | WIRED	

1024	Kimbra - Top of the 
        World (Official Music   3243	 2015-01-05 19:00	    220673	      368
               Video)	

1025	Aly Raisman On Dr. 
      Larry Nassar’s Medical 
      Treatment: I Didn’t Know  2978	 2014-12-17 19:32	     93272	      354
      It Was Abuse | Megyn Kelly 
             TODAY	

1026	When Someone Has A 
     Crush on You | Lilly Singh	 959	 2021-01-19 20:12	     139481	      360

1051	Demi Lovato - Sorry 
   Not Sorry in the Live Lounge	 3929	 2021-01-19 20:12	     265864	      365

Expected Output Format

 channel_id
     ...

Get the number of male and female premium users in the platform.

Table: USER

user_id	   name	         gender	age	country	    premium_membership
2000	John White	       M	63	AUSTRALIA	      1
2001	John Andrews	   M	67	AUSTRALIA	      1
2002	April Robinson	   F	60	SRILANKA	      1
2003	Kathy Ryan	       F	81	SRILANKA	      0
2004	Megan Bradshaw	   F	53	AUSTRALIA	      1
2005	Melissa Sullivan   F	45	BANGLADESH	      1
2006	Amber Smith	       F	97	BANGLADESH	      0
2007	Sara Higgins	   F	85	ENGLAND	          1
2008	Robert Taylor	   M	68	AMERICA	          0
2009	William Wagner	   M	16	AMERICA       	  0
2010	Herbert Mcdowell   M	97	BANGLADESH    	  1
2011	Brett Carter	   M	26	AUSTRALIA	      0 
2012	Brian Knight	   M	81	AUSTRALIA	      1
2013	Jennifer Johnson   F	46	SRILANKA	      0
2014	Clarence Gonzales  M	92	SRILANKA	      0

there is still having lot of data in the table, but I just gave the limited data for example to get the solution related to question.

and the expected output format should be as follows:

Table:

gender	total_users
   F	  ...
   M	  ...