Answered: The Driver Relationship team wants to…

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

See similar textbooks

Task 2:

The Driver Relationship team wants to arrange workshops and education materials to the drivers. However, the team wants to create clusters of the drivers based on their experience in InstantStay. To collect these detail, you will need to create a SQL function called DRIVER_STATUS to determine the level of the driver as follows:

MASTER: more than 4 travels
PRO: more than 2 travels
ROOKIE: 2 or less travels

In addition, run the function to verify it works as expected and send them back the driver levels.

Task: Create the DRIVER_STATUS function to create clusters of drivers based on experience.

DRIVER_ID
DRIVER_FIRST_NAME DRIVER_LAST_NAME DRIVER_DRIVING_LICENSE_ID
DRIVER_START_DATE DRIVER_DRIVING_LICENSE_CHECKED
DRIVER_RATING
2001
Willie
Butler
1874501
2019-09-12
1
4.4
2002
Justin
Howard
1953853
2019-09-09
1
4.8
2003
Anthony
Walker
1735487
2019-09-15
1
3.5
2004
Ece
Yilmaz
1734747
2019-08-15
1

Instructions Page 2 of 4
query.sql
1 DELIMITER <
2 CREATE FUNCTION DRIVER_STATUS(int DRIVER_ID)
3 RETURNS VARCHAR
Task 2:
4
5 BEGIN
</>
6 DECLARE
7 //to store number of travels
The Driver Relationship team wants to arrange workshops and education materials to the
drivers. However, the team wants to create clusters of the drivers based on their
8 Count int;
experience in InstantStay. To collect these detail, you will need to create a SQL function
9 //to store level
called DRIVER STATUS to determine the level of the driver as follows:
10 Level VARCHAR;
11
• MASTER: more than 4 travels
12 //Selects according to passed value
13 SELECT COUNT (*) INTOO Level FROM TRAVEL WHERE DRIVER_ID = DRIVERID;
• PRO: more than 2 travels
14
15 //returns level
16 IF( Count > 4) THEN
• ROOKIE: 2 or less travels
17 RETURN "MASTER";
In addition, run the function to verify it works as expected and send them back the driver
18 ELSE IF (Count > 2) THEN
levels.
10 DETUDN UDDOL
Task
9 SQL Viewer
Create the DRIVER_STATUS function to create clusters of drivers based on
experience.
0.00
out of
10.00
O out of 1 checks passed. Review the results below for more details.
ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to
your MYSQL server version for the right syntax to use near 'int DRIVER_ID) RETURNS VARCHAR BEGIN
Checks
DECLARE //to store number of travels Cou' at line 1
SQL Database Test • Incomplete
Create the DRIVER_STATUS function

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

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