What is the most popular bundle in California? What are the most popular single
ID: 3720389 • Letter: W
Question
What is the most popular bundle in California?
What are the most popular single service pricings?
What city has the most Comcast customers?
Which Extra Service is the most expensive?
Property Propenty Address VARCHAR25) Property Chy VARCHAR15 Propery State VARCHAR2) Single Service Pricing SinglaServiceD INT Phone PhonelD VARCHARI5) TV-TO VARCHAR(5) Bundle Bund eID INT Bunde Price INT Duration INT Bunde A BundlelD INT Bundle B BundlelD INT Indexes Bundle A Bundle B Phone TV BundeID INT Bundle DNT First Name VARCHAR 20 Last-Name VARCHAR(46) Address VARCHAR(45) ? ? VARCHAR(1 Stato VARCHAR2 ZIP VAHCHAH(5) Phone VARCHARI1 PhonelD INT P TVID INT IntemetID INT Price DECIMAL 10,2 Description VARCHAR(1024) ?package VARCHAR( 255) O Channels INT Cescription VARCHAR(1024) Free HD DECIMAL(T) Price DECIMALI Description VARCHARI1024) B IID I Payment ing Date DATETIME Due Date DATETIME Contract ContractiID INT Start Date DATETIME End Date DATETIME BL BIID INT Contract ContradtiD1 INT Bil Conract ContradID INT Singie Sarvice Prioing SngleServicelD VARCHAR 18 Extra pelD INT BII BID INT Extra Services TypelD VARCHAR11) Fee DEC MAL(10.2)Explanation / Answer
Below are the sql scripts that created based on the physical structure of the tables shown in the question
Question 1
What is the most popular bundle in California?
SQl CODE
SELECT CASE
WHEN BundleACount > BundleBCount THEN 'Bundle A is the most popular in california'
WHEN BundleBCount > BundleACount THEN 'Bundle B is the most popular in california'
ELSE ''
END
FROM
(SELECT
COUNT(COALESCE(ba.bundleId,0)) as BundleACount,
COUNT(COALESCE(bb.bundleid,0)) as BundleBCount
from bundle as b
inner join BundleA as ba on ba.bundleid = b.bundle_a_bundleID
inner join BundleB as bb on bb.bundleid = b.bundle_b_bundleID
inner join contract as c on c.bundle_bundleID = b.bundleid
inner join bill as bi on bi.contract_contractid = c.contractid
inner join property as p on p.account_number = bi.property_account_number
inner join customer as c on c.customerid = p.customer_customerid
where c.city = 'california') A
Question 2
What are the most popular single service pricings?
SQL CODE
SELECT CASE
WHEN PhoneServiceCount > TVServiceCount AND PhoneServiceCount > InternetServiceCount
THEN 'Phone Service is the most popular service'
WHEN TVServiceCount > PhoneServiceCount AND TVServiceCount > InternetServiceCount
THEN 'TV Service is the most popular service'
WHEN InternetServiceCount > PhoneServiceCount AND InternetServiceCount > TVServiceCount
THEN 'Internet Service is the most popular service'
ELSE ''
END
FROM
(SELECT
COUNT(COALESCE(pn.phoneid,0)) as PhoneServiceCount,
COUNT(COALESCE(tv.tvid,0)) as TVServiceCount,
COUNT(COALESCE(i.internetid,0)) as InternetServiceCount
from SingleServicePricing as s
inner join Phone as pn on pn.phoneid = s.phone_phoneid
inner join TV as tv on tv.tvid = s.tv_tvid
inner join Internet as i on i.internetid = s.internet_internetid
inner join contract as c on c.SingleServicePricing_singleserviceid = s.singleserviceid
inner join bill as bi on bi.contract_contractid = c.contractid
inner join property as p on p.account_number = bi.property_account_number
inner join customer as c on c.customerid = p.customer_customerid) A
Question 3
What city has the most Comcast customers?
SQL CODE
SELECT
COUNT(COALESCE(c.city,0)) as cityCount,
c.city as City,
'City with Maximum ComCast' As Description
from SingleServicePricing as s
inner join Phone as pn on pn.phoneid = s.phone_phoneid
inner join TV as tv on tv.tvid = s.tv_tvid
inner join Internet as i on i.internetid = s.internet_internetid
inner join contract as c on c.SingleServicePricing_singleserviceid = s.singleserviceid
inner join bill as bi on bi.contract_contractid = c.contractid
inner join property as p on p.account_number = bi.property_account_number
inner join customer as c on c.customerid = p.customer_customerid
group by c.city
having COUNT(c.city) =
(select MAX(cityCount) from
(SELECT
COUNT(COALESCE(c.city,0)) as cityCount,
c.city as City
from SingleServicePricing as s
inner join Phone as pn on pn.phoneid = s.phone_phoneid
inner join TV as tv on tv.tvid = s.tv_tvid
inner join Internet as i on i.internetid = s.internet_internetid
inner join contract as c on c.SingleServicePricing_singleserviceid = s.singleserviceid
inner join bill as bi on bi.contract_contractid = c.contractid
inner join property as p on p.account_number = bi.property_account_number
inner join customer as c on c.customerid = p.customer_customerid
group by c.city as b))
Question 4
Which Extra Service is the most expensive?
SQL CODE
SELECT
TypeId,
Description,
Fee
From ExtraService
Where Fee = (SELECT MAX(FEE) As COST FROM ExtraService)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.