Create a user-defined function called VEHICLES_BY_VEHICLE_TYPE that receives an

Question

Create a user-defined function called VEHICLES_BY_VEHICLE_TYPE that receives an input parameter of a concatenated make and model and then queries the VEHICLES and SALES_FACTS tables to return the total vehicles sold by that combination. Execute your function for a sample input value of your choosing to demonstrate that it works correctly.

CREATE TABLE Vehicles (

vehicle_Code VARCHAR2(10),

description VARCHAR2(100),

PRIMARY KEY (vehicle_Code));

CREATE TABLE Sales_Facts (

sale_day DATE NOT NULL,

vehicle_Code VARCHAR2(10) NOT NULL,

plan_ID VARCHAR2(10) NOT NULL,

dealer_ID VARCHAR2(5) NOT NULL,

vehicles_sold NUMBER(8,2) NOT NULL,

gross_sales_amt NUMBER(8,2) NOT NULL,

CONSTRAINT PK_Sales_Facts PRIMARY KEY (sale_day, vehicle_Code, plan_ID, dealer_ID),

CONSTRAINT FK_Sale_Day FOREIGN KEY(sale_day) References Times(sale_day),

CONSTRAINT FK_Vehicle_Code FOREIGN KEY(vehicle_Code) References Vehicles(vehicle_Code),

CONSTRAINT FK_Fin_Plan_ID FOREIGN KEY(plan_ID) References Financing_Plans(plan_ID),

CONSTRAINT FK_Dealer_ID FOREIGN KEY(dealer_ID) References Dealerships(dealer_ID));

Explanation / Answer

Sql code:

//user defined function VEHICLES_BY_VEHICLE_TYPE
CREATE OR REPLACE FUNCTION VEHICLES_BY_VEHICLE_TYPE (V_AUTODESC VARCHAR2) //concatenation of make and model
RETURN VARCHAR2
IS vehicles_sold VARCHAR2 (50);
BEGIN
SELECT NVL(SUM (vehicles_sold), 0)
INTO vehicles_sold
FROM Vehicles v, Sales_Facts s
WHERE V.vehicle_Code = S.vehicle_Code AND V.description = V_AUTODESC;
RETURN vehicles_sold; //return the total vehicles sold by that combination
EXCEPTION WHEN OTHERS THEN RETURN 0;
END;


SELECT VEHICLES_BY_VEHICLE_TYPE('TOYOTA COROLLA') VEHICLESOLD FROM DUAL;

Worked on ORACLE

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.