HW2
.docx
keyboard_arrow_up
School
New Jersey Institute Of Technology *
*We aren’t endorsed by this school
Course
434
Subject
Computer Science
Date
May 4, 2024
Type
docx
Pages
9
Uploaded by JusticeUniverse25366 on coursehero.com
1-
Write a PL/SQL program using an implicit
cursor that displays each state name,
year, and value for each year in the following format:
CS434-002
HOMEWORK 2
General Instructions. Read this carefully.
This is more important than the individual questions.
There are NO GROUP HOMEWORKS IN THIS CLASS. YOU NEED TO WORK ALONE.
1) Do not copy code from any other person. You can ask questions and get answers, but NEVER copy code. Also do not copy and paste English text unless I specifically permit it. NEVER.
2) Save this file. Put your answer to every question into the space AFTER the box. Then submit the whole file.
3) SHOW EVERYTHING. Anything you don't show will be automatically assumed as not done.
Don't logically argue that "of course you must have done it." If we don't see it, then it is not done.
4) The whole homework is worth 50 points.
Points will appear in [ ] brackets.
If you miss the due date by up to one week, there will be a late penalty of 8 points subtracted.
If you miss the due date by MORE than a week you will get ZERO points.
This homework has four purposes:
-
Continue writing "simple" PL/SQL programs. -
Start writing programs with cursors.
-
Start writing programs with triggers
-
Start writing programs with Object Relational tables
The state name should be written only once. You can only use the DRINKINGWATER
table. If
you use any other table, 0 points.
Show the program at the first red arrow.
►DECLARE
v_last_state VARCHAR2(100) := NULL; BEGIN
DBMS_OUTPUT.PUT_LINE('STATE YEAR VALUE');
FOR r IN (SELECT State, Year, Value FROM drinkingwater ORDER BY State, Year)
LOOP
IF v_last_state IS NULL OR v_last_state != r.State THEN
END IF;
DBMS_OUTPUT.PUT_LINE(r.State); v_last_state := r.State; END IF;
DBMS_OUTPUT.PUT_LINE(' ' || TO_CHAR(r.Year) || ' ' || r.Value);
END LOOP;
END;
/
2-
This question has three steps:
STEP1:
Copy and paste the following table into an Excel table, name it COVID_DAILY.CSV
Import it into an ORACLE table.
COUNTRY
NEW_CASES
ACTIVE_CASES
USA
62773
8584883
India
21668
200401
Brazil
78297
1052579
Russia
9270
310556
UK
6753
729854
France
27166
3630068
Spain
6255
248557
Italy
25673
497350
Turkey
14046
147606
Germany
13655
135950
Colombia
4579
38510
Argentina
8204
155943
Mexico
6674
266074
Poland
21045
286511
Iran
8308
191275
South Africa
1474
27035
Ukraine
9084
187591
Indonesia
5144
141070
Peru
7114
44648
Czechia
14353
164268
Canada
3018
30672
Chile
5563
30856
Romania
5236
49823
STEP2:
Write a PL/SQL
statement to add a new column (SUMMATION) into the table COVID_DAILY. [3 point]
Show the PL/SQL statement at the first red arrow.
STEP2:
Write a PL/SQL program using an implicit
cursor that adds NEW_CASES and ACTIVE_CASES values of COVID_DAILY in a row and then saves them in a SUMMATION column. [7 points]
Show the program at the second red arrow.
►
ALTER TABLE COVID_DAILY ADD SUMMATION NUMBER
►BEGIN
FOR c_row IN (SELECT COUNTRY, NEW_CASES, ACTIVE_CASES FROM COVID_DAILY)
LOOP
UPDATE COVID_DAIL
WHERE COUNTRY = c_row.COUNTRY;
END LOOP;
END;
3-
Create a trigger DrinkWater
that will guarantee any time the table DRINKINGWATER2019’s STATEPOPULATION or DRINKINGPOPULATION status is updated, the trigger inserts a new row to the TRACKINGWATER table and writes the following message to the output window.
Test it with: The output should look like this:
Update New York population 4
STATE: New York
Old Population Value: 19453561
New Population Value: 19453565
Update Florida Drinking Population 3
STATE: Florida Old Drinking Population Value: 20405336
New Drinking Population Value:
20405339
Update Florida population 3
STATE: Florida
Old Population Value: 20405336
New Population Value: 20405339
Update Vermont population 1
STATE: Vermont
Old Population Value: 623989
New Population Value: 623990
TRACKINGWATER should look like:
STATE
OldPopulation
NewPopulation
OldDrinkingPop
NewDrinkingPop
New York
19453561
19453565
null
null
Florida
null
null
20405336
20405339
Florida
21477737
21477742
null
null
Vermont
623989
623990
null
null
Show the TRACKINGWATER table creation SQL statement at the first red arrow [1]
Show the trigger program at the second red arrow [4].
►
CREATE TABLE TRACKINGWATER (
STATE VARCHAR2(50),
OldPopulation NUMBER,
NewDrinkingPop NUMBER
);
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Related Questions
Assignment : PL/SQL Practice
Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL.
Write an anonymous PL/SQL block that will update the salary of all doctors in the Pediatrics area by 1000 (Note: Current salary + 1000). Verify that the salary has been updated by issuing a select * from doctor where area = ‘Pediatrics’. You may have to run the select statement twice to check the data before and after the update.
arrow_forward
SBN Title Author 12345678 The Hobbit J.R.R. Tolkien 45678912 DaVinci Code Dan Brown Your student ID DBS311 Your Name
use the following statement to Write a PL/SQL Function that accepts 4 parameters, 2 strings representing students names, and 2 integers representing the marks they received in DBS311. The function will determine which student had the higher mark and return the name of the student. If the marks were the same, then return the word "same" and return "error" if an error occurs or the calculation can not be determined.
arrow_forward
Assume that you have a product code that has the following format: XXXXNNN
Where X is a character and N is a digit. Notice that the length of the character part is not fixed, it may range from 2 to 4 characters, while the length of the second part may range from 2 to 3 digits but the first digit from the left is always 0 (zero).
Write a PL/SQL program to separate the code into its two parts as shown in the following example: if the input is ABC031, the output should be:
Product Name is: ABC
Serial Number is: 031
Hints: Use the following functions if needed:
instr( string1, string2 [, start_position [, nth_appearance ] ] )
substr( string, start_position, [ length ] )
length( string )
DBMS_OUTPUT.PUT_LINE( )
arrow_forward
Assume that you have a product code that has the following format: XXXXNNN
Where X is a character and N is a digit. Notice that the length of the character part is not fixed, it may range from 2 to 4 characters, while the length of the second part may range from 2 to 3 digits but the first digit from the left is always 0 (zero).
Write a PL/SQL program to separate the code into its two parts as shown in the following example: if the input is ABC031, the output should be:
Product Name is: ABC
Serial Number is: 031
arrow_forward
Database:
CUSTOMER (CustomerID, LastName, FIrstName, Phone, EmailAddress)
PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount, CustomerID)
PURCHASE_ITEM (InvoiceNumber, InvoiceLineNumber, Item Number, RetailPrice)
ITEM (ItemNumber, ItemDesciption, Cost, ArtistLastName, ArtistFirstName)
Write an SQL statement to show which cutomers bought which items, and include any items that have not been sold. Include CUSTOMER.LastName, CUSTOMER.FirstName, IvoiceNumber, InvoiceDate, ItemNumber, ItemDescription, ArtistLastName, and ArtistFirstName,. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in ascending order. Note that in Microsoft Access this require multiple queries.
From Database Concepts 9th Ed. (Kroenke)
arrow_forward
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.
arrow_forward
Databases are used to store different kinds of data such as names, currency, text, graphics, etc. Determining the appropriate data type for each attribute is important for database performance, storage size, and conducting accurate analysis on databases. As with other programming languages, SQL supports many data types. However, you will mostly use two categories while writing SQL queries: Characters, and numbers.
Consider the following standard SQL data types:
Characters
char
varchar
Numbers
integer
decimal
float
Different data types could be used for the same attribute. For example, both char and varchar could be used to store “names” in a database. Examine the attributes for the Characters and Numbers data types.
Using specific examples, compare data types for each category. For example, why you should choose char rather than varchar or opposite?
arrow_forward
INFO 2303 Database Programming
Assignment : PL/SQL Practice
Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL.
Write an anonymous PL/SQL block that will update the salary of all doctors in the Pediatrics area by 1000 (Note: Current salary + 1000). Verify that the salary has been updated by issuing a select * from doctor where area = ‘Pediatrics’. You may have to run the select statement twice to check the data before and after the update.
arrow_forward
SQL Database
Write PL/SQL or T-SQL procedures to accomplish the following tasks:
Obtain the first name and last name, separated by a space, of the guide whose number currently is stored in I_GUIDE_NUM. Place these values in the variables I_FIRST_NAME and I_LAST_NAME. Output the contents of I_GUIDE_NUM, I_FIRST_NAME, and I_ LAST_NAME.
Obtain the number of persons, customer number, and customer last name for every reservation whose ID currently is stored in I_RESERVATION_ID. Place these values in the variables I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME, respectively. Output the contents of I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME.
Add a row to the GUIDE table.
Change the last name of the guide whose number is stored in I_GUIDE_NUM to the value currently found in I_LAST_NAME.
Delete the guide whose number is stored in I_GUIDE_NUM
arrow_forward
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.
arrow_forward
For the remaining questions, use the following table of people:
people (id (pk), firstname, lastname, spouse_id (fk))
The table was created as:
CREATE TABLE people (id INT NOT NULL AUTO INCREMENT PRIMARY KEY,
firstname VARCHAR(30), lastname VARCHAR (30), spouse id INT,
CONSTRAINT spouse fk FOREIGN KEY (spouse id) REFERENCES people (id));
This is some sample data:
Participation Activity 9
+----
| id | firstname | lastname | spouse_id |
+
1 | Marty
2 | Jennifer
| McFly
| Parker
| McFly
| Tannen
| McFly
|
|
3 | Lorraine
4 | Biff
5 | George
NULL |
NULL |
5 |
NULL |
3 |
+----+--
arrow_forward
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
arrow_forward
Assignment : PL/SQL Practice
Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL.
Write an anonymous PL/SQL block that will insert a new doctor into the DOCTOR Verify that insert has been done successfully by issuing a select * from doctor.
arrow_forward
Computer Science
* IN SQL* Write an SQL statement that selects all customers with a city starting with any character, followed by "land".
arrow_forward
INFO 2303 Database Programming
Assignment : PL/SQL Practice
Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL.
Question:
Write an anonymous PL/SQL block that will delete all patients for doctors that works in the Family Practice Verify that patients have been deleted accordingly. Then do a select * from patients where doc_id = (select doc_id from doctor where area = ‘Family Practice’. You should get no rows.
Patietn table:
CREATE TABLE PATIENT(PT_ID NUMBER(3),PT_LNAME VARCHAR2(15),PT_FNAME VARCHAR2(15),PTDOB DATE,DOC_ID NUMBER(3),NEXTAPPTDATE DATE,LASTAPPTDATE DATE,CONSTRAINT PATIENT_PT_ID_PK PRIMARY KEY (PT_ID),CONSTRAINT PATIENT_DOC_ID_FK FOREIGN KEY(DOC_ID)REFERENCES DOCTOR);
INSERT INTO patient VALUES(168, 'James', 'Paul', TO_DATE('14-MAR-97'), 432, TO_DATE('01-JUL-03'), TO_DATE('01-JUN-03'));INSERT INTO patient VALUES(331, 'Anderson', 'Brian', TO_DATE('06-MAR-48'), 235,TO_DATE('01-JUL-03'), TO_DATE('01-JUN-03'));INSERT INTO patient…
arrow_forward
relation:
Book (BookID, Author, ISBN, Title)
Write a PL/SQL block of code that performs the following tasks:
Read a Book id provided by user at run time and store it in a variable.
Fetch the Author and title of the book having the entered id and store them inside two variables
Display the fetched book author and title.
arrow_forward
SQL QUERIES
Employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)Department(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)A: Write a query in SQL to display the minimum, maximum salary and DEPARTMENT_ID of all thosedepartments whose minimum salary is greater than 4000 in ascending order.
arrow_forward
SQL
A programmer wrote code with the underlying SQL to check if a user can access / log into a system.
SELECT *
FROM users
WHERE login = ‘ replaced by the input login ‘
AND password = ‘ replaced by the input password ‘
What is the query if login is ‘OR true -- and password is Hello? Write the query.
What does the query above return?
What is the name of this security issue?
arrow_forward
Write SQL queries for the following statements.
1) Write a stored procedure which takes an integer value as argument from user and displays its table up to 10 in given
For example: if user send 7 as an argument then, 7 x 1 = 7
7 x 2 = 14
……
7 x 10 = 70.
2) Write a stored procedure which takes two arguments from user, display table of first argument from 1 to value of second
arrow_forward
Student Name:
Student Number:
Write a PL/SQL Program to do the following
Your Program should request the user to enter the temperature. Then based on the
users input your program should display the following messages
a. Print "Hot" if the temperature is above 80 degrees,
b. Print "Nice Weather" if it's between 50 and 80 degrees,
c. Print "cold" if it is less than 50 degrees
Test 40, 55 and 85 as inputs;
Guideline:
Create a unique code
Add Comments to the program
●
Take clear screen shots of the program source code and the outputs Explain the
code block briefly
You must submit the code as one .sql file and paste all the screenshots in the Word
document and upload it to Moodle before deadline.
99+
DELL
arrow_forward
**In SQL**
Write a SELECT statement that returns these columns: InstructorDept The DepartmentName column from the Departments table for a related instructor LastName The LastName column from the Instructors table FirstName The FirstName column from the Instructors table CourseDescription The CourseDescription column from the Courses table CourseDept The DepartmentName column from the Departments table for a related instructor Return one row for each course that’s in a different department than the department of the instructor assigned to teach that course. (Hint: You will need to join the Departments table to both the Instructors table and the Courses table, which will require you to use table aliases to distinguish the two tables.)
arrow_forward
Blood Donor Management System The blood donation clinic is looking at creating a database to store the donors’ details. The clinic is hoping to start by adding donor names and assign them unique IDs. They then want to add their details and relevant information such as blood type, first name, last name and contact number.
Write an interactive program that allows a clinic administrator to store donors’ details on the SQLite 3 database with the following specifications:
2.1 The program must have a menu for the administrator to select one of the following:• Register a donor• Display the record of all registered donors• Update the existing record• Delete a record• Exit the program
2.2 The program should display feedback messages for any successfully executed task and use exception handlers for any errors produced in the code.
2.3 The program must have the following functions:• The donor_register function to add donors• The donor_update function to update the donor details• The donor_remove…
arrow_forward
Write SQL queries for the following statements.
1) Write SQL code which declares two variables for name and address. Store your name and address in respective variables. Display name and address in a single line using print statement.
2) Display table of 10 in given format using
10 x 1 = 10
10 x 2 = 20
.
.
.
10 x 10 = 100.
3) Write a code which declares a variable and stores age of youngest employee from “Employee” table. And display name of youngest employee 5 times his.
4) Assume that in “Employee” table, Emp_id are assigned from 1 to onward. ie first employee’s eid is 1, second employee’s eid is 2 and so on. Write a code that displays name of eldest employee without using aggregate function of “max”. You can use count function
arrow_forward
PL/SQL Question
I need to build pl/sql block that prompts a user for the number of visitors each day for the past 5 days and then displays the average number of visitors per day.
For example;
day 1: 19
day 2: 21
day 3: 23
...
The avg number of visitors is: ___
like this.
arrow_forward
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…
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education
Related Questions
- Assignment : PL/SQL Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Write an anonymous PL/SQL block that will update the salary of all doctors in the Pediatrics area by 1000 (Note: Current salary + 1000). Verify that the salary has been updated by issuing a select * from doctor where area = ‘Pediatrics’. You may have to run the select statement twice to check the data before and after the update.arrow_forwardSBN Title Author 12345678 The Hobbit J.R.R. Tolkien 45678912 DaVinci Code Dan Brown Your student ID DBS311 Your Name use the following statement to Write a PL/SQL Function that accepts 4 parameters, 2 strings representing students names, and 2 integers representing the marks they received in DBS311. The function will determine which student had the higher mark and return the name of the student. If the marks were the same, then return the word "same" and return "error" if an error occurs or the calculation can not be determined.arrow_forwardAssume that you have a product code that has the following format: XXXXNNN Where X is a character and N is a digit. Notice that the length of the character part is not fixed, it may range from 2 to 4 characters, while the length of the second part may range from 2 to 3 digits but the first digit from the left is always 0 (zero). Write a PL/SQL program to separate the code into its two parts as shown in the following example: if the input is ABC031, the output should be: Product Name is: ABC Serial Number is: 031 Hints: Use the following functions if needed: instr( string1, string2 [, start_position [, nth_appearance ] ] ) substr( string, start_position, [ length ] ) length( string ) DBMS_OUTPUT.PUT_LINE( )arrow_forward
- Assume that you have a product code that has the following format: XXXXNNN Where X is a character and N is a digit. Notice that the length of the character part is not fixed, it may range from 2 to 4 characters, while the length of the second part may range from 2 to 3 digits but the first digit from the left is always 0 (zero). Write a PL/SQL program to separate the code into its two parts as shown in the following example: if the input is ABC031, the output should be: Product Name is: ABC Serial Number is: 031arrow_forwardDatabase: CUSTOMER (CustomerID, LastName, FIrstName, Phone, EmailAddress) PURCHASE (InvoiceNumber, InvoiceDate, PreTaxAmount, CustomerID) PURCHASE_ITEM (InvoiceNumber, InvoiceLineNumber, Item Number, RetailPrice) ITEM (ItemNumber, ItemDesciption, Cost, ArtistLastName, ArtistFirstName) Write an SQL statement to show which cutomers bought which items, and include any items that have not been sold. Include CUSTOMER.LastName, CUSTOMER.FirstName, IvoiceNumber, InvoiceDate, ItemNumber, ItemDescription, ArtistLastName, and ArtistFirstName,. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in ascending order. Note that in Microsoft Access this require multiple queries. From Database Concepts 9th Ed. (Kroenke)arrow_forwardDevelop 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.arrow_forward
- Databases are used to store different kinds of data such as names, currency, text, graphics, etc. Determining the appropriate data type for each attribute is important for database performance, storage size, and conducting accurate analysis on databases. As with other programming languages, SQL supports many data types. However, you will mostly use two categories while writing SQL queries: Characters, and numbers. Consider the following standard SQL data types: Characters char varchar Numbers integer decimal float Different data types could be used for the same attribute. For example, both char and varchar could be used to store “names” in a database. Examine the attributes for the Characters and Numbers data types. Using specific examples, compare data types for each category. For example, why you should choose char rather than varchar or opposite?arrow_forwardINFO 2303 Database Programming Assignment : PL/SQL Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Write an anonymous PL/SQL block that will update the salary of all doctors in the Pediatrics area by 1000 (Note: Current salary + 1000). Verify that the salary has been updated by issuing a select * from doctor where area = ‘Pediatrics’. You may have to run the select statement twice to check the data before and after the update.arrow_forwardSQL Database Write PL/SQL or T-SQL procedures to accomplish the following tasks: Obtain the first name and last name, separated by a space, of the guide whose number currently is stored in I_GUIDE_NUM. Place these values in the variables I_FIRST_NAME and I_LAST_NAME. Output the contents of I_GUIDE_NUM, I_FIRST_NAME, and I_ LAST_NAME. Obtain the number of persons, customer number, and customer last name for every reservation whose ID currently is stored in I_RESERVATION_ID. Place these values in the variables I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME, respectively. Output the contents of I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME. Add a row to the GUIDE table. Change the last name of the guide whose number is stored in I_GUIDE_NUM to the value currently found in I_LAST_NAME. Delete the guide whose number is stored in I_GUIDE_NUMarrow_forward
- 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.arrow_forwardFor the remaining questions, use the following table of people: people (id (pk), firstname, lastname, spouse_id (fk)) The table was created as: CREATE TABLE people (id INT NOT NULL AUTO INCREMENT PRIMARY KEY, firstname VARCHAR(30), lastname VARCHAR (30), spouse id INT, CONSTRAINT spouse fk FOREIGN KEY (spouse id) REFERENCES people (id)); This is some sample data: Participation Activity 9 +---- | id | firstname | lastname | spouse_id | + 1 | Marty 2 | Jennifer | McFly | Parker | McFly | Tannen | McFly | | 3 | Lorraine 4 | Biff 5 | George NULL | NULL | 5 | NULL | 3 | +----+--arrow_forwardINFO 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 : REQUIREDarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education