Use SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows: A) FUNCTION: Use the region table to create a function called ‘Return_region_name’ which accepts an input parameter ‘region_id’, and print the name of the region with that id. Then execute this function with region_id = 1
Q: Create a Procedure in PL/SQL takes employee_id, rate_salary, rate_commission. This function will…
A: Find the required procedure in PL/SQL given as below :
Q: 2) Create in PL/SQL a stored procedure called 'Del Details' to delete the record of orderedProduct…
A: 2) The SQL query is given below: CREATE PROCEDURE [dbo].[Del Details] -- Add the parameters for the…
Q: What considerations should be made when determining whether or not to establish an index for a…
A: Introduction: After the CREATE NONCLUSTERED INDEX clause, give the index a name. You don't have to…
Q: Write the T-SQL scripts for the following tasks: Write a query to select the employees whose…
A: Please refer to the following step for the complete solution of the problem above.
Q: SELECT MAX(datediff(minute,start_time,end_time)) as "Longest_ride",…
A: Your SQL code contains error related to function datediff.
Q: Write an anonymous PL/SQL block that will update the salary of all doctors in the Pediatrics area by…
A: The UPDATE command is used to update the existing record with the WHERE clause. The SET command used…
Q: In SQL, write the appropriate statements to insert default values for these three tables, each table…
A: Prescription table: INSERT INTO prescription (pre_no,pre_date,pre_amount)VALUES (10,12,1200); INSERT…
Q: In The SQL Oracle Application Using the UNION operator, write a query that displays the…
A: Given:- In The SQL Oracle Application Using the UNION operator, write a query that displays the…
Q: write sample sql quiries for these Write a query to display employee number, first name, job id…
A: Please refer below for your reference: Queries are: 1. SELECT employee number, first name, job id…
Q: 3. Modify p2ql.sql to display the name and salary for all employees whose salary is not in the range…
A: 4. SELECT ENAME,JOB,HIREDDATE FROM EMPLOYEE WHERE HIREDDATE BETWEEN #20/2/1981 AND #1/5/1981…
Q: Question 7 Writes a PL/SQL function that calculates the factorial of a number passed as a parameter.…
A: I give the PL/SQL code along with its output screenshot
Q: You have to only develop a SQL query Write a query to display the resort name, rate of all the…
A: Here have to determine about sal query for given scenario.
Q: SQL QUERIES Employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_D…
A: Write a query in SQL to display the HIRE_DATE of every employee in following format.Format:…
Q: True or FalseString comparisons in SQL are case sensitive.
A: Explanation: The data in DBMS is stored and retrieved by using a specific language called SQL. DBMS…
Q: Write PL/SQL or T-SQL procedures to accomplish the following tasks: A: obtain the name and credit…
A: Actually, SQL stands for Structured Query Language.
Q: Use SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows: A) FUNCTION:…
A: A) : create function Return_region_name { @region_id num, } returns varchar as begin (select name…
Q: Assume the following tables: Student (studid, email, name) Club (clubid, telephone, name,…
A: Assume the following tables: Student (studid, email, name) Club (clubid, telephone, name,…
Q: Create a procedure in PL/SQL takes Department Id and return record data type has maximum salary,…
A: Find the required procedure in PL/SQL given as below :
Q: in SQL, Create a Stored Function to identify the total number of staff assigned to each ward (the…
A: A stored function (also known as a consumer feature or user-described characteristic) is a fixed of…
Q: Write a SQL query to fetch the different projects available from the EmployeeSalary table. Write a…
A: Question 1. Write a SQL query to fetch the different projects available from the EmployeeSalary…
Q: Write sql query to create given table.
A: Given, Table: BONUS
Q: You write the SQL query below. Add a statement to your SQL query that will retrieve the first 3…
A: SELECT employee_id, SUBSTR(postal_code,1,3) AS new_postal_code FROM employee ORDER BY postal_code
Q: QL QUERIES Employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_D…
A: To write a query in SQL for the above criteria. 1.SQL Min() and Max() functions are used to find the…
Q: Write and SQL query using the MAX operator to show all data from the Order Items table in which the…
A: The Structured Query Language (SQL) query to display all the data from the Order items table in…
Q: Assignment : PL/SQL Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle…
A: Hey there, I am writing the required solution based on the above given question. Please do find the…
Q: Write a function and a call program in PL/SQL to sum the even integers between 1 and any number that…
A: given to write pl/sqlprocedure function for sum of 1 to n even numbers: Below is the procedure of…
Q: We have a single customer with the surname "Ashworth", write the SQL to return the Firstname,…
A: -- create a tableCREATE TABLE customers ( FirstName varchar(255),Surname varchar(255),Phonenumber…
Q: Use SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows: )…
A: Below is the required procedure:
Q: Appointments AppointmentID 1 P1 2 P2 PatientID AppointmentTime AppointmentDate TreatmentID DentistID…
A: As per out guidelines, we are supposed to answer only 1st 3 questions. Kindly repost the remaining…
Q: The given SQL creates a Song table and inserts three songs. Write three UPDATE statements to make…
A: Solution - (Note: all data used in this solution is dummy.) Song table at starting - Task 1:…
Q: What is true about table functions in standard SQL? (a) table functions can operate on only one…
A: The table-valued function is a function that is able to return tables. Hence, correct answer is…
Q: They need all the service requests with the category of Plumbing (category number 1) and Heating…
A: The given requirement is to be solved in the form of SQL command where the only service requests are…
Q: QUESTION 2. Write a PL/SQL PROCEDURE that can list the Top-N customers (id and name) and the amounts…
A: ANSWER: PL/SQL Procedure:
Q: Employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_D…
A: SELECT * FROM employees WHERE salary > ALL(SELECT avg(salary)FROM employees GROUP BY…
Q: 6. Which one of the following is not an aggregate function in SQL? a. COUNT b. ORDER c. SUM d. MAX
A: 5. Given two tables X and Y, Which of the following relational algebra is false? a. X JOIN Y is not…
Q: NFO 2303 Database Programing Assignment # : PL/SQL Procedure & Function Practice Note: PL/SQL…
A: What is SQL? SQL stands for Structured Query Language SQL lets you access and manipulate databases…
Q: How do I do this question in SQL command? Use the Oracle database employees table and CASE…
A: Given, Use the Oracle database employees table and CASE expression to decode the department id.…
Q: Answer these question by using these tables. Please. Create in PL/SQL a stored procedure called…
A: I have answered this question in step 2.
Q: 2.Write a program in PL/SQL to input a number and calculate its Factorial.
A: CODE: DECLARE fact_no number :=1; num number := &1;BEGIN while num > 0 loop…
Q: Write a script to create a PL/SQL procedure that modifies the next date of appointment of certain…
A: write a script to create a PL/SQL procedure that modifies the next date of appointment of ceratin…
Q: For below DEPARTMENT table write a SQL query so that repeated group names are shown with quotation…
A: Actually, SQL stands for Structured Query Language.
Q: In SQL, write the appropriate statements to insert default values for these two tables, each table…
A: SQL Query for the creation of table:
Q: uld like to create a function to sum up total order amount within the date range, but i don't know…
A: We need to update select [State], count (*) as [TotalOrderAmount] FROM [SQLBook].[dbo].[Orders]…
Q: Please help with the following: Using oracle sql live or sql developer what sql statements could be…
A: The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
Q: In SQL, Query embedded inside another query are called as Embedded query Nested query O Sub query…
A: Sub query
Q: Write SQL statements for given questions in order to get familiar to basic SQL data manipulation…
A: according to our policy we are allowed to solve only three questions.please post remaining questions…
Q: 31) Using functions display: a) sum of all salaries b) average of all salaries c) highest salary…
A: As per our company guidelines we are supposed to answer only one question. Kindly repost other…
Use SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows:
A) FUNCTION: Use the region table to create a function called ‘Return_region_name’ which accepts an input
parameter ‘region_id’, and print the name of the region with that id. Then execute this function with
region_id = 1
Step by step
Solved in 2 steps
- Use SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows: A) FUNCTION: Use the region table to create a function called ‘Return_region_name’ which accepts an input parameter ‘region_id’, and print the name of the region with that id. Then execute this function with region_id = 1. [LAB-7] (3) B) PROCEDURE: Use the cust_node table to create a procedure called ‘Get_node_id’ that takes the cust_id as an input parameter to show its corresponding node id to the banker. Then execute this procedure with cust_id=5. [LAB-7] (2) C) Trigger: Create a row-level trigger called ‘Compute_Amount_trigger’ on the cust_txn table to automatically update the amount of withdraw transaction made after inserting or updating amount on the cust_txn table. Additionally, this trigger must show this statement ‘TRIGGER MESSAGE: The amount of cust_id and txn_type is modified successfully'. Then test your trigger by updating the cust_txn table. For example, update the amount of the…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.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
- 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.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. B: Write a Query in SQL to display characters only from 2 to 5 of “Last_Name” from Employees table. C: Write a query in SQL to display the HIRE_DATE of every employee in following format.Format: 11-july-2019Consider the following table: Instructor (Instructor_Id, Instructor_Name, specialization, Address, #Department_Id) Write in PL/SQL a function named NumberInstructor_department that takes the id of a department as an input parameter and returns the number of instructors who are working in that department.
- Problem Your task is to write an SQL query that uses SQL's time constants to dynamically determine the timezone the database server is configured to use. Your query should return the timezone as a single string value with the timezone offset relative to the Coordinated Universal Time (UTC). The single output column should be named tz_offset, and the value should be in the form: UTC: where and use two digits in 24-hour format, padded with leading zeroes if necessary. Example: if the server used India's timezone the output should be: | tz_offset | | UTC+05:30 (1 row) query.sql> init.sql 1sql 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. B: Write a Query in SQL to display characters only from 2 to 5 of “Last_Name” from Employees table. C: Write a query in SQL to display the HIRE_DATE of every employee in following format.Format: 11-july-2019SQL 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)B: Write a Query in SQL to display characters only from 2 to 5 of “Last_Name” from Employees table.
- 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.Instructor (Instructor_Id, Instructor_Name, specialization, Address, #Department_Id)Write in PL/SQL a function named NumberInstructor_department that takes the id of a department as an input parameter and returns the number of instructors who are working in that department.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) C: Write a query in SQL to display the HIRE_DATE of every employee in following format.Format: 11-july-2019