6. Study the following block, and answer the questions below: (a) What are declared in the package specification? (b) What information can you get from the function ck.tax.pp? (c) What is the name of the table referred in the block? (d) Create a PL/SQL block that uses the function. 1 CREATE OR REPLACE PACKAGE tax_pkg IS CURSOR peur_tax IS SELECT state, taxrate FROM bb_tax; FUNCTION ck_tax_pp 3 4 (p_state IN CHAR) RETURN NUMBER; END; 10 CREATE OR REPLACE PACKAGE BODY tax_pkg IS FUNCTION ck_tax_pp 11 12 13 14 (p_state IN CHAR) 15 RETURN NUMBER 16 IS lv_tax_num NUMBER ( 3, 3) := 0; 17 18 BEGIN 19 FOR pcur_rec IN pcur_tax LOOP IF pcur_rec.state = p_state THEN lv_tax_num := pcur_rec.taxrate; RETURN lv_tax_num; 20 21 22 23 EXIT; 24 END IF; 25 END LOOP; END; END; 26 27

6. Study the following block, and answer the questions below: (a) What are declared in the package specification? (b) What information can you get from the function ck.tax.pp? (c) What is the name of the table referred in the block? (d) Create a PL/SQL block that uses the function. 1 CREATE OR REPLACE PACKAGE tax_pkg IS CURSOR peur_tax IS SELECT state, taxrate FROM bb_tax; FUNCTION ck_tax_pp 3 4 (p_state IN CHAR) RETURN NUMBER; END; 10 CREATE OR REPLACE PACKAGE BODY tax_pkg IS FUNCTION ck_tax_pp 11 12 13 14 (p_state IN CHAR) 15 RETURN NUMBER 16 IS lv_tax_num NUMBER ( 3, 3) := 0; 17 18 BEGIN 19 FOR pcur_rec IN pcur_tax LOOP IF pcur_rec.state = p_state THEN lv_tax_num := pcur_rec.taxrate; RETURN lv_tax_num; 20 21 22 23 EXIT; 24 END IF; 25 END LOOP; END; END; 26 27

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

Please answer as many questions as you can!
b) Consider the relational table PARTSUPP of the TPCHR sample database. Find SELECT statements that will use the index in the ways specified in the questions (i) to (iv) below. The values used to create the query such that the queries can meet the specified criteria is up to you, but the values must be sensible. i. ii. iii. iv. Execution of the first SELECT statement must traverse the index vertically then horizontally and it must not access the relational table PARTSUPP. Execution of the second SELECT statement must traverse the index vertically then horizontally and it must access the relational table PARTSUPP. Execution of the third SELECT statement must traverse the leaf level of the index horizontally and it must not access the relational table PARTSUPP. Execution of the fourth SELECT statement must traverse the leaf level of the index horizontally and it must access the relational table PARTSUPP. Deliverables Submit your spooled file solution 1b.lst (or solution 1b.pdf) that…
In the Testa EV database described in the previous question, fill in the blanks in the following MySQL statement to list (in this order) all the part numbers of components of 'EV Dashboard' (with Part_No=1) and their quantities; sort the results in ascending order of quantity of components (i.e., components with small quantities are listed first). (Please ensure correctness in the spelling of all names and commands. Do not enter unnecessary symbols such as spaces or commas)
(b) Consider the schema of Customer table and write SQL statements for the following queries. cid sname address age occupation salary Payment mode gender 123 sam xacoas 31 manager 60000 cash 124 david flaca 38 administrator 45000 credit i. Write the SQL code to list details of clients in descending order of the salary. ii. Write the SQL code to show details of clients whosere salary is greater than Rs 45000 and age is between 35 and 45.
Write the appropriate SQL DDL statements to declare the relational libraryDatabase. Diagram of Figure 4.6. Select the keys and reference actions that are triggered.
(a) Write the sql syntax to create a table name Client with the following restrictions; Client(cid, cname, address, age, occupation, salary, payment_mode) Where cid is the primary key. cname do not accept null values. Payment_mode accepts only cash or credit entries. The default address is “vacoas". client is at least 25 years old. Salary is above Rs 30000. (b) Consider the schema of Customer table in part(a) and write SQL statements for the following queries. i. Using SQL, write the command to add a new column in the client table with the following data definition; (Column “gender" of char data type and field size of 1). ii. Using SQL, write the command to insert the values in the Client table. cid address age occupation salary Payment_mode gender cname 123 sam vacoas 31 manager 60000 cash m 124 david flacq 38 administrator 45000 credit m iii. Write the SQL query to display the cid of all employees. iv. Write the SQL code to list all administrators whose salary is greater than Rs…
PL/SQL stands for Procedural Language/SQL, and it is an extension of the SQL language. (a) Name and explain THREE important features that are offered by PL/SQL blocks. (b) PL/SQL supports two types of subprogrammes; Procedures; and functions. Define Procedures and describe their TWO advantages.
Some Built-in functions are added in SQL;2008. Describe four (4) of the functions.
Write SQL update statements to do the following on the database schema shown in Figure 1.i. Insert a new course, <‘Knowledge Engineering’, ‘CS4390’, 3, ‘CS’>.ii. Delete the record for the student whose name is ‘Smith’ and whose student number is 17.
In a PL/SQL package, why split the interface from the implementation?
1) a. Explain the concept of ACID properties in DBMS in your own words? b. How is the pattern matching done in the SQL? c. Identify and with practical examples give the different types of languages that are available in the DBMS?
Assume the e-commercial database shown in the Appendix. Write the following queries in (a) relational algebra and (b) SQL. State your assumptions if the semantics is not clear. 1) Find the names of all students who have taken courses in both the Computer Science and Mathematics departments. APPENDIX - You may detach this page from the exam: STUDENTS(student id, first_name, last _name, email, major_code, class_year) COURSES(course id, course_name, course_description, credits, department_code) ENROLLMENTS(student id, course id, semester, grade) MAJORS(major code, major_name, department_code) DEPARTMENTS(department code, department_name)