Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question
100%

Retrieval Queries

Consider the following database (Figure 1) that describes a ride sharing scenario. Each ride has a unique ID and is offered by a driver. It has two locations: from and to. We track start and end time in addition to the price of the ride. Multiple passengers might share one ride. The information about which passengers travelled on each ride is tracked in the RIDE PASSENGER table.

Cars are identified by their license plates and are associated with their drivers. Cars can be of different types, e.g., ‘compact’, ‘standard’, ‘premium’, ‘van’, etc.

Hint: Sample data is shown in Figure 2.

 

A. Create an SQL statement to serve the described information needs

(i) Retrieve the name and revenue of the driver with the highest revenue. 

(ii) Retrieve the total price of all journeys for each category of cars according to the number of seats of the cars (not type).

(iii) Retrieve fully-utilized rides, i.e., the number of passengers is the same as the number of seats.

ID NAME
1 Steven Johnson
2 Clark Kent
3 Jenna Stevens
Peter Parker
4
LICENSE_PLATE_NR
A-AA8888
C-YM4444
Q-BB222
ID
2019
2020
2021
ID
995
996
997
998
999
DRIVER
1
2
1
NAME
Barbara Simons
James Jackson
Thomas White
TO
FROM
POI-10 POI-8
POI-30 POI-27
POI-12
POI-32
Amy Rose
Luke Jones
CITY
New York
Washington
c.kent@gmail.com
Washington j.stevens@gmail.com
Texas
p.parker@gmail.com
DRIVER
2
3
1
RIDE ID
2019
2019
2020
2019
2019
DRIVER
EMAIL
MOBILE
s.johnson@gmail.com 123456789
123456788
123456778
123456777
CAR
NR_OF_SEATS
4
8
4
RIDE
START_TIME
2020-08-04 16:11:27
2020-08-05 08:08:08
2020-08-06 12:12:12
PASSENGER
ADDRESS
New York
Washington
Alabama
Ohio
Florida
COLOR
Red
Green
Blue
RIDE_PASSENGER
END_TIME
EMAIL
b.simons@gmail.com
j.jackson@gmail.com
t.thomas@gmail.com
a.rose@gmail.com
l.jones@gmail.com
2020-08-04 16:55:27
2020-08-05 09:09:09
2020-08-06 13:13:13
DRIVING LICENSE
PASSENGER_ID
995
996
996
997
998
TYPE
Premium
Van
Compact
123
234
345
986
PRICE
10
18
6
MOBILE
162836279
305849384
105839048
103940394
17403830284
Transcribed Image Text:ID NAME 1 Steven Johnson 2 Clark Kent 3 Jenna Stevens Peter Parker 4 LICENSE_PLATE_NR A-AA8888 C-YM4444 Q-BB222 ID 2019 2020 2021 ID 995 996 997 998 999 DRIVER 1 2 1 NAME Barbara Simons James Jackson Thomas White TO FROM POI-10 POI-8 POI-30 POI-27 POI-12 POI-32 Amy Rose Luke Jones CITY New York Washington c.kent@gmail.com Washington j.stevens@gmail.com Texas p.parker@gmail.com DRIVER 2 3 1 RIDE ID 2019 2019 2020 2019 2019 DRIVER EMAIL MOBILE s.johnson@gmail.com 123456789 123456788 123456778 123456777 CAR NR_OF_SEATS 4 8 4 RIDE START_TIME 2020-08-04 16:11:27 2020-08-05 08:08:08 2020-08-06 12:12:12 PASSENGER ADDRESS New York Washington Alabama Ohio Florida COLOR Red Green Blue RIDE_PASSENGER END_TIME EMAIL b.simons@gmail.com j.jackson@gmail.com t.thomas@gmail.com a.rose@gmail.com l.jones@gmail.com 2020-08-04 16:55:27 2020-08-05 09:09:09 2020-08-06 13:13:13 DRIVING LICENSE PASSENGER_ID 995 996 996 997 998 TYPE Premium Van Compact 123 234 345 986 PRICE 10 18 6 MOBILE 162836279 305849384 105839048 103940394 17403830284
DRIVER
ID INT
NAME VARCHAR(100)
CITY VARCHAR(50)
EMAIL VARCHAR(50)
MOBILE VARCHAR(20)
DRIVING LICENSE VARCHAR(10)
Indexes
RIDE
ID INT
DRIVER INT
FROM VARCHAR(100)
TO VARCHAR(100)
START_TIME DATETIME
END_TIME DATETIME
◇ PRICE INT
Indexes
H+
H-
CAR
LICENSE PLATE_NR VARCHAR(20)
DRIVER INT
-NR_OF_SEATS INT
>COLOR VARCHAR(10)
TYPE VARCHAR(10)
Indexes
PASSENGER
ID INT
NAME VARCHAR(100)
ADDRESS VARCHAR(100)
EMAIL VARCHAR(100)
MOBILE VARCHAR(50)
Indexes
RIDE_PASSENGER
RIDE_ID INT
PASSENGER_ID INT
Indexes
Figure 1: Database schema for question 1
Transcribed Image Text:DRIVER ID INT NAME VARCHAR(100) CITY VARCHAR(50) EMAIL VARCHAR(50) MOBILE VARCHAR(20) DRIVING LICENSE VARCHAR(10) Indexes RIDE ID INT DRIVER INT FROM VARCHAR(100) TO VARCHAR(100) START_TIME DATETIME END_TIME DATETIME ◇ PRICE INT Indexes H+ H- CAR LICENSE PLATE_NR VARCHAR(20) DRIVER INT -NR_OF_SEATS INT >COLOR VARCHAR(10) TYPE VARCHAR(10) Indexes PASSENGER ID INT NAME VARCHAR(100) ADDRESS VARCHAR(100) EMAIL VARCHAR(100) MOBILE VARCHAR(50) Indexes RIDE_PASSENGER RIDE_ID INT PASSENGER_ID INT Indexes Figure 1: Database schema for question 1
Expert Solution
steps

Step by step

Solved in 4 steps

Blurred answer
Follow-up Questions
Read through expert solutions to related follow-up questions below.
Follow-up Question

(iv) Retrieve the names and cities of all drivers who have cars of type ‘Premium’

(v) Show the cities and the number of drivers, where at least 1000 drivers operate.

(i.e., do not include cities where less than 1000 drivers operate).

(vi) Retrieve the names and emails of passengers who have not booked any ride yet

Solution
Bartleby Expert
SEE SOLUTION
Knowledge Booster
Database Environment
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education