Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Create a PL/SQL function to increment the salary of all employees of an organization by 15%, and for employees with department ID 101 give another 10% hike on the updated salary.
The function will be called as -> Select new_salary(10000, 101) from dual;
The first parameter is the current salary and the second parameter is the department ID.
Also, write the query to update the salary of all records of the salary column in the employee table.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by stepSolved in 3 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- 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_forwardIn the preceding PL/SQL block, determine the values and data types for each of the following cases. a. The value of v_customer in the nested block is: b. The value of v_name in the nested block is: c. The value of v_credit_rating in the nested block is: d. The value of v_customer in the main block is: e. The value of v_name in the main block is: f. The value of v_credit_rating in the main block is: de Academyarrow_forwardCreate a PL/SQL database programming block with DECLARE BEGIN END; In Declare section define a local scalar (simple) variables. lv_deptno NUMBER And assign initial values as 10 lv_deptno NUMBER := 50; In Declare section again, define a cursor and select last_name, salary and manager_id of employees the ones who are working in the department defined in a) In this example department will be lv_deptno:=50; Sample code: Cursor c_emp as SELECT last_name, salary, manager_id FROM employees WHERE department_id = lv_deptno; In the execution section (BEGIN and END) Create CURSOR FOR LOOP for cursor to process In this cursor loop, your code has to check salary and also manager_id numbers each time new records being fetched. And based on these conditions either employee gets raise or not get raise. If employee salary is less than 5000 and also if employees managers is either 101 or 124 then it means employee is due for raise otherwise employee is not due for raise Sample…arrow_forward
- Write and execute a SQL query to list all patient numbers and encounter ids which do not have any data (missing rows) in VisitSummary table. Order the result by patient number, encounter id. Write and execute a SQL query to list all patients whose information about race or gender has changed during their future visits. A future visit is defined as T1.encounter_id < T2.encounter_id. Your output should have patient number, old and new race values, old and new gender values, and the encounter ids. Write and execute a SQL query to list all the patient data for all encounters. Even if patient data is missing in other tables, you still need to list the patient_nbr and encounter_id in your output. Your output should list all the columns (without repeating them) starting with encounter_id, patient_nbr... and it should be ordered by patient_nbr and then encounter_id. Write and execute a SQL query to list all the patient data for all encounters. If the patient data is missing…arrow_forwardWrite a function and a call program in PL/SQL to sum the even integers between 1 and any number that is passed as parameter to the function. Use the editor to format your answerarrow_forwardHow can the CAST function be combined with other functions to enhance data type compatibility in SQL queries?arrow_forward
- Translate the following SQL query to a pandas statement in Python so that it can be executed on the pandas DataFrame. SELECT COUNT(*) FROM sqldata WHERE cases > 1000 AND date = "2020-10-10";arrow_forwardIn Oracle PL/SQL programming language, %TYPE keyword would give you some advantages when you are declaring local variables. Describe at least one but maximum two benefits of %TYPE usage.arrow_forwardUse SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows: ) PROCEDURE: Use the cust_node table to create a procedure called ‘Get_node_id’ that takes the cust_idas an input parameter to show its corresponding node id to the banker. Then execute this procedure withcust_id=5.arrow_forward
- Create a function to increment the salary of employees of an organization by 15%, and for employees with department ID 100 give another 10% hike on the updated salary. Also, write the query to update the salary of all records of the salary column in the employee table using the created function. In PL/SQLarrow_forwardRefer to the film and inventory tables of the Sakila database. The tables in this lab have the same columns and data types but fewer rows. Write a query that lists the titles of films with the fewest rows in the inventory table. This query requires a subquery that computes the minimum of counts by film_id: SELECT MIN(count_film_id) FROM ( SELECT COUNT(film_id) AS count_film_id FROM inventory GROUP BY film_id ) AS temp_table; This subquery is provided in the template.arrow_forwardplz fix the following so it runs on sql developper or sql live CREATE TABLE DONORS (donor_id INT NOT NULL,donor_name varchar(255) NOT NULL,donor_age int,PRIMARY KEY (donor_id ),);CREATE TABLE DONATIONS(pledge_id INT NOT NULL,pledge_date varchar(255) NOT NULL,amount_pledged int,Is_paid int,donor_id int,PRIMARY KEY (pledge_id ),FOREIGN KEY (donor_id) REFERENCES DONORS(donor_id));arrow_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