SQL Case Study 92.7 BIG FM Radio Network

 

                                                        92.7 BIG FM Radio Network

Digits n Data - https://nitish2162001.github.io/webpa/#sql-schema 

SQL CHALLENGE - 


Digits n Data  -  https://nitish2162001.github.io/webpa/#sql-schema

SQL CHALLENGE - 92.7 BIG FM radio network


Tables names:

Stations

Hosts

Shows

Partnerships

ShowPartnerships

Awards

OnlinePresence

Schema:

SQL Schema


                          

CREATE TABLE Stations (


                                StationID INT PRIMARY KEY,


                                StationName VARCHAR(255),


                                Location VARCHAR(255),


                                Frequency DECIMAL(5,2),


                                LaunchDate DATE


                            );


                                                       

CREATE TABLE Hosts (


                                HostID INT PRIMARY KEY,


                                HostName VARCHAR(255),


                                ShowCount INT,


                                JoinDate DATE


                            );


                                                       

CREATE TABLE Shows (


                                ShowID INT PRIMARY KEY,


                                ShowName VARCHAR(255),


                                HostID INT, -- Foreign key referencing Hosts table


                                StationID INT, -- Foreign key referencing Stations table


                                LaunchDate DATE,


                                CONSTRAINT fk_Shows_HostID FOREIGN KEY (HostID) REFERENCES Hosts(HostID),


                                CONSTRAINT fk_Shows_StationID FOREIGN KEY (StationID) REFERENCES Stations(StationID)


                            );


                            

CREATE TABLE Partnerships (


                                PartnershipID INT PRIMARY KEY,


                                PartnerName VARCHAR(255),


                                PartnershipType VARCHAR(100),


                                StartDate DATE,


                                EndDate DATE


                            );


                            


CREATE TABLE ShowPartnerships (


                                ShowID INT,


                                PartnershipID INT,


                                CONSTRAINT fk_ShowPartnerships_ShowID FOREIGN KEY (ShowID) REFERENCES Shows(ShowID),


                                CONSTRAINT fk_ShowPartnerships_PartnershipID FOREIGN KEY (PartnershipID) REFERENCES Partnerships(PartnershipID)


                            );


CREATE TABLE Awards (


                                AwardID INT PRIMARY KEY,


                                AwardName VARCHAR(255),


                                Year INT,


                                ShowID INT, -- Foreign key referencing Shows table


                                CONSTRAINT fk_Awards_ShowID FOREIGN KEY (ShowID) REFERENCES Shows(ShowID)


                            );


                            


CREATE TABLE OnlinePresence (


                                PresenceID INT PRIMARY KEY,


                                PlatformName VARCHAR(255),


                                Link VARCHAR(255),


                                LaunchDate DATE


                            );


                            

INSERT INTO Stations (StationID, StationName, Location, Frequency, LaunchDate)


                            VALUES


                                (1, 'Asansol', 'East', 92.7, '2006-09-01'),


                                (2, 'Bhubaneswar', 'East', 92.7, '2006-09-01'),


                                (3, 'Guwahati', 'East', 92.7, '2006-09-01'),


                                (4, 'Jamshedpur', 'East', 92.7, '2006-09-01'),


                                (5, 'Kolkata', 'East', 92.7, '2006-09-01'),


                                (6, 'Ranchi', 'East', 92.7, '2006-09-01'),


                                (7, 'Rourkela', 'East', 92.7, '2006-09-01'),


                                (8, 'Solapur', 'West', 92.7, '2006-09-01'),


                                (9, 'Nashik', 'West', 92.7, '2006-09-01'),


                                (10, 'Pune', 'West', 92.7, '2006-09-01'),


                                (11, 'Vadodara', 'West', 92.7, '2006-09-01'),


                                (12, 'Bhopal', 'West', 92.7, '2006-09-01'),


                                (13, 'Gwalior', 'West', 92.7, '2006-09-01'),


                                (14, 'Indore', 'West', 92.7, '2006-09-01'),


                                (15, 'Mumbai', 'West', 92.7, '2006-09-01'),


                                (16, 'Rajkot', 'West', 92.7, '2006-09-01'),


                                (17, 'Srinagar', 'North 1', 92.7, '2006-09-01'),


                                (18, 'Jammu', 'North 1', 92.7, '2006-09-01'),


                                (19, 'Shimla', 'North 1', 92.7, '2006-09-01'),


                                (20, 'Jhansi', 'North 2', 92.7, '2006-09-01'),


                                (21, 'Allahabad', 'North 2', 92.7, '2006-09-01'),


                                (22, 'Aligarh', 'North 2', 92.7, '2006-09-01'),


                                (23, 'Bareilly', 'North 2', 92.7, '2006-09-01'),


                                (24, 'Agra', 'North 2', 92.7, '2006-09-01'),


                                (25, 'Ajmer', 'North 2', 92.7, '2006-09-01'),


                                (26, 'Delhi', 'North 2', 92.7, '2006-09-01'),


                                (27, 'Patiala', 'North 2', 92.7, '2006-09-01'),


                                (28, 'Chandigarh', 'North 2', 92.7, '2006-09-01'),


                                (29, 'Hisar', 'North 2', 92.7, '2006-09-01'),


                                (30, 'Shillong', 'North 2', 92.7, '2006-09-01'),


                                (31, 'Agartala', 'North 2', 92.7, '2006-09-01'),


                                (32, 'Itanagar', 'North 2', 92.7, '2006-09-01'),


                                (33, 'Aizawl', 'North 2', 92.7, '2006-09-01'),


                                (34, 'Nagpur', 'South', 92.7, '2006-09-01'),


                                (35, 'Aurangabad', 'South', 92.7, '2006-09-01'),


                                (36, 'Ahmednagar', 'South', 92.7, '2006-09-01'),


                                (37, 'Kolhapur', 'South', 92.7, '2006-09-01'),


                                (38, 'Mangalore', 'South', 92.7, '2006-09-01'),


                                (39, 'Mysore', 'South', 92.7, '2006-09-01'),


                                (40, 'Hyderabad', 'South', 92.7, '2006-09-01'),


                                (41, 'Bangalore', 'South', 92.7, '2006-09-01'),


                                (42, 'Chennai', 'South', 92.7, '2006-09-01'),


                                (43, 'Trivandrum', 'South', 92.7, '2006-09-01'),


                                (44, 'Pondicherry', 'South', 92.7, '2006-09-01'),


                                (45, 'Lucknow', 'South', 92.7, '2006-09-01'),


                                (46, 'Gorakhpur', 'South', 92.7, '2006-09-01'),


                                (47, 'Varanasi', 'South', 92.7, '2006-09-01'),


                                (48, 'Patna', 'South', 92.7, '2006-09-01'),


                                (49, 'Muzaffarpur', 'South', 92.7, '2006-09-01'),


                                (50, 'Kanpur', 'South', 92.7, '2006-09-01'),


                                (51, 'Itanagar', 'New', 92.7, '2006-09-01'),


                                (52, 'Kota', 'New', 92.7, '2006-09-01'),


                                (53, 'Udaipur', 'New', 92.7, '2006-09-01'),


                                (54, 'Shimla', 'New', 92.7, '2006-09-01'),


                                (55, 'Delhi', 'New', 92.7, '2006-09-01'),


                                (56, 'Tirupati', 'New', 92.7, '2006-09-01'),


                                (57, 'Patna', 'New', 92.7, '2006-09-01'),


                                (58, 'Varanasi', 'New', 92.7, '2006-09-01');


INSERT INTO Hosts (HostID, HostName, ShowCount, JoinDate)


                            VALUES


                                (1, 'Vrajesh Hirjee', 2, '2006-09-01'),


                                (2, 'Annu Kapoor', 3, '2006-09-01'),


                                (3, 'Neelesh Misra', 2, '2006-09-01'),


                                (4, 'Bhawana Somaaya', 1, '2006-09-01'),


                                (5, 'Kamini Khanna', 1, '2006-09-01'),


                                (6, 'Richa Anirudh', 2, '2006-09-01'),


                                (7, 'RJ Khaas Koushik', 1, '2006-09-01'),


                                (8, 'RJ Pat Pat Pataki Shruti', 2, '2006-09-01'),


                                (9, 'RJ Rapid Rashmi', 3, '2006-09-01'),


                                (10, 'RJ Rani', 1, '2006-09-01'),


                                (11, 'RJ Khurafati Nitin', 2, '2006-09-01'),


                                (12, 'RJ Sangram', 1, '2006-09-01'),


                                (13, 'RJ Abhilash', 1, '2006-09-01');


INSERT INTO Shows (ShowID, ShowName, HostID, StationID, LaunchDate)


                            VALUES


                                (1, 'Suhaana Safar with Annu Kapoor', 2, 1, '2006-09-01'), -- Assuming HostID 2


                                (2, 'Yaadon Ka Idiot Box with Neelesh Misra', 3, 2, '2006-09-01'),


                                (3, 'Dhun Badal Ke Toh Dekho Season 1 (with Vidya Balan)', 4, 3, '2019-01-01'),


                                (4, 'Dhun Badal Ke Toh Dekho Season 2 (with Sadhguru)', 5, 4, '2019-01-01'),


                                (5, '21 Din Wellness In with Sunil Shetty', 1, 6, '2023-01-01'), -- Assuming HostID 1


                                (6, 'Green Ganesha', 6, 7, '2023-01-01');


INSERT INTO Partnerships (PartnershipID, PartnerName, PartnershipType, StartDate, EndDate)


                            VALUES


                                (1, 'Spotify', 'Music Streaming', '2006-09-01', NULL),


                                (2, 'Gaana', 'Music Streaming', '2006-09-01', NULL),


                                (3, 'JioSaavn', 'Music Streaming', '2006-09-01', NULL),


                                (4, 'Hungama Music', 'Music Streaming', '2021-10-01', NULL),


                                (5, 'Big Living Shop', 'E-commerce', '2023-01-01', NULL),


                                (6, 'Blue Mic', 'Independent Artists Platform', '2021-10-01', NULL);


INSERT INTO ShowPartnerships (ShowID, PartnershipID)


                            VALUES


                                (1, 1), -- ShowID 1 associated with PartnershipID 1


                                (2, 2), -- ShowID 2 associated with PartnershipID 2


                                (3, 3), -- ShowID 3 associated with PartnershipID 3


                                (4, 4), -- ShowID 4 associated with PartnershipID 4


                                (5, 5), -- ShowID 5 associated with PartnershipID 5


                                (6, 6); -- ShowID 6 associated with PartnershipID 6


INSERT INTO Awards (AwardID, AwardName, Year, ShowID)


                            VALUES


                                (1, 'IRF Gold', 2019, 1), -- Award for ShowID 1 in the year 2019


                                (2, 'IRF Gold', 2018, 2), -- Award for ShowID 2 in the year 2018


                                (3, 'ABBY', 2017, 3), -- Award for ShowID 3 in the year 2017


                                (4, 'NYF Radio Award', 2014, 4), -- Award for ShowID 4 in the year 2014


                                (5, 'Indian Radio Forum Gold', 2019, 5), -- Award for ShowID 5 in the year 2019


                                (6, 'Indian Radio Forum Gold', 2018, 6); -- Award for ShowID 6 in the year 2018


INSERT INTO OnlinePresence (PresenceID, PlatformName, Link, LaunchDate)


                            VALUES


                                (1, 'Spotify', 'https://open.spotify.com/bigfm', '2020-01-01'),


                                (2, 'Gaana', 'https://gaana.com/bigfm', '2020-01-01'),


                                (3, 'JioSaavn', 'https://www.jiosaavn.com/bigfm', '2020-01-01'),


                                (4, 'Hungama Music', 'https://www.hungama.com/bigfm', '2021-10-01'),


                                (5, 'BIG Living Shop', 'https://bigliving.shop', '2023-01-01');                  


Questions

1)Retrieve all stations in the "East" region?

select * from stations where location ='East';


2)List all shows hosted by "Vrajesh Hirjee"?

select s.showid,h.hostname,s.showname

from shows s

join hosts h

on s.hostid = h.hostid

where hostname = 'Vrajesh Hirjee';


3)Count the number of awards each show has won?

select s.showid,s.showname,

count(awardid) as Num_of_Awards

from awards a

join shows s

on a.showid = s.showid

group by s.showname,s.showid;


4)Find shows that have partnerships with "Spotify"?


select s.showid,s.showname,p.partnername

from shows s join showpartnerships sp

on s.ShowID = sp.ShowID

join partnerships p

on p.partnershipid = sp.PartnershipID

where partnername = 'spotify';


5)Retrieve hosts who joined before 2010?

select HOSTid,hostname,joindate

from hosts

where year(joindate) < 2010;

6)List the shows and their launch dates in descending order of launch date?


select showid,showname,launchdate

from shows

order by launchdate desc;


7)Find the total count of shows for each host?

select hostid,hostname, sum(showcount) as Total_shows

from hosts

group by hostid,hostname;


8)Show the online presence platforms with their links?

select presenceid,platformname,link 

from onlinepresence;


9)Retrieve the stations in the "South" region launched after 2010?

select stationid,stationname,location,frequency,launchdate

from stations 

where location = 'south'

and year(launchdate) >2010;


10)Rank hosts based on the number of shows they have hosted?

select hostid,hostname,showcount,

rank() over(order by showcount desc) as show_rank

from hosts

order by hostid,showcount desc;



Follow @Thanaselvi C for more updates :
Linked in : https://www.linkedin.com/in/thanaselvi-c-98310211a/


Comments

Post a Comment

Popular posts from this blog

SQL Case Study Finance Analysis

Image
                                                                    Finance Analysis SQL Challenge 4 - Challenge 4 - Finance Analysis   create database Finance_Analysis -- Create the Customers table CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, City VARCHAR(50) NOT NULL, State VARCHAR(2) NOT NULL ); -------------------- -- Populate the Customers table INSERT INTO Customers (CustomerID, FirstName, LastName, City, State) VALUES (1, 'John', 'Doe', 'New York', 'NY'), (2, 'Jane', 'Doe', 'New York', 'NY'), (3, 'Bob', 'Smith', 'San Francisco', 'CA'), (4, 'Alice', 'Johnson', 'San Francisco', 'CA'), (5, 'Michael', 'Lee', 'Los Angeles', 'CA'), (6, 'Jennifer', 'Wang', 'Los Angel...
Read more

SQL Query Order of Execution

Image
                                                  SQL Query Order of Execution SQL SQL - ( Structured Query Language ) is the most wanted tool for everyone who is living in IT Industry ,it like breathing air and water -this both can be like EXCEL & SQL without this 2 we can't do any projects. Lets learn about Order of Execution in SQL Why the order of SQL is Important ? Each and every task there will be an ORDER to reach like If we want to land in GOOD IT JOB with the proper role as Business Analyst or Data Analyst  should be good to have an education criteria with IT KINGS- EXCEL , SQL , POWERBI / TABLEAU / MICRO STRATEGY / STATISTICS / STORY TELLING (communication). As like this same way there will be an ORDER in SQL queries SQL Query Order of Execution Why Order of Execution is Important ? Helps to understand the concept easily Can able to debug query...
Read more

A Day in the Life of a Data Analyst Interviewee

                                                            Data Analyst⌛ Am sharing my interview experience held on  Nov:4:2023✅ As usual : Tell me about your previous project work experience and tools used. ☝SQL : Joins - Left join ,Right join, Inner Join, Outer join Like operators - Like 'A%; What is a Stored Procedure?  A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. Different Types of Indexes in SQL Server  There are various types of indexes in SQL server:  Clustered Index  Non-Clustered Index  Column Store Index  Filtered Index  Hash Index  Unique Index  ☺Power BI - ...
Read more