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

User Satisfaction team wants to send monthly summaries for each user. They need the following details with the user ID:

  • The last day of the month when the users traveled most recently
  • One week after the last day of the month when the users traveled most recently

You need to return a three-column output with USER_IDLAST_TRAVEL_MONTH and NOTIFICATION. LAST_TRAVEL_MONTH should be calculated using the MAX of the LAST_DAY of the TRAVEL_END_TIME field. Similarly, NOTIFICATION should be calculated with DATE_ADD function to add one week.

 

Query monthly summaries for each user.

 

Make sure to add one week to the last day of the month using the INTERVAL keyword in the DATE_ADD() function.

In the CARS table, all the cars in the Instant Ride system are kept with the license plate, model
and year:
CAR_ID
1001
1002
1003
1004
CARS Table
TRAVEL ID
5002
5001
5004
5006
5007
5008
5009
5010
TRAVEL START TIME
2015-10-01 04:04
2015-10-01 08:57:33
2016-10-01 13:31:20
2015-10-02 08:MAR
2015-10-02 16:31M
2015-10-03 19:12:14
2015-10-05 16063
2015-10-03 17:17:12
2015-10-05 21:16:48
2019-10-03 23:21:40
CAR PLATE
TRAVELS Table
BB-542-AB
BB-883-EE
BB-451-ZN
88-189-MM
Finally, the transactions of the rides are stored in the TRAVELS table. For each travel, start and
end time with location are stored. In addition, the involved driver, car and user are listed for each
drive. Price and discount information are also available in the database:
TRAVEL END TIME
2010-10-01 04:14:10
2015-10-01 08:12:38
2010-10-01 14:10
2015-10-470158
2011-10-02 14:10
2019-10-43 19:28:45
2019-10-05 16:08:56
2019-10-43 17:37:42
2019-10-05 21:26:18
2019-10-43 23:39:10
TRAVEL START LOCATION
1514 York Road
47 Church Street
2 Windsor
CAR MODEL
2 Queensway
50 Mina
39 Park Road
37 The Drive
77 Mil Road
16 Church Road
TOYOTA PRIUS
TESLA MODEL 3
TOYOTA AURIS
MERCEDES E200
TRAVEL END LOCATION
Church Lane
68 High Street
35 W
27 Mainan
24 Milane
23 any
91 West Street
17 Stanley Road
724 Spring Road
30 North Road
TRAVEL PRICE
15.44
12.37
14.81
4.41
25.12
13.55
25.62
DRIVER ID
2001
2001
2007
2003
2001
2003
2002
CAR YEAR
2001
2001
2003
2018
2019
2019
2019
CAR ID USER ID
100
1008
1001
1003
1002
1004
1003
1005
1002
2002
3001
3007
3007
3005
3001
3005
3003
TRAVEL DISCOUNT
NULL
MAL
NULL
0.13
MAI
0.10
0.14
0.25
MAL
You are assigned as the database administrator to collect and manage transactional data of the
Instant Ride operations. Your main task is to create SQL scripts to help other teams to retrieve the
requested data. In the following activities, you will create the scripts, run against the database and
send the result to the corresponding teams.
Transcribed Image Text:In the CARS table, all the cars in the Instant Ride system are kept with the license plate, model and year: CAR_ID 1001 1002 1003 1004 CARS Table TRAVEL ID 5002 5001 5004 5006 5007 5008 5009 5010 TRAVEL START TIME 2015-10-01 04:04 2015-10-01 08:57:33 2016-10-01 13:31:20 2015-10-02 08:MAR 2015-10-02 16:31M 2015-10-03 19:12:14 2015-10-05 16063 2015-10-03 17:17:12 2015-10-05 21:16:48 2019-10-03 23:21:40 CAR PLATE TRAVELS Table BB-542-AB BB-883-EE BB-451-ZN 88-189-MM Finally, the transactions of the rides are stored in the TRAVELS table. For each travel, start and end time with location are stored. In addition, the involved driver, car and user are listed for each drive. Price and discount information are also available in the database: TRAVEL END TIME 2010-10-01 04:14:10 2015-10-01 08:12:38 2010-10-01 14:10 2015-10-470158 2011-10-02 14:10 2019-10-43 19:28:45 2019-10-05 16:08:56 2019-10-43 17:37:42 2019-10-05 21:26:18 2019-10-43 23:39:10 TRAVEL START LOCATION 1514 York Road 47 Church Street 2 Windsor CAR MODEL 2 Queensway 50 Mina 39 Park Road 37 The Drive 77 Mil Road 16 Church Road TOYOTA PRIUS TESLA MODEL 3 TOYOTA AURIS MERCEDES E200 TRAVEL END LOCATION Church Lane 68 High Street 35 W 27 Mainan 24 Milane 23 any 91 West Street 17 Stanley Road 724 Spring Road 30 North Road TRAVEL PRICE 15.44 12.37 14.81 4.41 25.12 13.55 25.62 DRIVER ID 2001 2001 2007 2003 2001 2003 2002 CAR YEAR 2001 2001 2003 2018 2019 2019 2019 CAR ID USER ID 100 1008 1001 1003 1002 1004 1003 1005 1002 2002 3001 3007 3007 3005 3001 3005 3003 TRAVEL DISCOUNT NULL MAL NULL 0.13 MAI 0.10 0.14 0.25 MAL You are assigned as the database administrator to collect and manage transactional data of the Instant Ride operations. Your main task is to create SQL scripts to help other teams to retrieve the requested data. In the following activities, you will create the scripts, run against the database and send the result to the corresponding teams.
Scenario and Database Model: Instant Ride
InstantRide is the new ride sharing application in the city and it has just started its operations.
With the help of the Instant Ride mobile application, the users request a ride with their location.
Drivers and cars are assigned to the request; and then the driver picks up the user to ride their
requested location. Information for the users, drivers and cars are stored in the database as well as
the travel transactions.
In the USERS table, information for the users are stored with their first name, last name and email:
USER_ID
3001
3002
3003
3004
3005
3006
3007
3008
USERS Table
2001
2002
2003
2004
USER_FIRST_NAME
Willie
Justin
Anthony
Ece
Jack
Ryan
Nursin
Sarah
Bobby
Randy
Jose
DRIVERS Table
Nursin
Butler
Howard
In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license
number and check and rating information:
Walker
DRIVER_ID DRIVER_FIRST_NAME DRIVER LAST NAME DRIVER DRIVING LICENSE_ID DRIVER_START_DATE DRIVER DRIVING LICENSE CHECKED DRIVER_RATING
1874501
1953853
Yilmaz
USER_LAST_NAME
1735487
Hill
1734747
Collins
Yilmaz
Price
Griffin
Clark
Thomas
Yilmaz
2019-09-12
2019-09-09
2019-09-15
2019-08-15
1
1
USER_EMAIL
j.hill@xmail.com
r.collins@xmail.com
n.atak@gmail.com
s.price@xmail.com
b.griffin@xmail.com
r.clark@xmail.com
j.thomas@xmail.com
n.yilmaz@xmail.com
1
1
4.8
3.5
0
Transcribed Image Text:Scenario and Database Model: Instant Ride InstantRide is the new ride sharing application in the city and it has just started its operations. With the help of the Instant Ride mobile application, the users request a ride with their location. Drivers and cars are assigned to the request; and then the driver picks up the user to ride their requested location. Information for the users, drivers and cars are stored in the database as well as the travel transactions. In the USERS table, information for the users are stored with their first name, last name and email: USER_ID 3001 3002 3003 3004 3005 3006 3007 3008 USERS Table 2001 2002 2003 2004 USER_FIRST_NAME Willie Justin Anthony Ece Jack Ryan Nursin Sarah Bobby Randy Jose DRIVERS Table Nursin Butler Howard In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license number and check and rating information: Walker DRIVER_ID DRIVER_FIRST_NAME DRIVER LAST NAME DRIVER DRIVING LICENSE_ID DRIVER_START_DATE DRIVER DRIVING LICENSE CHECKED DRIVER_RATING 1874501 1953853 Yilmaz USER_LAST_NAME 1735487 Hill 1734747 Collins Yilmaz Price Griffin Clark Thomas Yilmaz 2019-09-12 2019-09-09 2019-09-15 2019-08-15 1 1 USER_EMAIL j.hill@xmail.com r.collins@xmail.com n.atak@gmail.com s.price@xmail.com b.griffin@xmail.com r.clark@xmail.com j.thomas@xmail.com n.yilmaz@xmail.com 1 1 4.8 3.5 0
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

Blurred answer
Knowledge Booster
Binary numbers
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