Write PL/SQL or T-SQL procedures to accomplish the following tasks: A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. B: obtain the order date, customer number and name for the order whose number currently is stored in I_ORDER_NUM. place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM and I_CUSTOMER_NAME , respectively. output the contents of I_ORDER_DATE, I_CUSTOMER_NUM , and I_CUSTOMER_NAME. C: add a row to the ORDERS table. D: change the date of the whole number is stored in I_ORDER_DATE to the date currently found in I_ORDER_DATE. E: delete the order whose number is stored in I_ORDER_NUM

Write PL/SQL or T-SQL procedures to accomplish the following tasks: A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. B: obtain the order date, customer number and name for the order whose number currently is stored in I_ORDER_NUM. place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM and I_CUSTOMER_NAME , respectively. output the contents of I_ORDER_DATE, I_CUSTOMER_NUM , and I_CUSTOMER_NAME. C: add a row to the ORDERS table. D: change the date of the whole number is stored in I_ORDER_DATE to the date currently found in I_ORDER_DATE. E: delete the order whose number is stored in I_ORDER_NUM

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter8: Sql Functions And Procedures

Section: Chapter Questions

Problem 9TD

See similar textbooks

Similar questions

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
T-SQL procedure A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. B: obtain the order date, customer number and name for the order whose number currently is stored in I_ORDER_NUM. place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM and I_CUSTOMER_NAME , respectively. output the contents of I_ORDER_DATE, I_CUSTOMER_NUM , and I_CUSTOMER_NAME. C: add a row to the ORDERS table. D: change the date of the whole number is stored in I_ORDER_DATE to the date currently found in I_ORDER_DATE. E: delete the order whose number is stored in I_ORDER_NUM.
Pl/sql Program to increase the salary of employee whose id is 400 based on the following conditions :If experience is more than 10 years, increase salary by 20%, if experience is greater than 5 years, increase salary by 10% otherwise 5%
Consider 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.
USE NORTHWIND DATABASE Write a SELECT statement using the CASE function. The statement returns two columns basedon how many orders the customer has in the database. The first column is the customer name andthe second column, Membership Type, is based on which case applies to the customer:If the customer has more than 25 orders they are a Diamond Customer.If the customer has more than 15 orders they are a Gold Customer.If the customer has more than 10 orders they are a Silver Customer.If the customer has more than 5 orders they are an Iron Customer.Otherwise the customer is a Valued Customer.
The following figure is part of a university database. LNAME and FNAME stands for last name and first name, respectively. Both CNUM and CNO stand for course number. CNAME stands for course name. Specify the following queries in both relational algebra expression and SQL state- ments: 1) List the names and majors of all students who live in the city of Fullerton. 2) List the course number (CNUM), course name (CNAME) and number of units (UNITS) of each course that the student "Mary Willson" has enrolled. 3) For the course number "CPSC332", list the names of the studens who enrolled in the course. 4) For the students who live in in the city of either Fullerton or Brea, count the number of students in each city. 5) List the names of all students who live in Fullerton and do not enroll in any courses. STUDENT SSN FNAME LNAME DBIRTH ADRRESS CITY MAJOR ENROLL SNO CNO GRADE COURSE CNUM CNAME ТЕXTBOOK UNITS DEPARTMENT
T-SQL procedure SQL SERVER ONLY A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT.
Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. 1. Write an anonymous block to retrieve the doctor’s ID and name which in charge of certain patient. Allow the user to enter the patient’s ID. 2. Write an anonymous PL/SQL block that will insert a new doctor into the DOCTOR table. Verify that insert has been done successfully by issuing a select * from doctor. 3. 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. 4. Write an anonymous PL/SQL block that will delete all patients for doctors that works in the Family Practice area. 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’.…
Schema: Employees (EmpID int, Name varchar(50), Age int, Salary float, DepartmentID int) Departments (DepartmentID int, DepartmentName varchar(50), ManagerID int) Write a query to find the names of all employees who work in the "Sales" department and earn a salary greater than $50,000 per year, sorted by their age in ascending order. In MS SQL please
Write a PL\SQL program to increase the salary of employee whose ID is 400 based on the following conditions: 17 experience is more than 10 years, increase salary by 20% If experience is greater than 5 years, increase salary by 10% Otherwise 5% en minutes remaining .
Database course: Write SQL queries to do the following A. List names of IT students. B. List titles of courses of type E. C. Display the results of student with ID 201006 D. List student names and the courses they took. E. List total credits that student with ID 201002.
use sql to answer the following question an erd also the pl/sql procedure has been providing as below shown Logon Procedure. Code: create or replace PROCEDURE member_ck_sp (p_user IN VARCHAR2, p_pass IN VARCHAR2, p_FullName OUT VARCHAR2) IS failour_msg varchar2(50) := 'INVALID USER NAME'; CURSOR MEMBER_CUR IS SELECT firstname, lastname, cookie, username, password FROM bb_shopper WHERE upper(username) = upper(p_user) AND password = p_pass; BEGIN p_FullName := failour_msg; FOR REC_CUR IN MEMBER_CUR LOOP p_FullName := rec_cur.firstname || ' ' || rec_cur.lastname; dbms_output.put_line('Welcome: ' || p_pass || 'Cookie: ' || rec_cur.cookie); END LOOP; if p_FullName = failour_msg then dbms_output.put_line(failour_msg); end if; END member_ck_sp; Task2 : Updating Order Status Create a procedure named STATUS_SHIP_SP that allows an employee in the Shipping. Department to update an order status to add shipping information. The BB_BASKETSTATUS table lists events for each order so that a…