A Guide to SQL
9th Edition
ISBN: 9781111527273
Author: Philip J. Pratt
Publisher: Course Technology Ptr
expand_more
expand_more
format_list_bulleted
Question
Chapter 8, Problem 8TD
Program Plan Intro
Stored procedures:
- A procedure is a collection of procedural and SQL statements.
- A procedure may have input parameter, output parameter and both parameters.
- It has a declared with a unique named with a unit of procedural code using the proprietary RDBMS and it is invoked by a host language library routine.
Syntax for stored procedure:
CREATE FUNCTION fun_name(argument IN data-type)RETRUN data-type[IS]
BEGIN
PL/SQL statements;
Return (value or expression);
END;
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
This question should be answered in PL/SQL only!
Create a procedure that takes instructor_ID as a parameter. Name it INSTR_SECTIONS. The procedure displays instructor first and last names, and also a list of sections that the instructor is scheduled to teach. Only one instructor per procedure call. You give/pass to a procedure instructor_ID as a parameter, the procedure prints INFO related only to that instructor.
By using this table answer this question in SQL :
Write a SQL query, which shows a list of all employees last name, their department names and their department city where that department is located from the employees table. If an employee does not belong to a department, then their department number and department name should show as “N/A” and their department city should show up as “No City info Found”. MAKE SURE you have all the employees displayed including the employees who do not have a department.
Write a stored procedure in MySQL, PL/SQL, or T-SQL that changes the price of an item with a given item ID. How would you use this stored procedure to change the price of item AD72 to $84.99?
Chapter 8 Solutions
A Guide to SQL
Ch. 8 - Prob. 1RQCh. 8 - Prob. 2RQCh. 8 - Prob. 3RQCh. 8 - Prob. 4RQCh. 8 - Prob. 5RQCh. 8 - Prob. 6RQCh. 8 - Prob. 7RQCh. 8 - Prob. 8RQCh. 8 - Prob. 9RQCh. 8 - In PL/SQL, how do you assign variables the same...
Ch. 8 - Prob. 11RQCh. 8 - Prob. 12RQCh. 8 - Prob. 13RQCh. 8 - Prob. 14RQCh. 8 - Prob. 15RQCh. 8 - Prob. 16RQCh. 8 - Prob. 17RQCh. 8 - Prob. 18RQCh. 8 - Prob. 19RQCh. 8 - Prob. 20RQCh. 8 - Prob. 21RQCh. 8 - Prob. 22RQCh. 8 - Prob. 1TDCh. 8 - Prob. 2TDCh. 8 - Prob. 3TDCh. 8 - Prob. 4TDCh. 8 - Prob. 5TDCh. 8 - Prob. 6TDCh. 8 - Prob. 7TDCh. 8 - Prob. 8TDCh. 8 - Prob. 9TDCh. 8 - Prob. 10TDCh. 8 - Prob. 1CATCh. 8 - Prob. 2CATCh. 8 - Prob. 3CATCh. 8 - Prob. 4CATCh. 8 - Prob. 5CATCh. 8 - Prob. 6CATCh. 8 - Prob. 7CATCh. 8 - Prob. 9CATCh. 8 - Prob. 1SCGCh. 8 - Prob. 2SCGCh. 8 - Prob. 3SCGCh. 8 - Prob. 4SCGCh. 8 - Prob. 5SCGCh. 8 - Prob. 6SCGCh. 8 - Prob. 7SCGCh. 8 - SQL includes many numerical functions. Two of...
Knowledge Booster
Similar questions
- In PL/SQL, how do you assign variables the same type as a column in the database?arrow_forwardSolmaris is offering a monthly discount for owners who pay their condo fees on a quarterly basis. The discount is 1.75 percent of the monthly fee. For each condo, list the location number, unit number, owner number, owner’s last name, condo fee, and discount. The discount should be rounded to the nearest dollar. Snip and paste the SQL command(s) and results below. SQL includes many numerical functions. Two of these functions are FLOOR and CEIL. Use the Internet to research these functions. Are the functions available in Oracle, SQL Server, and Access? Write a paragraph that discusses what the functions do and any differences and/or similarities between the functions in Oracle, SQL Server, and Access. Then perform the following tasks: Solmaris Condominium Group would like to know the impact of discounting its condo fees by 3 percent. Write an SQL statement in MySQL that displays the condo ID, unit number, discounted condo fee, discounted condo fee with the CEIL function, and…arrow_forwardInclude both SQL commands and SQL results in your answers: KimTay Pet Supplies is running a promotion that is valid for up to 20 days after an order is placed. List the invoice number, customer ID, customer first and last names, and the promotion date for each invoice. The promotion date is 20 days after the invoice was placed. Write PL/SQL (or MySQL) procedures to accomplish the following task: The procedure accepts the ID of a customer stored in the input variable named I_CUST_ID and displays the name (first name concatenated with last name) and credit limit of for this customer.arrow_forward
- Use the following table to find the OUTPUT of the following SQL queries: Table Name: Patients MEDICAL NO FIRST NAME SUR NAME DOB GENDER BLOOD TYPE PCR TEST PHONE FAX WEIGHT SMOKER 1001 Said Alalawi 12-Jun-2001 Male 98877445 22114454 68 NO Negative 1002 Othman Mualla 20-Apr-2000 Male 78 YES Positive 1003 Sameera Bader 19-Oct-2005 Female 25541122 52 NO Positive 1004 Fahad Alhinal 24-Feb-2004 Male 91122544 AB 66 YES Negative 1005 Shamsa Alfahdi 15-Sep-2002 Female 35 NO Negative SELECT COALESCE (phone, fax, 9999) FROM patients WHERE blood_type='A'; Answer:arrow_forwardWrite a PL/SQL using a cursor with Basic loop to update the Price attribute of all flight records inFlight table according to the following conditions: 1- If the FName is Oman Air, change price to 500.2-If the FName is Emirates, change price to 200.3- If the FName is Air India, change price to 250.arrow_forwardHow does the COALESCE function compare with the ISNULL function when handling NULL values in SQL?arrow_forward
- Write PL/SQL procedure to display the name of all employees in the department which has Department_ID = 99 then display the total number of employees in this department. Note: you must use CURSOR for this question. You can use the following DBMS output statement : dbms_output.put_line('employee name '||X ); which means display ‘employee name’ together with the value of the variable Xarrow_forwardUse the following table to find the OUTPUT of the following SQL queries: Table Name: Patients MEDICAL NO FIRST NAME SUR NAME BLOOD TYPE PCR TEST DOB GENDER PHONE FAX WEIGHT SMOKER 1001 Said Alalawi 12-Jun-2001 Male 98877445 22114454 A 68 NO Negative 1002 Othman Mualla 20-Apr-2000 Male 78 YES Positive 1003 Sameera Bader 19-Oct-2005 Female 25541122 52 NO Positive 1004 Fahad Alhinai 24-Feb-2004 Male 91122544 AB 66 YES Negative 1005 shamsa Alfahdi 15-Sep-2002 Female 35 NO Negative SELECT NVL2 (phone, fax, 0) FROM patients WHERE medical no = 1005; Answer:arrow_forwardHow do I do this question in SQL command? Use the Oracle database employees table and CASE expression to decode the departmentid. Display the department id, last name, salary, and a column called “New Salary” whose value is based on the following conditions: If the department id is 10 then 1.25 * salary If the department id is 90 then 1.5 * salaryIf the department id is 130 then 1.75 * salary Otherwise, display the old salary.arrow_forward
- Using DML SQL, write the appropriate SQL query for each of the following: Insert A new customer with the following information: (Name: Ali, SSN: 1111, Address: Riyadh, Phone: 05555) Delete all the loans for customer with SSN: 2222 Select all customer who got loans more than 250000 SAR, print the customers SSN, name Print all the Accounts in Bank: ALBILAD and in branch no: 5arrow_forwardUse the following table to find the OUTPUT of the following SQL queries: Table Name: Patients FIRST NAME SUR NAME BLOOD TYPE SMOKER MEDICAL NO DOB GENDER PHONE FAX WEIGHT PCR TEST 1001 Said Alalawi 12-Jun-2001 Male 98877445 22114454 A 68 NO Negative 1002 Othman Mualla 20-Apr-2000 Male B 78 YES Positive 1003 Sameera Bader 19-Oct-2005 Female 25541122 52 NO Positive 1004 Fahad Alhinai 24-Feb-2004 Male 91122544 AB 66 YES Negative 1005 Shamsa Alfahdi 15-Sep-2002 Female B 35 NO Negative SELECT TO CHAR (DOB, 'DD/YY/MM') FROM patients WHERE Medical no=1001; Answer:arrow_forwardKindly do all the parts of the given sql questionsarrow_forward
arrow_back_ios
arrow_forward_ios
Recommended textbooks for you
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr