CampusX Data Science Mentorship Program 2022-23 - Session 33 - SQL Grouping + Sorting | DSMP 2023

Dataset Link - https://drive.google.com/drive/folders/1YYYbQWjFmg242LZyE_6W_9CizNyVDOPk?usp=share_link ------------------------------------------------------------------------------------------------------------------------------------------------------- Enroll in this Programme from our Website - https://learnwith.campusx.in/ Course Website Link - https://campusx1040.graphy.com/s/preview/courses/CampusX-Data-Science-Mentorship-Program#6376dbd8e4b065a4cadac4db YouTube Playlist - https://www.youtube.com/playlist?list=PLKnIA16_RmvbAlyx4_rdtR66B7EHX5k3z ------------------------------------------------------------------------------------------------------------------------------------------------------- Task Link - https://colab.research.google.com/drive/16W8I4tit19rOJJzGJclolxGUE6vlEBJE?usp=sharing Dataset for problem 10: https://www.kaggle.com/datasets/ulrikthygepedersen/shipping-ecommerce ------------------------------------------------------------------------------------------------------------------------------------------------------- SQL Operators - https://drive.google.com/file/d/13Cu0VUqbENcUX76bx_aoBS0EAtzDPDrE/view?usp=share_link InfyTQ ANimation Course Link -https://infytq.onwingspan.com/web/en/app/toc/lex_auth_01275806667282022456_shared/overview ----------------------- | Time stamp | ----------------------- Chapters ************************************************************************** 00:00 Live Start 6:11 Session Start 9:43 Sorting Data 12:26 1. find the top 5 Samsung phones with the biggest screen size 16:32 2. sort all the phones in descending order of the number of total cameras 19:45 3. sort data on the basis of ppi in decreasing order 22:50 4. find the phone with 2nd largest battery 29:00 5. find the name and rating of the worst rated apple phone 31:40 6. sort phones alphabetically and then on the basis of rating in desc order 34:56 7. sort phones alphabetically and then on the basis of price in ascending order ********************************* * 35:41 -- Order By Animation * ********************************* 38:11 Doubt Clearance 10. find the phone name, price of the costliest phone ********************************************************************************************************* 39:56 Grouping Data 45:14 1. Group smartphones by brand and get the count, average price, max rating, avg screen size, and avg battery capacity ********************************* * 53:21 -- Group By Animation * ********************************* 56:16 Group smartphones by whether they have an NFC and get the average price and rating Avg price of 5g phones vs avg price of non 5g phones 1:00:43 Analysis of Fast Charging Available 1:01:27 Group smartphones by the extended memory available and get the average price 1:02:37 Doubt Clearance 1:04:46 GroupBY on multiple columns 1:06:41 Group smartphones by the brand and processor brand and get the count of models and the average primary camera resolution (rear) 1:11:33 Find the top 5 most costly phone brands 1:14:45 Which brand makes the smallest-screen smartphones 1:16:35 8. Group smartphones by the brand, and find the brand with the highest number of models that have both NFC and an IR blaster 1:20:18 9. Find all Samsung 5g enabled smartphones and find out the avg price for NFC and Non-NFC phones 1:23:15 Doubt Clearance 1:24:08 COUNT(*) ************************************************************************************************************* 1:27:40 Having clause 1:30:35 Costliest Brand which has at least 20 phones. 1:33:05 find the avg rating of smartphone brands that have more than 20 phones 1:34:06 Find the top 3 brands with the highest avg ram that has a refresh rate of at least 90 Hz and fast charging available and don't consider brands that have less than 10 phones 1:37:42 Find the avg price of all the phone brands with avg rating of 70 and num_phones more than 10 among all 5g enabled phones 1:40:42 Doubt Clearance ************************************************************************************************************* 1:44:47 Practice on IPL Dataset 1:45:19 - find the top 5 batsmen in IPL 1:47:42 - find the 2nd highest 6 hitters in IPL 1:49:36 - Find Virat Kohli's performance against all IPL teams [info not available- bowling team] 1:51:41 - Find the top 10 batsmen with centuries in IPL [Need sub-query] 1:58:06 - find the top 5 batsmen with the highest strike rate who have played a min of 1000 balls 2:01:54 session End Doubt Clearance #datanalytics #Database #DBMS #SQL #DML #sqlgroupby #groupby