Question
*** SQL Databases. Please be concise and show full explanation. Please help. Show your own work. Has to run on Oracle SQL Developer *** Schema
*** SQL Databases. Please be concise and show full explanation. Please help. Show your own work. Has to run on Oracle SQL Developer ***
Schema down below, tables, and with the schema: Write a stored procedure to display the number of times Dog X was examined at Location Y. X and Y are to be input parameters to the stored procedure. X specifies the dogName and Y specifies the locName. It is expected that your stored procedure will work for any instances of the above schema. Demonstrate that the stored procedure works by trying it on the tables below using all nine possible combinations of X and Y values.
Create tables:
CREATE TABLE Veterinarians ( vid INTEGER, vetName CHAR(15), PRIMARY KEY (vid));
CREATE TABLE Dogs ( did INTEGER, dogName CHAR(15), PRIMARY KEY (did));
CREATE TABLE Examine ( vid INTEGER, did INTEGER, lid INTEGER, fee REAL, PRIMARY KEY (vid, did, lid), FOREIGN KEY (vid) REFERENCES Veterinarians, FOREIGN KEY (did) REFERENCES Dogs);
CREATE TABLE Location ( lid INTEGER, locName CHAR(15), PRIMARY KEY (lid));
The query:
CREATE OR REPLACE PROCEDURE DogLocation (p_dogName VARCHAR2, p_locName VARCHAR2), (cur OUT SYS_REFCURSOR) AS BEGIN OPEN cur FOR SELECT d.dogName, Count(l.locName) AS NoOfTimes FROM Dogs d LEFT OUTER JOIN Examine e ON d.did=e.did LEFT OUTER JOIN Location l ON e.lid=l.lid WHERE d.dogName=p_dogName AND l.locName = p_locName; END; /
--Procedure DogLocation gets compiled, however, with this Error--
How would you go about fixing this small problem? Has to run on Oracle SQL Developer. Please show sample output to show it works.
Veterinarians (vid: Integer, vetName: String), Examine (vid: Integer, did: Integer, lid: Integer, fee: Real); Dogs (did: Integer, dogName: String); Location (lid: Integer, locName: String); Use the following instances Veterinarians vetName Alice vid 112 211 ar Jim Dogs did 324 582 731 dogName Fido Tiger Examine did 324 731 324 582 731 324 582 731 582 582 lid 1001 1003 1001 1001 1002 1001 1002 fee 10 20 30 50 35 25 35 20 25 vid 112 112 112 211 211 211 211 211 1001 Location lid 1001 1002 1003 locName St. Cloud Minneapolis DuluthStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started