UC24-CS4092-HW2

TiepTran-HW2.sql

--Q1
((SELECT bus."fromCity" FROM bus)
INTERSECT
(SELECT bus."toCity" FROM bus))
INTERSECT
(SELECT city.name FROM city)

-- Q2
SELECT b.number, b."toCity", s."arrivalTime"
FROM Bus as b
JOIN Schedule as s ON b.company = s.bcompany AND b.number = s.bnum
WHERE b.company = 'Kbus' AND b."fromCity" = 'Cincinnati'
AND s."departureDate" >= date('2024-01-01') AND s."departureDate" < date('2024-02-01')

-- Q3
SELECT b."fromCity" AS departureCity, b.number AS bnum, s."departureTime" AS departureTime
FROM Bus AS b
INNER JOIN Schedule AS s 
ON b.company = s.bcompany AND b.number = s.bnum
ORDER BY departureCity ASC, bnum ASC, departureTime ASC

-- Q4
SELECT MIN(price) AS cheapest_price
FROM (
    SELECT (b1.price + b2.price) AS price
    FROM Bus AS b1
    JOIN Bus AS b2 ON b1."toCity" = b2."fromCity" AND b1."fromCity" = b2."toCity"
    WHERE b1."fromCity" = 'Cincinnati' AND b1."toCity" = 'New York'
) AS round_trip_prices

-- Q5
(SELECT DISTINCT b.company
FROM Bus AS b)
EXCEPT
(SELECT b.company
FROM Bus AS b
WHERE (b."fromCity" = 'Cincinnati' and b."toCity" = 'Chicago') 
 or (b."fromCity" = 'Chicago' and b."toCity" = 'Cincinnati'))

-- Q6 
WITH BusWithState as (
	SELECT Bus.company, Bus.number, Bus."fromCity", Bus."toCity", c1.state as fromState, c2.state as toState
	FROM Bus
	INNER JOIN City as c1 ON c1.name = Bus."fromCity"  
	INNER JOIN City as c2 ON c2.name = Bus."toCity"
)

SELECT City.state, count(case when BusWithState.fromState = BusWithState.toState then 1 end) 
FROM City
LEFT JOIN BusWithState ON City.name = BusWithState."fromCity"
GROUP BY City.state

-- Q7
with 
	oneBusLine as (
		select b.price, b."fromCity", b."toCity"
		from Bus as b),
	
	twoBusLine as (
		select (b.price + obl.price) as twoLinePrice, obl."fromCity" as ogDest, 
			    b."toCity" as twoLineDest
		from Bus as b
		join oneBusLine as obl on b."fromCity" = obl."toCity"),
	
	threeBusLine as (
		select (b.price + tbl.twoLinePrice) as threeLinePrice, ogDest, b."toCity" as threeLineDest
		from Bus as b
		join twoBusLine as tbl on b."fromCity" = twoLineDest)
	
-- select * from threeBusLine
	
select min(underThreeBusLine.price) as cheapestRoute
from 
	(
		select * from oneBusLine
		union
		select * from twoBusLine
		union
		select * from threeBusLine
	) as underThreeBusLine
where underThreeBusLine."fromCity" = 'Cincinnati' and underThreeBusLine."toCity" = 'Philadelphia'


-- Q8
with 
	oneBusLine as (
		select b.price, b."fromCity", b."toCity", b.company, b.number, 
			   s1."departureDate", s1."departureTime", s1."arrivalDate", s1."arrivalTime"
		from Bus as b
		join Schedule as s1 on s1.bcompany = b.company and s1.bnum = b.number),
	
	twoBusLine as (
		select (b.price + obl.price) as twoLinePrice, obl."fromCity" as ogDest, b."toCity" as twoLineDest, 
				b.company, b.number, s2."departureDate", s2."departureTime", s2."arrivalDate", s2."arrivalTime"
		from Bus as b
		join oneBusLine as obl on b."fromCity" = obl."toCity"
	    join Schedule as s2 on s2.bcompany = b.company and s2.bnum = b.number
	    where obl."arrivalDate" < s2."departureDate" 
		      or (obl."arrivalTime" <= s2."departureTime" and obl."arrivalDate" = s2."departureDate")),
	
	threeBusLine as (
		select (b.price + tbl.twoLinePrice) as threeLinePrice, ogDest, b."toCity" as threeLineDest,
				b.company, b.number, s3."departureDate", s3."departureTime", s3."arrivalDate", s3."arrivalTime"
		from Bus as b
		join twoBusLine as tbl on b."fromCity" = twoLineDest
		join Schedule as s3 on s3.bcompany = b.company and s3.bnum = b.number
	    where tbl."arrivalDate" < s3."departureDate" 
		     or (tbl."arrivalTime" <= s3."departureTime" and tbl."arrivalDate" = s3."departureDate"))
	
-- select * from twoBusLine

select min(underThreeBusLine.price) as cheapestRoute
from 
	(
		select * from oneBusLine
		union
		select * from twoBusLine
		union
		select * from threeBusLine
	) as underThreeBusLine
where underThreeBusLine."fromCity" = 'Cincinnati' and underThreeBusLine."toCity" = 'Philadelphia' 
and ( underThreeBusLine."arrivalDate" > underThreeBusLine."departureDate" 
	 or underThreeBusLine."arrivalTime" - underThreeBusLine."departureTime" <= INTERVAL '14 hours')