Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Part 1.2 SQL (Total: 23 + 10 Bonus Points) Consider the following schema and example instance. The SQL statements that you write should return the

image text in transcribed

image text in transcribedimage text in transcribed

image text in transcribed

image text in transcribedimage text in transcribed

Part 1.2 SQL (Total: 23 + 10 Bonus Points) Consider the following schema and example instance. The SQL statements that you write should return the correct result for every possible instance of the schema! City name New York Cincinnati Columbus Chicago state NY OH OH IL Bus company number price fromCity toCity Whitedog 13 210 New York Cincinnati Whitedog 102 56 Columbus Cincinnati Whitedog 2 115 Chicago Cincinnati Schedule company bnum departure Time Whitedog 13 2020-01-12 08:13 Whitedog 102 2020-01-13 12:15 Kbus 2 2020-01-12 10:30 Kbus 2 2020-01-13 11:30 arrival Time 2020-01-12 17:20 2020-01-13 13:30 2020-01-12 15:20 2020-01-12 16:20 Hints: Attributes with black background are the primary key attributes of a relation Attributes fromCity and toCity of relation Bus are both foreign keys to attribute name of relation City. Attributes company and bnum of relation Schedule are foreign keys to company and number of Bus. Question 1.2.1 (3 Points) Write a query that returns cities for which both incoming and outgoing connections exists (toCity and fromCity attributes of relation Bus). Make sure that each such city is only returned once by the query. Question 1.2.2 (3 Points) Write an SQL query that returns the bus number, destination, and arrival time of "Whitedog' buses departing from Chicago' on Jan 2020. You can assume that the data type of the arrivalTime and departure Time columns are of type VARCHAR. Question 1.2.3 (4 Points) Write an SQL query that returns the cheapest price for round trips between New York and Cincinnati (a bus ride from New York to Cincinnati plus a bus ride from Cincinnati to New York). For this query you can ignore the actual scheduled times of the buses. Question 1.2.4 (3 Points) Write an SQL query that returns companies that operate at least 5 bus lines (use the number column). Question 1.2.5 (4 Points) Write an SQL query that returns the name of cities which are neither the destination nor start point (fromCity or toCity) of any bus route

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Big Data Systems A 360-degree Approach

Authors: Jawwad ShamsiMuhammad Khojaye

1st Edition

0429531575, 9780429531576

More Books

Students also viewed these Databases questions