b) Implement a stored PL/SQL function that finds a supplier who supplies the cheapest (lowest supply cost) or the dearest (highest supply cost) for a part specified by a user. The function should obtain the part key as its formal parameter, and it should return a string of values consisting of the supplier key, supplier name, and the cost (cheapest and dearest). Example of the string output are as follow: Supplier with cheapest cost: 1344, Supplier #000001344, $172.09. Supplier with dearest cost: 2882, Supplier #000002882, $791.07. Supplier with cheapest cost: 770, Supplier #000000770, $74.55. Supplier with dearest cost: 2308, Supplier #000002308, $826.35. Next use the function to list the string output as specified above for part numbers 3753, 43064, 57574 and 60000 using a select statement.

b) Implement a stored PL/SQL function that finds a supplier who supplies the cheapest (lowest supply cost) or the dearest (highest supply cost) for a part specified by a user. The function should obtain the part key as its formal parameter, and it should return a string of values consisting of the supplier key, supplier name, and the cost (cheapest and dearest). Example of the string output are as follow: Supplier with cheapest cost: 1344, Supplier #000001344, $172.09. Supplier with dearest cost: 2882, Supplier #000002882, $791.07. Supplier with cheapest cost: 770, Supplier #000000770, $74.55. Supplier with dearest cost: 2308, Supplier #000002308, $826.35. Next use the function to list the string output as specified above for part numbers 3753, 43064, 57574 and 60000 using a select statement.

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

Similar questions

Write a PL/SQL function that accepts employee ID as input and returns employee first name , last name and email . Also, write an anonymous block with the function call Use the employees object of Oracle Live SQL HR Scheme, Employees object descriptions is given here. Column Null? Туре NOT EMPLOYEE ID FIRST NAME NULL NUMBER(6,0) VARCHAR2(20) NOT LAST NAME NULL VARCHAR2(25) NOT EMAIL NULL VARCHAR2(25) PHONE NUMBER VARCHAR2(20) NOT HIRE DATE NULL DATE NOT NULL JOB ID VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ ID NUMBER(6,0) DEPARTMENT ID NUMBER(4,0)
Develop a two small PL/SQL programs. 1. Enter students ID from the keyboard. The program then finds student's first name, last name and phone, stores them in variables and prints them on the screen. 2. Similar to the above, only this time the program will receive student's first name only (instead of student's ID)Then program finds full name and address and prints it on the screen. For both programs please implement NO_DATA_FOUND and TOO_MANY_ROWS exceptions. (please refer to chapter 8 in PL/SQL by Example book)For number 2 program, display a message "Possibly you have two or more students with the first name ...." in TOO_MANY_ROWS exception block.
1. Write a PL/SQL program using an explicit cursor that displays all patients PLCO_ID numbers from the colo_proc_data table on the screen if the patients’ procedure is biopsy, the procedure is staging, interval status of linked cancer is never screened, the procedure type is invasive Scopes, biopsies, & surgeries, the procedure type is Lymphadenectomy/Lymph Node Sampling, and the procedure result is abnormal, confirmed metastasis.
INFO 2303 Database Programing Assignment # : PL/SQL Procedure & Function Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Write a function that takes patient date of birth. If the patient born after 30th June 1990 then he/she will be required to set appointment for immunization. Call this function from anonymous block to allow the user to enter the patient ID. Expected output: Enter the patient ID: 168 The status of X-immunization : REQUIRED
Write a function called ValidSupplier(SupplierID) that takes as its only input a SupplierID. If the supplier exists in the database your function should return True otherwise it should return False For example: Test Result print(ValidSupplier(13)) True
Develop a two small PL/SQL programs. 1. Enter instructor ID from the keyboard. The program then finds instrutor's first name, last name and phone, stores them in variables and prints them on the screen. USE WHILE LOOP to print all instructors while selecting them one by one in the loop. 2. Similar to the above, only this time the program will receive student's first name only (instead of ID)Then program finds full name and address and prints it on the screen (work with STUDENT table here) For both programs please implement NO_DATA_FOUND and TOO_MANY_ROWS exceptions. (please refer to chapter 8 in PL/SQL by Example book)For number 2 program, display a message "Possibly you have two or more students with the first name ...." in TOO_MANY_ROWS exception block. 1* select table_name from user_tablesSQL> desc studentName Null? Type----------------------------------------- -------- ----------------------------STUDENT_ID NOT NULL NUMBER(8)SALUTATION VARCHAR2(5)FIRST_NAME…
Write a VBA program that uses ADO to access the Sales Orders database and displays the ID, first name, last name, city, and state of any requested customer. The program first requests the user to enter the desired customer ID via an input box. That customer’s information is then displayed as follows (e.g. for CustomerID = 7): Hint: The SQL string should be a concatenation of a literal part “Select …from … where … = ” and a variable, say, RequestedID.
Computer Science This question is related to pl/SQL:- Using a Cursor in a Package In this assignment, you work with the sales tax computation because the Brewbean's lead programmer expects the rates and states applying the tax to undergo some changes. The tax rates are currently stored in packaged variables but need to be more dynamic to handle the expected changes. The lead programmer has asked you to develop a package that holds the tax rates by state in a packaged cursor. The BB TAX table is updated as needed to reflect which states are applying sales tax and at what rates. This package should contain a function that can receive a two-character state abbreviation (the shopper's state) as an argument, and it must be able to find a match in the cursor and return the correct tax rate. Use an anonymous block to test the function with the state value NC.
Question 18 Write a function that takes a "CustomerID" and returns a JSON list of product names and their total sales each year to that customer. Show how would you call this function?
1. Write a SQL function that accepts a principal mortgage amount, an annual percentage rate (APR), and the number of years a mortgage will be paid back over. Calculate the associated monthly mortgage payment according to the following annuity formula: ? = ?( ? + (? /((1+?)^? ( −1 )) where: A = Monthly Payment Amount P = Principle (Initial) Mortgage Amount i = APR / 12 = Monthly Interest Rate n = years * 12 = Total Number of Payments Be sure to specify that data types of the input arguments have enough significant digits to account for realistic mortgage amounts (as large as in the millions of dollars) and APRs (down to the sixth decimal place will be sufficient).
Write a function called ValidSupplier(SupplierID) that takes as its only input a SupplierID. If the supplier exists in the database your function should return True otherwise it should return False
Write a PL/SQL Function that accepts 4 integers, 2 representing the homeTeamID and visitorTeamID, and the other 2 representing the score for each team during a game : htscore and vtscore The function will determine which team won the match and return the teamID of the winning team. You can achive this by comparing htscore > or < then vtscore.. if htscore is bigger then return hteamID or else return vteamID. If the game was a tie (the 2 scores are the same value) return a 0.